 | |
#1
 
| |||
| |||
|
exists or limit -
07-11-2008
, 05:41 AM
#2
| |||
| |||
|
|
Hi, I need to test if an entity exist in a document set. For instance, test if there is any "Location" in a given set of 42 documents. I don't need to retrieve the actual rows, the count or whatever, but just the existence. Query A (get all Location in the corpus): select xxx from yyy where zzz execution time: 3 seconds (lots of results) Query B (exist) select 1 from dual where exists (select 1 from yyy where zzz) execution time: still 3 seconds Query C (limit) select xxx from yyy where zzz and rownum = 1 execution time: still 3 seconds I can't understand why query B and C are as slow as Query A. It should stop at the first matching row found, and before all of them are visited. But here it's like it does not make any difference at all. In others SGBD (MySQL mainly), using limit or exists in this case is very effective, performance wise. In Oracle, "exists" is just king of syntaxic sugar, or does it really means something for the optimizer and execution plan generated ? As I'am very new to the Oracle world, maybe I'm missing something obvious. But for the moment I'm stuck with pretty poor performance... -- Hugo |
correctly like it shouldn't take anywhere near 3 seconds. So the question
is where is it spending its time?
explain plan for
select ..... ;
select table(dbms_xplan.display) from dual;
Jim
#3
| |||
| |||
|
|
Hi, I need to test if an entity exist in a document set. For instance, test if there is any "Location" in a given set of 42 documents. I don't need to retrieve the actual rows, the count or whatever, but just the existence. Query A (get all Location in the corpus): select xxx from yyy where zzz execution time: 3 seconds (lots of results) Query B (exist) select 1 from dual where exists (select 1 from yyy where zzz) execution time: still 3 seconds Query C (limit) select xxx from yyy where zzz and rownum = 1 execution time: still 3 seconds I can't understand why query B and C are as slow as Query A. It should stop at the first matching row found, and before all of them are visited. But here it's like it does not make any difference at all. In others SGBD (MySQL mainly), using limit or exists in this case is very effective, performance wise. In Oracle, "exists" is just king of syntaxic sugar, or does it really means something for the optimizer and execution plan generated ? As I'am very new to the Oracle world, maybe I'm missing something obvious. But for the moment I'm stuck with pretty poor performance... -- Hugo |
correctly like it shouldn't take anywhere near 3 seconds. So the question
is where is it spending its time?
explain plan for
select ..... ;
select table(dbms_xplan.display) from dual;
Jim
#4
| |||
| |||
|
|
Hi, I need to test if an entity exist in a document set. For instance, test if there is any "Location" in a given set of 42 documents. I don't need to retrieve the actual rows, the count or whatever, but just the existence. Query A (get all Location in the corpus): select xxx from yyy where zzz execution time: 3 seconds (lots of results) Query B (exist) select 1 from dual where exists (select 1 from yyy where zzz) execution time: still 3 seconds Query C (limit) select xxx from yyy where zzz and rownum = 1 execution time: still 3 seconds I can't understand why query B and C are as slow as Query A. It should stop at the first matching row found, and before all of them are visited. But here it's like it does not make any difference at all. In others SGBD (MySQL mainly), using limit or exists in this case is very effective, performance wise. In Oracle, "exists" is just king of syntaxic sugar, or does it really means something for the optimizer and execution plan generated ? As I'am very new to the Oracle world, maybe I'm missing something obvious. But for the moment I'm stuck with pretty poor performance... -- Hugo |
correctly like it shouldn't take anywhere near 3 seconds. So the question
is where is it spending its time?
explain plan for
select ..... ;
select table(dbms_xplan.display) from dual;
Jim
#5
| |||
| |||
|
|
Hi, I need to test if an entity exist in a document set. For instance, test if there is any "Location" in a given set of 42 documents. I don't need to retrieve the actual rows, the count or whatever, but just the existence. Query A (get all Location in the corpus): select xxx from yyy where zzz execution time: 3 seconds (lots of results) Query B (exist) select 1 from dual where exists (select 1 from yyy where zzz) execution time: still 3 seconds Query C (limit) select xxx from yyy where zzz and rownum = 1 execution time: still 3 seconds I can't understand why query B and C are as slow as Query A. It should stop at the first matching row found, and before all of them are visited. But here it's like it does not make any difference at all. In others SGBD (MySQL mainly), using limit or exists in this case is very effective, performance wise. In Oracle, "exists" is just king of syntaxic sugar, or does it really means something for the optimizer and execution plan generated ? As I'am very new to the Oracle world, maybe I'm missing something obvious. But for the moment I'm stuck with pretty poor performance... -- Hugo |
correctly like it shouldn't take anywhere near 3 seconds. So the question
is where is it spending its time?
explain plan for
select ..... ;
select table(dbms_xplan.display) from dual;
Jim
#6
| |||||||
| |||||||
|
|
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? |
================================================== ====================
*Query A (select xxx from yyy where zzz)
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
Plan hash value: 143475067
-----------------------------------------------------------------------------------------------------
Quote:
|
Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |
Quote:
|
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
------------------------------------------------------------------------------------------------------
Quote:
|
Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |
Quote:
|
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)
------------------------------------------------------------------------------------------------------
Quote:
|
Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |
Quote:
|
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
#7
| |||||||
| |||||||
|
|
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? |
================================================== ====================
*Query A (select xxx from yyy where zzz)
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
Plan hash value: 143475067
-----------------------------------------------------------------------------------------------------
Quote:
|
Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |
Quote:
|
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
------------------------------------------------------------------------------------------------------
Quote:
|
Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |
Quote:
|
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)
------------------------------------------------------------------------------------------------------
Quote:
|
Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |
Quote:
|
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
#8
| |||||||
| |||||||
|
|
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? |
================================================== ====================
*Query A (select xxx from yyy where zzz)
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
Plan hash value: 143475067
-----------------------------------------------------------------------------------------------------
Quote:
|
Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |
Quote:
|
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
------------------------------------------------------------------------------------------------------
Quote:
|
Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |
Quote:
|
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)
------------------------------------------------------------------------------------------------------
Quote:
|
Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |
Quote:
|
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
#9
| |||||||
| |||||||
|
|
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? |
================================================== ====================
*Query A (select xxx from yyy where zzz)
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
Plan hash value: 143475067
-----------------------------------------------------------------------------------------------------
Quote:
|
Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |
Quote:
|
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
------------------------------------------------------------------------------------------------------
Quote:
|
Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |
Quote:
|
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)
------------------------------------------------------------------------------------------------------
Quote:
|
Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |
Quote:
|
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
#10
| |||
| |||
|
|
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_"."UIDENTITY" 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 ... read more » |
select unless the target of the select is a view?
In the second query why I think the exists is not doing what you
expect is because of the way the query is written Oracle cannot check
the exists until the entire subquery result set is returned. If you
could write the SQL as a coordinated subquery which would be fired
once for every row in the outer query then as soon as Oracle gott a
hit it could stop the sub-query.
Also if you run these queries back to back the result times may not be
reliable since the second and third queries potentially benefit or may
be adversly impacted by blocks cached by the previous queries.
HTH -- Mark D Powell --
 |
« 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.