Kbase 16306: Error 565 FIND LAST with BEGINS fails - Oracle bug
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/05/1998 |
|
Error 565 FIND LAST with BEGINS fails - Oracle bug
ORACLE may produce the erroneous results when given an INDEX_DESC hint
using a LIKE operator in the where clause on the same index.
A INDEX_DESC hint may be generated by the DataServer for the following
4GL operations:
FIND PREV/LAST when an ascending index is used
FIND NEXT/FIRST when the 1st componet of the index is descending.
The LIKE operator is used when:
the 4gl BEGINS operator is used
the 4gl MATCHES operator is used
A FIND USING is performed with an abbreviated index.
Below is an example of query that fails with this type of hint:
find last salesrep where sales-rep begins "S".
Display salesrep.
** FIND FIRST/LAST failed for table salesrep. (565)
The sql code captured from Oracle "TKPROF" or Progress startup
parameter "-Dsv qt_debug,SQL" which is generated from the query is
shown below. This code when executed under SQLPlus will exhibit the
same failure:
SQL> SELECT /*+ INDEX_DESC(T0 SALESREP##REP) */ PROGRESS_RECID FROM
DEMO.SALESREP T0 WHERE (U##SALES_REP LIKE upper('S%') ESCAPE '\')
order by U##SALES_REP DESC;
no rows selected
There are two bugs logged with ORACLE:
bug# 167696 - Failures with hint INDEX_DESC using single index and
bug# 296269 - Failures with hint INDEX_DESC using concatenated index.
ORACLE TAR #9461404.6 (bugs 167696 & 296269) have been fixed and
are available in ORACLE version 7.3.3 (not a pactch, commercial
release) or can be obtained in a backport on 7.2.2 from Oracle
Tecnical Support (contact ORACLE Tech support for the platform
availability). When the patch or the commercial version is installed
the hints will work as expected for INDEX_DESC.
However, if you want to avoid this ORACLE problem before getting
the patch, the startup parameter -znohint may be used. This will turn
off ALL hints sent to ORACLE. However, this method may potentially
cause performance problems in some applications. To avoid potential
performance issues you will need to rewrite the FIND statements that
expose this ORACLE bug as new queries and use
QUERY-TUNING (NO-INDEX-HINT) to prevent index hints on these
(very small number of) queries. Please note that the QUERY-TUNING
statement can't be used on FINDs.
Progress Software Technical Support Note # 16306