Re: sql tuning question

Hi.

 I would try following ( regarding first part of query only ):

1. Drop an index on table_a ( by mynumber field). Create an index on table_a with ( mynumber, method, mydate ) fields.
2. Rewrite the query: select 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 rtrim(a.cheque) is null ...

 HTH. Michael.

 P.S. It would be nice of you to post the solution ( eventually ).

In article <84t618$937$1_at_nnrp1.deja.com>,   kshave_at_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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Jan 05 2000 - 15:12:46 CST