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

From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Date: Tue, 2 Mar 2004 16:40:43 -0000

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@xxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

References:
- RE: query slow in 9i, but not slow in 8i
  - From: Jesse, Rich

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

Other related posts: