Re: Index design question

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?
Received on Sun Nov 28 1999 - 12:59:03 CST