Re: Index design question

Toss the union and use your original sql with the two indexes. You need 2 because of the OR clause means the 2 where conditions are   evaluated seperately

michael_bialik_at_my-deja.com wrote in message <81hkct$dg1$1_at_nnrp1.deja.com>...
>Thanx, but no go!
>
> 1. I have about 50 fields in CLAIM table, so defining all of them in
> index is not feasible.
> 2. When using such an index with the optimizer ( I tried CBO and RBO )
> actually disregards condition for OPEN_DATE fields and performs
> INDEX RANGE SCAN for all company's claims ( and it takes a LOT of
> time ).
>
> I'm going to try using 2 indexes instead:
> IND_1 ( company_id, open_date )
> IND_2 ( company_id, close_date )
>
> and
>
> SELECT * FROM claim
> WHERE company_id = :p1 AND
> open_date between :p2 and :p3
> UNION
> SELECT * FROM claim
> WHERE company_id = :p1 AND
> close_date between :p2 AND :p3;
>
>

> > I have a claim system :
> >
> > CREATE TABLE claim (
> > icompany_d NUMBER(4),
> > claim_no NUMBER(12),
> > open_date DATE,
> > close_date DATE,
> > description VARCHAR2(30),
> > constraint claim_pk primary key ( company_id, claim_no));
> >
> > I need to write a query that performs :
> >
> > SELECT * FROM claim
> > WHERE company_id = :p1 AND
> > ( open_date between :p2 and :p3 OR
> > close_date between :p2 AND :p3 );
> >
> > What do you think is the best index ( or indexes ) to support
> > such query?
> >
> > TIA. Michael.
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>
Received on Wed Nov 24 1999 - 15:33:51 CST