| Forcing an index in a query [message #373669] |
Tue, 01 May 2001 05:01  |
Nagaraju Korada
Messages: 3
Registered: April 2001
|
Junior Member |
|
|
i have an index idx1 on a table say temptable
index idx1 is comprised of a,b and c columns.
now index gets used when we query with a following condition say:
where a = 1 and b= 2 and c=3
but index doesn't get used when we give a condition like
where a = 1 and b = 2 and c >= 3
but i want to force the index to be used:
so i gave the following query, but still it is slow and the index is not used
select /*+ INDEX(t idx1) */
a,b,c
from temptable t
where a = 1 and b = 2 and c >= 3
can anybody help me on this?
i don't understand what is wrong with this?
|
|
|
|
| Re: Forcing an index in a query [message #373677 is a reply to message #373669] |
Tue, 01 May 2001 12:18  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
It works fine for me in 8.1.5
create table t (a number, b number, c number);
create index idx1 on t(a, b, c);
-- no data in table, no stats gathered
select /*+ INDEX(t idx1) */
a,b,c
from t
where a = 1 and b = 2 and c >= 3
SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=78)
..INDEX (RANGE SCAN) OF IDX1 (NON-UNIQUE) (Cost=2 Card=1 Bytes=78)
select /*+ INDEX(t idx1) */
a,b,c
from t
where a = 1 and b = 2 and c = 3
SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=78)
..INDEX (RANGE SCAN) OF IDX1 (NON-UNIQUE) (Cost=1 Card=1 Bytes=78)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
