CODEWITHSUNDEEP
databaseoracle-databaseselect
SWE
SWE

Reputation: 485

Oracle Select Query optimization

I ‘m executing some sql statements on my data and I have the following situation.

Select statement looks like this

select *
from t1, t2
where t1.id = t2.t1_id
t1.c1 = 'X' and t1.c2  in ('Y', 'Z')

Running the explain plan I saw that the col2 filtering has cost 11 (Table Access with filter predicates) and the col1 1 (table access index).

How can I reduce the cost of the searching on col2? Should I put any hint in my query?

Thanks in advance,

Upvotes: 1

Views: 307

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270793

I prefer the ANSI syntax for queries:

select *
from t1 join
     t2
     on t1.id = t2.t1_id
where t1.c1 = 'X' and t1.c2  in ('Y', 'Z');

Consider an index on t1(c1, c2, id) and t2(t1_id). The first index should be used to get the rows from t1. The second should speed up the join to t2.

EDIT:

As per David's comment, an index on t1(c1, c2) might perform slightly better.

Upvotes: 1

Related Questions