Re: sql tuning question

From: Frank van Bortel <f.van.bortel_at_vnl.nl>
Date: Wed, 05 Jan 2000 11:00:52 -0800
Message-ID: <387394E4.BAAA28E7@vnl.nl>

kshave@health.gov.mb.ca wrote:

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.

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:

select /*+ ORDERED */ a.<some columns>, b.<more columns>
from  b,a                                 -- make b the driving table
where a.mynumber = b.mynumber
and   a.method   = :b1
and   a.mydate > to_date(:b2, 'RRRRMMDD') -- convert what is not known anyway, not what is
and   a.cheque is null                    -- why rtrim and check for null?

union

...
Or, hinting to use the index:

select /*+ INDEX(a index_name_of_a) */ a.<some columns>, b.<more columns>
from  a,b
where a.mynumber = b.mynumber
and   a.method   = :b1
and   a.mydate > to_date(:b2, 'RRRRMMDD')
and   a.cheque is null

union

...

--
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
  Received on Wed Jan 05 2000 - 13:00:52 CST