Hi all,
if i h've composite index. How is it evaluate on a query?
when i use only one column from this index or two columns in the WHERE
clause.
Thank's
Bensmail
Printable View
Hi all,
if i h've composite index. How is it evaluate on a query?
when i use only one column from this index or two columns in the WHERE
clause.
Thank's
Bensmail
Hope following examples helps you...
Whenever leading composite index column is in a WHERE condition, it uses index. Else you can force query to use index by supplying a hint. The order of the columns used in the definition is important. Generally, the most commonly accessed or most selective columns go first.Code:CREATE TABLE test (id number, id1 number, name varchar2(10))
CREATE INDEX ix_test ON test (id, id1)
I have created composite index on id and id1;
INSERT INTO test VALUES (1,1,'one');
INSERT INTO test VALUES (2,2,'two');
INSERT INTO test VALUES (1,3,'three');
INSERT INTO test VALUES (2,4,'four');
INSERT INTO test VALUES (1,5,'five');
INSERT INTO test VALUES (2,6,'six');
INSERT INTO test VALUES (1,7,'seven');
INSERT INTO test VALUES (2,9,'eight');
INSERT INTO test VALUES (3,3,'nine');
INSERT INTO test VALUES (3,4,'ten');
COMMIT;
See following SELECT statements how optimizer uses composite index
SQL> SELECT * FROM test WHERE id = 1;
ID ID1 NAME
---------- ---------- ----------
1 1 one
1 3 three
1 5 five
1 7 seven
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
2 1 INDEX (RANGE SCAN) OF 'IX_TEST' (NON-UNIQUE)
SQL> SELECT * FROM test WHERE id1 = 1;
ID ID1 NAME
---------- ---------- ----------
1 1 one
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TEST'
SQL> SELECT /*+ INDEX (test, ix_test) */ * FROM test WHERE id1 = 1;
ID ID1 NAME
---------- ---------- ----------
1 1 one
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=34 Card=1 Bytes=33)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=34 Card=1 By
tes=33)
2 1 INDEX (FULL SCAN) OF 'IX_TEST' (NON-UNIQUE) (Cost=26 Car
d=1)
SQL> SELECT * FROM test WHERE id = 1 and id1 = 5;
ID ID1 NAME
---------- ---------- ----------
1 5 five
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
2 1 INDEX (RANGE SCAN) OF 'IX_TEST' (NON-UNIQUE)
SQL> SELECT * FROM test WHERE id1 = 1 and id = 1;
ID ID1 NAME
---------- ---------- ----------
1 1 one
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
2 1 INDEX (RANGE SCAN) OF 'IX_TEST' (NON-UNIQUE)
Sameer
you forgot to analyze the table, in 9i it may or may not skip the use of index but in order to use that you have to analyze the table, in your example RULE is used