Oracle SQL Hints --- Other Hints

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2013-01-08 05:23:46

Share to 

Other Hints RELATIONAL Usage: RELATIONAL([<@Block>] <Table>) Description: Instructs the optimizer to convert the Object to a relational table, similar to RELATIONAL function. HELLODBA.COM>desc xmltable  Name                                       Null?    Type  ----------------------------------------- --------- ---------  TABLE of PUBLIC.XMLTYPE   HELLODBA.COM>select * from xmltable;   SYS_NC_ROWINFO$ ------------------------------------------------------------------------------------------------------- <other_xml>   <outline_data>     <hint>       <IGNORE_OPTIM_EMBEDDED_HINTS/>     </hint>     ... ...   </outline_data> </other_xml>   1 row selected.   HELLODBA.COM>select /*+ relational(x) */* from xmltable x;   SYS_NC_OID$                      XMLDATA -------------------------------- ---------------------------------------------------------------------- 5477ABC43C2D4A85917F7328AA961884 <other_xml><outline_data><hint><IGNORE_OPTIM_EMBEDDED_HINTS></IGNORE_OPTIM_EMBED DED_HINTS></hint><hint><OPTIMIZER_FEATURES_ENABLE>10.2.0.3</OPTIMIZER_FEATURES_E NABLE></hint><hint><![CDATA[ALL_ROWS]]></hint><hint><OUTLINE_LEAF>"SEL$3BA1AD7C" … …   MONITOR Usage: MONITOR                      Description: Instruct Oracle monitor the running status of the statement, regardless of whether it fulfills the criteria (Parallel Query or Running for more than 5 seconds) or not. HELLODBA.COM>show parameter CONTROL_MANAGEMENT_PACK_ACCESS   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ control_management_pack_access       string      DIAGNOSTIC+TUNING HELLODBA.COM>select /*+ monitor */count(*) from t_users u;     COUNT(*) ----------         31   HELLODBA.COM>select sql_text, status from v$sql_monitor where sql_text like '%monitor%';   SQL_TEXT                                                     STATUS ------------------------------------------------------------ ------------------- select /*+ monitor */count(*) from t_users u                 DONE (ALL ROWS) NO_MONITOR Usage: NO_MONITOR                   Description: Prevent Oracle monitor the running status of the statement, regardless of whether it fulfills the criteria (Parallel Query or Running for more than 5 seconds) or not. HELLODBA.COM>select /*+ no_monitor parallel(o 2) full(o) */ /*identifier*/ count(*) from t_objects o;     COUNT(*) ----------      72116   HELLODBA.COM>select sql_text, status from v$sql_monitor where sql_text like '%identifier%';   no rows selected NESTED_TABLE_FAST_INSERT Usage: NESTED_TABLE_FAST_INSERT Description: Instructs SQL executor to insert data into nested table in fast mode. From the trace content of 10046 event, the data was inserted in batch. HELLODBA.COM>CREATE OR REPLACE TYPE simple_type AS TABLE OF VARCHAR2(30);   2  /   Type created.   HELLODBA.COM>CREATE TABLE t_nt_table (a NUMBER, b simple_type) NESTED TABLE b STORE AS t_nt_b;   Table created.   HELLODBA.COM>INSERT /*+ */ INTO t_nt_table select object_id, simple_type(object_name) from t_objects;   72116 rows created.   Elapsed: 00:00:18.77   HELLODBA.COM>INSERT /*+ NESTED_TABLE_FAST_INSERT */ INTO t_nt_table select object_id, simple_type(object_name) from t_objects;   72116 rows created.   Elapsed: 00:00:07.79 NESTED_TABLE_GET_REFS Usage: NESTED_TABLE_GET_REFS Description: With this hint, user can access to nested table directly. HELLODBA.COM>select /*+ */count(*) from T_NT_B; select /*+ */count(*) from T_NT_B                            * ERROR at line 1: ORA-22812: cannot reference nested table column's storage table   HELLODBA.COM>select /*+ nested_table_get_refs */count(*) from T_NT_B;     COUNT(*) ----------      72116 NESTED_TABLE_SET_SETID Usage: NESTED_TABLE_SET_SETID Description: With this hint, user can access to nested table directly. HELLODBA.COM>select /*+ NESTED_TABLE_SET_SETID */count(*) from T_NT_B;     COUNT(*) ----------      72116 NO_MONITORING Usage: NO_MONITORING                Description: Prevent Oracle monitor the column usage in predication, consequently, the dictionary table col_usage$ will not be updated by the execution of the statement. HELLODBA.COM>select like_preds from sys.SQLT$_DBA_COL_USAGE_V where owner ='DEMO' and table_name = 'T_TABLES' and column_name = 'TABLE_NAME';   LIKE_PREDS ----------         18   HELLODBA.COM>select /*run(7)*/count(*) from t_tables where 7=7 and table_name like 'T%';     COUNT(*) ----------         30   HELLODBA.COM>exec dbms_stats.gather_table_stats('DEMO', 'T_TABLES');   PL/SQL procedure successfully completed.   HELLODBA.COM>select like_preds from sys.SQLT$_DBA_COL_USAGE_V where owner ='DEMO' and table_name = 'T_TABLES' and column_name = 'TABLE_NAME';   LIKE_PREDS ----------         19   HELLODBA.COM>select /*+ no_monitoring *//*run(8)*/count(*) from t_tables where 8=8 and table_name like 'T%';     COUNT(*) ----------         30   HELLODBA.COM>exec dbms_stats.gather_table_stats('DEMO', 'T_TABLES');   PL/SQL procedure successfully completed.   HELLODBA.COM>select like_preds from sys.SQLT$_DBA_COL_USAGE_V where owner ='DEMO' and table_name = 'T_TABLES' and column_name = 'TABLE_NAME';   LIKE_PREDS ----------         19 NO_SQL_TUNE Usage: NO_SQL_TUNE                  Description: Prevent the optimizer to do SQL tuning on the statement. HELLODBA.COM>select /* No_TUNE(2) *//*+NO_SQL_TUNE*/count(*) from t_users;     COUNT(*) ----------         31   … … HELLODBA.COM>exec :exec_name := dbms_sqltune.execute_tuning_task (:task_name, 'EXEC_'||substr(:task_name, length(:task_name)-4));   PL/SQL procedure successfully completed.   HELLODBA.COM>select dbms_sqltune.report_tuning_task (:task_name) from dual; ... ... ADDITIONAL INFORMATION SECTION -------------------------------------------------------------------------- - 不支持的 SQL 语句类型。   RESTRICT_ALL_REF_CONS Usage: RESTRICT_ALL_REF_CONS        Description: Restricts all cascaded operations caused by referencing constraints in the transactions. HELLODBA.COM>select owner, table_name, constraint_name, r_owner, r_constraint_name, delete_rule from dba_constraints where constraint_name = 'T_C_FK';   OWNER          TABLE_NAME        CONSTRAINT_NAME      R_OWNER        R_CONSTRAINT_NAME    DELETE_RULE -------------- ----------------- -------------------- -------------- -------------------- --------- DEMO           T_C               T_C_FK               DEMO           T_P_PK               CASCADE   HELLODBA.COM>delete /*+RESTRICT_ALL_REF_CONS*/from t_p where a=3;   1 row deleted.   HELLODBA.COM>select count(a) from t_c where a=3;     COUNT(A) ----------          1   HELLODBA.COM>commit; commit * ERROR at line 1: ORA-02091: transaction rolled back ORA-02292: integrity constraint (DEMO.T_C_FK) violated - child record found USE_HASH_AGGREGATION