Same index, different plan

From: Baumgartel, Paul <paul.baumgartel_at_credit-suisse.com>
Date: Fri, 4 Apr 2008 10:27:14 -0400
Message-ID: <21469B88E0EA11498818517F2103353101C66195@EPRI17P32001A.csfb.cs-group.com>

A colleague is asking for help with a 10.2.0.3 (Sun Solaris 8) optimizer issue. A unhinted query

SELECT max(history_timestamp) AS MaxStamp FROM dmsdb.t_dms_deal_security_xref_h
WHERE history_timestamp<sysdate+1 AND eod_snapshot='N';

yields the plan

---

---

| Id | Operation | Name | Rows |
Bytes | Cost (%CPU)|

---

---

| 0 | SELECT STATEMENT | | 1 |
13 | 86560 (18)|
| 1 | SORT AGGREGATE | | 1 |
13 | |
| 2 | INDEX FAST FULL SCAN| T_DMS_DEAL_SECURITY_XREF_H_AK1 | 94M|
1168M| 86560 (18)|

---

---

and runs in about 5 minutes.

However, the same query, with a hint specifying the index that's being used for the non-hinted version, yields

---

---

| Id | Operation | Name | Rows |
Bytes | Cost (%CPU)|

---

---

| 0 | SELECT STATEMENT | | 1 |
13 | 330K (1)|
| 1 | SORT AGGREGATE | | 1 |
13 | |
| 2 | INDEX RANGE SCAN| T_DMS_DEAL_SECURITY_XREF_H_AK1 | 94M|
1168M| 330K (1)|

---

---

and runs in about 3 minutes. Note that the access path has changed from fast full scan to range scan, and note the higher cost of the ran

Why would hinting to use an index that's being used anyway change the access path from fast full scan to range scan?

Paul Baumgartel
CREDIT SUISSE
Information Technology
Prime Services Databases Americas
One Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel_at_credit-suisse.com
www.credit-suisse.com

---

Please access the attached hyperlink for an important electronic communications disclaimer:

http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html

---

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

Received on Fri Apr 04 2008 - 09:27:14 CDT