 | |
#21
 
| |||
| |||
|
Re: exists or limit -
07-11-2008
, 10:08 AM
|
Anyway, I tested all day long, and I now think my problem is more about my data design. I have a very disparate repartition of my data. |
execution, with a different execution plan:
-------------------------------------------------------------------------------------------------------
Quote:
|
Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |
Quote:
|
0 | SELECT STATEMENT | | 1 | 21 (0)| 00:00:01 | * 1 | FILTER | | | | | 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 | * 3 | TABLE ACCESS BY INDEX ROWID | ENTITYHIERARCHY | 1 | 12 | 3 (0)| 00:00:01 | 4 | NESTED LOOPS | | 1 | 46 | 19 (0)| 00:00:01 | 5 | NESTED LOOPS | | 4 | 136 | 7 (0)| 00:00:01 | 6 | NESTED LOOPS | | 4 | 116 | 7 (0)| 00:00:01 | 7 | NESTED LOOPS | | 1 | 19 | 4 (0)| 00:00:01 | 8 | NESTED LOOPS | | 1 | 10 | 2 (0)| 00:00:01 | * 9 | INDEX RANGE SCAN | SYS_C0020601 | 1 | 6 | 2 (0)| 00:00:01 | * 10 | INDEX UNIQUE SCAN | SYS_C0020595 | 1 | 4 | 0 (0)| 00:00:01 | 11 | TABLE ACCESS BY INDEX ROWID| KNOWLEDGESET | 1 | 9 | 2 (0)| 00:00:01 | * 12 | INDEX RANGE SCAN | DOCUMENTIDX | 1 | 1 (0)| 00:00:01 | 13 | TABLE ACCESS BY INDEX ROWID | POSITIONEDELEMENT | 8 | 80 | 3 (0)| 00:00:01 | * 14 | INDEX RANGE SCAN | KSIDX | 23 | 2 (0)| 00:00:01 | * 15 | INDEX UNIQUE SCAN | SYS_C0020605 | 1 | 5 | 0 (0)| 00:00:01 | * 16 | INDEX RANGE SCAN | ENTITIESIDX | 4 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------- |
And what is more surprising is that the parameters value are among the
"worst" : the biggest corpus with the most abundant entity. Why does it
not always use this very execution plan ?
I'm quite sure (but I don't know how to test that), that if I manage to
force oracle to use this execution plan instead of the former one, the
query will be faster in *most* cases.
--
Hugo
#22
| |||
| |||
|
|
gym dot scuba dot kennedy at gmail wrote: We would need to see the explain plan.(at the very least) If it is set up correctly like it shouldn't take anywhere near 3 seconds. So the question is where is it spending its time? Here we go: ================================================== ==================== *Query A (select xxx from yyy where zzz) PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------- Plan hash value: 143475067 ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 46 | 95 (3)| 00:00:02 | |* 1 | HASH JOIN | | 1 | 46 | 95 (3)| 00:00:02 | | 2 | NESTED LOOPS | | 249 | 8466 | 12 (9)| 00:00:01 | | 3 | NESTED LOOPS | | 247 | 7163 | 12 (9)| 00:00:01 | | 4 | NESTED LOOPS | | 2 | 38 | 5 (20)| 00:00:01 | | 5 | NESTED LOOPS | | 1 | 10 | 3 (34)| 00:00:01 | | 6 | SORT UNIQUE | | 1 | 6 | 2 (0)| 00:00:01 | |* 7 | INDEX RANGE SCAN | SYS_C0020601 | 1 | 6 | 2 (0)| 00:00:01 | |* 8 | INDEX UNIQUE SCAN | SYS_C0020595 | 1 | 4 | 0 (0)| 00:00:01 | | 9 | TABLE ACCESS BY INDEX ROWID| KNOWLEDGESET | 4 | 36 | 2 (0)| 00:00:01 | |* 10 | INDEX RANGE SCAN | DOCUMENTIDX | 4 | | 1 (0)| 00:00:01 | | 11 | TABLE ACCESS BY INDEX ROWID | POSITIONEDELEMENT | 116 | 1160 | 5 (0)| 00:00:01 | |* 12 | INDEX RANGE SCAN | KSIDX | 335 | | 2 (0)| 00:00:01 | |* 13 | INDEX UNIQUE SCAN | SYS_C0020605 | 1 | 5 | 0 (0)| 00:00:01 | | 14 | TABLE ACCESS BY INDEX ROWID | ENTITYHIERARCHY | 697 | 8364 | 82 (0)| 00:00:01 | |* 15 | INDEX RANGE SCAN | FULLHIERARCHYIDX | 697 | | 5 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("PARENTENTI8_"."UIDENTITY"="ENTITY7_"."UIDE NTITY") 7 - access("IDDOCUMENTLIST"=115) 8 - access("DOCUMENT4_"."IDDOCUMENT"="IDDOCUMENT") 10 - access("KNOWLEDGES5_"."IDDOCUMENT"="DOCUMENT4_"."I DDOCUMENT") 12 - access("POSITIONED6_"."IDKNOWLEDGESET"="KNOWLEDGES 5_"."KNOWLEDGESET") 13 - access("POSITIONED6_"."UIDENTITY"="ENTITY7_"."UIDE NTITY") 15 - access("PARENTENTI8_"."IDPARENTENTITY"=41 AND "PARENTENTI8_"."DEPTH">0) ================================================== ===================== *Query B (select 1 from dual where exists (select 1 from yyy where zzz)) PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------ Plan hash value: 1543062091 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 96 (2)| 00:00:02 | |* 1 | FILTER | | | | | | | 2 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | |* 3 | HASH JOIN | | 1 | 46 | 94 (2)| 00:00:02 | | 4 | NESTED LOOPS | | 249 | 8466 | 11 (0)| 00:00:01 | | 5 | NESTED LOOPS | | 247 | 7163 | 11 (0)| 00:00:01 | | 6 | NESTED LOOPS | | 2 | 38 | 4 (0)| 00:00:01 | | 7 | NESTED LOOPS | | 1 | 10 | 2 (0)| 00:00:01 | |* 8 | INDEX RANGE SCAN | SYS_C0020601 | 1 | 6 | 2 (0)| 00:00:01 | |* 9 | INDEX UNIQUE SCAN | SYS_C0020595 | 1 | 4 | 0 (0)| 00:00:01 | | 10 | TABLE ACCESS BY INDEX ROWID| KNOWLEDGESET | 4 | 36 | 2 (0)| 00:00:01 | |* 11 | INDEX RANGE SCAN | DOCUMENTIDX | 4 | | 1 (0)| 00:00:01 | | 12 | TABLE ACCESS BY INDEX ROWID | POSITIONEDELEMENT | 116 | 1160 | 5 (0)| 00:00:01 | |* 13 | INDEX RANGE SCAN | KSIDX | 335 | | 2 (0)| 00:00:01 | |* 14 | INDEX UNIQUE SCAN | SYS_C0020605 | 1 | 5 | 0 (0)| 00:00:01 | | 15 | TABLE ACCESS BY INDEX ROWID | ENTITYHIERARCHY | 697 | 8364 | 82 (0)| 00:00:01 | |* 16 | INDEX RANGE SCAN | FULLHIERARCHYIDX | 697 | | 5 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( EXISTS (SELECT /*+ */ 0 FROM "ENTITYHIERARCHY" "PARENTENTI8_","ENTITY" "ENTITY7_","POSITIONEDELEMENT" "POSITIONED6_","KNOWLEDGESET" "KNOWLEDGES5_","DOCUMENT" "DOCUMENT4_","DOCUMENTLISTCONTENT" "DOCUMENTLISTCONTENT" WHERE "IDDOCUMENTLIST"=115 AND "DOCUMENT4_"."IDDOCUMENT"="IDDOCUMENT" AND "KNOWLEDGES5_"."IDDOCUMENT"="DOCUMENT4_"."IDDOCUME N T" AND "POSITIONED6_"."IDKNOWLEDGESET"="KNOWLEDGES5_"."KN OWLEDGESET" AND "POSITIONED6_"."UIDENTITY"="ENTITY7_"."UIDENTI TY" AND "PARENTENTI8_"."DEPTH">0 AND "PARENTENTI8_"."IDPARENTENTITY"=41 AND "PARENTENTI8_"."UIDENTITY"="ENTITY7_"."UIDENTITY") ) 3 - access("PARENTENTI8_"."UIDENTITY"="ENTITY7_"."UIDE NTITY") 8 - access("IDDOCUMENTLIST"=115) 9 - access("DOCUMENT4_"."IDDOCUMENT"="IDDOCUMENT") 11 - access("KNOWLEDGES5_"."IDDOCUMENT"="DOCUMENT4_"."I DDOCUMENT") 13 - access("POSITIONED6_"."IDKNOWLEDGESET"="KNOWLEDGES 5_"."KNOWLEDGESET") 14 - access("POSITIONED6_"."UIDENTITY"="ENTITY7_"."UIDE NTITY") 16 - access("PARENTENTI8_"."IDPARENTENTITY"=41 AND "PARENTENTI8_"."DEPTH">0) ================================================== ===================== *Query C (select xxx from yyy where zzz and rownum = 1) ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 46 | 95 (3)| 00:00:02 | |* 1 | COUNT STOPKEY | | | | | | |* 2 | HASH JOIN | | 1 | 46 | 95 (3)| 00:00:02 | | 3 | NESTED LOOPS | | 249 | 8466 | 12 (9)| 00:00:01 | | 4 | NESTED LOOPS | | 247 | 7163 | 12 (9)| 00:00:01 | | 5 | NESTED LOOPS | | 2 | 38 | 5 (20)| 00:00:01 | | 6 | NESTED LOOPS | | 1 | 10 | 3 (34)| 00:00:01 | | 7 | SORT UNIQUE | | 1 | 6 | 2 (0)| 00:00:01 | |* 8 | INDEX RANGE SCAN | SYS_C0020601 | 1 | 6 | 2 (0)| 00:00:01 | |* 9 | INDEX UNIQUE SCAN | SYS_C0020595 | 1 | 4 | 0 (0)| 00:00:01 | | 10 | TABLE ACCESS BY INDEX ROWID| KNOWLEDGESET | 4 | 36 | 2 (0)| 00:00:01 | |* 11 | INDEX RANGE SCAN | DOCUMENTIDX | 4 | | 1 (0)| 00:00:01 | | 12 | TABLE ACCESS BY INDEX ROWID | POSITIONEDELEMENT | 116 | 1160 | 5 (0)| 00:00:01 | |* 13 | INDEX RANGE SCAN | KSIDX | 335 | | 2 (0)| 00:00:01 | |* 14 | INDEX UNIQUE SCAN | SYS_C0020605 | 1 | 5 | 0 (0)| 00:00:01 | | 15 | TABLE ACCESS BY INDEX ROWID | ENTITYHIERARCHY | 697 | 8364 | 82 (0)| 00:00:01 | |* 16 | INDEX RANGE SCAN | FULLHIERARCHYIDX | 697 | | 5 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM=1) 2 - access("PARENTENTI8_"."UIDENTITY"="ENTITY7_"."UIDE NTITY") 8 - access("IDDOCUMENTLIST"=115) 9 - access("DOCUMENT4_"."IDDOCUMENT"="IDDOCUMENT") 11 - access("KNOWLEDGES5_"."IDDOCUMENT"="DOCUMENT4_"."I DDOCUMENT") 13 - access("POSITIONED6_"."IDKNOWLEDGESET"="KNOWLEDGES 5_"."KNOWLEDGESET") 14 - access("POSITIONED6_"."UIDENTITY"="ENTITY7_"."UIDE NTITY") 16 - access("PARENTENTI8_"."IDPARENTENTITY"=41 AND "PARENTENTI8_"."DEPTH">0) As a closing remarks, why I really don't understand is that query B is really fast on a tiny corpus (2 docs), but slower on a bigger corpus (2000 docs). If exists really stop at the first matching line, it should be as fast in both cases (assuming a matching line could be found in almost every document). -- Hugo |
indexes (unique or not) Do the row counts in the query look approx correct?
When was the lat time you ran analyze against the tables and indexes? Which
version is this?(of Oracle)
Jim
#23
| |||
| |||
|
|
gym dot scuba dot kennedy at gmail wrote: We would need to see the explain plan.(at the very least) If it is set up correctly like it shouldn't take anywhere near 3 seconds. So the question is where is it spending its time? Here we go: ================================================== ==================== *Query A (select xxx from yyy where zzz) PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------- Plan hash value: 143475067 ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 46 | 95 (3)| 00:00:02 | |* 1 | HASH JOIN | | 1 | 46 | 95 (3)| 00:00:02 | | 2 | NESTED LOOPS | | 249 | 8466 | 12 (9)| 00:00:01 | | 3 | NESTED LOOPS | | 247 | 7163 | 12 (9)| 00:00:01 | | 4 | NESTED LOOPS | | 2 | 38 | 5 (20)| 00:00:01 | | 5 | NESTED LOOPS | | 1 | 10 | 3 (34)| 00:00:01 | | 6 | SORT UNIQUE | | 1 | 6 | 2 (0)| 00:00:01 | |* 7 | INDEX RANGE SCAN | SYS_C0020601 | 1 | 6 | 2 (0)| 00:00:01 | |* 8 | INDEX UNIQUE SCAN | SYS_C0020595 | 1 | 4 | 0 (0)| 00:00:01 | | 9 | TABLE ACCESS BY INDEX ROWID| KNOWLEDGESET | 4 | 36 | 2 (0)| 00:00:01 | |* 10 | INDEX RANGE SCAN | DOCUMENTIDX | 4 | | 1 (0)| 00:00:01 | | 11 | TABLE ACCESS BY INDEX ROWID | POSITIONEDELEMENT | 116 | 1160 | 5 (0)| 00:00:01 | |* 12 | INDEX RANGE SCAN | KSIDX | 335 | | 2 (0)| 00:00:01 | |* 13 | INDEX UNIQUE SCAN | SYS_C0020605 | 1 | 5 | 0 (0)| 00:00:01 | | 14 | TABLE ACCESS BY INDEX ROWID | ENTITYHIERARCHY | 697 | 8364 | 82 (0)| 00:00:01 | |* 15 | INDEX RANGE SCAN | FULLHIERARCHYIDX | 697 | | 5 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("PARENTENTI8_"."UIDENTITY"="ENTITY7_"."UIDE NTITY") 7 - access("IDDOCUMENTLIST"=115) 8 - access("DOCUMENT4_"."IDDOCUMENT"="IDDOCUMENT") 10 - access("KNOWLEDGES5_"."IDDOCUMENT"="DOCUMENT4_"."I DDOCUMENT") 12 - access("POSITIONED6_"."IDKNOWLEDGESET"="KNOWLEDGES 5_"."KNOWLEDGESET") 13 - access("POSITIONED6_"."UIDENTITY"="ENTITY7_"."UIDE NTITY") 15 - access("PARENTENTI8_"."IDPARENTENTITY"=41 AND "PARENTENTI8_"."DEPTH">0) ================================================== ===================== *Query B (select 1 from dual where exists (select 1 from yyy where zzz)) PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------ Plan hash value: 1543062091 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 96 (2)| 00:00:02 | |* 1 | FILTER | | | | | | | 2 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | |* 3 | HASH JOIN | | 1 | 46 | 94 (2)| 00:00:02 | | 4 | NESTED LOOPS | | 249 | 8466 | 11 (0)| 00:00:01 | | 5 | NESTED LOOPS | | 247 | 7163 | 11 (0)| 00:00:01 | | 6 | NESTED LOOPS | | 2 | 38 | 4 (0)| 00:00:01 | | 7 | NESTED LOOPS | | 1 | 10 | 2 (0)| 00:00:01 | |* 8 | INDEX RANGE SCAN | SYS_C0020601 | 1 | 6 | 2 (0)| 00:00:01 | |* 9 | INDEX UNIQUE SCAN | SYS_C0020595 | 1 | 4 | 0 (0)| 00:00:01 | | 10 | TABLE ACCESS BY INDEX ROWID| KNOWLEDGESET | 4 | 36 | 2 (0)| 00:00:01 | |* 11 | INDEX RANGE SCAN | DOCUMENTIDX | 4 | | 1 (0)| 00:00:01 | | 12 | TABLE ACCESS BY INDEX ROWID | POSITIONEDELEMENT | 116 | 1160 | 5 (0)| 00:00:01 | |* 13 | INDEX RANGE SCAN | KSIDX | 335 | | 2 (0)| 00:00:01 | |* 14 | INDEX UNIQUE SCAN | SYS_C0020605 | 1 | 5 | 0 (0)| 00:00:01 | | 15 | TABLE ACCESS BY INDEX ROWID | ENTITYHIERARCHY | 697 | 8364 | 82 (0)| 00:00:01 | |* 16 | INDEX RANGE SCAN | FULLHIERARCHYIDX | 697 | | 5 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( EXISTS (SELECT /*+ */ 0 FROM "ENTITYHIERARCHY" "PARENTENTI8_","ENTITY" "ENTITY7_","POSITIONEDELEMENT" "POSITIONED6_","KNOWLEDGESET" "KNOWLEDGES5_","DOCUMENT" "DOCUMENT4_","DOCUMENTLISTCONTENT" "DOCUMENTLISTCONTENT" WHERE "IDDOCUMENTLIST"=115 AND "DOCUMENT4_"."IDDOCUMENT"="IDDOCUMENT" AND "KNOWLEDGES5_"."IDDOCUMENT"="DOCUMENT4_"."IDDOCUME N T" AND "POSITIONED6_"."IDKNOWLEDGESET"="KNOWLEDGES5_"."KN OWLEDGESET" AND "POSITIONED6_"."UIDENTITY"="ENTITY7_"."UIDENTI TY" AND "PARENTENTI8_"."DEPTH">0 AND "PARENTENTI8_"."IDPARENTENTITY"=41 AND "PARENTENTI8_"."UIDENTITY"="ENTITY7_"."UIDENTITY") ) 3 - access("PARENTENTI8_"."UIDENTITY"="ENTITY7_"."UIDE NTITY") 8 - access("IDDOCUMENTLIST"=115) 9 - access("DOCUMENT4_"."IDDOCUMENT"="IDDOCUMENT") 11 - access("KNOWLEDGES5_"."IDDOCUMENT"="DOCUMENT4_"."I DDOCUMENT") 13 - access("POSITIONED6_"."IDKNOWLEDGESET"="KNOWLEDGES 5_"."KNOWLEDGESET") 14 - access("POSITIONED6_"."UIDENTITY"="ENTITY7_"."UIDE NTITY") 16 - access("PARENTENTI8_"."IDPARENTENTITY"=41 AND "PARENTENTI8_"."DEPTH">0) ================================================== ===================== *Query C (select xxx from yyy where zzz and rownum = 1) ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 46 | 95 (3)| 00:00:02 | |* 1 | COUNT STOPKEY | | | | | | |* 2 | HASH JOIN | | 1 | 46 | 95 (3)| 00:00:02 | | 3 | NESTED LOOPS | | 249 | 8466 | 12 (9)| 00:00:01 | | 4 | NESTED LOOPS | | 247 | 7163 | 12 (9)| 00:00:01 | | 5 | NESTED LOOPS | | 2 | 38 | 5 (20)| 00:00:01 | | 6 | NESTED LOOPS | | 1 | 10 | 3 (34)| 00:00:01 | | 7 | SORT UNIQUE | | 1 | 6 | 2 (0)| 00:00:01 | |* 8 | INDEX RANGE SCAN | SYS_C0020601 | 1 | 6 | 2 (0)| 00:00:01 | |* 9 | INDEX UNIQUE SCAN | SYS_C0020595 | 1 | 4 | 0 (0)| 00:00:01 | | 10 | TABLE ACCESS BY INDEX ROWID| KNOWLEDGESET | 4 | 36 | 2 (0)| 00:00:01 | |* 11 | INDEX RANGE SCAN | DOCUMENTIDX | 4 | | 1 (0)| 00:00:01 | | 12 | TABLE ACCESS BY INDEX ROWID | POSITIONEDELEMENT | 116 | 1160 | 5 (0)| 00:00:01 | |* 13 | INDEX RANGE SCAN | KSIDX | 335 | | 2 (0)| 00:00:01 | |* 14 | INDEX UNIQUE SCAN | SYS_C0020605 | 1 | 5 | 0 (0)| 00:00:01 | | 15 | TABLE ACCESS BY INDEX ROWID | ENTITYHIERARCHY | 697 | 8364 | 82 (0)| 00:00:01 | |* 16 | INDEX RANGE SCAN | FULLHIERARCHYIDX | 697 | | 5 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM=1) 2 - access("PARENTENTI8_"."UIDENTITY"="ENTITY7_"."UIDE NTITY") 8 - access("IDDOCUMENTLIST"=115) 9 - access("DOCUMENT4_"."IDDOCUMENT"="IDDOCUMENT") 11 - access("KNOWLEDGES5_"."IDDOCUMENT"="DOCUMENT4_"."I DDOCUMENT") 13 - access("POSITIONED6_"."IDKNOWLEDGESET"="KNOWLEDGES 5_"."KNOWLEDGESET") 14 - access("POSITIONED6_"."UIDENTITY"="ENTITY7_"."UIDE NTITY") 16 - access("PARENTENTI8_"."IDPARENTENTITY"=41 AND "PARENTENTI8_"."DEPTH">0) As a closing remarks, why I really don't understand is that query B is really fast on a tiny corpus (2 docs), but slower on a bigger corpus (2000 docs). If exists really stop at the first matching line, it should be as fast in both cases (assuming a matching line could be found in almost every document). -- Hugo |
indexes (unique or not) Do the row counts in the query look approx correct?
When was the lat time you ran analyze against the tables and indexes? Which
version is this?(of Oracle)
Jim
#24
| |||
| |||
|
|
gym dot scuba dot kennedy at gmail wrote: We would need to see the explain plan.(at the very least) If it is set up correctly like it shouldn't take anywhere near 3 seconds. So the question is where is it spending its time? Here we go: ================================================== ==================== *Query A (select xxx from yyy where zzz) PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------- Plan hash value: 143475067 ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 46 | 95 (3)| 00:00:02 | |* 1 | HASH JOIN | | 1 | 46 | 95 (3)| 00:00:02 | | 2 | NESTED LOOPS | | 249 | 8466 | 12 (9)| 00:00:01 | | 3 | NESTED LOOPS | | 247 | 7163 | 12 (9)| 00:00:01 | | 4 | NESTED LOOPS | | 2 | 38 | 5 (20)| 00:00:01 | | 5 | NESTED LOOPS | | 1 | 10 | 3 (34)| 00:00:01 | | 6 | SORT UNIQUE | | 1 | 6 | 2 (0)| 00:00:01 | |* 7 | INDEX RANGE SCAN | SYS_C0020601 | 1 | 6 | 2 (0)| 00:00:01 | |* 8 | INDEX UNIQUE SCAN | SYS_C0020595 | 1 | 4 | 0 (0)| 00:00:01 | | 9 | TABLE ACCESS BY INDEX ROWID| KNOWLEDGESET | 4 | 36 | 2 (0)| 00:00:01 | |* 10 | INDEX RANGE SCAN | DOCUMENTIDX | 4 | | 1 (0)| 00:00:01 | | 11 | TABLE ACCESS BY INDEX ROWID | POSITIONEDELEMENT | 116 | 1160 | 5 (0)| 00:00:01 | |* 12 | INDEX RANGE SCAN | KSIDX | 335 | | 2 (0)| 00:00:01 | |* 13 | INDEX UNIQUE SCAN | SYS_C0020605 | 1 | 5 | 0 (0)| 00:00:01 | | 14 | TABLE ACCESS BY INDEX ROWID | ENTITYHIERARCHY | 697 | 8364 | 82 (0)| 00:00:01 | |* 15 | INDEX RANGE SCAN | FULLHIERARCHYIDX | 697 | | 5 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("PARENTENTI8_"."UIDENTITY"="ENTITY7_"."UIDE NTITY") 7 - access("IDDOCUMENTLIST"=115) 8 - access("DOCUMENT4_"."IDDOCUMENT"="IDDOCUMENT") 10 - access("KNOWLEDGES5_"."IDDOCUMENT"="DOCUMENT4_"."I DDOCUMENT") 12 - access("POSITIONED6_"."IDKNOWLEDGESET"="KNOWLEDGES 5_"."KNOWLEDGESET") 13 - access("POSITIONED6_"."UIDENTITY"="ENTITY7_"."UIDE NTITY") 15 - access("PARENTENTI8_"."IDPARENTENTITY"=41 AND "PARENTENTI8_"."DEPTH">0) ================================================== ===================== *Query B (select 1 from dual where exists (select 1 from yyy where zzz)) PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------ Plan hash value: 1543062091 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 96 (2)| 00:00:02 | |* 1 | FILTER | | | | | | | 2 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | |* 3 | HASH JOIN | | 1 | 46 | 94 (2)| 00:00:02 | | 4 | NESTED LOOPS | | 249 | 8466 | 11 (0)| 00:00:01 | | 5 | NESTED LOOPS | | 247 | 7163 | 11 (0)| 00:00:01 | | 6 | NESTED LOOPS | | 2 | 38 | 4 (0)| 00:00:01 | | 7 | NESTED LOOPS | | 1 | 10 | 2 (0)| 00:00:01 | |* 8 | INDEX RANGE SCAN | SYS_C0020601 | 1 | 6 | 2 (0)| 00:00:01 | |* 9 | INDEX UNIQUE SCAN | SYS_C0020595 | 1 | 4 | 0 (0)| 00:00:01 | | 10 | TABLE ACCESS BY INDEX ROWID| KNOWLEDGESET | 4 | 36 | 2 (0)| 00:00:01 | |* 11 | INDEX RANGE SCAN | DOCUMENTIDX | 4 | | 1 (0)| 00:00:01 | | 12 | TABLE ACCESS BY INDEX ROWID | POSITIONEDELEMENT | 116 | 1160 | 5 (0)| 00:00:01 | |* 13 | INDEX RANGE SCAN | KSIDX | 335 | | 2 (0)| 00:00:01 | |* 14 | INDEX UNIQUE SCAN | SYS_C0020605 | 1 | 5 | 0 (0)| 00:00:01 | | 15 | TABLE ACCESS BY INDEX ROWID | ENTITYHIERARCHY | 697 | 8364 | 82 (0)| 00:00:01 | |* 16 | INDEX RANGE SCAN | FULLHIERARCHYIDX | 697 | | 5 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( EXISTS (SELECT /*+ */ 0 FROM "ENTITYHIERARCHY" "PARENTENTI8_","ENTITY" "ENTITY7_","POSITIONEDELEMENT" "POSITIONED6_","KNOWLEDGESET" "KNOWLEDGES5_","DOCUMENT" "DOCUMENT4_","DOCUMENTLISTCONTENT" "DOCUMENTLISTCONTENT" WHERE "IDDOCUMENTLIST"=115 AND "DOCUMENT4_"."IDDOCUMENT"="IDDOCUMENT" AND "KNOWLEDGES5_"."IDDOCUMENT"="DOCUMENT4_"."IDDOCUME N T" AND "POSITIONED6_"."IDKNOWLEDGESET"="KNOWLEDGES5_"."KN OWLEDGESET" AND "POSITIONED6_"."UIDENTITY"="ENTITY7_"."UIDENTI TY" AND "PARENTENTI8_"."DEPTH">0 AND "PARENTENTI8_"."IDPARENTENTITY"=41 AND "PARENTENTI8_"."UIDENTITY"="ENTITY7_"."UIDENTITY") ) 3 - access("PARENTENTI8_"."UIDENTITY"="ENTITY7_"."UIDE NTITY") 8 - access("IDDOCUMENTLIST"=115) 9 - access("DOCUMENT4_"."IDDOCUMENT"="IDDOCUMENT") 11 - access("KNOWLEDGES5_"."IDDOCUMENT"="DOCUMENT4_"."I DDOCUMENT") 13 - access("POSITIONED6_"."IDKNOWLEDGESET"="KNOWLEDGES 5_"."KNOWLEDGESET") 14 - access("POSITIONED6_"."UIDENTITY"="ENTITY7_"."UIDE NTITY") 16 - access("PARENTENTI8_"."IDPARENTENTITY"=41 AND "PARENTENTI8_"."DEPTH">0) ================================================== ===================== *Query C (select xxx from yyy where zzz and rownum = 1) ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 46 | 95 (3)| 00:00:02 | |* 1 | COUNT STOPKEY | | | | | | |* 2 | HASH JOIN | | 1 | 46 | 95 (3)| 00:00:02 | | 3 | NESTED LOOPS | | 249 | 8466 | 12 (9)| 00:00:01 | | 4 | NESTED LOOPS | | 247 | 7163 | 12 (9)| 00:00:01 | | 5 | NESTED LOOPS | | 2 | 38 | 5 (20)| 00:00:01 | | 6 | NESTED LOOPS | | 1 | 10 | 3 (34)| 00:00:01 | | 7 | SORT UNIQUE | | 1 | 6 | 2 (0)| 00:00:01 | |* 8 | INDEX RANGE SCAN | SYS_C0020601 | 1 | 6 | 2 (0)| 00:00:01 | |* 9 | INDEX UNIQUE SCAN | SYS_C0020595 | 1 | 4 | 0 (0)| 00:00:01 | | 10 | TABLE ACCESS BY INDEX ROWID| KNOWLEDGESET | 4 | 36 | 2 (0)| 00:00:01 | |* 11 | INDEX RANGE SCAN | DOCUMENTIDX | 4 | | 1 (0)| 00:00:01 | | 12 | TABLE ACCESS BY INDEX ROWID | POSITIONEDELEMENT | 116 | 1160 | 5 (0)| 00:00:01 | |* 13 | INDEX RANGE SCAN | KSIDX | 335 | | 2 (0)| 00:00:01 | |* 14 | INDEX UNIQUE SCAN | SYS_C0020605 | 1 | 5 | 0 (0)| 00:00:01 | | 15 | TABLE ACCESS BY INDEX ROWID | ENTITYHIERARCHY | 697 | 8364 | 82 (0)| 00:00:01 | |* 16 | INDEX RANGE SCAN | FULLHIERARCHYIDX | 697 | | 5 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM=1) 2 - access("PARENTENTI8_"."UIDENTITY"="ENTITY7_"."UIDE NTITY") 8 - access("IDDOCUMENTLIST"=115) 9 - access("DOCUMENT4_"."IDDOCUMENT"="IDDOCUMENT") 11 - access("KNOWLEDGES5_"."IDDOCUMENT"="DOCUMENT4_"."I DDOCUMENT") 13 - access("POSITIONED6_"."IDKNOWLEDGESET"="KNOWLEDGES 5_"."KNOWLEDGESET") 14 - access("POSITIONED6_"."UIDENTITY"="ENTITY7_"."UIDE NTITY") 16 - access("PARENTENTI8_"."IDPARENTENTITY"=41 AND "PARENTENTI8_"."DEPTH">0) As a closing remarks, why I really don't understand is that query B is really fast on a tiny corpus (2 docs), but slower on a bigger corpus (2000 docs). If exists really stop at the first matching line, it should be as fast in both cases (assuming a matching line could be found in almost every document). -- Hugo |
indexes (unique or not) Do the row counts in the query look approx correct?
When was the lat time you ran analyze against the tables and indexes? Which
version is this?(of Oracle)
Jim
#25
| |||
| |||
|
|
gym dot scuba dot kennedy at gmail wrote: We would need to see the explain plan.(at the very least) If it is set up correctly like it shouldn't take anywhere near 3 seconds. So the question is where is it spending its time? Here we go: ================================================== ==================== *Query A (select xxx from yyy where zzz) PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------- Plan hash value: 143475067 ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 46 | 95 (3)| 00:00:02 | |* 1 | HASH JOIN | | 1 | 46 | 95 (3)| 00:00:02 | | 2 | NESTED LOOPS | | 249 | 8466 | 12 (9)| 00:00:01 | | 3 | NESTED LOOPS | | 247 | 7163 | 12 (9)| 00:00:01 | | 4 | NESTED LOOPS | | 2 | 38 | 5 (20)| 00:00:01 | | 5 | NESTED LOOPS | | 1 | 10 | 3 (34)| 00:00:01 | | 6 | SORT UNIQUE | | 1 | 6 | 2 (0)| 00:00:01 | |* 7 | INDEX RANGE SCAN | SYS_C0020601 | 1 | 6 | 2 (0)| 00:00:01 | |* 8 | INDEX UNIQUE SCAN | SYS_C0020595 | 1 | 4 | 0 (0)| 00:00:01 | | 9 | TABLE ACCESS BY INDEX ROWID| KNOWLEDGESET | 4 | 36 | 2 (0)| 00:00:01 | |* 10 | INDEX RANGE SCAN | DOCUMENTIDX | 4 | | 1 (0)| 00:00:01 | | 11 | TABLE ACCESS BY INDEX ROWID | POSITIONEDELEMENT | 116 | 1160 | 5 (0)| 00:00:01 | |* 12 | INDEX RANGE SCAN | KSIDX | 335 | | 2 (0)| 00:00:01 | |* 13 | INDEX UNIQUE SCAN | SYS_C0020605 | 1 | 5 | 0 (0)| 00:00:01 | | 14 | TABLE ACCESS BY INDEX ROWID | ENTITYHIERARCHY | 697 | 8364 | 82 (0)| 00:00:01 | |* 15 | INDEX RANGE SCAN | FULLHIERARCHYIDX | 697 | | 5 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("PARENTENTI8_"."UIDENTITY"="ENTITY7_"."UIDE NTITY") 7 - access("IDDOCUMENTLIST"=115) 8 - access("DOCUMENT4_"."IDDOCUMENT"="IDDOCUMENT") 10 - access("KNOWLEDGES5_"."IDDOCUMENT"="DOCUMENT4_"."I DDOCUMENT") 12 - access("POSITIONED6_"."IDKNOWLEDGESET"="KNOWLEDGES 5_"."KNOWLEDGESET") 13 - access("POSITIONED6_"."UIDENTITY"="ENTITY7_"."UIDE NTITY") 15 - access("PARENTENTI8_"."IDPARENTENTITY"=41 AND "PARENTENTI8_"."DEPTH">0) ================================================== ===================== *Query B (select 1 from dual where exists (select 1 from yyy where zzz)) PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------ Plan hash value: 1543062091 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 96 (2)| 00:00:02 | |* 1 | FILTER | | | | | | | 2 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | |* 3 | HASH JOIN | | 1 | 46 | 94 (2)| 00:00:02 | | 4 | NESTED LOOPS | | 249 | 8466 | 11 (0)| 00:00:01 | | 5 | NESTED LOOPS | | 247 | 7163 | 11 (0)| 00:00:01 | | 6 | NESTED LOOPS | | 2 | 38 | 4 (0)| 00:00:01 | | 7 | NESTED LOOPS | | 1 | 10 | 2 (0)| 00:00:01 | |* 8 | INDEX RANGE SCAN | SYS_C0020601 | 1 | 6 | 2 (0)| 00:00:01 | |* 9 | INDEX UNIQUE SCAN | SYS_C0020595 | 1 | 4 | 0 (0)| 00:00:01 | | 10 | TABLE ACCESS BY INDEX ROWID| KNOWLEDGESET | 4 | 36 | 2 (0)| 00:00:01 | |* 11 | INDEX RANGE SCAN | DOCUMENTIDX | 4 | | 1 (0)| 00:00:01 | | 12 | TABLE ACCESS BY INDEX ROWID | POSITIONEDELEMENT | 116 | 1160 | 5 (0)| 00:00:01 | |* 13 | INDEX RANGE SCAN | KSIDX | 335 | | 2 (0)| 00:00:01 | |* 14 | INDEX UNIQUE SCAN | SYS_C0020605 | 1 | 5 | 0 (0)| 00:00:01 | | 15 | TABLE ACCESS BY INDEX ROWID | ENTITYHIERARCHY | 697 | 8364 | 82 (0)| 00:00:01 | |* 16 | INDEX RANGE SCAN | FULLHIERARCHYIDX | 697 | | 5 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( EXISTS (SELECT /*+ */ 0 FROM "ENTITYHIERARCHY" "PARENTENTI8_","ENTITY" "ENTITY7_","POSITIONEDELEMENT" "POSITIONED6_","KNOWLEDGESET" "KNOWLEDGES5_","DOCUMENT" "DOCUMENT4_","DOCUMENTLISTCONTENT" "DOCUMENTLISTCONTENT" WHERE "IDDOCUMENTLIST"=115 AND "DOCUMENT4_"."IDDOCUMENT"="IDDOCUMENT" AND "KNOWLEDGES5_"."IDDOCUMENT"="DOCUMENT4_"."IDDOCUME N T" AND "POSITIONED6_"."IDKNOWLEDGESET"="KNOWLEDGES5_"."KN OWLEDGESET" AND "POSITIONED6_"."UIDENTITY"="ENTITY7_"."UIDENTI TY" AND "PARENTENTI8_"."DEPTH">0 AND "PARENTENTI8_"."IDPARENTENTITY"=41 AND "PARENTENTI8_"."UIDENTITY"="ENTITY7_"."UIDENTITY") ) 3 - access("PARENTENTI8_"."UIDENTITY"="ENTITY7_"."UIDE NTITY") 8 - access("IDDOCUMENTLIST"=115) 9 - access("DOCUMENT4_"."IDDOCUMENT"="IDDOCUMENT") 11 - access("KNOWLEDGES5_"."IDDOCUMENT"="DOCUMENT4_"."I DDOCUMENT") 13 - access("POSITIONED6_"."IDKNOWLEDGESET"="KNOWLEDGES 5_"."KNOWLEDGESET") 14 - access("POSITIONED6_"."UIDENTITY"="ENTITY7_"."UIDE NTITY") 16 - access("PARENTENTI8_"."IDPARENTENTITY"=41 AND "PARENTENTI8_"."DEPTH">0) ================================================== ===================== *Query C (select xxx from yyy where zzz and rownum = 1) ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 46 | 95 (3)| 00:00:02 | |* 1 | COUNT STOPKEY | | | | | | |* 2 | HASH JOIN | | 1 | 46 | 95 (3)| 00:00:02 | | 3 | NESTED LOOPS | | 249 | 8466 | 12 (9)| 00:00:01 | | 4 | NESTED LOOPS | | 247 | 7163 | 12 (9)| 00:00:01 | | 5 | NESTED LOOPS | | 2 | 38 | 5 (20)| 00:00:01 | | 6 | NESTED LOOPS | | 1 | 10 | 3 (34)| 00:00:01 | | 7 | SORT UNIQUE | | 1 | 6 | 2 (0)| 00:00:01 | |* 8 | INDEX RANGE SCAN | SYS_C0020601 | 1 | 6 | 2 (0)| 00:00:01 | |* 9 | INDEX UNIQUE SCAN | SYS_C0020595 | 1 | 4 | 0 (0)| 00:00:01 | | 10 | TABLE ACCESS BY INDEX ROWID| KNOWLEDGESET | 4 | 36 | 2 (0)| 00:00:01 | |* 11 | INDEX RANGE SCAN | DOCUMENTIDX | 4 | | 1 (0)| 00:00:01 | | 12 | TABLE ACCESS BY INDEX ROWID | POSITIONEDELEMENT | 116 | 1160 | 5 (0)| 00:00:01 | |* 13 | INDEX RANGE SCAN | KSIDX | 335 | | 2 (0)| 00:00:01 | |* 14 | INDEX UNIQUE SCAN | SYS_C0020605 | 1 | 5 | 0 (0)| 00:00:01 | | 15 | TABLE ACCESS BY INDEX ROWID | ENTITYHIERARCHY | 697 | 8364 | 82 (0)| 00:00:01 | |* 16 | INDEX RANGE SCAN | FULLHIERARCHYIDX | 697 | | 5 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM=1) 2 - access("PARENTENTI8_"."UIDENTITY"="ENTITY7_"."UIDE NTITY") 8 - access("IDDOCUMENTLIST"=115) 9 - access("DOCUMENT4_"."IDDOCUMENT"="IDDOCUMENT") 11 - access("KNOWLEDGES5_"."IDDOCUMENT"="DOCUMENT4_"."I DDOCUMENT") 13 - access("POSITIONED6_"."IDKNOWLEDGESET"="KNOWLEDGES 5_"."KNOWLEDGESET") 14 - access("POSITIONED6_"."UIDENTITY"="ENTITY7_"."UIDE NTITY") 16 - access("PARENTENTI8_"."IDPARENTENTITY"=41 AND "PARENTENTI8_"."DEPTH">0) As a closing remarks, why I really don't understand is that query B is really fast on a tiny corpus (2 docs), but slower on a bigger corpus (2000 docs). If exists really stop at the first matching line, it should be as fast in both cases (assuming a matching line could be found in almost every document). -- Hugo |
indexes (unique or not) Do the row counts in the query look approx correct?
When was the lat time you ran analyze against the tables and indexes? Which
version is this?(of Oracle)
Jim
#26
| |||
| |||
|
|
Hugo wrote : Anyway, I tested all day long, and I now think my problem is more about my data design. I have a very disparate repartition of my data. As a complement to that, some parameter values make a very fast query execution, with a different execution plan: ---------------------------------------------------------------------------*---------------------------- *| Id *| Operation * * * * * * * * * * * * | Name * * * * * * *| Rows *| Bytes | Cost (%CPU)| Time * * | *---------------------------------------------------------------------------*---------------------------- *| * 0 | SELECT STATEMENT * * * * * * * * *| * * * * * * * * * | * * 1 | * * * | * *21 * (0)| 00:00:01 | *|* *1 | *FILTER * * * * * * * * * * * * * | * * * * * * * * * | * * * | * * * | * * * * * *| * * * * *| *| * 2 | * FAST DUAL * * * * * * * * * * * | * * * * * * * * * | * * 1 | * * * | * * 2 * (0)| 00:00:01 | *|* *3 | * TABLE ACCESS BY INDEX ROWID * * | ENTITYHIERARCHY * | * * 1 | * *12 | * * 3 * (0)| 00:00:01 | *| * 4 | * *NESTED LOOPS * * * * * * * * * | * * * * * * * * * | * * 1 | * *46 | * *19 * (0)| 00:00:01 | *| * 5 | * * NESTED LOOPS * * * * * * * * *| * * * * * * * * * | * * 4 | * 136 | * * 7 * (0)| 00:00:01 | *| * 6 | * * *NESTED LOOPS * * * * * * * * | * * * * * * * * * | * * 4 | * 116 | * * 7 * (0)| 00:00:01 | *| * 7 | * * * NESTED LOOPS * * * * * * * *| * * * * * * * * * | * * 1 | * *19 | * * 4 * (0)| 00:00:01 | *| * 8 | * * * *NESTED LOOPS * * * * * * * | * * * * * * * * * | * * 1 | * *10 | * * 2 * (0)| 00:00:01 | *|* *9 | * * * * INDEX RANGE SCAN * * * * *| SYS_C0020601 * * *| * * 1 | * * 6 | * * 2 * (0)| 00:00:01 | *|* 10 | * * * * INDEX UNIQUE SCAN * * * * | SYS_C0020595 * * *| * * 1 | * * 4 | * * 0 * (0)| 00:00:01 | *| *11 | * * * *TABLE ACCESS BY INDEX ROWID| KNOWLEDGESET ** *| * * 1 | * * 9 | * * 2 * (0)| 00:00:01 | *|* 12 | * * * * INDEX RANGE SCAN * * * * *| DOCUMENTIDX * * * | * * 1 | * * * | * * 1 * (0)| 00:00:01 | *| *13 | * * * TABLE ACCESS BY INDEX ROWID | POSITIONEDELEMENT | * * 8 | * *80 | * * 3 * (0)| 00:00:01 | *|* 14 | * * * *INDEX RANGE SCAN * * * * * | KSIDX * * * * * * | * *23 | * * * | * * 2 * (0)| 00:00:01 | *|* 15 | * * *INDEX UNIQUE SCAN * * * * * *| SYS_C0020605 * * *| * * 1 | * * 5 | * * 0 * (0)| 00:00:01 | *|* 16 | * * INDEX RANGE SCAN * * * * * * *| ENTITIESIDX * * * | * * 4 | * * * | * * 2 * (0)| 00:00:01 | *---------------------------------------------------------------------------*---------------------------- And what is more surprising is that the parameters value are among the "worst" : the biggest corpus with the most abundant entity. Why does it not always use this very execution plan ? I'm quite sure (but I don't know how to test that), that if I manage to force oracle to use this execution plan instead of the former one, the query will be faster in *most* cases. -- Hugo |
Search for "exists" at http://asktom.oracle.com
jg
--
@home.com is bogus.
"Sometimes it's difficult to remember what stuff to forget." - Jared
Still
#27
| |||
| |||
|
|
Hugo wrote : Anyway, I tested all day long, and I now think my problem is more about my data design. I have a very disparate repartition of my data. As a complement to that, some parameter values make a very fast query execution, with a different execution plan: ---------------------------------------------------------------------------*---------------------------- *| Id *| Operation * * * * * * * * * * * * | Name * * * * * * *| Rows *| Bytes | Cost (%CPU)| Time * * | *---------------------------------------------------------------------------*---------------------------- *| * 0 | SELECT STATEMENT * * * * * * * * *| * * * * * * * * * | * * 1 | * * * | * *21 * (0)| 00:00:01 | *|* *1 | *FILTER * * * * * * * * * * * * * | * * * * * * * * * | * * * | * * * | * * * * * *| * * * * *| *| * 2 | * FAST DUAL * * * * * * * * * * * | * * * * * * * * * | * * 1 | * * * | * * 2 * (0)| 00:00:01 | *|* *3 | * TABLE ACCESS BY INDEX ROWID * * | ENTITYHIERARCHY * | * * 1 | * *12 | * * 3 * (0)| 00:00:01 | *| * 4 | * *NESTED LOOPS * * * * * * * * * | * * * * * * * * * | * * 1 | * *46 | * *19 * (0)| 00:00:01 | *| * 5 | * * NESTED LOOPS * * * * * * * * *| * * * * * * * * * | * * 4 | * 136 | * * 7 * (0)| 00:00:01 | *| * 6 | * * *NESTED LOOPS * * * * * * * * | * * * * * * * * * | * * 4 | * 116 | * * 7 * (0)| 00:00:01 | *| * 7 | * * * NESTED LOOPS * * * * * * * *| * * * * * * * * * | * * 1 | * *19 | * * 4 * (0)| 00:00:01 | *| * 8 | * * * *NESTED LOOPS * * * * * * * | * * * * * * * * * | * * 1 | * *10 | * * 2 * (0)| 00:00:01 | *|* *9 | * * * * INDEX RANGE SCAN * * * * *| SYS_C0020601 * * *| * * 1 | * * 6 | * * 2 * (0)| 00:00:01 | *|* 10 | * * * * INDEX UNIQUE SCAN * * * * | SYS_C0020595 * * *| * * 1 | * * 4 | * * 0 * (0)| 00:00:01 | *| *11 | * * * *TABLE ACCESS BY INDEX ROWID| KNOWLEDGESET ** *| * * 1 | * * 9 | * * 2 * (0)| 00:00:01 | *|* 12 | * * * * INDEX RANGE SCAN * * * * *| DOCUMENTIDX * * * | * * 1 | * * * | * * 1 * (0)| 00:00:01 | *| *13 | * * * TABLE ACCESS BY INDEX ROWID | POSITIONEDELEMENT | * * 8 | * *80 | * * 3 * (0)| 00:00:01 | *|* 14 | * * * *INDEX RANGE SCAN * * * * * | KSIDX * * * * * * | * *23 | * * * | * * 2 * (0)| 00:00:01 | *|* 15 | * * *INDEX UNIQUE SCAN * * * * * *| SYS_C0020605 * * *| * * 1 | * * 5 | * * 0 * (0)| 00:00:01 | *|* 16 | * * INDEX RANGE SCAN * * * * * * *| ENTITIESIDX * * * | * * 4 | * * * | * * 2 * (0)| 00:00:01 | *---------------------------------------------------------------------------*---------------------------- And what is more surprising is that the parameters value are among the "worst" : the biggest corpus with the most abundant entity. Why does it not always use this very execution plan ? I'm quite sure (but I don't know how to test that), that if I manage to force oracle to use this execution plan instead of the former one, the query will be faster in *most* cases. -- Hugo |
Search for "exists" at http://asktom.oracle.com
jg
--
@home.com is bogus.
"Sometimes it's difficult to remember what stuff to forget." - Jared
Still
#28
| |||
| |||
|
|
Hugo wrote : Anyway, I tested all day long, and I now think my problem is more about my data design. I have a very disparate repartition of my data. As a complement to that, some parameter values make a very fast query execution, with a different execution plan: ---------------------------------------------------------------------------*---------------------------- *| Id *| Operation * * * * * * * * * * * * | Name * * * * * * *| Rows *| Bytes | Cost (%CPU)| Time * * | *---------------------------------------------------------------------------*---------------------------- *| * 0 | SELECT STATEMENT * * * * * * * * *| * * * * * * * * * | * * 1 | * * * | * *21 * (0)| 00:00:01 | *|* *1 | *FILTER * * * * * * * * * * * * * | * * * * * * * * * | * * * | * * * | * * * * * *| * * * * *| *| * 2 | * FAST DUAL * * * * * * * * * * * | * * * * * * * * * | * * 1 | * * * | * * 2 * (0)| 00:00:01 | *|* *3 | * TABLE ACCESS BY INDEX ROWID * * | ENTITYHIERARCHY * | * * 1 | * *12 | * * 3 * (0)| 00:00:01 | *| * 4 | * *NESTED LOOPS * * * * * * * * * | * * * * * * * * * | * * 1 | * *46 | * *19 * (0)| 00:00:01 | *| * 5 | * * NESTED LOOPS * * * * * * * * *| * * * * * * * * * | * * 4 | * 136 | * * 7 * (0)| 00:00:01 | *| * 6 | * * *NESTED LOOPS * * * * * * * * | * * * * * * * * * | * * 4 | * 116 | * * 7 * (0)| 00:00:01 | *| * 7 | * * * NESTED LOOPS * * * * * * * *| * * * * * * * * * | * * 1 | * *19 | * * 4 * (0)| 00:00:01 | *| * 8 | * * * *NESTED LOOPS * * * * * * * | * * * * * * * * * | * * 1 | * *10 | * * 2 * (0)| 00:00:01 | *|* *9 | * * * * INDEX RANGE SCAN * * * * *| SYS_C0020601 * * *| * * 1 | * * 6 | * * 2 * (0)| 00:00:01 | *|* 10 | * * * * INDEX UNIQUE SCAN * * * * | SYS_C0020595 * * *| * * 1 | * * 4 | * * 0 * (0)| 00:00:01 | *| *11 | * * * *TABLE ACCESS BY INDEX ROWID| KNOWLEDGESET ** *| * * 1 | * * 9 | * * 2 * (0)| 00:00:01 | *|* 12 | * * * * INDEX RANGE SCAN * * * * *| DOCUMENTIDX * * * | * * 1 | * * * | * * 1 * (0)| 00:00:01 | *| *13 | * * * TABLE ACCESS BY INDEX ROWID | POSITIONEDELEMENT | * * 8 | * *80 | * * 3 * (0)| 00:00:01 | *|* 14 | * * * *INDEX RANGE SCAN * * * * * | KSIDX * * * * * * | * *23 | * * * | * * 2 * (0)| 00:00:01 | *|* 15 | * * *INDEX UNIQUE SCAN * * * * * *| SYS_C0020605 * * *| * * 1 | * * 5 | * * 0 * (0)| 00:00:01 | *|* 16 | * * INDEX RANGE SCAN * * * * * * *| ENTITIESIDX * * * | * * 4 | * * * | * * 2 * (0)| 00:00:01 | *---------------------------------------------------------------------------*---------------------------- And what is more surprising is that the parameters value are among the "worst" : the biggest corpus with the most abundant entity. Why does it not always use this very execution plan ? I'm quite sure (but I don't know how to test that), that if I manage to force oracle to use this execution plan instead of the former one, the query will be faster in *most* cases. -- Hugo |
Search for "exists" at http://asktom.oracle.com
jg
--
@home.com is bogus.
"Sometimes it's difficult to remember what stuff to forget." - Jared
Still
#29
| |||
| |||
|
|
Hugo wrote : Anyway, I tested all day long, and I now think my problem is more about my data design. I have a very disparate repartition of my data. As a complement to that, some parameter values make a very fast query execution, with a different execution plan: ---------------------------------------------------------------------------*---------------------------- *| Id *| Operation * * * * * * * * * * * * | Name * * * * * * *| Rows *| Bytes | Cost (%CPU)| Time * * | *---------------------------------------------------------------------------*---------------------------- *| * 0 | SELECT STATEMENT * * * * * * * * *| * * * * * * * * * | * * 1 | * * * | * *21 * (0)| 00:00:01 | *|* *1 | *FILTER * * * * * * * * * * * * * | * * * * * * * * * | * * * | * * * | * * * * * *| * * * * *| *| * 2 | * FAST DUAL * * * * * * * * * * * | * * * * * * * * * | * * 1 | * * * | * * 2 * (0)| 00:00:01 | *|* *3 | * TABLE ACCESS BY INDEX ROWID * * | ENTITYHIERARCHY * | * * 1 | * *12 | * * 3 * (0)| 00:00:01 | *| * 4 | * *NESTED LOOPS * * * * * * * * * | * * * * * * * * * | * * 1 | * *46 | * *19 * (0)| 00:00:01 | *| * 5 | * * NESTED LOOPS * * * * * * * * *| * * * * * * * * * | * * 4 | * 136 | * * 7 * (0)| 00:00:01 | *| * 6 | * * *NESTED LOOPS * * * * * * * * | * * * * * * * * * | * * 4 | * 116 | * * 7 * (0)| 00:00:01 | *| * 7 | * * * NESTED LOOPS * * * * * * * *| * * * * * * * * * | * * 1 | * *19 | * * 4 * (0)| 00:00:01 | *| * 8 | * * * *NESTED LOOPS * * * * * * * | * * * * * * * * * | * * 1 | * *10 | * * 2 * (0)| 00:00:01 | *|* *9 | * * * * INDEX RANGE SCAN * * * * *| SYS_C0020601 * * *| * * 1 | * * 6 | * * 2 * (0)| 00:00:01 | *|* 10 | * * * * INDEX UNIQUE SCAN * * * * | SYS_C0020595 * * *| * * 1 | * * 4 | * * 0 * (0)| 00:00:01 | *| *11 | * * * *TABLE ACCESS BY INDEX ROWID| KNOWLEDGESET ** *| * * 1 | * * 9 | * * 2 * (0)| 00:00:01 | *|* 12 | * * * * INDEX RANGE SCAN * * * * *| DOCUMENTIDX * * * | * * 1 | * * * | * * 1 * (0)| 00:00:01 | *| *13 | * * * TABLE ACCESS BY INDEX ROWID | POSITIONEDELEMENT | * * 8 | * *80 | * * 3 * (0)| 00:00:01 | *|* 14 | * * * *INDEX RANGE SCAN * * * * * | KSIDX * * * * * * | * *23 | * * * | * * 2 * (0)| 00:00:01 | *|* 15 | * * *INDEX UNIQUE SCAN * * * * * *| SYS_C0020605 * * *| * * 1 | * * 5 | * * 0 * (0)| 00:00:01 | *|* 16 | * * INDEX RANGE SCAN * * * * * * *| ENTITIESIDX * * * | * * 4 | * * * | * * 2 * (0)| 00:00:01 | *---------------------------------------------------------------------------*---------------------------- And what is more surprising is that the parameters value are among the "worst" : the biggest corpus with the most abundant entity. Why does it not always use this very execution plan ? I'm quite sure (but I don't know how to test that), that if I manage to force oracle to use this execution plan instead of the former one, the query will be faster in *most* cases. -- Hugo |
Search for "exists" at http://asktom.oracle.com
jg
--
@home.com is bogus.
"Sometimes it's difficult to remember what stuff to forget." - Jared
Still
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.