| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: query slow in 9i, but not slow in 8i
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_at_quadtechworld.com QuadTech, Sussex, WI USA
> -----Original Message-----
> From: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk]
> Sent: Tuesday, March 02, 2004 10:41 AM
> To: oracle-l_at_freelists.org
> 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_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
>
-- 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:47:16 CST
 |
 |