Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: indexing "select" columns

Re: indexing "select" columns

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 05 Jun 2001 11:20:15 -0400
Message-ID: <7ttphtsacss9filitl0trb7q09rd97fd34@4ax.com>

On Tue, 5 Jun 2001 00:14:37 +0200, you wrote:

>
>"Leon" <lrzhemov_at_home.com> wrote in message
>news:8cb139cf.0106041232.192801d6_at_posting.google.com...
>> I hope Oracle would use only index to select information from the
>> table as soon as all information is present in an index, so
>> select col2
>> from table1
>> where col1= value
>> would use only index as soon as index
>> CREATE UNIQUE INDEX12 ON table1(COL1, COL2)
>> exists
>> Unfortunately statistics for select like that shows that speed of this
>> query is exactly the same as with index "CREATE UNIQUE INDEX1 ON
>> table1(COL1)"
>>
>> Does anybody has good experience in using "index only" (simulating
>> IOT) queries?
>> Potentially it should be faster then IOT as soon as indexes could be
>> cached in memory but IOT can't.
>>
>> Thanks
>> Leon
>
>Your example is not going to work.
>To have an index only query, the columns referred to must be *all* present
>in the select list, or there shouldn't be any column at all
>(like 'x', 1, or count(*))
>
>Please try it.

the above will work -- the column on the leading edge of the index is in the predicate (where col1 = value) and the other column is selected - they need not both be selected.

select col2 from table1 where col1=value

will use the index on table1(col1,col2)

he is saying that there doesn't seem to be any speed up i believe.

In order to see the speedup, he'd have to do a much more aggresive test then he probably is. If you test in isolation and everything is cached, you won't see much different -- whats 2 logical ios versus 1 after all (in isolation).

scale it up and the query that can be answered from the index will be faster, make better use of the buffer cache (don't have to cache that table block), ultimately do less physical io because of that -- over all it'll be faster.

My question to him is -- why on earth do you believe that IOT's are NOT cached (they are).

>
>Hth,
>
>Sybrand Bakker, Oracle DBA
>
>

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp 
Received on Tue Jun 05 2001 - 10:20:15 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US