Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Index design question
Thanx, but no go!
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
WHERE company_id = :p1 AND close_date between :p2 AND :p3;
Thank you again for your response. Michael.
In article <81ge0e$6kr$1_at_mail.messe.de>,
"Reiner Neumann" <reiner.neumann_at_messe.de> wrote:
> 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.
>
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Wed Nov 24 1999 - 15:13:06 CST