- 论坛徽章:
- 0
|
本帖最后由 Maxshine 于 2010-09-25 23:13 编辑
回复 11# duolanshizhe
这是两种情况下的explain plan. 执行的sql是- SELECT distinct T1.PARTNUMBER, T1.FIELD5, T1.URL, T1.CATENTRY_ID, T1.BASEITEM_ID, T1.FIELD4, T1.OID, T1.ONAUCTION, T1.MFNAME, T1.ONSPECIAL, T1.BUYABLE, T1.FIELD2, T1.MFPARTNUMBER, T1.MEMBER_ID, T1.FIELD1, T1.STATE, T1.FIELD3, T1.ITEMSPC_ID, T1.LASTUPDATE, T1.MARKFORDELETE, T1.STARTDATE, T1.ENDDATE, T1.AVAILABILITYDATE, T1.LASTORDERDATE, T1.ENDOFSERVICEDATE, T1.DISCONTINUEDATE, T1.OPTCOUNTER, T1.CATENTTYPE_ID, CATENTREL.SEQUENCE FROM CATENTREL, CATENTRY T1 WHERE T1.CATENTTYPE_ID = 'ItemBean' AND T1.MARKFORDELETE <> 1 AND CATENTREL.CATENTRY_ID_PARENT = 100000 AND CATENTREL.CATENTRY_ID_CHILD = T1.CATENTRY_ID AND CATENTREL.CATRELTYPE_ID = 'PRODUCT_ITEM' AND ( ( NOT EXISTS (SELECT 1 FROM PRODUCTSET, PRSETCEREL WHERE PRSETCEREL.CATENTRY_ID =T1.CATENTRY_ID AND PRODUCTSET.MARKFORDELETE = 0 AND PRODUCTSET.PRODUCTSET_ID = PRSETCEREL.PRODUCTSET_ID AND PRODUCTSET.PRODUCTSET_ID in ( 100008,100009,10000028,10000029,10000128,10000129,10000228,10000229,10000328,10000329,10000428,10000429,10000528,10000529,10000628,1000062910000728,10000729,1000001033,1000001034,1000001133,1000001134,1000001233,1000001234,1000001333,1000001334,1000001433,10000014341000001533,1000001534,1000001633,1000001634,1000001733,1000001734,1000001833,1000001834,1000001933,1000001934,1000002033,1000002034,1000002133 ) ) ) ) ORDER BY CATENTREL.SEQUENCE, T1.PARTNUMBER
复制代码 DBMS_STAT分析后得到的explain plan:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 1027266203
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 148 | 14 (15)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 148 | 14 (15)| 00:00:01 |
| 2 | HASH UNIQUE | | 1 | 148 | 13 (8)| 00:00:01 |
| 3 | NESTED LOOPS | | | | | |
| 4 | NESTED LOOPS | | 1 | 148 | 7 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID | CATENTREL | 1 | 36 | 4 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
|* 6 | INDEX RANGE SCAN | SYS_C0051795 | 1 | | 3 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | I263103 | 1 | | 2 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | | | | |
| 9 | NESTED LOOPS | | 1 | 23 | 5 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID| PRSETCEREL | 1 | 14 | 4 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | I0000702 | 3 | | 3 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | SYS_C0053804 | 1 | | 0 (0)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID | PRODUCTSET | 1 | 9 | 1 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID | CATENTRY | 1 | 112 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("CATENTREL"."CATRELTYPE_ID"='PRODUCT_ITEM' AND
"CATENTREL"."CATENTRY_ID_PARENT"=100000)
7 - access("CATENTREL"."CATENTRY_ID_CHILD"="SYS_ALIAS_1"."CATENTRY_ID" AND
"SYS_ALIAS_1"."CATENTTYPE_ID"='ItemBean')
filter( NOT EXISTS (SELECT 0 FROM "PRSETCEREL" "PRSETCEREL","PRODUCTSET"
"PRODUCTSET" WHERE "PRODUCTSET"."PRODUCTSET_ID"="PRSETCEREL"."PRODUCTSET_ID" AND
"PRODUCTSET"."MARKFORDELETE"=0 AND ("PRODUCTSET"."PRODUCTSET_ID"=100008 OR
"PRODUCTSET"."PRODUCTSET_ID"=100009 OR "PRODUCTSET"."PRODUCTSET_ID"=10000028 OR
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
"PRODUCTSET"."PRODUCTSET_ID"=10000029 OR "PRODUCTSET"."PRODUCTSET_ID"=10000128 OR
"PRODUCTSET"."PRODUCTSET_ID"=10000129 OR "PRODUCTSET"."PRODUCTSET_ID"=10000228 OR
"PRODUCTSET"."PRODUCTSET_ID"=10000229 OR "PRODUCTSET"."PRODUCTSET_ID"=10000328 OR
"PRODUCTSET"."PRODUCTSET_ID"=10000329 OR "PRODUCTSET"."PRODUCTSET_ID"=10000428 OR
"PRODUCTSET"."PRODUCTSET_ID"=10000429 OR "PRODUCTSET"."PRODUCTSET_ID"=10000528 OR
"PRODUCTSET"."PRODUCTSET_ID"=10000529 OR "PRODUCTSET"."PRODUCTSET_ID"=10000628 OR
"PRODUCTSET"."PRODUCTSET_ID"=10000729 OR "PRODUCTSET"."PRODUCTSET_ID"=1000001033 OR
"PRODUCTSET"."PRODUCTSET_ID"=1000001034 OR "PRODUCTSET"."PRODUCTSET_ID"=1000001133 OR
"PRODUCTSET"."PRODUCTSET_ID"=1000001134 OR "PRODUCTSET"."PRODUCTSET_ID"=1000001233 OR
"PRODUCTSET"."PRODUCTSET_ID"=1000001234 OR "PRODUCTSET"."PRODUCTSET_ID"=1000001333 OR
"PRODUCTSET"."PRODUCTSET_ID"=1000001334 OR "PRODUCTSET"."PRODUCTSET_ID"=1000001433 OR
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
"PRODUCTSET"."PRODUCTSET_ID"=1000001534 OR "PRODUCTSET"."PRODUCTSET_ID"=1000001633 OR
"PRODUCTSET"."PRODUCTSET_ID"=1000001634 OR "PRODUCTSET"."PRODUCTSET_ID"=1000001733 OR
"PRODUCTSET"."PRODUCTSET_ID"=1000001734 OR "PRODUCTSET"."PRODUCTSET_ID"=1000001833 OR
"PRODUCTSET"."PRODUCTSET_ID"=1000001834 OR "PRODUCTSET"."PRODUCTSET_ID"=1000001933 OR
"PRODUCTSET"."PRODUCTSET_ID"=1000001934 OR "PRODUCTSET"."PRODUCTSET_ID"=1000002033 OR
"PRODUCTSET"."PRODUCTSET_ID"=1000002034 OR "PRODUCTSET"."PRODUCTSET_ID"=1000002133 OR
"PRODUCTSET"."PRODUCTSET_ID"=1000062910000728 OR
"PRODUCTSET"."PRODUCTSET_ID"=10000014341000001533) AND "PRSETCEREL"."CATENTRY_ID"=:B1 AND
("PRSETCEREL"."PRODUCTSET_ID"=100008 OR "PRSETCEREL"."PRODUCTSET_ID"=100009 OR
"PRSETCEREL"."PRODUCTSET_ID"=10000028 OR "PRSETCEREL"."PRODUCTSET_ID"=10000029 OR
"PRSETCEREL"."PRODUCTSET_ID"=10000128 OR "PRSETCEREL"."PRODUCTSET_ID"=10000129 OR
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
"PRSETCEREL"."PRODUCTSET_ID"=10000228 OR "PRSETCEREL"."PRODUCTSET_ID"=10000229 OR
"PRSETCEREL"."PRODUCTSET_ID"=10000328 OR "PRSETCEREL"."PRODUCTSET_ID"=10000329 OR
"PRSETCEREL"."PRODUCTSET_ID"=10000428 OR "PRSETCEREL"."PRODUCTSET_ID"=10000429 OR
"PRSETCEREL"."PRODUCTSET_ID"=10000528 OR "PRSETCEREL"."PRODUCTSET_ID"=10000529 OR
"PRSETCEREL"."PRODUCTSET_ID"=10000628 OR "PRSETCEREL"."PRODUCTSET_ID"=10000729 OR
"PRSETCEREL"."PRODUCTSET_ID"=1000001033 OR "PRSETCEREL"."PRODUCTSET_ID"=1000001034 OR
"PRSETCEREL"."PRODUCTSET_ID"=1000001133 OR "PRSETCEREL"."PRODUCTSET_ID"=1000001134 OR
"PRSETCEREL"."PRODUCTSET_ID"=1000001233 OR "PRSETCEREL"."PRODUCTSET_ID"=1000001234 OR
"PRSETCEREL"."PRODUCTSET_ID"=1000001333 OR "PRSETCEREL"."PRODUCTSET_ID"=1000001334 OR
"PRSETCEREL"."PRODUCTSET_ID"=1000001433 OR "PRSETCEREL"."PRODUCTSET_ID"=1000001534 OR
"PRSETCEREL"."PRODUCTSET_ID"=1000001633 OR "PRSETCEREL"."PRODUCTSET_ID"=1000001634 OR
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
"PRSETCEREL"."PRODUCTSET_ID"=1000001733 OR "PRSETCEREL"."PRODUCTSET_ID"=1000001734 OR
"PRSETCEREL"."PRODUCTSET_ID"=1000001833 OR "PRSETCEREL"."PRODUCTSET_ID"=1000001834 OR
"PRSETCEREL"."PRODUCTSET_ID"=1000001933 OR "PRSETCEREL"."PRODUCTSET_ID"=1000001934 OR
"PRSETCEREL"."PRODUCTSET_ID"=1000002033 OR "PRSETCEREL"."PRODUCTSET_ID"=1000002034 OR
"PRSETCEREL"."PRODUCTSET_ID"=1000002133 OR "PRSETCEREL"."PRODUCTSET_ID"=1000062910000728
OR "PRSETCEREL"."PRODUCTSET_ID"=10000014341000001533)))
10 - filter("PRSETCEREL"."PRODUCTSET_ID"=100008 OR "PRSETCEREL"."PRODUCTSET_ID"=100009
OR "PRSETCEREL"."PRODUCTSET_ID"=10000028 OR "PRSETCEREL"."PRODUCTSET_ID"=10000029 OR
"PRSETCEREL"."PRODUCTSET_ID"=10000128 OR "PRSETCEREL"."PRODUCTSET_ID"=10000129 OR
"PRSETCEREL"."PRODUCTSET_ID"=10000228 OR "PRSETCEREL"."PRODUCTSET_ID"=10000229 OR
"PRSETCEREL"."PRODUCTSET_ID"=10000328 OR "PRSETCEREL"."PRODUCTSET_ID"=10000329 OR
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
"PRSETCEREL"."PRODUCTSET_ID"=10000428 OR "PRSETCEREL"."PRODUCTSET_ID"=10000429 OR
"PRSETCEREL"."PRODUCTSET_ID"=10000528 OR "PRSETCEREL"."PRODUCTSET_ID"=10000529 OR
"PRSETCEREL"."PRODUCTSET_ID"=10000628 OR "PRSETCEREL"."PRODUCTSET_ID"=10000729 OR
"PRSETCEREL"."PRODUCTSET_ID"=1000001033 OR "PRSETCEREL"."PRODUCTSET_ID"=1000001034 OR
"PRSETCEREL"."PRODUCTSET_ID"=1000001133 OR "PRSETCEREL"."PRODUCTSET_ID"=1000001134 OR
"PRSETCEREL"."PRODUCTSET_ID"=1000001233 OR "PRSETCEREL"."PRODUCTSET_ID"=1000001234 OR
"PRSETCEREL"."PRODUCTSET_ID"=1000001333 OR "PRSETCEREL"."PRODUCTSET_ID"=1000001334 OR
"PRSETCEREL"."PRODUCTSET_ID"=1000001433 OR "PRSETCEREL"."PRODUCTSET_ID"=1000001534 OR
"PRSETCEREL"."PRODUCTSET_ID"=1000001633 OR "PRSETCEREL"."PRODUCTSET_ID"=1000001634 OR
"PRSETCEREL"."PRODUCTSET_ID"=1000001733 OR "PRSETCEREL"."PRODUCTSET_ID"=1000001734 OR
"PRSETCEREL"."PRODUCTSET_ID"=1000001833 OR "PRSETCEREL"."PRODUCTSET_ID"=1000001834 OR
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
"PRSETCEREL"."PRODUCTSET_ID"=1000001933 OR "PRSETCEREL"."PRODUCTSET_ID"=1000001934 OR
"PRSETCEREL"."PRODUCTSET_ID"=1000002033 OR "PRSETCEREL"."PRODUCTSET_ID"=1000002034 OR
"PRSETCEREL"."PRODUCTSET_ID"=1000002133 OR "PRSETCEREL"."PRODUCTSET_ID"=1000062910000728
OR "PRSETCEREL"."PRODUCTSET_ID"=10000014341000001533)
11 - access("PRSETCEREL"."CATENTRY_ID"=:B1)
12 - access("PRODUCTSET"."PRODUCTSET_ID"="PRSETCEREL"."PRODUCTSET_ID")
filter("PRODUCTSET"."PRODUCTSET_ID"=100008 OR "PRODUCTSET"."PRODUCTSET_ID"=100009
OR "PRODUCTSET"."PRODUCTSET_ID"=10000028 OR "PRODUCTSET"."PRODUCTSET_ID"=10000029 OR
"PRODUCTSET"."PRODUCTSET_ID"=10000128 OR "PRODUCTSET"."PRODUCTSET_ID"=10000129 OR
"PRODUCTSET"."PRODUCTSET_ID"=10000228 OR "PRODUCTSET"."PRODUCTSET_ID"=10000229 OR
"PRODUCTSET"."PRODUCTSET_ID"=10000328 OR "PRODUCTSET"."PRODUCTSET_ID"=10000329 OR
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
"PRODUCTSET"."PRODUCTSET_ID"=10000428 OR "PRODUCTSET"."PRODUCTSET_ID"=10000429 OR
"PRODUCTSET"."PRODUCTSET_ID"=10000528 OR "PRODUCTSET"."PRODUCTSET_ID"=10000529 OR
"PRODUCTSET"."PRODUCTSET_ID"=10000628 OR "PRODUCTSET"."PRODUCTSET_ID"=10000729 OR
"PRODUCTSET"."PRODUCTSET_ID"=1000001033 OR "PRODUCTSET"."PRODUCTSET_ID"=1000001034 OR
"PRODUCTSET"."PRODUCTSET_ID"=1000001133 OR "PRODUCTSET"."PRODUCTSET_ID"=1000001134 OR
"PRODUCTSET"."PRODUCTSET_ID"=1000001233 OR "PRODUCTSET"."PRODUCTSET_ID"=1000001234 OR
"PRODUCTSET"."PRODUCTSET_ID"=1000001333 OR "PRODUCTSET"."PRODUCTSET_ID"=1000001334 OR
"PRODUCTSET"."PRODUCTSET_ID"=1000001433 OR "PRODUCTSET"."PRODUCTSET_ID"=1000001534 OR
"PRODUCTSET"."PRODUCTSET_ID"=1000001633 OR "PRODUCTSET"."PRODUCTSET_ID"=1000001634 OR
"PRODUCTSET"."PRODUCTSET_ID"=1000001733 OR "PRODUCTSET"."PRODUCTSET_ID"=1000001734 OR
"PRODUCTSET"."PRODUCTSET_ID"=1000001833 OR "PRODUCTSET"."PRODUCTSET_ID"=1000001834 OR
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
"PRODUCTSET"."PRODUCTSET_ID"=1000001933 OR "PRODUCTSET"."PRODUCTSET_ID"=1000001934 OR
"PRODUCTSET"."PRODUCTSET_ID"=1000002033 OR "PRODUCTSET"."PRODUCTSET_ID"=1000002034 OR
"PRODUCTSET"."PRODUCTSET_ID"=1000002133 OR "PRODUCTSET"."PRODUCTSET_ID"=1000062910000728
OR "PRODUCTSET"."PRODUCTSET_ID"=10000014341000001533)
13 - filter("PRODUCTSET"."MARKFORDELETE"=0)
14 - filter("SYS_ALIAS_1"."MARKFORDELETE"<>1)
116 rows selected. |
|