Re: OUTLINES

From: Niall Litchfield <niall.litchfield_at_dial.pipex.com>
Date: Mon, 17 Jul 2006 05:54:34 +0100
Message-ID: <NYKdnS1hKZiQhSbZRVnyuA@pipex.net>

Mladen Gogala wrote:
> On Sun, 16 Jul 2006 08:07:51 +0100, Jonathan Lewis wrote:
>

>> If you take the query 'select * from t1 where n1 = 1' and do:
>>     create stored outline for .....
>> then the text stored and used for matching will be exactly the text
>> you supplied.. But when you run the query with cursor sharing
>> enabled, the actual run-time text optimised will be more like:
>>     select * from t1 where n1 = ":SYS_B0"
>> and that won't match the text stored.

>
> Well, it doesn't make them very useful in these situations. That can be
> summarized by saying that outlines do work with cursor sharing, but not in
> any meaningful way. Oh, well.
>

My reading of the reply was not that, but that one had to work out what cursor_sharing would do to the query (or just capture the rewritten query) and duplicate that work.

i.e instead of doing

create stored outline for ' select * from t1 where n1=1'

do

create stored outline for 'select * from t1 where n1 = ":SYS_B0"'

I haven't tested this, and neither am I likely to when my clock still hasn't reached 6am.

-- 

Niall Litchfield
Oracle DBA
http://www.orawin.info/services/

Received on Sun Jul 16 2006 - 23:54:34 CDT