Re: exists or limit

From: Hugo <hugo_at_nospam.invalid>
Date: Fri, 11 Jul 2008 17:08:15 +0200
Message-ID: <487777aa$0$21057$426a74cc@news.free.fr>


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
Received on Fri Jul 11 2008 - 10:08:15 CDT

Original text of this message