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

From: "Jesse, Rich" <Rich.Jesse@xxxxxxxxxxxxxxxxx>
To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
Date: Tue, 2 Mar 2004 10:49:39 -0600
Woo-hoo!  Hopefully we'll have time to try this out.  This is the approach I
want to look at to ease some of our BidnessObjects queries that are hitting
our OLTP DB (hey, I haven't been able to justify a DW/DM for *7* years now).
I never thought of this approach for this problem.

Thanks!
Rich

Rich Jesse                        System/Database Administrator
rich.jesse@xxxxxxxxxxxxxxxxx      QuadTech, Sussex, WI USA


> -----Original Message-----
> From: Jonathan Lewis [mailto:jonathan@xxxxxxxxxxxxxxxxxx]
> Sent: Tuesday, March 02, 2004 10:41 AM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Re: query slow in 9i, but not slow in 8i
> 
> 
> 
> 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
-----------------------------------------------------------------
RE: query slow in 9i, but not slow in 8i

Other related posts:
