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

nickli2000 (AT) gmail (DOT) com wrote in news:4800fb42-4248-4184-bb4b-6108db8579d0
@m34g2000hsf.googlegroups.com:

What are "two leading columns"?

nickli2000 (AT) gmail (DOT) com wrote in news:4800fb42-4248-4184-bb4b-6108db8579d0
@m34g2000hsf.googlegroups.com:

What are "two leading columns"?

nickli2000 (AT) gmail (DOT) com wrote in news:4800fb42-4248-4184-bb4b-6108db8579d0
@m34g2000hsf.googlegroups.com:

What are "two leading columns"?

nickli2000 (AT) gmail (DOT) com wrote in news:4800fb42-4248-4184-bb4b-6108db8579d0
@m34g2000hsf.googlegroups.com:

What are "two leading columns"?

On Jan 13, 3:49*pm, "Ana C. Dent" <anaced... (AT) hotmail (DOT) com> wrote:
I think the OP just means the query has the value of the first two
columns defined to the table. Where the columns are located relative
to each other in a row makes no difference.

If you have several queries that go against a table and the query has
multiple column comparisons in the where cause such as
where col1 = var1
and col2 = var2
then yes a single composite index would be better than two separate
single column indexes.

You want to select your indexes (other than PK and perhaps UK) based
on what columns are available to your queries . You may find you have
several queries that know both columns and a few that have only one.
To support the queries with only one column of the two you would most
likely want to place that column first in the composit index. If you
have some queries that use only one column but both column are used by
a couple of queries then you still may need two indexes but one will
be composit index to support the queries that provide both columns and
the other will be a single column index on the trailing column in the
composit index to support those queries that provide only this value.

I hope my description is clear.

-- Mark D Powell --

On Jan 13, 3:49*pm, "Ana C. Dent" <anaced... (AT) hotmail (DOT) com> wrote:
I think the OP just means the query has the value of the first two
columns defined to the table. Where the columns are located relative
to each other in a row makes no difference.

If you have several queries that go against a table and the query has
multiple column comparisons in the where cause such as
where col1 = var1
and col2 = var2
then yes a single composite index would be better than two separate
single column indexes.

You want to select your indexes (other than PK and perhaps UK) based
on what columns are available to your queries . You may find you have
several queries that know both columns and a few that have only one.
To support the queries with only one column of the two you would most
likely want to place that column first in the composit index. If you
have some queries that use only one column but both column are used by
a couple of queries then you still may need two indexes but one will
be composit index to support the queries that provide both columns and
the other will be a single column index on the trailing column in the
composit index to support those queries that provide only this value.

I hope my description is clear.

-- Mark D Powell --

On Jan 13, 3:49*pm, "Ana C. Dent" <anaced... (AT) hotmail (DOT) com> wrote:
I think the OP just means the query has the value of the first two
columns defined to the table. Where the columns are located relative
to each other in a row makes no difference.

If you have several queries that go against a table and the query has
multiple column comparisons in the where cause such as
where col1 = var1
and col2 = var2
then yes a single composite index would be better than two separate
single column indexes.

You want to select your indexes (other than PK and perhaps UK) based
on what columns are available to your queries . You may find you have
several queries that know both columns and a few that have only one.
To support the queries with only one column of the two you would most
likely want to place that column first in the composit index. If you
have some queries that use only one column but both column are used by
a couple of queries then you still may need two indexes but one will
be composit index to support the queries that provide both columns and
the other will be a single column index on the trailing column in the
composit index to support those queries that provide only this value.

I hope my description is clear.

-- Mark D Powell --

On Jan 13, 3:49*pm, "Ana C. Dent" <anaced... (AT) hotmail (DOT) com> wrote:
I think the OP just means the query has the value of the first two
columns defined to the table. Where the columns are located relative
to each other in a row makes no difference.

If you have several queries that go against a table and the query has
multiple column comparisons in the where cause such as
where col1 = var1
and col2 = var2
then yes a single composite index would be better than two separate
single column indexes.

You want to select your indexes (other than PK and perhaps UK) based
on what columns are available to your queries . You may find you have
several queries that know both columns and a few that have only one.
To support the queries with only one column of the two you would most
likely want to place that column first in the composit index. If you
have some queries that use only one column but both column are used by
a couple of queries then you still may need two indexes but one will
be composit index to support the queries that provide both columns and
the other will be a single column index on the trailing column in the
composit index to support those queries that provide only this value.

I hope my description is clear.

-- Mark D Powell --

On Jan 13, 3:34*pm, nickli2... (AT) gmail (DOT) com wrote:
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.

Assume the following table exists:
CREATE TABLE T1(
COLUMN_1 VARCHAR2(10) NOT NULL,
COLUMN_2 VARCHAR2(10) NOT NULL,
COLUMN_3 VARCHAR2(20),
COLUMN_4 VARCHAR2(20),
COLUMN_5 VARCHAR2(20),
COLUMN_6 VARCHAR2(20),
COLUMN_7 VARCHAR2(20),
COLUMN_8 VARCHAR2(20),
COLUMN_9 VARCHAR2(20),
COLUMN_10 VARCHAR2(20));

If your queries typically have a WHERE clause like this:
WHERE
COLUMN_1='my_val'
AND COLUMN_2='my_val2'

It probably makes sense to create an index on (COLUMN_1,COLUMN_2).
The same index could potentially satisfy a WHERE clause that specifies
only one of the two columns by using an index skip scan.

---

If your queries typically have a WHERE clause like this:
WHERE
COLUMN_1='my_val'

It probably does not make sense to include COLUMN_2 in the index, as
doing so will likely increase the clustering factor of the index,
making it appear in cost determination to be a more expensive data
access method. Of course, if you are only selecting COLUMN_1 and
COLUMN_2, Oracle may only need to visit the index to retrieve the
rows, and not visit the table.

The best answer is to test. One such test on Oracle 10.2.0.3:
INSERT INTO
T1
SELECT
DBMS_RANDOM.STRING('A',10),
DBMS_RANDOM.STRING('A',5),
DBMS_RANDOM.STRING('A',15),
DBMS_RANDOM.STRING('A',15),
DBMS_RANDOM.STRING('A',15),
DBMS_RANDOM.STRING('A',15),
DBMS_RANDOM.STRING('A',15),
DBMS_RANDOM.STRING('A',15),
DBMS_RANDOM.STRING('A',15),
DBMS_RANDOM.STRING('A',15)
FROM
{a table with at least 100,000 rows}
WHERE
ROWNUM<=100000;

COMMIT;

Now, let's create three indexes, the first of which will be a unique
index on the two columns:
CREATE UNIQUE INDEX T1_IND1 ON T1(COLUMN_1,COLUMN_2);
CREATE INDEX T1_IND2 ON T1(COLUMN_1);
CREATE INDEX T1_IND3 ON T1(COLUMN_2);

Let's make certain that the table and index stats are current:
EXEC
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAM E=>'T1',CASCADE=>TRUE);

A simple select on only the two columns:
SELECT
COLUMN_1,
COLUMN_2
FROM
T1;

The DBMS_XPLAN:
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------

In the above, Oracle only needed to visit the index, and avoided the
visit to the table.

--
SELECT
COLUMN_1,
COLUMN_2,
COLUMN_3
FROM
T1
WHERE
COLUMN_1<'BBB';

------------------------------------------------------------------------------------
------------------------------------------------------------------------------------
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COLUMN_1"<'BBB')

In the above, Oracle determined that the best access method is a full
table scan, even though there were two indexes on COLUMN_1 that could
have been used.

--
SELECT
COLUMN_1,
COLUMN_2,
COLUMN_3
FROM
T1
WHERE
COLUMN_1<'BBB'
AND COLUMN_2<'BBB';

-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COLUMN_1"<'BBB' AND "COLUMN_2"<'BBB')
filter("COLUMN_2"<'BBB')

In the above, Oracle determined that the number of rows that would be
returned would be very small (0.031% of the rows), so the index based
access appeared to be the best option.

--
SELECT
COLUMN_1,
COLUMN_2,
COLUMN_3
FROM
T1
WHERE
COLUMN_2<'BBB';

------------------------------------------------------------------------------------
------------------------------------------------------------------------------------
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COLUMN_2"<'BBB')

In the above, Oracle predicted that 1747 rows (1.75%) of the rows in
the table would be returned, and still performed a full table scan,
rather than using one of the two available indexes.

--
SELECT
COLUMN_2,
COUNT(*)
FROM
T1
GROUP BY
COLUMN_2
HAVING
COUNT(*)>1;

--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(COUNT(*)>1)

In the above, Oracle was able to return the results by only visiting
the index on COLUMN_2.

--
Let's repeat the above, this time forcing Oracle to use the composite
index:
SELECT /*+ INDEX(T1,T1_IND1) */
COLUMN_2,
COUNT(*)
FROM
T1
GROUP BY
COLUMN_2
HAVING
COUNT(*)>1;

---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(COUNT(*)>1)

In this case, Oracle required a little more time to return the result,
and was still able to satisfy the query using only the composite index
on COLUMN_1,COLUMN_2.

--
Let's try another query that cannot be satisfied using just an index,
as it includes COLUMN_3 (this query specifies the 10 matching COLUMN_2
values returned by the previous query):
SELECT
COLUMN_1,
COLUMN_2,
COLUMN_3
FROM
T1
WHERE
COLUMN_2 IN (
'WJTai',
'lADDY',
'XyjEy',
'YbVSm',
'oGbDL',
'vAVOV',
'FBuTH',
'Omkfw',
'mGjHX',
'xywzx');

--------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(("COLUMN_2"='FBuTH' OR "COLUMN_2"='Omkfw' OR
"COLUMN_2"='WJTai' OR
"COLUMN_2"='XyjEy' OR "COLUMN_2"='YbVSm' OR
"COLUMN_2"='lADDY' OR "COLUMN_2"='mGjHX' OR
"COLUMN_2"='oGbDL' OR "COLUMN_2"='vAVOV' OR
"COLUMN_2"='xywzx'))

Oracle quickly returned the 20 matching rows by probing the T1_IND3
index 10 times.

--
Let's try the same using the composite index:
SELECT /*+ INDEX(T1,T1_IND1) */
COLUMN_1,
COLUMN_2,
COLUMN_3
FROM
T1
WHERE
COLUMN_2 IN (
'WJTai',
'lADDY',
'XyjEy',
'YbVSm',
'oGbDL',
'vAVOV',
'FBuTH',
'Omkfw',
'mGjHX',
'xywzx');

-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("COLUMN_2"='FBuTH' OR "COLUMN_2"='Omkfw' OR
"COLUMN_2"='WJTai' OR
"COLUMN_2"='XyjEy' OR "COLUMN_2"='YbVSm' OR
"COLUMN_2"='lADDY' OR "COLUMN_2"='mGjHX' OR
"COLUMN_2"='oGbDL' OR "COLUMN_2"='vAVOV' OR
"COLUMN_2"='xywzx'))

The above required three times as long to execute, and the index
lookup changed from an index range scan to an index full scan.

--
Let's try an inline view to restrict the query results to 100 rows:
SELECT
T1.COLUMN_1,
T1.COLUMN_2,
T1.COLUMN_3,
T1.COLUMN_4
FROM
(SELECT
COLUMN_1,
COLUMN_2,
COLUMN_3
FROM
T1
WHERE
ROWNUM<=100) V_T1,
T1
WHERE
V_T1.COLUMN_1=T1.COLUMN_1
AND V_T1.COLUMN_2=T1.COLUMN_2;

--------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(ROWNUM<=100)
6 - access("V_T1"."COLUMN_1"="T1"."COLUMN_1" AND
"V_T1"."COLUMN_2"="T1"."COLUMN_2")

In the above, the inline view was satisfied using an INDEX FAST FULL
SCAN on the T1_IND1 index, without visiting the table, even though
COLUMN_3 is included in the inline view - a smart optimization by
Oracle.

--
Let's try again, this time also matching COLUMN_3 from the inline
view:
SELECT
T1.COLUMN_1,
T1.COLUMN_2,
T1.COLUMN_3,
T1.COLUMN_4
FROM
(SELECT
COLUMN_1,
COLUMN_2,
COLUMN_3
FROM
T1
WHERE
ROWNUM<=100) V_T1,
T1
WHERE
V_T1.COLUMN_1=T1.COLUMN_1
AND V_T1.COLUMN_2=T1.COLUMN_2
AND V_T1.COLUMN_3=T1.COLUMN_3;

--------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(ROWNUM<=100)
5 - filter("V_T1"."COLUMN_3"="T1"."COLUMN_3")
6 - access("V_T1"."COLUMN_1"="T1"."COLUMN_1" AND
"V_T1"."COLUMN_2"="T1"."COLUMN_2")

In the above, Oracle determined that the T1_IND1 index alone could not
satisfy the inline view, as the value of COLUMN_3 is needed in the
WHERE clause - Oracle performed a full table scan (stopping after
reading 100 rows) in the inline view, rather than use an index. This
query will likely return different rows than the previous query.

--
Let's remove the COLUMN_1 line from the WHERE clause:
SELECT
T1.COLUMN_1,
T1.COLUMN_2,
T1.COLUMN_3,
T1.COLUMN_4
FROM
(SELECT
COLUMN_1,
COLUMN_2,
COLUMN_3
FROM
T1
WHERE
ROWNUM<=100) V_T1,
T1
WHERE
V_T1.COLUMN_2=T1.COLUMN_2
AND V_T1.COLUMN_3=T1.COLUMN_3;

-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("V_T1"."COLUMN_3"="T1"."COLUMN_3")
4 - filter(ROWNUM<=100)
6 - access("V_T1"."COLUMN_2"="T1"."COLUMN_2")

In the above, Oracle performed a full table scan (stopping after
reading 100 rows) in the inline view, and this time used the index on
COLUMN_2 to find the matching rows in T1.

A look at a 10053 trace file for your queries, as well as
experimentation with DBMS_XPLAN might help you make the right
decision, rather than relying on broad rules of thumb.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.