Re: query uses function based index in DEV1 db but does NOT use it in DEV2 db

From: Yong Huang <yong321_at_yahoo.com>
Date: Thu, 26 Apr 2012 07:10:06 -0700 (PDT)
Message-ID: <1335449406.77881.YahooMailClassic_at_web181217.mail.ne1.yahoo.com>

> 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

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

Received on Thu Apr 26 2012 - 09:10:06 CDT