Re: Smells like oracle bug?
- From: "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
- To: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
- Date: Fri, 14 Jul 2006 06:42:42 +0100
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
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 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@xxxxxxxxxxxx> 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@xxxxxxxxx>
*To:* jkstill@xxxxxxxxx
*Cc:* gints.plivna@xxxxxxxxx ; oracle-l <oracle-l@xxxxxxxxxxxxx>
*Sent:* Thursday, July 13, 2006 10:31 PM
*Subject:* Re: Smells like oracle bug?
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
Other related posts:
