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