Oracle HINT (INDEX_DESC) yeilds unexpected results

From: Deirdre Gerhardt <gerhardt_at_reunion.progress.com>
Date: 1996/05/20
Message-ID: <4nq53u$mlq_at_progress.bedford.progress.com>#1/1


I have logged the following issue with ORACLE regarding some unexpected behavior using the HINT (INDEX_DESC). I am wondering if anyone has encountered this before and knows if it is a ORACLE limitation or a bug. I am seeing this on versions 7.0, 7.1.6, 7.2.2 and 7.3.1 on Solaris, HP-UX and Sun-OS. It looks like a generic problem.

Problem Description:

ORACLE Hints INDEX_DESC does not yeild expected results in WHERE clause with LIKE phrase for a unique index defined as VARCHAR. Fails in OCI call as well as SQLPLUS. (See QUERY A)

Reproducing Material:

SQL>create table foo ( bar1 varchar2(3),

                       bar2 number null);

SQL>create unique index foo##bar1 on foo (bar1);

INSERT INTO foo VALUES ('AAA', 1);
INSERT INTO foo VALUES ('DKP', 2);
INSERT INTO foo VALUES ('SLS', 3);

SQL> describe foo
 Name                            Null?    Type
 ------------------------------- -------- ----
 BAR1                                     VARCHAR2(3)
 BAR2                                     NUMBER

SQL> select * from foo;

BAR BAR2
--- ----------

AAA          1
DKP          2
SLS          3


QUERY A) Uses HINT INDEX_DESC - Expect Results, but no row is selected


  SQL> SELECT /*+ INDEX_DESC(T0 FOO##BAR1) */ BAR1,BAR2 FROM FOO T0 WHERE (BAR1   LIKE upper ('S%') ESCAPE '\') order by BAR1 DESC;

  no rows selected

QUERY B) Uses HINT INDEX_ASC


  SQL> SELECT /*+ INDEX_ASC(T0 FOO##BAR1) */ BAR1,BAR2 FROM FOO T0 WHERE (BAR1   LIKE upper ('S%') ESCAPE '\') order by BAR1;

  BAR BAR2

  • ---------- SLS 3
QUERY C) No HINT

  SQL> SELECT BAR1,BAR2 FROM FOO T0 WHERE (BAR1 LIKE upper ('S%') ESCAPE '\')   order by BAR1 DESC;

  BAR BAR2

  • ---------- SLS 3
Received on Mon May 20 1996 - 00:00:00 CEST

Original text of this message