On Feb 27, 1:47 pm, Frank van Bortel <frank.van.bor..._at_gmail.com>
wrote:
> John.Plan..._at_googlemail.com schreef:
>
>
>
> > Rows Execution Plan
> > ------- ---------------------------------------------------
> > 0 SELECT STATEMENT MODE: ALL_ROWS
> > 0 SORT (ORDER BY)
>
> Why all_rows? Are you using Java as a reporting tool?
>
> And I would suspect a rewrite of:
>
> SELECT id,code,startsat,endswith,ref
> FROM
> PRDS WHERE startsat>= :1 AND ord = -1 AND id IN (SELECT id FROM
> PRJS WHERE proj = :2) ORDER BY id,code
>
> To:
>
> SELECT prds.id
> , prds.code
> , prds.startsat
> , prds.endswith
> , prds.ref
> FROM
> PRDS, PRJS
> WHERE prds.startsat>= :1
> AND prds.ord = -1
> AND prds.id = prjs.id
> and prjs.proj = :2
> ORDER BY id,code
>
> would be faster - in lists tend to be slow, especially when they
> grow - which depends on the value of bind variable :2 (which you
> did not change, so this is off topic...)
> Depending on the definition, the join on id may need an outer join.
> --
> Regards,
> Frank van Bortel
>
> Top-posting is one way to shut me up...
It looks like the range scan on PRJS_IDI index reads a lot more data
in the second exeecution than in the first (compare *query* values
between the two executions). You should make sure that the exact same
values are bound to the second query and if so, open a case with
Oracle as this may be a bug.
Received on Tue Feb 27 2007 - 16:17:54 CST
