Re: ***SPAM*** Re: Plea for Query Tuning Help

Charlotte,

My guess is that there is an index on id that gets used correctly when the query
is of the form:

SELECT *
FROM NASTY_VIEW
WHERE id = :bind

but this "good" index isn't used in the right part of the plan in the other querys.

A suggestion, may not be the most maintainable approach, is to find out what that index is and then hint the query so that that index is used in the right place. For example if the index is GOOD_IDX on table BIG_TABLE then you could try the following:

SELECT /*+ index(NASTY_VIEW.BIG_TABLE GOOD_IDX) /* * FROM NASTY_VIEW
WHERE id = (SELECT :bind FROM dual)

or

SELECT /*+ leading(NASTY_VIEW.BIG_TABLE) /* * FROM NASTY_VIEW
WHERE id = (SELECT :bind FROM dual)

or a combintaion of the two:

SELECT /*+ leading(NASTY_VIEW.BIG_TABLE) index(NASTY_VIEW.BIG_TABLE GOOD_IDX) /* *
FROM NASTY_VIEW
WHERE id = (SELECT :bind FROM dual)

of course assuming that BIG_TABLE isn't aliased in the view. If it is then you must use the alias name in place of BIG_TABLE in the hints.

In case you don't know this type of hint on a table within a view is called a global hint for more details see "Global Hints" in chapter 5 of the Data Performance Tuning Guide and Reference.

I hope this helps.

Chris

Quoting Charlotte Hammond <charlottejanehammond_at_yahoo.com>:

> Hi All,
>
> Thanks to all who have responded both on and off the
> list. A couple of people have asked me to post the
> plan but it is huge (due to NASTY_VIEW) so I'd rather
> not (at least yet!) as it'd probably be more
> off-putting than anything else :-)
>
> Essentially I just want the WHERE id = on NASTY_VIEW
> to treat the right value the same if it's a bind or if
> it's coming from a sub-query. Many good suggestions
> but no luck yet. I thought I'd post the results to
> see if anyone can spot anything from them...
>
> Here's an even simpler example:
>
> The Good case again:
>
> SELECT *
> FROM NASTY_VIEW
> WHERE id = :bind
>
> --> < 1 sec, (CBO=410)
>
>
> Any now some attempts to use a value from a SELECT.
>
> 1. Simple select:
>
> SELECT *
> FROM NASTY_VIEW
> WHERE id = (SELECT :bind FROM dual)
>
> --> long time (CBO=17378)
>

Chris Dunscombe

www.christallize.com

--
http://www.freelists.org/webpage/oracle-l