Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Index design question

Re: Index design question

From: <michael_bialik_at_my-deja.com>
Date: Wed, 24 Nov 1999 21:13:06 GMT
Message-ID: <81hkct$dg1$1@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;


 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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US