製品アップデート情報一覧
EXPLAIN <SELECT_STATMENT>explain select * from t1 left join t2 on t1.id = t2.id;
+---------------------------------------------------------------------------------+| query plan |+---------------------------------------------------------------------------------+| ============================================ || |ID|OPERATOR |NAME|EST.ROWS| || -------------------------------------------- || |0 |HASH JOIN | |12500.00| || |1 |├─TABLE FULL SCAN (B) |t2 |10000.00| || |2 |└─TABLE FULL SCAN (P) |t1 |10000.00| || -------------------------------------------- || Details: || ------------------------------------- || 0 - JOIN TYPE: left outer join, EQUAL: [eq(singleton.t1.id, singleton.t2.id)] || 1 - STORAGE: LIBRASTORE, BLOCK OFFSET: 1 || 2 - STORAGE: LIBRASTORE, BLOCK OFFSET: 1 |+---------------------------------------------------------------------------------+
Operator Name | Operator Introduction |
SORT | The SORT operator is used to sort the input data. |
TOPN | If the ORDER BY statement block is followed by a LIMIT statement, the optimizer will further optimize the execution plan, generate a TOP-N SORT operator, and use heapsort to select the TOP-N data. |
LIMIT | The LIMIT operator is used to limit the number of rows of data output, which has the same function as the LIMIT operator in MySQL. |
FILTER | The FILTER operator is used to perform filtering operations on data according to specified predicate conditions, usually appearing in WHERE/HAVING/ON clauses. |
HASH JOIN | Use HASH JOIN to perform JOIN operations on large datasets. The optimizer uses tables in two datasets. The smaller dataset is used to build a HASH table in memory according to the JOIN conditions, and then the database scans the larger dataset and probes the HASH table to find the rows that meet the join conditions. |
COLUMN READ | A late materialization operator. The read-only analysis engine supports pushing some filtering conditions down to the TableScan operator. Specifically, it scans the column data related to the filtering conditions first, filters the data to obtain the rows that meet the conditions, and then scans the remaining column data of these rows to continue with subsequent calculations, reducing the computing volume of I/O scans and data processing. |
TABLE FULL SCAN | Performs a full-table scan on the target table. |
UNION | The UNION operator is used to perform a union operation on the result sets of two queries. |
WINDOW FUNCTION | The WINDOW FUNCTION operator is used to implement the analysis functions (also known as window functions) in SQL, calculating the results of relevant rows within the window. Each group of window functions can return multiple rows, and each row within the group is the result of logic calculation based on the window. |
HASH GROUP BY | The GROUP BY operator is mainly used for group aggregation calculations in SQL. Ordinary aggregation functions (SUM/MAX/MIN/AVG/COUNT/STDDEV) are completed by allocating the GROUP BY operator. |
PROJECTION | A projection operator, corresponding to the SELECT list in SQL statements, has the capability of mapping each input data to new output data. |
EXCHANGE RECEIVER | A data receiving operator, used to receive data when various compute nodes exchange data during the MPP query. |
EXCHANGE SENDER | A data sending operator, used to send data when various compute nodes exchange data during the MPP query. |
Operator Name | Detail Info |
SORT | ORDER BY KEY: Represents the sort key used by the sort operator. |
TOPN | ORDER BY KEY: Represents the sort key used by the sort operator. OFFSET: The offset specified by the Limit operation. COUNT: The number of preserved rows specified by the Limit operation. |
LIMIT | OFFSET: The offset specified by the Limit operation. COUNT: The number of preserved rows specified by the Limit operation. |
FILTER | CONDITIONS: Represents the predicate condition adopted by the FILTER operator when filtering data. |
HASH JOIN | JOIN TYPE: The current JOIN type, such as inner join, left outer join, and semi join. NON EQUAL: Specifies whether the current JOIN is a Cartesian product. If this field does not exist, it indicates it is not a Cartesian product. EQUAL: Displays the equivalent condition adopted when two tables are joined. OTHER COND: Displays the non-equivalent condition when two tables are joined. In addition, it can be seen that in the EXPLAIN results above, the operator names with IDs 1 and 2 are followed by the marks (B) and (P) respectively, which mark the Build and Probe sides during the HASH JOIN operation, where B represents Build and P represents Probe. |
COLUMN READ | COLUMN READ: The column name read by the late materialization. |
TABLE FULL SCAN | STORAGE: The underlying storage type being read. Currently, two types are supported: LIBRASTORE and TDSQL. BLOCK OFFSET: The query block number of the current table in the entire SQL statement, used to assist with Hint. |
UNION | No. |
WINDOW FUNCTION | WINDOW FUCN DESCS: The window function name. PARTITION BY: The partition key. ORDER BY: The sorting key used for sorting. FRAME: The window definition of a window function. |
HASH GROUP BY | GROUP BY: The group by key specified when executing aggregate functions. AGG FUNCS: The aggregate functions specified in SQL. |
PROJECTION | EXPRS: The list of expressions executed by the PROJECTION operator, mainly including casts being performed and various scalarfuncs. |
EXCHANGE RECEIVER | No. |
EXCHANGE SENDER | ExchangeType: Methods for data exchange, including: PASS: Sends data from multiple nodes to one node. BCJ: Broadcasts data from one node to multiple nodes. For example, broadcast the data from the build table to nodes during JOIN to perform the JOIN operation. HASH: Distributes data to nodes after it is hashed with the HASH function. For example, scatter and redistribute data from left and right tables during the JOIN operation. HASH (BY PARTITION): When two tables are involved in a JOIN operation, if one table has a join key that is also its partition key, the other table is shuffled according to the distribution method of the table where the join key is the partition key. |
/*+ HASH_JOIN_PROBE(t1) */ indicates that the optimizer forces the use of t1 table as the probe table in the hash join.フィードバック