Re: [long post] Re: exists or limit

From: gym dot scuba dot kennedy at gmail <kennedyii_at_verizon.net>
Date: Fri, 11 Jul 2008 15:19:12 GMT
Message-ID: <QRKdk.10$6O4.4@trnddc06>

"Hugo" <hugo_at_nospam.invalid> wrote in message news:48775338$0$6991$426a74cc_at_news.free.fr...
> 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 matching line, it should
> be as fast in both cases (assuming a matching line could be found in
> almost every document).
>
> --
> Hugo

Please post the actual SQL statements not the summary. Also identify any 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 Received on Fri Jul 11 2008 - 10:19:12 CDT

This message: [ Message body ]
Next message: Mladen Gogala: "Re: Select max within max"
Previous message: Hugo: "Re: exists or limit"
In reply to: Hugo: "[long post] Re: exists or limit"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message