Oracle hint details

HINT description of ORACLE hints is a mechanism provided by oracle to tell the optimizer to generate an execution plan as we tell it. We can use hints to achieve: 1) Type of optimizer used 2) optimization goal of the cost-based optimizer: all_rows or first_rows. 3) The access path of the table, including full table scan, index scan, or rowid. 4) connection types between tables 5) connection sequence between tables 6) statement parallelism Level 2. The HINT can generate the sequence of the table connection, the table connection method, the access path, and the degree of Parallelism Based on the following rules. 3. The HINT Application Scope dml statement query statement 4. Syntax {DELETE | INSERT | SELECT | UPDATE}/* + hint [text] [hint [text]... */or {DELETE | INSERT | SELECT | UPDATE} -- + hint [text] [hint [text]... if the syntax is incorrect, ORACLE automatically ignores the written HINT. /* + ALL_ROWS */indicates to select the overhead-Based Optimization Method for the statement block and obtain the optimal throughput to minimize resource consumption. example: SELECT/* + ALL_ROWS */EMP_NO, EMP_NAM, DAT_IN from bsempms where EMP_NO = 'scot T'; 2. /* + FIRST_ROWS */indicates to select the overhead-Based Optimization Method for the statement block and obtain the optimal response time to minimize resource consumption. example: SELECT/* + FIRST_ROWS */EMP_NO, EMP_NAM, DAT_IN from bsempms where EMP_NO = 'Scott '; 3. /* + CHOOSE */indicates that if the data dictionary contains statistics about the access table, the overhead-based optimization method is used to obtain the optimal throughput; it indicates that if no statistical information of the accessed table is in the data dictionary, the optimization method based on the Rule overhead will be used. For example: SELECT/* + CHOOSE */EMP_NO, EMP_NAM, DAT_IN from bsempms where EMP_NO = 'Scott '; 4. /* + RULE */indicates the RULE-based Optimization Method for the statement block. example: SELECT/* + RULE */EMP_NO, EMP_NAM, DAT_IN FROM BSE Mpms where EMP_NO = 'Scott '; 5. /* + FULL (TABLE) */indicates the method used to globally scan the TABLE. example: SELECT/* + FULL (A) */EMP_NO, EMP_NAM from bsempms a where EMP_NO = 'Scott '; 6. /* + ROWID (TABLE) */The prompt explicitly indicates that the specified TABLE is accessed according to the ROWID. for example: SELECT/* + ROWID (BSEMPMS) */* from bsempms where rowid> = 'aaaaaaaaaaaaa' AND EMP_NO = 'Scott '; 7. /* + CLUSTER (TABLE) */The prompt clearly indicates the access method for the selected CLUSTER scan for the specified TABLE, which is only valid for the CLUSTER object. example: SELECT/* + CLUSTER */BSEMPMS. EMP_NO, DPT_NO from bsempms, BSDPTMS WHERE DPT_NO = 'tec304 'and bsempms. DPT_NO = BSDPTMS. DPT_NO; 8. /* + INDEX (TABLE INDEX_NAME) */indicates the scan method for the selected INDEX on the TABLE. for example: SELECT/* + INDEX (BSEMPMS SEX_INDEX) USE SEX_INDEX because there are fewmale bsempms */from bsempms where sex = 'M'; 9. /* + INDEX_ASC (TABLE INDEX_NAME) */indicates the method used to scan the index in ascending order. example: SELECT/* + INDEX_ASC (BSEMPMS PK_BSEMPMS) */from bsempms where DPT_NO = 'Scott '; 10. /* + INDEX_COMBINE */select the bitmap access path for the specified table. If the INDEX _ COMBINE does not provide an index as a parameter. A boolean combination of the index is selected. example: SELECT/* + INDEX_COMBINE (BSEMPMS SAL_BMI HIREDATE_BMI) */* from bsempms where sal <5000000 and hiredate 11. /* + INDEX_JOIN (TABLE INDEX_NAME) */indicates that the command optimizer uses indexes as the access path. example: SELECT/* + INDEX_JOIN (BSEMPMS SAL_HMI HIREDATE_BMI) */SAL, hiredate from bsempms where sal <60000; 12. /* + INDEX_DESC (TABLE INDEX_NAME) */indicates the method used to scan the index in descending order. example: SELECT/* + INDEX_DESC (BSEMPMS PK_BSEMPM S) */from bsempms where DPT_NO = 'Scott '; 13. /* + INDEX_FFS (TABLE INDEX_NAME) */perform a quick full index scan on the specified TABLE, instead of a full TABLE scan. example: SELECT/* + INDEX_FFS (BSEMPMS IN_EMPNAM) */* from bsempms where DPT_NO = 'tec305'; 14. /* + ADD_EQUAL TABLE INDEX_NAM1, INDEX_NAM2 ,... */The system prompts you to clearly select the execution plan and combine the scanning of several single-column indexes. example: SELECT/* + INDEX_FFS (BSEMPMS IN_DPTNO, IN_EMPNO, IN_SEX) */* from bsempms where EMP_NO = 'Scott 'AND DPT_NO = 'tdc306'; 15. /* + USE_CONC AT */converts the OR condition following the WHERE clause in the query to the union all clause. example: SELECT/* + USE_CONCAT */* from bsempms where DPT_NO = 'tdc506 'and sex = 'M'; 16. /* + NO_EXPAND */for the or in-LIST query statement after the WHERE clause, NO_EXPAND will prevent it from being extended based on the optimizer. example: SELECT/* + NO_EXPAND */* from bsempms where DPT_NO = 'tdc506 'and sex = 'M'; 17. /* + NOWRITE */the query rewrite operation on the query block is prohibited. 18. /* + REWRITE */views can be used as parameters. 19. /* + MERGE (TABLE) */can MERGE various views. example: SELECT/* + MERGE (V) */. EM P_NO,. EMP_NAM, B. DPT_NO from bsempms a (SELET DPT_NO, AVG (SAL) AS AVG_SAL from bsempms B GROUP BY DPT_NO) V WHERE. DPT_NO = V. DPT_NO and. SAL> V. AVG_SAL; 20. /* + NO_MERGE (TABLE) */views that can be merged are not merged. example: SELECT/* + NO_MERGE (V) */. EMP_NO,. EMP_NAM, B. DPT_NO from bsempms a (SELECT DPT_NO, AVG (SAL) AS AVG_SAL from bsempms B GROUP BY DPT_NO) V WHERE. DPT_NO = V. DPT_NO and. SAL> V. AVG_SAL; 21. /* + ORDERED */Based on the table Now in the order FROM, ORDERED makes ORACLE connect to it in this order. example: SELECT/* + ORDERED */. COL1, B. COL2, C. COL3 FROM TABLE1 A, TABLE2 B, TABLE3 C WHERE. COL1 = B. COL1 and B. COL1 = C. COL1; 22. /* + USE_NL (TABLE) */connects the specified TABLE to the nested connected row source and uses the specified TABLE as the internal TABLE. example: SELECT/* + ORDERED USE_NL (BSEMPMS) */BSDPTMS. DPT_NO, BSEMPMS. EMP_NO, BSEMPMS. EMP_NAM from bsempms, bsdptms where bsempms. DPT_NO = BSDPTMS. DPT_NO; 23. /* + USE_MERGE (TABLE) */connects the specified TABLE with other row sources by means of the combined sorting connection. For example: SELECT/* + USE_MERGE (BSEMPMS, BSDPTMS) */* from bsempms, bsdptms where bsempms. DPT_NO = BSDPTMS. DPT_NO; 24. /* + USE_HASH (TABLE) */connects the specified TABLE with other row sources through the hash connection. for example: SELECT/* + USE_HASH (BSEMPMS, BSDPTMS) */* from bsempms, bsdptms where bsempms. DPT_NO = BSDPTMS. DPT_NO; 25. /* + DRIVING_SITE (TABLE) */forcibly execute a query for a TABLE at a location different from that selected by ORACLE. for example: SELECT/* + DRIVING_SITE (DEPT) */* from bsempms, DEPT @ bsdptms where bsempms. DPT_NO = DEPT. D PT_NO; 26. /* + LEADING (TABLE) */use the specified TABLE as the first TABLE in the connection order. 27. /* + CACHE (TABLE) */when a full TABLE scan is performed, the CACHE prompts that the retrieval block of the TABLE can be placed in the nearest least list of LRU recently used in the buffer CACHE. For example: SELECT/* + FULL (BSEMPMS) CAHE (BSEMPMS) */EMP_NAM from bsempms; 28. /* + NOCACHE (TABLE) */when a full TABLE scan is performed, the CACHE prompts that the retrieval block of the TABLE can be placed in the nearest least list of LRU in the buffer CACHE. For example: SELECT/* + FULL (BSEMPMS) NOCAHE (BSEMPMS) */EMP_NAM from bsempms; 29. /* + APPEND */Insert directly to the end of the table to increase the speed. insert/* + append */into test1 select * From test4; 30. /* + NOAPPEND */start regular insert by stopping the parallel mode during the lifetime of the insert statement. insert/* + noappend */into test1 select * from test4; 31. NO_INDEX: Specifies which indexes are not used/* + NO_INDEX (table [index [index]...]) */select/* + no_index (emp ind_emp_sal ind_emp_deptno) */* from emp where deptno = 200 and sal> 300; 32. parallel select/* + parallel (emp, 4) */* from emp where deptno = 200 and sal> 300; and: each SELECT/INSERT/UPDATE/DELETE command can have only one /* + */, But the prompt content can contain multiple, which can be separated by commas (,) or spaces. For example,/* + ordered index () use_nl () */--------- is similar to the following statement: insert into xxxx select/* + parallel (a) */* from xxx; the data volume is about 75 GB. The brother ran from the morning till the afternoon before it was finished. He came to ask me what happened and said that things that normally run with 2 hrs had been running for several hours. The system performance is also normal, and the cpu and I/O are not busy. The average READ speed is about 80 Mb/s, but the average write speed is less than 10 MB. Wait for a large number of ''px Deq Credit: send blkd 'In the event. here we can see that there is a problem with the parallel operation, and finally we know that there is a problem with the parallel usage, the operation is completed 20 minutes after the modification. The correct method should be: alter session enable dml parallel; insert/* + parallel (xxxx, 4) */into xxxx select/* + parallel () */* from xxx a; Because PDML is not enabled by oracle by default, DML statements must be manually enabled. In addition, parallelism is not an extensible feature. It is only necessary to make full use of resources when performing batch data operations in a data warehouse or a few tools such as DBA, in the OLTP environment, you need to be cautious when using parallelism. In fact, PDML still has many restrictions, such as the failure to support triggers, reference constraints, advanced replication, and distributed transactions. It also brings additional space usage, the same is true for PDDL. --------- Select count (*) From wid_serv_prod_mon_1100 a where. acct_month = 201010 and. partition_id = 10 and serv_state not in ('2hb ', '2hl', '2hj ', '2hp', '2hf ') and online_flag in (0) and incr_product_id in (2000020) and product_id in (2020966,202 0972, 2100297,202 1116) and billing_mode_id = 1 and exp_date> to_date ('2013', 'yyymmm ') and not exists (select/* + no_index (B IDX_W_CDR_MON_SERV_ID_1100) */1 from wid_cdr_mon_1100 B where B. acct_month = 201010 and B. ANA_EVENT_TYPE_4 in ('000000', '000000', '000000', '000000', '000000', '000000', '000000', '000000 ') and. serv_id = B. serv_id)