Re: Composite index question

On Jan 13, 10:19�pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 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';
>
> ---------------------------------------------------------------------------�-------------------------------
> | 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
> ...
>
> read more �- Hide quoted text -
>
> - Show quoted text -

Charles,

   Thanks a lot for your help and it really helps. I will go back and hit some Oracle books.

   Nick Received on Sun Jan 13 2008 - 23:38:08 CST