Re: Index design question

Thank you for your help.

 I implemented it and it works just fine ( with B-tree indexes ).  Is it possible to use bitmap indexes here ?   As far as I know they are useless for supporting range-scan queries.

 Michael.

In article <943815641.3199.0.nnrp-04.9e984b29_at_news.demon.co.uk>,   "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
> Trying to infer sensible ideas from the
> column and table names:
>
> How many different company_ids in the table.
> For a claim system I would guess from the
> column name that there would be few
> (e.g.: company id is the company that writes
> the policy, or paying out on the policy).
>
> Open date is likely to give a high-quality
> index because all the claims opened on a
> given date are probably going to be inserted
> into the table at about the same time, leading
> to a good cluster factor.
>
> Close date is likely to be a mediocre or
> poor index because claims are likely to
> close in a fairly random fashion, although
> the target is presumably to close a claim
> within a given number of days of opening
> it.
>
> Based on these assumptions I would favour:
> (open_date, company_id)
> (close_date, company_id)
> if you have to have B-tree indices, probably
> using a compressed index (v8.1) on both
> indices.
>
> If you are in a position to use bitmap indices -
> i.e. this is the DSS backend and not the OLTP
> front-end of a large system, then I would go
> for three bitmap indices, one on each column.
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> michael_bialik_at_my-deja.com wrote in message <81f2a6
$ida$1_at_nnrp1.deja.com>...
> >Hi.
> >
> > I have a claim system :
> >
> > CREATE TABLE claim (
> > company_id 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?
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun Nov 28 1999 - 14:44:43 CST