Sometimes Oracle choose wrong index even with "INDEX" hint due to index maintenance job (rename, creating or dropping), you need to change the hint in the SQLs to make it work. And for developers, they may not know which index is better, but they should know which column is best for selectivity. So is there any way to tell Oracle optimizer to choose indexes on specified columns or leading columns. I was considering an INDEX_COL hint by specifying "index_col (table name, col1, ...)" to get this case work.
I posted this idea to Google groups, and somebody reminded me that there is already an undocumented hint (found out by Jonathan Lewis) in Oracle 10g. It's really useful for both DBAs and developers, I like it very much.
The following undocumented hint will let Oracle 10g choose an index leading with column "col1" and "col2", but it will not choose index start with column "col2", not sure whether it is changed in Oracle 11g version:
/*+ index(table_alias tablename(col1, col2) ) */
Oracle have announced the 11g beta test program for quite a few days, is there anyone get the beta version? I'd like to know whether it become an documented hint now, we cannot use undocumented hint in our application now, but if it becomes an documented hint, we will absolutely use it.
Comments (3)
> is not correct in my case.
Here is the small test case:
Oracle 10.1.4 on Linux.
CREATE TABLE ABEDBA.VENDORS
(
CLIENTID NUMBER(8),
UPPERNAME CHAR(20 BYTE),
CONSTRAINT VENDORS_CLIENTPIN_IDX PRIMARY KEY (CLIENTID)
);
-- VENDORS_UpperNAME (Index)
CREATE INDEX ABEDBA.VENDORS_UNAME ON ABEDBA.VENDORS
(UPPERNAME);
-- VENDORS_CLIENTPIN_IDX (Index)
CREATE UNIQUE INDEX ABEDBA.VENDORS_CLIENTPIN_IDX ON ABEDBA.VENDORS
(CLIENTID);
--below 2 works as expected
select /*+ INDEX(V VENDORS(UPPERNAME)) */
clientid,uppername
from abedba.vendors v
where clientid and uppername like 'ABCD%';
------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS BY INDEX ROWID| VENDORS |
|* 2 | INDEX RANGE SCAN | VENDORS_UNAME |
------------------------------------------------------
select /*+ INDEX(v VENDORS(CLIENTID)) */
clientid,uppername
from abedba.vendors v
where clientid and uppername like 'ABCD%';
-------------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS BY INDEX ROWID| VENDORS |
|* 2 | INDEX RANGE SCAN | VENDORS_CLIENTPIN_IDX |
-------------------------------------------------------------
--But this one is not same as you said, it still choose col2, that is index on column UPPERNAME!
select /*+ INDEX(v VENDORS(CLIENTID, UPPERNAME)) */
clientid,uppername
from abedba.vendors v
where clientid and uppername like 'ABCD%';
-----------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS BY INDEX ROWID| VENDORS |
|* 2 | INDEX RANGE SCAN | VENDORS_UNAME |
-----------------------------------------------------
Posted by 木匠 | September 29, 2007 2:44 AM
Format problem, I post the header again:
''' but it will not choose index start with column "col2", ''' is not correct.
Posted by zhu1 (木匠) | September 29, 2007 2:46 AM
I like this valuable post, it's really helpful!
Posted by Yi Zhu1 木匠 | September 29, 2007 2:47 AM