Re: Bind Variables ignoring Index on 10.2.0.4

From: Pat <pat.casey_at_service-now.com>
Date: Mon, 7 Apr 2008 10:41:13 -0700 (PDT)
Message-ID: <dfac3be0-2205-424a-99ca-7c5705df9824@8g2000hsu.googlegroups.com>

Sorry, just realized my hint was wrong.

Forcing the index does, indeed, alter the plan. Doesn't look like he can use the NLS index though, since he's doing a full scan followed by lookups by row_id e.g. it's just a really inefficient way to do a table scan.

SQL> explain plan for delete /*+ INDEX(sys_trigger SYS_TRIGGER_SYSID) */ from sys_trigger where "sys_id" = :id;

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT

---

Plan hash value: 3221851421

---

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

---

| 0 | DELETE STATEMENT | | 1 | 87 | 3277 (1)| 00:00:40 |

|   1 |  DELETE 		     | SYS_TRIGGER	 |	 |	 |	      | 	 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| SYS_TRIGGER	 |     1 |    87 |
3277   (1)| 00:00:40 |
|   3 |    INDEX FULL SCAN	     | SYS_TRIGGER_SYSID | 32505 |	 |

20 (0)| 00:00:01 |

---

PLAN_TABLE_OUTPUT

---

Predicate Information (identified by operation id):

---

   2 -
filter(NLSSORT("sys_id",'nls_sort=''BINARY_CI''')=NLSSORT(:ID,'nls_sort=''BINARY_CI

              '''))

16 rows selected. Received on Mon Apr 07 2008 - 12:41:13 CDT