Re: Index design question

Hi !
The best index will be an index which results in an index-only-access .
In this case it is
CREATE INDEX Index1 on claim
(company_id,
open_date ,
close_date );

The optimizer has to read the index only , not the data of the table to find out the results.
To give the results for 'select *' he has to access the table. So if you need only the DESCRIPTION of your hits, test it with an index containing the field description also. This is the real index-only-acces and
is a very fast solution for retrieving (but some overhead at insertion).

Hth,
Reiner

<michael_bialik_at_my-deja.com> schrieb in im Newsbeitrag: 81f2a6$ida$1_at_nnrp1.deja.com...
> Hi.
>
> 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 - 04:16:54 CST