** Credit Web
SQL Optimization Tips oracle hint
Optimal adjustment of the database in SQL, it often will use HINT tips. Currently supported HINT ORACLE as follows:
In the SQL statement optimization process, we often use hint, is to sum up the optimization process in the SQL usage in the common Oracle HINT:
1. / * + ALL_ROWS * /
Show that the choice of the block cost-based optimization method, and the best throughput, minimize consumption of resources.
For example:
SELECT / * + ALL + _ROWS * / EMP_NO, EMP_NAM, DAT_IN FROM BSEMPMS WHERE EMP_NO = 'SCOTT';
2. / * + FIRST_ROWS * /
Show that the choice of the block cost-based optimization method, and the best response time to minimize resource consumption.
For example:
SELECT / * + FIRST_ROWS * / EMP_NO, EMP_NAM, DAT_IN FROM BSEMPMS WHERE EMP_NO = 'SCOTT';
3. / * + CHOOSE * /
That if the data dictionary tables have access to statistical information, cost-based optimization method, and get the best throughput;
That if the data dictionary table does not have access statistical information, will be spending rule-based optimization method;
For example:
SELECT / * + CHOOSE * / EMP_NO, EMP_NAM, DAT_IN FROM BSEMPMS WHERE EMP_NO = 'SCOTT';
4. / * + RULE * /
Show that the choice of the block rule-based optimization method.
For example:
SELECT / * + RULE * / EMP_NO, EMP_NAM, DAT_IN FROM BSEMPMS WHERE EMP_NO = 'SCOTT';
5. / * + FULL (TABLE) * /
Select the table that the global scanning method.
For example:
SELECT / * + FULL (A) * / EMP_NO, EMP_NAM FROM BSEMPMS A WHERE EMP_NO = 'SCOTT';
6. / * + ROWID (TABLE) * /
Suggest a clear demonstration of the specified table is based on ROWID visit.
For example:
SELECT / * + ROWID (BSEMPMS) * / * FROM BSEMPMS WHERE ROWID> = 'AAAAAAAAAAAAAA'
AND EMP_NO = 'SCOTT';
7. / * + CLUSTER (TABLE) * /
Suggest a clear demonstration of the specified table scan access method chosen cluster, only on the cluster object effectively.
For 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) * /
Select the table that the index scan.
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) * /
Ascending index on the table that the scanning method of choice.
For example:
SELECT / * + INDEX_ASC (BSEMPMS PK_BSEMPMS) * / FROM BSEMPMS WHERE DPT_NO = 'SCOTT';
10. / * + INDEX_COMBINE * /
Select the bitmap for the specified table access pass, if INDEX_COMBINE does not provide the index as a parameter, will select a Boolean combination of bitmap indexing method.
For example:
SELECT / * + INDEX_COMBINE (BSEMPMS SAL_BMI HIREDATE_BMI) * / * FROM BSEMPMS
WHERE SAL <5000000 AND HIREDATE <SYSDATE;
11. / * + INDEX_JOIN (TABLE INDEX_NAME) * /
Suggest a clear command optimizer use an index as the access path.
For example:
SELECT / * + INDEX_JOIN (BSEMPMS SAL_HMI HIREDATE_BMI) * / SAL, HIREDATE
FROM BSEMPMS WHERE SAL <60000;
12. / * + INDEX_DESC (TABLE INDEX_NAME) * /
Select the table that the index in descending order of the scan.
For example:
SELECT / * + INDEX_DESC (BSEMPMS PK_BSEMPMS) * / FROM BSEMPMS WHERE DPT_NO = 'SCOTT';
13. / * + INDEX_FFS (TABLE INDEX_NAME) * /
The implementation of the specified table index fast full scan, rather than a full table scan approach.
For example:
SELECT / * + INDEX_FFS (BSEMPMS IN_EMPNAM) * / * FROM BSEMPMS WHERE DPT_NO = 'TEC305';
14. / * + ADD_EQUAL TABLE INDEX_NAM1, INDEX_NAM2 ,...*/
Suggest a clear plan for the implementation of the choice of several separate index scan together.
For example:
SELECT / * + INDEX_FFS (BSEMPMS IN_DPTNO, IN_EMPNO, IN_SEX) * / * FROM BSEMPMS WHERE EMP_NO = 'SCOTT' AND DPT_NO = 'TDC306';
15. / * + USE_CONCAT * /
On the query behind the OR WHERE conditions into a combination of UNION ALL query.
For example:
SELECT / * + USE_CONCAT * / * FROM BSEMPMS WHERE DPT_NO = 'TDC506' AND SEX = 'M';
16. / * + NO_EXPAND * /
For behind the OR WHERE IN-LIST or query statement, NO_EXPAND to prevent the optimizer be extended based.
For example:
SELECT / * + NO_EXPAND * / * FROM BSEMPMS WHERE DPT_NO = 'TDC506' AND SEX = 'M';
17. / * + NOWRITE * /
Against the query rewriting query block operation.
18. / * + REWRITE * /
You can view as a parameter.
19. / * + MERGE (TABLE) * /
Be able to view the merger of the various inquiries accordingly.
For example:
SELECT / * + MERGE (V) * / A. EMP_NO, A. 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 A. DPT_NO = V. DPT_NO
AND A. SAL> V. AVG_SAL;
20. / * + NO_MERGE (TABLE) * /
For no longer can merge the view of the merger.
For example:
SELECT / * + NO_MERGE (V) * / A. EMP_NO, A. 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 A. DPT_NO = V. DPT_NO AND A. SAL> V. AVG_SAL;
21. / * + ORDERED * /
According to the table in FROM the order, ORDERED this order, its connection to ORACLE.
For example:
SELECT / * + ORDERED * / A.COL1, B.COL2, C.COL3 FROM TABLE1 A, TABLE2 B, TABLE3 C WHERE A.COL1 = B.COL1 AND B.COL1 = C.COL1;
22. / * + USE_NL (TABLE) * /
Nested table with the specified connection to connect the line source, and to specify the table as an internal table.
For 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) * /
The specified source table with other rows merge sort through the link connection.
For example:
SELECT / * + USE_MERGE (BSEMPMS, BSDPTMS) * / * FROM BSEMPMS, BSDPTMS WHERE BSEMPMS.DPT_NO = BSDPTMS.DPT_NO;
24. / * + USE_HASH (TABLE) * /
The specified source table with the other line connection through the hash link.
For example:
SELECT / * + USE_HASH (BSEMPMS, BSDPTMS) * / * FROM BSEMPMS, BSDPTMS WHERE BSEMPMS.DPT_NO = BSDPTMS.DPT_NO;
25. / * + DRIVING_SITE (TABLE) * /
Force and the location of choice ORACLE table different query execution.
For example:
SELECT / * + DRIVING_SITE (DEPT) * / * FROM BSEMPMS, DEPT @ BSDPTMS WHERE BSEMPMS.DPT_NO = DEPT.DPT_NO;
26. / * + LEADING (TABLE) * /
Connect the specified table as the first order in the table.
27. / * + CACHE (TABLE) * /
When the full-table scan, CACHE prompted able to retrieve the table block placed in the buffer cache LRU list in least recently used end of the most recent example:
SELECT / * + FULL (BSEMPMS) CAHE (BSEMPMS) * / EMP_NAM FROM BSEMPMS;
28. / * + NOCACHE (TABLE) * /
When the full-table scan, CACHE prompted able to retrieve the table block placed in the buffer cache LRU list in least recently used end of the most recent example:
SELECT / * + FULL (BSEMPMS) NOCAHE (BSEMPMS) * / EMP_NAM FROM BSEMPMS;
29. / * + APPEND * /
Directly inserted into the table's end, faster.
insert / * + append * / into test1 select * from test4;
30. / * + NOAPPEND * /
Insert the statement by the parallel mode of survival during the stop to start the regular insert.
insert / * + noappend * / into test1 select * from test4;
31. / * + LEADING (TABLE) * /
With ORADERED a bit like, but is more applicable than the ORDERED.
In the data analysis with the data warehouse system in general are 10 million above the table, so for such a large table related query, we will find away HASH JOIN would be the best choice, of course, ORACLE 9I slowly from the beginning to HASH JION closer
0 comments:
Post a Comment