sql tuning question

From: <kshave_at_health.gov.mb.ca>
Date: Tue, 04 Jan 2000 16:10:48 GMT
Message-ID: <84t618$937$1@nnrp1.deja.com>

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. Received on Tue Jan 04 2000 - 10:10:48 CST