Re: Smells like oracle bug?

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Fri, 14 Jul 2006 06:42:42 +0100
Message-ID: <7765c8970607132242v31213badh5173c6966e8a3797@mail.gmail.com>

I'll agree that you may get no result - though I'm not sure the halting problem is relevant in most cases here, just reasonable timescales to wait for bad plans. I'll agree with the first one only as so far as to say that if you get a result it will be the same as the unhinted plan (anything else is a bug). The third is just wrong

SQL> set autotrace on explain
SQL> create index idx1 on dept(deptno);
create index idx1 on dept(deptno)

ERROR at line 1:
ORA-01408: such column list already indexed

SQL> create index idx1 on dept(dname);

Index created.

SQL> select /*+ index dept(dname) */ empno,ename 2 from emp;

EMPNO ENAME
---------- ----------

      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS

     EMPNO ENAME


---------- ----------

      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.

Execution Plan

Plan hash value: 3956160932

|   0 | SELECT STATEMENT  |      |    14 |   280 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   280 |     3   (0)| 00:00:01 |


--------------------------------------------------------------------------

Note

dynamic sampling used for this statement

SQL>
Hints are not designed to allow users to hint access paths that don't satisfy the query correctly. They are designed to say either "set this strategic goal for optimisation (RULE,FIRST_ROWS_N ....) or else "I know that the best access path for this query (or part of it) is the following, please don't bother second guessing me. Can you imagine how unreliable Oracle based systems would be in general if the hints that developers have added to various code around the planet for the last decade weren't checked to make sure they were valid. Come to think of it there are more than enough hints in various versions of the data dictionary for that possibility to be worrying.
On 7/13/06, jaromir nemec <jaromir_at_db-nemec.com> wrote:

>
>  Hi,
>
>
> > You cannot (or at least shouldn't be able to) provide a hint to an
> access path that
>
> > gives wrong results.
>
>
>
> Conscious or unconscious abuse of hints in a SQL statement may lead only
> to three kind of results:
> - you get some result
> - you get no result (see halting problem of Alan Tuning:)
> - you get an error
> Other behavior (i.e. receiving two different results) is in my opinion a
> bug.
>
>
>
> Regards
>
> Jaromir
>
> ----- Original Message -----
> *From:* Niall Litchfield <niall.litchfield_at_gmail.com>
> *To:* jkstill_at_gmail.com
> *Cc:* gints.plivna_at_gmail.com ; oracle-l <oracle-l_at_freelists.org>
> *Sent:* Thursday, July 13, 2006 10:31 PM
> *Subject:* Re: Smells like oracle bug?
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jul 14 2006 - 00:42:42 CDT