My id does not have any privilege to create tables, so I can’t create the plan table needed for the sql execution plan. I just want to try to optimize a few SQL statements. Is this plan table really required? Or is there any way to skip it so I can finally try this sql optimizer? Thanks
The use of the Plan Table is an essential part of the SQL optimization process. The execution plan of the original SQL statement is compared to the execution plan of the SQL alternatives and then you are presented with only the SQL alternatives that produce unique execution plans.
A temporary or permanent execution table is created which is used to retrieve the execution plan for a SQL statement. The priority is to use temporary table whenever possible and only to create the permanent table if the use of temporary tables is not supported in the database.
You could have your DBA use the following script to create this plan table for you:
create table QUEST_SL_EXPLAIN
( STATEMENT_ID VARCHAR2(30),
PLAN_ID NUMBER,
TIMESTAMP DATE,
REMARKS VARCHAR2(80),
OPERATION VARCHAR2(30),
OPTIONS VARCHAR2(255),
OBJECT_NODE VARCHAR2(128),
OBJECT_OWNER VARCHAR2(30),
OBJECT_NAME VARCHAR2(30),
OBJECT_ALIAS VARCHAR2(65),
OBJECT_INSTANCE NUMBER,
OBJECT_TYPE VARCHAR2(30),
OPTIMIZER VARCHAR2(255),
SEARCH_COLUMNS NUMBER,
ID NUMBER,
PARENT_ID NUMBER,
DEPTH NUMBER,
POSITION NUMBER,
COST NUMBER,
CARDINALITY NUMBER,
BYTES NUMBER,
OTHER_TAG VARCHAR2(255),
PARTITION_START VARCHAR2(255),
PARTITION_STOP VARCHAR2(255),
PARTITION_ID NUMBER,
OTHER LONG,
DISTRIBUTION VARCHAR2(30),
CPU_COST NUMBER(38),
IO_COST NUMBER(38),
TEMP_SPACE NUMBER(38),
ACCESS_PREDICATES VARCHAR2(4000),
FILTER_PREDICATES VARCHAR2(4000) );