Re: sql tuning question

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Wed, 05 Jan 2000 18:52:21 GMT
Message-ID: <38739295.2559103@news.demon.nl>

On Wed, 05 Jan 2000 11:00:52 -0800, Frank van Bortel <f.van.bortel_at_vnl.nl> wrote:

><!doctype html public "-//w3c//dtd html 4.0 transitional//en">
><html>
>kshave_at_health.gov.mb.ca wrote:
><blockquote TYPE=CITE>I am having some trouble tuning a particular sql
>statement.
>explain plan for
> select a.<some columns>, b.<more columns> from a,b
> where a.mynumber = b.mynumber and a.method = :b1
> and to_char(a.mydate,'RRRRMMDD')<=:b2
> and rtrim(a.cheque) is null
>union
>select c.<some columns>, d.<more columns> from c,d
> where c.id = d.id and c.method = :b3
> and to_char(c.mydate,'RRRRMMDD')<=:b4
> and rtrim(c.cheque) is null
>order by <something>
> ;
>SELECT STATEMENT   Cost = 66882
>   SORT UNIQUE
>     UNION-ALL
>       HASH JOIN
>         TABLE ACCESS FULL b
>         TABLE ACCESS FULL a
>       NESTED LOOPS
>         TABLE ACCESS FULL c
>         TABLE ACCESS BY ROWID d
>           INDEX UNIQUE
>SCAN PK_d
>A - 3.5 million rows
> B - 700 rows
> C - 77 rows
> D - 700 rows
>The bottom portion of this query is fine. The top portion is what I
> have a problem with.
>Table a has a non-unique index set up on the 'mynumber' column (which
> is not being used, as you can see from the explain plan output). Table
> b has a primary key (and unique index) set up on the 'mynumber' column.
>Is there anyway to use an index on table a?
>--
> -Keith
>Sent via Deja.com http://www.deja.com/
> Before you buy.</blockquote>
>
> Do you want that? Using an index is not always the most effecient
>way to access data.
> As a rule of thumb: when you retrieve more than 15% of a table, a full
>scan will be
> more efficient than an index scan, followed by a tale access by rowid.
> AFAIK, the optimizer uses this rule of thumb too, but sets my 15% to
>5% (!).
> You might try:<tt></tt>
><tt>select /*+ ORDERED */ a.<some columns>, b.<more columns></tt>
> <tt>from  b,a                                
>-- make b the driving table</tt>
> <tt>where a.mynumber = b.mynumber</tt>
> <tt>and   a.method   = :b1</tt>
> <tt>and   a.mydate > to_date(:b2, 'RRRRMMDD') -- convert
>what is not known anyway, not what is</tt>
> <tt>and   a.cheque is null                   
>-- why rtrim and check for null?</tt><tt></tt>
><tt>union</tt><tt></tt>
>...
> Or, hinting to use the index:
><tt>select /*+ INDEX(a index_name_of_a) */ a.<some columns>, b.<more
>columns></tt>
> <tt>from  a,b</tt>
> <tt>where a.mynumber = b.mynumber</tt>
> <tt>and   a.method   = :b1</tt>
> <tt>and   a.mydate > to_date(:b2, 'RRRRMMDD')</tt>
> <tt>and   a.cheque is null</tt><tt></tt>
><tt>union</tt><tt></tt>
>...
>--
> Met vriendelijke groet/kind regards,
>Frank van Bortel
> Technical consultant Oracle
>Work:                               
>Home:
> ----------------------------------   ----------------------------
> Inter Access V&L                    
>Hunzestraat 4
> Palatijn 3, 7521 PN Enschede        
>7555 WB Hengelo
> PoBox 545, 7500 AM Enschede         
>(31)074-2425046
> 053-4341500
>  </html>
>

Your contributions are always worthwile, but please remember there are people out there who can't read html posts (all forte free agent users)

Hth,

Sybrand Bakker, Oracle DBA Received on Wed Jan 05 2000 - 12:52:21 CST