Re: exists or limit
Date: Fri, 11 Jul 2008 07:45:06 -0700 (PDT)
Message-ID: <929784ed-5f14-4629-8196-1b5b585594d8@26g2000hsk.googlegroups.com>
On Jul 11, 8:32 am, Hugo <h..._at_nospam.invalid> wrote:
> 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_"."UIDENTITY")
>
> 7 - access("IDDOCUMENTLIST"=115)
>
> 8 - access("DOCUMENT4_"."IDDOCUMENT"="IDDOCUMENT")
>
> 10 - access("KNOWLEDGES5_"."IDDOCUMENT"="DOCUMENT4_"."IDDOCUMENT")
>
> 12 -
> access("POSITIONED6_"."IDKNOWLEDGESET"="KNOWLEDGES5_"."KNOWLEDGESET")
>
> 13 - access("POSITIONED6_"."UIDENTITY"="ENTITY7_"."UIDENTITY")
>
> 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_"."IDDOCUMEN
> T" AND
> "POSITIONED6_"."IDKNOWLEDGESET"="KNOWLEDGES5_"."KNOWLEDGESET" AND
>
> "POSITIONED6_"."UIDENTITY"="ENTITY7_"."UIDENTITY" AND
> "PARENTENTI8_"."DEPTH">0 AND
> "PARENTENTI8_"."IDPARENTENTITY"=41 AND
> "PARENTENTI8_"."UIDENTITY"="ENTITY7_"."UIDENTITY"))
> 3 - access("PARENTENTI8_"."UIDENTITY"="ENTITY7_"."UIDENTITY")
>
> 8 - access("IDDOCUMENTLIST"=115)
>
> 9 - access("DOCUMENT4_"."IDDOCUMENT"="IDDOCUMENT")
>
> 11 - access("KNOWLEDGES5_"."IDDOCUMENT"="DOCUMENT4_"."IDDOCUMENT")
>
> 13 -
> access("POSITIONED6_"."IDKNOWLEDGESET"="KNOWLEDGES5_"."KNOWLEDGESET")
>
> 14 - access("POSITIONED6_"."UIDENTITY"="ENTITY7_"."UIDENTITY")
>
> 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_"."UIDENTITY")
>
> 8 - access("IDDOCUMENTLIST"=115)
>
> 9 - access("DOCUMENT4_"."IDDOCUMENT"="IDDOCUMENT")
>
> 11 - access("KNOWLEDGES5_"."IDDOCUMENT"="DOCUMENT4_"."IDDOCUMENT")
>
> 13 -
> access("POSITIONED6_"."IDKNOWLEDGESET"="KNOWLEDGES5_"."KNOWLEDGESET")
>
> 14 - access("POSITIONED6_"."UIDENTITY"="ENTITY7_"."UIDENTITY")
>
> 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 »
I have trouble seeing how these explain plans match a single table 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 -- Received on Fri Jul 11 2008 - 09:45:06 CDT