Re: Concatenated Indexes version 6.-0 0.28 and above

In <CB0uH4.4xL_at_shakti.ncst.ernet.in> hemant_at_shakti.ncst.ernet.in (STOCKHOLDING CORPORATION OF INDIA LTD.) writes:

>Re : Concatenated Indexes in Oracle v6.0.28 and above
 

>How does the selectivity of the columns used, and the ordering
>in the index make a difference to performance ?
 

>For example, the two columns to be indexed could be
> AIRLINE (char)
> TCKT_NO (char)
>where TCKT_NO is more 'selective' (fewer, or no, duplicate /
>multiplicate rows than for AIRLINE).
 

>Should the index be AIRLINE + TCKT_NO or
>TCKT_NO + AIRLINE.
It depends very much on how you intend to use it. An index on AIRLINE + TCKT_NO will be used by queries that reference both columns in the WHERE clause, and by queries that only reference AIRLINE. It will not be used by queries which only reference TCKT_NO.

An index on TCKT_NO + AIRLINE will be used by queries that reference both columns in the WHERE clause, and by queries that only reference TCKT_NO. It will not be used by queries which only reference AIRLINE.

My gut feel (judging by your column names) is that AIRLINE + TCKT_NO would be the better choice:

This index would be highly selective (presumably unique) and would be a good candidate for replacement of an index on AIRLINE alone, which is probably not selective enough for efficient maintenance. It would speed up queries which were ordering by AIRLINE or selecting a particular AIRLINE to a (broadly speaking) equivalent degree, but will impose less overhead when DELETing, INSERTing or UPDATing. It will also allow "instant" access to a particular ticket PROVIDING THAT THE AIRLINE IS KNOWN AND IS COMMUNICATED TO THE OPTIMISER VIA THE WHERE CLAUSE. (it sees to me unlikely that you would know the ticket number without knowing the airline).

Whereas an index on TCKT_NO + AIRLINE would be:

Equally selective (presumably unique), would NOT be a candidate for replacement of an index on AIRLINE, and brings no significant benefits as an alternative to an index on TCKT_NO, which is (presumably) already highly selective.

>Does it make a difference if TCKT_NO is number.

This would be preferable as it reduces the length of the index entry thereby reducing disk space required (by a measurable amount, but probably not by a significant amount relative to the total storage requirement for the table and all associated indexes) and therefore also improving access times (by a probably not significant and probably not measurable amount, unless the difference is sufficient to reduce the depth of the ISAM tree).

>Does it make a difference if the combination is Unique and,
>therefore, a Unique Index is created ?

I would not expect to see a significant difference. In theory, a unique index is more efficient than a duplicate index containing unique values. IMHO if TCKT_NO is unique for a particular AIRLINE (as I assume it would be) then you *should* create a unique index. If there is any possibility that an AIRLINE might issue (or more likely re-issue) a ticket with a duplicate number, then your application probably ought to provide specific logic to cope with this event, in which case your index probably should have a third component to *allow* an individual realworld  piece of paper to be tracked.

>What is the case in other DBMSs ?

The above discussion is intended to be generic (to any RDBMS where the index would be b-tree based) and not specific to Oracle V6.

graeme

--
Disclaimer:	The author's opinions are his own, and not necessarily
		those of Pyramid Technology Ltd. or Pyramid Technology Inc.
---------------------------------------------------------------------------
      -m------- Graeme Sargent                 Voice: +44 (0)252 373035
    ---mmm----- Senior Database Consultant     Fax  : +44 (0)252 373135
  -----mmmmm--- Pyramid Technology Ltd.        Telex: Tell who???
-------mmmmmmm- Farnborough, Hants  GU14 7PL   Email: graeme_at_pyra.co.uk
---------------------------------------------------------------------------
    We have the technology.  The tricky bit is learning how to use it.