RE: query uses function based index in DEV1 db but does NOT use it in DEV2 db
- From: "Taylor, Chris David" <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>
- To: 'Yong Huang' <yong321@xxxxxxxxx>, "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 26 Apr 2012 10:46:21 -0500
Good test - I knew I should have double checked that :D
Chris Taylor
"Quality is never an accident; it is always the result of intelligent effort."
-- John Ruskin (English Writer 1819-1900)
Any views and/or opinions expressed herein are my own and do not necessarily
reflect the views of Ingram Industries, its affiliates, its subsidiaries or its
employees.
-----Original Message-----
From: Yong Huang [mailto:yong321@xxxxxxxxx]
Sent: Thursday, April 26, 2012 9:10 AM
To: oracle-l@xxxxxxxxxxxxx
Cc: Taylor, Chris David; litanli@xxxxxxxxx
Subject: Re: query uses function based index in DEV1 db but does NOT use it in
DEV2 db
> First, a hint is already "commented out" (I believe) by the user of /*
> and */. Those are alternative comment marks - right? So, Oracle will
> see your hint regardless if you put "--" in front of it or not. :)
In my test, prefixing -- to /*+ hint */ disables the hint. The following test
is on Oracle 10.2.0.4.
SQL> create table testhint (id int);
Table created.
SQL> alter table testhint add constraint pk_testhint primary key (id);
Table altered.
SQL> set autot traceonly explain
SQL> select count(*) from testhint;
Execution Plan
----------------------------------------------------------
Plan hash value: 172670250
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TESTHINT | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
SQL> select /*+ index(testhint pk_testhint) */
2 count(*) from testhint;
Execution Plan
----------------------------------------------------------
Plan hash value: 2694521703
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| PK_TESTHINT | 1 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
SQL> select --/*+ index(testhint pk_testhint) */
2 count(*) from testhint;
Execution Plan
----------------------------------------------------------
Plan hash value: 172670250
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TESTHINT | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
The same is true in Oracle 11.2.0.3. The only difference is that the default
path becomes index full scan so I use a FULL hint to change it to full table
scan.
To Li Li,
If you could provide us a small test case, it would be much easier to explain.
Otherwise, you may need to use event 10053 to check the cost calculation in
both databases.
Yong Huang
--
//www.freelists.org/webpage/oracle-l
Other related posts:
