Composite index question

#21

On Jan 13, 10:19*pm, Charles Hooper <hooperc2... (AT) yahoo (DOT) com> wrote:

Quote:

On Jan 13, 6:32*pm, Charles Hooper <hooperc2... (AT) yahoo (DOT) com> wrote:

On Jan 13, 3:34*pm, nickli2... (AT) gmail (DOT) 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.S TRING('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,TABNAM E=>'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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Composite index question

comp.databases.oracle.misc comp.databases.oracle.misc

Re: Composite index question - 01-13-2008 , 11:38 PM