Re: Bug in Oracle V7 Hints Index_Desc

Andrew Deacon (deacon_at_inf.ethz.ch) wrote:
: --
: 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

^^^^^

   If the sytax is changed to index_desc(index_name), then oracle no    longer uses the hint and it will be a full table scan if there are    no other indexes defined or based on some other index if it can.    But the point is it no longer uses index_desc. In the above eg    u got the correct result because D0 happened to be "physically"    before 'D1'. Change the where condition from < 'D1' to < 'D3'    You will get rows in the order D1 D2 and then D0 which is not    what index_desc is supposed to do.

: Still this is a strange thing to happen.

   ^^
   I have regsitered this a TAR with oracle. let's see what they got    to say.

Regards
 Ajoy     Received on Thu Aug 18 1994 - 18:26:45 CEST