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

From: Taylor, Chris David <ChrisDavid.Taylor_at_ingrambarge.com>
Date: Thu, 26 Apr 2012 10:46:21 -0500
Message-ID: <C5533BD628A9524496D63801704AE56D75B24916B8_at_SPOBMEXC14.adprod.directory>

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_at_yahoo.com] Sent: Thursday, April 26, 2012 9:10 AM
To: oracle-l_at_freelists.org
Cc: Taylor, Chris David; litanli_at_gmail.com 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

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

Received on Thu Apr 26 2012 - 10:46:21 CDT