1)using hint
change access path
change join method
1@@@@change join method, "not in" anitjoin.
question:
What's the meaning of "HASH JOIN ANTI" on step 2?
What's the meaning of "Row" and "Byte" on each execution plan?
overview:
<1>remove all index on sales table.
<2>run sql without hint.
<3>run sql with hint "MERGE_AJ".
@@@
@@@<1>remove all index on sales table.
@@@
SQL> get dai
1 REM script DAI.SQL (drop all indexes)
2 REM prompts for a table name; % is appended
3 REM does not touch indexes associated with constraints
4 REM ==================================================
5 accept table_name -
6 prompt 'on which table : '
7 set termout off
8 store set sqlplus_settings replace
9 save buffer.sql replace
10 set heading off verify off autotrace off feedback off
11 spool doit.sql
12 SELECT 'drop index '||i.index_name||';'
13 FROM user_indexes i
14 WHERE i.table_name LIKE UPPER('&table_name.%')
15 AND NOT EXISTS
16 (SELECT 'x'
17 FROM user_constraints c
18 WHERE c.index_name = i.index_name
19 AND c.table_name = i.table_name
20 AND c.status = 'ENABLED');
21 spool off
22 @doit
23 get buffer.sql nolist
24 @sqlplus_settings
25* set termout on
26
SQL> @dai
on which table : sales
@@@
@@@<2>run sql without hint => HASH_AJ.
@@@
@@@not in => antijoin
SQL> set autot traceonly
SQL> get ws_06_01.sql
1 SELECT c.cust_last_name
2 FROM customers c
3 WHERE c.country_id = 52790
4 AND c.cust_id NOT IN (
5 select s.cust_id
6 from sales s)
7* /
SQL> @ws_06_01.sql
14424 rows selected.
Execution Plan
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13953 | 313K| 921 (4)| 00:00:12 | | |
|* 1 | HASH JOIN ANTI | | 13953 | 313K| 921 (4)| 00:00:12 | | |
|* 2 | TABLE ACCESS FULL | CUSTOMERS | 19019 | 334K| 408 (1)| 00:00:05 | | |
| 3 | PARTITION RANGE ALL| | 927K| 4527K| 501 (4)| 00:00:07 | 1 | 28 |
| 4 | TABLE ACCESS FULL | SALES | 927K| 4527K| 501 (4)| 00:00:07 | 1 | 28 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C"."CUST_ID"="S"."CUST_ID")
2 - filter("C"."COUNTRY_ID"=52790)
3177 consistent gets
@@@
@@@<3>run sql with hint "MERGE_AJ".
@@@
SQL> get ws_06_02.sql
1 SELECT c.cust_last_name
2 FROM customers c
3 WHERE c.country_id = '52790'
4 AND c.cust_id NOT IN (
5 SELECT /*+ &hint */ s.cust_id
6 FROM sales s)
7* /
SQL> @ws_06_02.sql
Enter value for hint: MERGE_AJ
14424 rows selected.
Execution Plan
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13953 | 313K| | 3927 (4)| 00:00:48 | | |
| 1 | MERGE JOIN ANTI | | 13953 | 313K| | 3927 (4)| 00:00:48 | | |
| 2 | SORT JOIN | | 19019 | 334K| 1064K| 521 (2)| 00:00:07 | | |
|* 3 | TABLE ACCESS FULL | CUSTOMERS | 19019 | 334K| | 408 (1)| 00:00:05 | | |
|* 4 | SORT UNIQUE | | 927K| 4527K| 21M| 3407 (4)| 00:00:41 | | |
| 5 | PARTITION RANGE ALL| | 927K| 4527K| | 501 (4)| 00:00:07 | 1 | 28 |
| 6 | TABLE ACCESS FULL | SALES | 927K| 4527K| | 501 (4)| 00:00:07 | 1 | 28 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("C"."COUNTRY_ID"=52790)
4 - access("C"."CUST_ID"="S"."CUST_ID")
filter("C"."CUST_ID"="S"."CUST_ID")
3177 consistent gets
2@@@@change join method, "exist" semijoin.
question:
How do you explain "HASH_SJ" "MEGRE_SJ" "NESTED LOOPS SEMI"?
What's the meaning of "HASH JOIN RIGHT SEMI" on step 5?
overview:
<1>delete all index on table customers
<2>run sql without hint, HASH_SJ semijoin.
<3>run sql with hint, MERGE_SJ semijoin
<4>create index, run sql with hint HASH_SJ.
<5>"HASH JOIN RIGHT SEMI"
@@@
@@@<1>delete all index on table customers
@@@
SQL> get dai
1 REM script DAI.SQL (drop all indexes)
2 REM prompts for a table name; % is appended
3 REM does not touch indexes associated with constraints
4 REM ==================================================
5 accept table_name -
6 prompt 'on which table : '
7 set termout off
8 store set sqlplus_settings replace
9 save buffer.sql replace
10 set heading off verify off autotrace off feedback off
11 spool doit.sql
12 SELECT 'drop index '||i.index_name||';'
13 FROM user_indexes i
14 WHERE i.table_name LIKE UPPER('&table_name.%')
15 AND NOT EXISTS
16 (SELECT 'x'
17 FROM user_constraints c
18 WHERE c.index_name = i.index_name
19 AND c.table_name = i.table_name
20 AND c.status = 'ENABLED');
21 spool off
22 @doit
23 get buffer.sql nolist
24 @sqlplus_settings
25* set termout on
26
SQL> @dai
on which table : customers
@@@
@@@<2>run sql without hint, HASH_SJ semijoin.
@@@
SQL> set autot traceonly
SQL> get ws_06_03.sql
1 SELECT country_name
2 FROM countries co
3 WHERE EXISTS (SELECT 'x'
4 FROM customers c
5 WHERE co.country_id = c.country_id
6* and c.cust_credit_limit < 2000)
SQL> @ws_06_03.sql
Execution Plan
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 456 | 412 (2)| 00:00:05 |
|* 1 | HASH JOIN SEMI | | 19 | 456 | 412 (2)| 00:00:05 |
| 2 | TABLE ACCESS FULL| COUNTRIES | 23 | 345 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| CUSTOMERS | 11003 | 99027 | 408 (1)| 00:00:05 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("CO"."COUNTRY_ID"="C"."COUNTRY_ID")
3 - filter("C"."CUST_CREDIT_LIMIT"<2000)
1686 consistent gets
@@@
@@@<3>run sql with hint, MERGE_SJ semijoin
@@@
SQL> get ws_06_04.sql
1 SELECT country_name
2 FROM countries co
3 WHERE EXISTS (SELECT /*+ &hint */ 'x'
4 FROM customers c
5 WHERE co.country_id = c.country_id
6* and c.cust_credit_limit < 2000)
SQL> @ws_06_04.sql
Enter value for hint: MERGE_SJ
Execution Plan
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 456 | | 455 (2)| 00:00:06 |
| 1 | MERGE JOIN SEMI | | 19 | 456 | | 455 (2)| 00:00:06 |
| 2 | TABLE ACCESS BY INDEX ROWID| COUNTRIES | 23 | 345 | | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | COUNTRIES_PK | 23 | | | 1 (0)| 00:00:01 |
|* 4 | SORT UNIQUE | | 11003 | 99027 | 440K| 453 (2)| 00:00:06 |
|* 5 | TABLE ACCESS FULL | CUSTOMERS | 11003 | 99027 | | 408 (1)| 00:00:05 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("CO"."COUNTRY_ID"="C"."COUNTRY_ID")
filter("CO"."COUNTRY_ID"="C"."COUNTRY_ID")
5 - filter("C"."CUST_CREDIT_LIMIT"<2000)
1465 consistent gets
@@@
@@@<4>create index, run sql with hint INDEX.
@@@
SQL> @ci
on which table : customers
on which column(s): country_id
creating index cust_country_id_idx ...
SQL> get ws_06_04.sql
1 SELECT country_name
2 FROM countries co
3 WHERE EXISTS (SELECT /*+ &hint */ 'x'
4 FROM customers c
5 WHERE co.country_id = c.country_id
6* and c.cust_credit_limit < 2000)
SQL> @ws_06_04.sql
Enter value for hint: index(c cust_country_id_idx)
Execution Plan
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 456 | 64 (0)| 00:00:01 |
| 1 | NESTED LOOPS SEMI | | 19 | 456 | 64 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | COUNTRIES | 23 | 345 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 9090 | 81810 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | CUST_COUNTRY_ID_IDX | 5 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("C"."CUST_CREDIT_LIMIT"<2000)
4 - access("CO"."COUNTRY_ID"="C"."COUNTRY_ID")
19 rows processed
@@@
@@@<5>semijoin in the sales table.
@@@
SQL> @dai
on which table : sales
SQL> get ws_06_05.sql
1 SELECT /*+ &hint */ COUNT(*)
2 FROM sales s
3 WHERE EXISTS (SELECT 'x'
4 FROM customers c
5 WHERE s.cust_id = c.cust_id)
6* /
SQL> @ws_06_05.sql
Enter value for hint: HASH_SJ
Execution Plan
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 547 (7)| 00:00:07 | | |
| 1 | SORT AGGREGATE | | 1 | 10 | | | | |
|* 2 | HASH JOIN RIGHT SEMI | | 927K| 9054K| 547 (7)| 00:00:07 | | |
| 3 | INDEX FAST FULL SCAN| CUSTOMERS_PK | 55500 | 270K| 34 (3)| 00:00:01 | | |
| 4 | PARTITION RANGE ALL | | 927K| 4527K| 501 (4)| 00:00:07 | 1 | 28 |
| 5 | TABLE ACCESS FULL | SALES | 927K| 4527K| 501 (4)| 00:00:07 | 1 | 28 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("S"."CUST_ID"="C"."CUST_ID")
3@@@@change the access path using hint index.
question:
How do you explain the "AND-EQUAL" on step 4?
overview:
<1>delete all index, create three new indexes.
<2>create index on three column, then check.
<3>use hint force to use one index.
<4>use hint force to use two index.
@@@
@@@<1>delete all index, create three new indexes.
@@@
SQL> get dai
1 REM script DAI.SQL (drop all indexes)
2 REM prompts for a table name; % is appended
3 REM does not touch indexes associated with constraints
4 REM ==================================================
5 accept table_name -
6 prompt 'on which table : '
7 set termout off
8 store set sqlplus_settings replace
9 save buffer.sql replace
10 set heading off verify off autotrace off feedback off
11 spool doit.sql
12 SELECT 'drop index '||i.index_name||';'
13 FROM user_indexes i
14 WHERE i.table_name LIKE UPPER('&table_name.%')
15 AND NOT EXISTS
16 (SELECT 'x'
17 FROM user_constraints c
18 WHERE c.index_name = i.index_name
19 AND c.table_name = i.table_name
20 AND c.status = 'ENABLED');
21 spool off
22 @doit
23 get buffer.sql nolist
24 @sqlplus_settings
25* set termout on
26
SQL> get ci
1 REM SQL Tuning Workshop
2 REM script CI.SQL (create index)
3 REM prompts for input; index name generated
4 REM =======================================
5 accept TABLE_NAME prompt " on which table : "
6 accept COLUMN_NAME prompt " on which column(s): "
7 set termout off
8 store set saved_settings replace
9 set heading off feedback off autotrace off
10 set verify off termout on
11 column dummy new_value index_name
12 SELECT 'creating index'
13 , SUBSTR( SUBSTR('&table_name',1,4)||'_' ||
14 TRANSLATE(REPLACE('&column_name', ' ', '')
15 , ',', '_')
16 , 1, 25
17 )||'_idx' dummy
18 , '...'
19 FROM dual;
20 CREATE INDEX &index_name
21 ON &table_name(&column_name)
22 NOLOGGING COMPUTE STATISTICS;
23 @saved_settings
24 set termout on
25 undef INDEX_NAME
26 undef TABLE_NAME
27* undef COLUMN_NAME
28
SQL> @dai
on which table : customers
@@@
@@@<2>create index on three column, then check.
@@@
SQL> @ci
on which table : customers
on which column(s): cust_gender
creating index cust_cust_gender_idx ...
SQL> @ci
on which table : customers
on which column(s): cust_postal_code
creating index cust_cust_postal_code_idx ...
SQL> @ci
on which table : customers
on which column(s): cust_credit_limit
creating index cust_cust_credit_limit_idx ...
SQL> @li
List indexes on table : customers
TABLE_NAME INDEX_TYPE INDEX_NAME
------------------------- -------------------- ------------------------------
CUSTOMERS UNIQUE CUSTOMERS_PK
NONUNIQUE CUST_CUST_CREDIT_LIMIT_IDX
CUST_CUST_GENDER_IDX
CUST_CUST_POSTAL_CODE_IDX
@@@
@@@<3>use hint force to use one index.
@@@
@@@use index => access(quick), do not use index => filter(slow waste)
SQL> get ws_06_06.sql
1 SELECT /*+ INDEX (c &indexname) */
2 c.*
3 FROM customers c
4 WHERE cust_gender = 'M'
5 AND cust_postal_code = 40804
6 AND cust_credit_limit = 10000
7* /
SQL> set autot traceonly
SQL> @ws_06_06.sql
Enter value for indexname: cust_cust_credit_limit_idx
Execution Plan
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 870 | 924 (1)| 00:00:12 |
|* 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 5 | 870 | 924 (1)| 00:00:12 |
|* 2 | INDEX RANGE SCAN | CUST_CUST_CREDIT_LIMIT_IDX | 5967 | | 12 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("CUST_POSTAL_CODE")=40804 AND "CUST_GENDER"='M')
2 - access("CUST_CREDIT_LIMIT"=10000)
@@@
@@@<4>use hint force to use two index.
@@@
SQL> get ws_06_07.sql
1 SELECT /*+ AND_EQUAL (c &index_name1, &index_name2) */
2 c.* -- ws06_07.sql
3 FROM customers c
4 WHERE cust_gender = 'M'
5 AND cust_postal_code = 40804
6 AND cust_credit_limit = 10000
7* /
SQL> @ws_06_07.sql
Enter value for index_name1: cust_cust_credit_limit_idx
Enter value for index_name2: cust_cust_gender_idx
Execution Plan
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 870 | 1359 (1)| 00:00:17 |
|* 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 5 | 870 | 1359 (1)| 00:00:17 |
| 2 | AND-EQUAL | | | | | |
|* 3 | INDEX RANGE SCAN | CUST_CUST_CREDIT_LIMIT_IDX | 5967 | | 12 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | CUST_CUST_GENDER_IDX | 27750 | | 52 (2)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CUST_CREDIT_LIMIT"=10000 AND TO_NUMBER("CUST_POSTAL_CODE")=40804 AND
"CUST_GENDER"='M')
3 - access("CUST_CREDIT_LIMIT"=10000)
4 - access("CUST_GENDER"='M')
转载于:https://blog.51cto.com/majesty/929853
2258
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
