The DISPLAY function displays the execution plan generated for an SQL statement after the statement is explained by EXPLAIN.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
Syntax
TYPE DBMS_XPLAN_TYPE_TABLE IS TABLE OF VARCHAR2(4000);
DBMS_XPLAN.DISPLAY(table_name VARCHAR2 DEFAULT 'PLAN_TABLE',
statement_id VARCHAR2 DEFAULT null,
format VARCHAR2 DEFAULT 'TYPICAL',
filter_preds VARCHAR2 DEFAULT null)
RETURN DBMS_XPLAN_TYPE_TABLE;
Parameters
| Parameter | Description |
|---|---|
| table_name | The table that stores the execution plan. The default value is PLAN_TABLE. |
| statement_id | The ID of the statement explained by EXPLAIN. You can run the EXPLAIN SET STATEMENT_ID='XXX' command to set the statement ID. |
| format | The plan format. Valid values:
|
| filter_preds | The additional filter predicates to restrict the operator or plan selected from PLAN_TABLE. |
Examples
/* Create a table. */
CREATE TABLE plan_table (statement_id VARCHAR(30), plan_id INT, gmt_create TIMESTAMP,
remarks VARCHAR(4000), operator VARCHAR(255), options VARCHAR(255), object_node VARCHAR(40),
object_owner VARCHAR(128), object_name VARCHAR(128), object_alias VARCHAR(261), object_instance INT,
object_type VARCHAR(30),optimizer VARCHAR(4000), search_columns INT, id INT, parent_id INT, depth INT,
position INT,is_last_child INT, cost INT, cardinality INT, bytes INT, rowset INT, other_tag VARCHAR(4000),
partition_start VARCHAR(4000), partition_stop VARCHAR(4000), partition_id INT, other VARCHAR(4000),
distribution VARCHAR(64), cpu_cost INT, io_cost INT, temp_space INT, access_predicates VARCHAR(4000),
filter_predicates VARCHAR(4000),startup_predicates VARCHAR(4000), projection VARCHAR(4000),
special_predicates VARCHAR(4000), time INT, qblock_name VARCHAR(128), other_xml VARCHAR(4000));
/* Use EXPLAIN to explain an SQL statement. */
obclient> EXPLAIN SELECT * FROM t1;
+-------------------------------------------------------------------+
| Query Plan |
+-------------------------------------------------------------------+
| ========================================== |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| ------------------------------------------ |
| |0 |TABLE SCAN|T1 |1 |2 | |
| ========================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([T1.C1]), filter(nil), rowset=16 |
| access([T1.C1]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([T1.__pk_increment]), range(MIN ; MAX)always true |
+-------------------------------------------------------------------+
/* Use the DBMS_XPLAN package to display an executed query plan. In an Oracle tenant, it is used together with the table function feature. */
obclient> SELECT DBMS_XPLAN.DISPLAY() FROM DUAL;
+-------------------------------------------------------------------+
| COLUMN_VALUE |
+-------------------------------------------------------------------+
| ========================================== |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| ------------------------------------------ |
| |0 |TABLE SCAN|T1 |1 |2 | |
| ========================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([T1.C1]), filter(nil), rowset=16 |
| access([T1.C1]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([T1.__pk_increment]), range(MIN ; MAX)always true |
+-------------------------------------------------------------------+