Oracle HINT (INDEX_DESC) yeilds unexpected results
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
SQL> SELECT BAR1,BAR2 FROM FOO T0 WHERE (BAR1 LIKE upper ('S%') ESCAPE '\') order by BAR1 DESC;
BAR BAR2
- ---------- SLS 3