Re: Bug in Oracle V7 Hints Index_Desc

-- 
In article <32h5ta$24q_at_ccnet.ccnet.com>, tulcoc_at_ccnet.com (Ajoy Cherian) writes:


> Looks like there is a serious problem with  index_desc hints. The

> result we get with index_desc in the following case is shocking

> 

> 

> create table cfkey(

>  CFK_SCREEN_CODE                 VARCHAR2(5),

>  CFK_SCREEN_TYPE                 VARCHAR2(2),

>  CFK_CF_KEY                      VARCHAR2(2),

>  CFK_CF_SCRN_CODE                VARCHAR2(5),

>  CFK_CF_SCRN_TYPE                VARCHAR2(1),

>  CFK_FUNCTION_CODE               VARCHAR2(1));

> 

> 

> CREATE UNIQUE INDEX CFKSET1 ON

> 			CFKEY ( CFK_SCREEN_CODE ,

> 				CFK_SCREEN_TYPE,

> 				CFK_CF_KEY );


> 

 many INSERTs deleted 


> 

> 

> The following are the queries we tried and the output.

> Note that the second column in the output retreives a row 

> which doesn't satisfy the second condition of the where clause

> in case 1 and case 3.!!!!!!!!

> 

> case 1

> ------

>   1   SELECT  /*+ INDEX_DESC (CFKEY CFKSET1) */ *

>   2    FROM CFKEY

>   3    WHERE

>   4     CFK_SCREEN_CODE      ='DEFGH' AND

>   5     CFK_SCREEN_TYPE      =  'D'  AND

>   6*    CFK_CF_KEY           < 'D1'

> 

> CFK_S CF CF CFK_C C C  

> ----- -- -- ----- - -        

> DEFGH F  A0 defgh f x        

> DEFGH E  D0 defgh e d        

> DEFGH D  D0 defgh d 5        


> 


The syntax for the hint INDEX_DESC is: INDEX_DESC (table index(es)).
But I think you should read that as an index or indexes of a table 
and not include the table name as above (why would the table name 
be needed?). Thus the following produces the correct result:
                 (Change indicated below here)  
                            VVVVVVVV
SQL> SELECT  /*+ INDEX_DESC (CFKSET1) */ *
  2  FROM CFKEY  WHERE  CFK_SCREEN_CODE      ='DEFGH' 
  3  AND CFK_SCREEN_TYPE      =  'D'  AND CFK_CF_KEY           < 'D1';

CFK_S CF CF CFK_C C C
----- -- -- ----- - -
DEFGH D  D0 defgh d 5

Still this is a strange thing to happen.