Thanks, Lisa.
This
particular table is rather small (~20,000 rows, 1.3 Mb in size, 20 distinct
values for VEH_YEAR) - so it may be faster doing a FTS scan, not sure. I was
mainly wondering why my hint was ignored. It's a WebSphere app (The person who
wrote it is long gone!) where this statement gets executed thousands of times a
day, so if I could shave some time off the query it may
help...
Thanks
again!
-
Jerry
-----Original Message-----
From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]
Sent: Friday, January 31, 2003 1:56 PM
To: [EMAIL PROTECTED]; Cunningham, Gerald
Subject: RE: index hint ignored?Hi Jerry,Methinks it's because this is a small table. 20 records? Peanuts. Why bother with the index.On the same token you should probably not spend a lot of time worrying about this... unless this is just a learning exercise :)hthLisa Koivu
Oracle Diaper Administrator
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA 33063-----Original Message-----
From: Cunningham, Gerald [mailto:[EMAIL PROTECTED]]
Sent: Friday, January 31, 2003 1:31 PM
To: Multiple recipients of list ORACLE-L
Subject: index hint ignored?Hi there.I have a non-unique index on a table, and I'm trying to force Oracle to use the index - but it always does a FTS. Why? (I've tried it with and without the alias)SQL> set autotrace traceonly
SQL> SELECT /*+ INDEX(A,vehicle_veh_year_indx) */ DISTINCT veh_year
2 FROM TIREADVISOR.vehicle A
3 ORDER BY veh_year DESC;20 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=118 Card=20 Bytes=80)1 0 SORT (ORDER BY) (Cost=118 Card=20 Bytes=80)
2 1 SORT (UNIQUE) (Cost=67 Card=20 Bytes=80)
3 2 TABLE ACCESS (FULL) OF 'VEHICLE' (Cost=16 Card=19607 Bytes=78428)===select TABLE_NAME, INDEX_NAME, COLUMN_NAME, COLUMN_POSITION
from dba_ind_columns
where INDEX_OWNER = 'TIREADVISOR'
and TABLE_NAME = ('VEHICLE') order by 1,2,4,3
TABLE_NAME INDEX_NAME
------------------------------ ------------------------------
COLUMN_NAME COLUMN_POSITION
-------------------- ---------------
VEHICLE VEHICLE_PK
VEH_ID 1VEHICLE VEHICLE_VEH_YEAR_INDX
VEH_YEAR 1Thanks for any help!- Jerry