CODEWITHSUNDEEP
sqlindexingoracle11gleft-join
Alex
Alex

Reputation: 347

Indexes in join with filter

Suppose the following SQL pseudocode:

select ... from A join B
on A.serial=27 and B.serial=34 and A.id=B.id;

Given that the tables are big, I need to use indexes for both serial, and obviously for the join condition A.id=B.id. Ideally, I need the filters applied before the join, as the result of the filters give much less rows than filter one table, then join, then filter second.

My question is: which combination of indexes would be fine for this situation? maybe two composites on A and B in the form (serial id)?

Thank you in advance!!!

Upvotes: 2

Views: 932

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44951

This is Oracle, Not MySQL.
You don't need indexes for the id columns for join purposes, however -
it is a good idea to define ID columns as primary keys and this implicitly creates unique indexes

You do need indexes on the serial columns 

Create index A_IX_SERIAL on A (SERIAL);
Create index B_IX_SERIAL on B (SERIAL);

P.s. How much is "much less rows"?
As much rows are return, it becomes a less good idea to use index.

Upvotes: 1

Related Questions