[long post] Re: exists or limit

From: Hugo <hugo_at_nospam.invalid>
Date: Fri, 11 Jul 2008 14:32:45 +0200
Message-ID: <48775338$0$6991$426a74cc@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

| 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

| 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)

| 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

Received on Fri Jul 11 2008 - 07:32:45 CDT

This message: [ Message body ]
Next message: Brandon: "nested select problem"
Previous message: Charles Hooper: "Re: Select max within max"
In reply to: gym dot scuba dot kennedy at gmail: "Re: exists or limit"
Next in thread: Mark D Powell: "Re: exists or limit"
Reply: Mark D Powell: "Re: exists or limit"
Reply: gym dot scuba dot kennedy at gmail: "Re: [long post] Re: exists or limit"

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

Original text of this message