Re: sql tuning question

Quick demonstration the index will never be used... Actually you are writing
select a.<some columns>

       , b.<more columns> from a,b
where b.mynumber = a.mynumber and a.method = :b1  /* this should have been a.mydate > to_date(:b2, 'RRRRMMDD', but that's not the issue here */
and to_char(a.mydate,'RRRRMMDD')<=:b2
and rtrim(a.cheque) is null

There are NO (non-join) criteria on the column a.mynumber, there are criteria on other columns (method, mydate), but those columns probably contain only very few values, so Oracle will choose a full table scan anyway.
The only thing that probably would help is an index on mydate or a combination of method and mydate, provided you change the where clause.

Hth,

--
Sybrand Bakker, Oracle DBA
<kshave_at_health.gov.mb.ca> wrote in message news:84t618$937$1_at_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 - 12:12:23 CST