Re: Composite index question
From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Sun, 13 Jan 2008 19:19:16 -0800 (PST)
Message-ID: <4aef7525-93cd-417b-bbec-17a02885bfcc@e6g2000prf.googlegroups.com>
Continuing my previous post.. this time with a table with much wider columns, and less randomness from one row to the next for the first two columns, which will be indexed. The results are a bit different from the previous results, and in some cases Oracle selected to use an index on a single column when both columns of the composite index were specified in the WHERE clause.
FROM
{a table with at least 100,000 rows}
WHERE
ROWNUM<=100000;
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
| 1 | INDEX FAST FULL SCAN| T2_IND1 | 1 | 100K| 100K| 00:00:00.20 | 1916 |
Date: Sun, 13 Jan 2008 19:19:16 -0800 (PST)
Message-ID: <4aef7525-93cd-417b-bbec-17a02885bfcc@e6g2000prf.googlegroups.com>
On Jan 13, 6:32 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Jan 13, 3:34 pm, nickli2..._at_gmail.com wrote: > > > Hi, > > > I have a question on how composite index should be used. As an > > example, my queries often select two leading columns, column_1 and > > column_2 of a table. Should I create a composite index using column_1 > > and column_2, or should I create two separated indexes for column_1 > > and column_2? What are the differences between these two approaches > > and performance implications? > > > Thanks in advance. > > > Nick > > I don't think that you have provided enough information for a > reasonably accurate answer to be provided. There are too many > possible exceptions to any type of broad indexing rule for this > situation. >
Continuing my previous post.. this time with a table with much wider columns, and less randomness from one row to the next for the first two columns, which will be indexed. The results are a bit different from the previous results, and in some cases Oracle selected to use an index on a single column when both columns of the composite index were specified in the WHERE clause.
CREATE TABLE T2(
COLUMN_1 VARCHAR2(40) NOT NULL, COLUMN_2 VARCHAR2(40) NOT NULL, COLUMN_3 VARCHAR2(255), COLUMN_4 VARCHAR2(255), COLUMN_5 VARCHAR2(255), COLUMN_6 VARCHAR2(255), COLUMN_7 VARCHAR2(255), COLUMN_8 VARCHAR2(255), COLUMN_9 VARCHAR2(255), COLUMN_10 VARCHAR2(255));
INSERT INTO
T2
SELECT
TRIM(TO_CHAR(ROWNUM,'000000'))||DBMS_RANDOM.STRING('A',10),
TRIM(TO_CHAR(ROWNUM*3.14,'000000'))||DBMS_RANDOM.STRING('A',34),
DBMS_RANDOM.STRING('A',255),
DBMS_RANDOM.STRING('A',255),
DBMS_RANDOM.STRING('A',255),
DBMS_RANDOM.STRING('A',255),
DBMS_RANDOM.STRING('A',255),
DBMS_RANDOM.STRING('A',255),
DBMS_RANDOM.STRING('A',255),
DBMS_RANDOM.STRING('A',255)
FROM
{a table with at least 100,000 rows}
WHERE
ROWNUM<=100000;
Each row will contain roughly 2096 bytes, and if the blocks are 80% used, that is roughly 262MB worth of data.
COMMIT;
CREATE UNIQUE INDEX T2_IND1 ON T2(COLUMN_1,COLUMN_2);
CREATE INDEX T2_IND2 ON T2(COLUMN_1);
CREATE INDEX T2_IND3 ON T2(COLUMN_2);
EXEC
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2',CASCADE=>TRUE);
SELECT
COLUMN_1,
COLUMN_2
FROM
T2;
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
| 1 | INDEX FAST FULL SCAN| T2_IND1 | 1 | 100K| 100K| 00:00:00.20 | 1916 |
-- SELECT COLUMN_1, COLUMN_2, COLUMN_3 FROM T2 WHERE COLUMN_1<='001800';Received on Sun Jan 13 2008 - 21:19:16 CST
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A- Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 46 | 1799 |00:00:00.42 | 638 | 562 | |* 2 | INDEX RANGE SCAN | T2_IND2 | 1 | 46 | 1799 |00:00:00.01 | 26 | 0 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("COLUMN_1"<='001800') -- SELECT COLUMN_1, COLUMN_2, COLUMN_3 FROM T2 WHERE COLUMN_1<'001800' AND COLUMN_2<'005652';
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A- Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 1 | 1799 |00:00:00.02 | 645 | |* 2 | INDEX RANGE SCAN | T2_IND3 | 1 | 3 | 1799 |00:00:00.01 | 33 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("COLUMN_1"<'001800') 2 - access("COLUMN_2"<'005652') Note that the above example did not use the composite index, even though both columns in the composite index were included in the WHERE clause. -- Instruct Oracle to use the composite index: SELECT /*+ INDEX(T2,T2_IND1) */ COLUMN_1, COLUMN_2, COLUMN_3 FROM T2 WHERE COLUMN_1<'001800' AND COLUMN_2<'005652';
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A- Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 1 | 1799 |00:00:00.02 | 649 | |* 2 | INDEX RANGE SCAN | T2_IND1 | 1 | 1 | 1799 |00:00:00.01 | 37 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("COLUMN_1"<'001800' AND "COLUMN_2"<'005652') filter("COLUMN_2"<'005652') -- An index being used to satisfy a query when grouping on the SUBSTR of COLUMN_2: SELECT SUBSTR(COLUMN_2,7,5), COUNT(*) FROM T2 GROUP BY SUBSTR(COLUMN_2,7,5) HAVING COUNT(*)>1;
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes |
--------------------------------------------------------------------------------------------------------------
|* 1 | FILTER | | 1 | | 15 | 00:00:01.11 | 736 | 187 | 186 | | 2 | HASH GROUP BY | | 1 | 5000 | 99985 | 00:00:00.95 | 736 | 187 | 186 | | 3 | INDEX FAST FULL SCAN| T2_IND3 | 1 | 100K| 100K| 00:00:00.25 | 736 | 1 | 0 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(COUNT(*)>1) -- Let's force it to use the composite index: SELECT /*+ INDEX(T2,T2_IND1) */ SUBSTR(COLUMN_2,7,5), COUNT(*) FROM T2 GROUP BY SUBSTR(COLUMN_2,7,5) HAVING COUNT(*)>1;
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A- Time | Buffers | Reads | Writes |
---------------------------------------------------------------------------------------------------------
|* 1 | FILTER | | 1 | | 15 | 00:00:01.05 | 955 | 186 | 186 | | 2 | HASH GROUP BY | | 1 | 5000 | 99985 | 00:00:00.91 | 955 | 186 | 186 | | 3 | INDEX FULL SCAN| T2_IND1 | 1 | 100K| 100K| 00:00:00.20 | 955 | 0 | 0 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(COUNT(*)>1) The above seems to indicate that the composite index allowed the query to complete slighly faster. -- SELECT T2.COLUMN_1, T2.COLUMN_2, T2.COLUMN_3, T2.COLUMN_4 FROM (SELECT COLUMN_1, COLUMN_2, COLUMN_3 FROM T2 WHERE ROWNUM<=100) V_T2, T2 WHERE V_T2.COLUMN_1=T2.COLUMN_1 AND V_T2.COLUMN_2=T2.COLUMN_2;
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A- Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 1 | 100 |00:00:00.01 | 145 | | 2 | NESTED LOOPS | | 1 | 100 | 201 |00:00:00.01 | 111 | | 3 | VIEW | | 1 | 100 | 100 |00:00:00.01 | 8 | |* 4 | COUNT STOPKEY | | 1 | | 100 |00:00:00.01 | 8 | | 5 | INDEX FAST FULL SCAN | T2_IND1 | 1 | 100K| 100 |00:00:00.01 | 8 | |* 6 | INDEX RANGE SCAN | T2_IND2 | 100 | 1 | 100 |00:00:00.01 | 103 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("V_T2"."COLUMN_2"="T2"."COLUMN_2") 4 - filter(ROWNUM<=100) 6 - access("V_T2"."COLUMN_1"="T2"."COLUMN_1") Note that in the above, Oracle uses the composite index on COLUMN_1,COLUMN_2 for the inline view, but only T2_IND2 (on COLUMN_1) for the join between the inline view and the table T2, even though COLUMN_1 and COLUMN_2 where specified in the WHERE clause. -- SELECT T2.COLUMN_1, T2.COLUMN_2, T2.COLUMN_3, T2.COLUMN_4 FROM (SELECT COLUMN_1, COLUMN_2, COLUMN_3 FROM T2 WHERE ROWNUM<=100) V_T2, T2 WHERE V_T2.COLUMN_1=T2.COLUMN_1 AND V_T2.COLUMN_2=T2.COLUMN_2 AND V_T2.COLUMN_3=T2.COLUMN_3;
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A- Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 1 | 100 |00:00:00.01 | 174 | | 2 | NESTED LOOPS | | 1 | 1 | 201 |00:00:00.01 | 140 | | 3 | VIEW | | 1 | 100 | 100 |00:00:00.01 | 37 | |* 4 | COUNT STOPKEY | | 1 | | 100 |00:00:00.01 | 37 | | 5 | TABLE ACCESS FULL | T2 | 1 | 100K| 100 |00:00:00.01 | 37 | |* 6 | INDEX RANGE SCAN | T2_IND2 | 100 | 1 | 100 |00:00:00.01 | 103 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("V_T2"."COLUMN_2"="T2"."COLUMN_2" AND "V_T2"."COLUMN_3"="T2"."COLUMN_3")) 4 - filter(ROWNUM<=100) 6 - access("V_T2"."COLUMN_1"="T2"."COLUMN_1") Note that in the above, Oracle uses a full table scan (stopping after reading 100 rows) for the inline view, but only T2_IND2 (on COLUMN_1) for the join between the inline view and the table T2, even though COLUMN_1 and COLUMN_2 where specified in the WHERE clause. -- SELECT T2.COLUMN_1, T2.COLUMN_2, T2.COLUMN_3 FROM (SELECT SUBSTR(COLUMN_2,7,5) SC2, MIN(COLUMN_1) MI_C1, MAX(COLUMN_1) MA_C1, COUNT(*) FROM T2 GROUP BY SUBSTR(COLUMN_2,7,5) HAVING COUNT(*)>1) V_T2, T2 WHERE V_T2.SC2=SUBSTR(T2.COLUMN_2,7,5) AND T2.COLUMN_1 BETWEEN V_T2.MI_C1 AND V_T2.MA_C1;
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 | 12500 | 30 | 00:00:03.95 | 34534 | 7826 | 744 | 764K| 764K| 967K (0)| | 2 | VIEW | | 1 | 5000 | 15 | 00:00:01.38 | 964 | 744 | 744 | | | | |* 3 | FILTER | | 1 | | 15 | 00:00:01.38 | 964 | 744 | 744 | | | | | 4 | HASH GROUP BY | | 1 | 5000 | 99985 | 00:00:01.22 | 964 | 744 | 744 | | | | | 5 | INDEX FAST FULL SCAN| T2_IND1 | 1 | 100K| 100K| 00:00:00.20 | 964 | 0 | 0 | | | | | 6 | TABLE ACCESS FULL | T2 | 1 | 100K| 100K| 00:00:02.00 | 33570 | 7082 | 0 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("V_T2"."SC2"=SUBSTR("T2"."COLUMN_2",7,5)) filter(("T2"."COLUMN_1">="V_T2"."MI_C1" AND "T2"."COLUMN_1"<="V_T2"."MA_C1")) 3 - filter(COUNT(*)>1) In the above, Oracle used the composite index for the inline view, and performed a full table scan to join the inline view to the table T2. Charles Hooper IT Manager/Oracle DBA K&M Machine-Fabricating, Inc.