Re: exists or limit

On Jul 11, 8:08 am, Hugo <h..._at_nospam.invalid> wrote:
> 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

Look up "plan stability" in the docs, like at http://tahiti.oracle.com

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