RE: index hint ignored?

[Janardhana Babu Donga]

Title: Message

Try Analyzing the table first and issue the select stmt.

 

-- Babu

-----Original Message-----
From: Cunningham, Gerald [mailto:[EMAIL PROTECTED]]
Sent: Friday, January 31, 2003 11:56 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: index hint ignored?

I've tried it both ways, with the comma and without - same result.

 

 

-----Original Message-----
From: K Gopalakrishnan [mailto:[EMAIL PROTECTED]]
Sent: Friday, January 31, 2003 2:13 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: index hint ignored?

Hi,

 

THe syntax is incorrect. Incorrect HINTs are treated as comments.

replace the comma with blank space and your hint will work

as expected.

 

Best Regards,
K Gopalakrishnan

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Cunningham, Gerald
Sent: Friday, January 31, 2003 10:31 AM
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                             1

 

VEHICLE                        VEHICLE_VEH_YEAR_INDX
VEH_YEAR                           1

 

 

 

Thanks for any help!

 

- Jerry