Re: index question

Hi,

  The 2 indexes are not the same.

  Think about a spreadsheet of data which is sorted on the above   fields. The results obviously will not be the same if the   spreadsheet is sorted in 2 different ways.

  Changing the index columns will affect the results in a positive/   negative method depending on the data usage.

  Some examples :

Scenario-1:

   Emp_name is known or used always for retrieval.    If empno+empname is a composite index and emp_no is not known, the    optimizer will go for a FULL TABLE SCAN hurting performance as it    did not have the leading field value in the index.

   Alternatively, if the index is empname+empno, then the index would    be used for the above scenario.

• Note : the index would still be used if we use partial values of the leading field in the index.

Scenario 2:

There is a composite field (say)

   Company_no + Location_no + Order_no.

 if the data spread contains the same value for the first 2 fields  but a unique range of numbers for every location of the company,  then it would be ideal if the index is defined as   Order_no + Location_no + Company for better hit.

 Again, if there are requirements to report for a company and given  location, then it would mean retaining the earlier index also in  addition to the reformed index shown above.

 To conclude, it depends on the usage and the pattern of data based  on which we could arrive at a implementation path.

 Hope this helps....

Regards
Rajagopal Venkataramany         

On Tue, 25 Jul 2000 21:13:50 -0800, ORACLE-L_at_fatcity.com wrote:

> hi gurus,
> this is my question.
> Will "Changing the column order in the index definition" affect the
> result?
>
> E.g.,
>
> Are the following statements same or what?
>
> Create unique index abc on table1(empno, ename)
>
> Create unique index abc on table1(ename, empno)
>
> --
> Author: paul
> INET: pjohnpeter_at_qssnet.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

Regards
Rajagopal Venkataramany