Re: Concatenated Indexes V6.0.27+

In <CB6ru5.Mz4_at_shakti.ncst.ernet.in> hemant_at_shakti.ncst.ernet.in (STOCKHOLDING CORPORATION OF INDIA LTD.) writes:

>About my query on Concatenated Indexes
> (AIRLINE + TCKT_NO)
 
> I think some people have misunderstood the question.

I don't believe I did.

> I WILL be running a query using both fields.
> Yet , I want to know whether ordering the fields in the Index
> makes a difference.
 

> I understand that it does not make a difference how I order the
> fields in the Query itself, the optimiser will identify that there
> exists an Index on both fields and will use it.
 

> But, somewhere, the DBA Guide says that a concatenated index
> should be created such that the more selective field is first
> in the key-list.
> That is why I wish to know whether AIRLINE+TCKT_NO is
> better or TCKT_NO+AIRLINE is better,
> assuming that TCKT_NO is more selective.

More selective first would be theoretically more efficient. Nevertheless I would recommend you go the other way unless:

1. You can guarantee that you never query for or otherwise process all (or many) TCKT_NOs for the same AIRLINE, or sort by AIRLINE (I am assuming you never sort by TCKT_NO/AIRLINE or TCKT_NO alone).

    or	b)  You also have an index on AIRLINE (which should become an
	    index on AIRLINE+TCKT_NO unless (typical # tickets per
	    airline) < ((total # of tickets) / 10) )

If you do, let's say >35% of your business with one airline then I could be persuaded that putting TCKT_NO first might make more sense (subject to the reqt above).

If you really believe that the difference is important in your particular scenario, then you *should* be carrying out tests to *measure* the difference in your particular scenario, so you can make an informed judgement.

> The supplementary questions are :
> 1. What if TCKT_NO is a number field ?

better as previously explained.

> 2. What if the combination is UNIQUE ?

then make it a UNIQUE index.

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.