Re: indexing "select" columns

From: Leon <lrzhemov_at_home.com>
Date: 5 Jun 2001 16:51:36 -0700
Message-ID: <8cb139cf.0106051551.3794ee4f@posting.google.com>

Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:<7ttphtsacss9filitl0trb7q09rd97fd34_at_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
> >
> >

Hi Tom,

I did not know that you switch your interests from “Ask Tom” to public forums. Yes I am interested in speed and efficiency improvement for my environment. I want to “replace” lookup tables to lookup indexes. I can handle 200 db calls per second on low-end server. I want to have more. I am using real time database of stock Options available in North-America. There are about 200’000 options for 5’000 firms. I do not need larger scale test, I want improvements for today. In all my tests short indexes produce result FASTER then indexes with data inside (Data and indexes cached) . I test by executing my queries for all symbols in a database and it should be enough for me, it is real life. I do not need improvements for VLDB. According to result please provide me with methodology how to check is table actually have been used or it is index-only query. Unless this kind of methodology exists index-only queries are undocumented and questionable feature of Oracle.

According to IOT please execute script and see the error message

CREATE TABLE io(a INT, b INT, c INT, d INT,

                primary key(c,b))

ORGANIZATION INDEX
TABLESPACE OPT STORAGE(INITIAL 1M NEXT 1M PCTINCREASE 0 ) CACHE ; You can try to create other indexes rather then primary one and see other ora errors. You can try to manage extra overflow tablespace IOT is far from being useful for me right now. The most important I have implemented test case for IOT and did not find performance improvement compare to regular table.

Thanks
Leon Received on Tue Jun 05 2001 - 18:51:36 CDT