RE: Order by question - newbie

[Christopher Spence]

Title: Message

If your grabing all rows, and your calling all columns (some in order, some display) why wouldn't it do a fts?
There is no reason to use the index.

Now if you overloaded the index, it may opt to use the index, but to what avail?  It is is the same size of the table.

 

In overloading, I refere to puting PersonalCode at the end of the index you created, this way all teh data will be available from the index, there will be no need to go to the table, altough the table will be the same size as the index, but the index will already be sorted.

 

Perhaps you may want to look at IOT, if this isn't high write table.

 

 

"Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes."

Christopher R. Spence
Oracle DBA
Phone: (978) 322-5744
Fax:    (707) 885-2275

Fuelspot
73 Princeton Street
North, Chelmsford 01863
 

-----Original Message-----
From: Csillag Zsolt [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, August 28, 2001 3:37 PM
To: Multiple recipients of list ORACLE-L
Subject: Order by question - newbie

Hi,

I have a table with 3 colums :  PersonalCode,  Name and  SpecialOrder.

The PersonalCode is the primary index and I have also an index  on SpecialOrder,Name columns.

However the following select:

Select PersonalCode From MyTable
  Order By SpecialOrder,Name

doesn't use the  SpecialOrder,Name index at all.
If I watch it in Explain Plan window I can see that :

SELECT STATEMENT, GOAL = CHOOSE                 1181    16378   2456700
 SORT ORDER BY                  1181    16378   2456700
  TABLE ACCESS FULL     ADMIN   MyTable 69      16378   2456700

How can I make the index work?


Thank you in advance

Zsolt Csillag
Hungary