-
composite Index
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...
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)
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.
Sameer
Last edited by Sameer; 12-09-2002 at 11:39 AM.
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|