Bug in Oracle V7 Hints Index_Desc

From: Ajoy Cherian <tulcoc_at_ccnet.com>
Date: 12 Aug 1994 17:59:22 -0700
Message-ID: <32h5ta$24q_at_ccnet.ccnet.com>

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 );


insert into cfkey values ('BCDEF','B','B1','bcdef','b','2');
insert into cfkey values ('CDEFG','C','C1','cdefg','c','3');
insert into cfkey values ('DEFGH','D','D1','defgh','d','4');
insert into cfkey values ('EFGHI','E','E1','efghi','e','5');
insert into cfkey values ('abcde','a','a1','ABCDE','A','1');
insert into cfkey values ('bcdef','b','b1','BCDEF','B','2');
insert into cfkey values ('cdefg','c','c1','CDEFG','C','3');
insert into cfkey values ('defgh','d','d1','DEFGH','D','4');
insert into cfkey values ('efghi','e','e1','EFGHI','E','5');
insert into cfkey values ('ABCDE','A','A2','abcde','a','6');
insert into cfkey values ('BCDEF','B','B2','bcdef','b','7');
insert into cfkey values ('CDEFG','C','C2','cdefg','c','8');
insert into cfkey values ('DEFGH','D','D2','defgh','d','9');
insert into cfkey values ('EFGHI','E','E2','efghi','e','0');
insert into cfkey values ('abcde','a','a2','ABCDE','A','6');
insert into cfkey values ('bcdef','b','b2','BCDEF','B','7');
insert into cfkey values ('cdefg','c','c2','CDEFG','C','8');
insert into cfkey values ('defgh','d','d2','DEFGH','D','9');
insert into cfkey values ('efghi','e','e2','EFGHI','E','0');
insert into cfkey values ('ABCDE','B','A1','abcde','a','a');
insert into cfkey values ('BCDEF','C','B1','bcdef','b','b');
insert into cfkey values ('CDEFG','D','C1','cdefg','c','c');
insert into cfkey values ('DEFGH','E','D1','defgh','d','d');
insert into cfkey values ('EFGHI','F','E1','efghi','e','e');
insert into cfkey values ('abcde','b','a1','ABCDE','A','A');
insert into cfkey values ('bcdef','c','b1','BCDEF','B','B');
insert into cfkey values ('cdefg','d','c1','CDEFG','C','C');
insert into cfkey values ('defgh','e','d1','DEFGH','D','D');
insert into cfkey values ('efghi','f','e1','EFGHI','E','E');
insert into cfkey values ('DEFGH','D','D0','defgh','d','5');
insert into cfkey values ('ABCDE','A','A1','abcde','a','1');
insert into cfkey values ('DEFGH','E','D0','defgh','e','d');
insert into cfkey values ('ABCDE','E','E0','abcde','e','e');
insert into cfkey values ('DEFGH','B','D0','defgh','b','d');

insert into cfkey values ('DEFGH','F','A0','defgh','f','x'); insert into cfkey values ('EFGHI','D','D0','efghi','a','y');

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        

case 2

---

  1 SELECT /*+ INDEX_DESC (CFKEY CFKSET1) */ *   2 FROM CFKEY
  3 WHERE

  4     CFK_SCREEN_CODE      ='DEFGH' AND
  5*    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        
DEFGH B  D0 defgh b d        

case 3

---

  1 SELECT /*+ INDEX_DESC (CFKEY CFKSET1) */ *   2 FROM CFKEY
  3 WHERE

  4     CFK_SCREEN_CODE      ='DEFGH' AND
  5*    CFK_SCREEN_TYPE      =  'D'

CFK_S CF CF CFK_C C C        
----- -- -- ----- - -        
DEFGH F  A0 defgh f x        
DEFGH E  D1 defgh d d        
DEFGH E  D0 defgh e d        
DEFGH D  D2 defgh d 9        
DEFGH D  D1 defgh d 4        
DEFGH D  D0 defgh d 5        

Background

---

The above stuff was tried on three different sites with oracle rdbms installed by three different sets of people. Platforms : Sun sparc2, sparc10 sparc20
Oracle : v 7.0.16.4 , 7.0.16.6
 So may be we can assume that the problem has nothing to do with oracle installation or m/c h/w.

The following are some of the observtions:

1. Result does not change by changing VARCHAR2 to char
2. Result does not change by declaring non-unique index.
3. Tried different sequence of the conditions in the where clause. same reult.
4. We Do not use the cost based optimizer. In one of the sites the initdb.ora has OPTIMIZER_MODE = RULE
5. Explain plan showed a Table access with Rowid and then Index with a range scan descending. Apparently the second condition in the where clause is never evaluated during this index range scan
6. Apparently Hints Index or Index_asc works.
7. Index_desc index works some times -- depending on the where clause (case 2)

As far as I can see we havent goofed up anything(or have we?). I still cant beleive Oracle can do such things. I can understand if the index_desc doesnt retrieve rows in the order i expect. But certainly i dont want a row which i never asked for. U guys have any idea? I would realy like to have some feedbacks on this before i report this as a bug to Oracle. As i said, I still cant believe something like this can happen and i hope -sincerely- that we have goofed up somewhere.

PLS HELP Thanks

Ajoy Received on Sat Aug 13 1994 - 02:59:22 CEST