Re: query slow in 9i, but not slow in 8i

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 2 Mar 2004 16:40:43 -0000
Message-ID: <013201c40075$1b490380$6702a8c0@Primary>

Any options for:

create index abc on t1(decode(col_flag,'N','N',null));

create view v1 as

        select {all_columns},decode(col_flag,'N','N',null)) flag_N
        from t1;

    Rewrite code in application to query v1 on flag_N
        (or t1 on the decode() expression if you prefer)

set the query_rewrite parameters as required by your version.

The resulting index will be very small, and reference only the rows you need.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

March 2004 Hotsos Symposium - The Burden of Proof Dynamic Sampling - an investigation
March 2004 Charlotte OUG (www.cltoug.org) CBO Tutorial April 2004 Iceland
June 2004 UK - Optimising Oracle Seminar

Original Message ----- From: "Jesse, Rich" <Rich.Jesse_at_quadtechworld.com> To: <oracle-l_at_freelists.org> Sent: Tuesday, March 02, 2004 3:39 PM Subject: RE: query slow in 9i, but not slow in 8i

> My opinion is that I'm not at all good at coming up with tangible costs
> associated with poor design. So now we have a program that checks a
2M-row
> table every 15 minutes to see if there are any new 'N' values in a Y/N
> column. And since we're on 8i using CS=F to compensate for a lack of
binds
> (my ignorance and lack of emphasis in Oracle classes are to blame for that
> one), we get an FTS on that table every 15 minutes. You can imagine
what's
> happening to our buffer cache. I'm hoping to compensate once again in 9i
by
> allowing the optimizer to peek at the binds while providing a histogram on
> that indexed column, so that it'll use it to get the 5-10 rows out of the
> 2M.
>
> Rich

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue Mar 02 2004 - 10:37:42 CST