CODEWITHSUNDEEP
oracle-databaseoracle11goracle10gdatabase-indexes
user10606387
user10606387

Reputation:

Recommended way to create composite index in Oracle

I have a oracle query structure like this:

SELECT <SOME_COLUMNS>
FROM A a
JOIN B b ON a.A_ID = b.A_ID
JOIN C c ON b.B_ID = c.B_ID

Ignore about WHERE clause.

Single column indexes are created on A_ID, B_ID and C_ID (C_ID is not used but it is the primary key in table C) already.

What is the best way to create other indexes out of these two methods for the above query?

Method 01

Method 02

Hope this will help others also.

Upvotes: 0

Views: 1602

Answers (2)

Pavel Smirnov
Pavel Smirnov

Reputation: 4799

You can create composite indexes within a single table only.

So as you proposed:

Create composite index for a.A_ID and b.A_ID
Create composite index for b.B_ID and c.B_ID

It's not possible.

In your case single column indexes on columns a.A_ID, b.A_ID, c.A_ID will be just fine.

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142713

Out of those two methods, it would be 01.

Method 02 won't work anyway; you can't create an index which contains columns from two tables (a and b).

Besides, depending on database version you use, foreign key columns should be indexed, otherwise locking might happen (and you'll wonder what's going on - lack of indexes is going on).

Upvotes: 0

Related Questions