Oracle Select Query optimization

Question

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,

Please paste the explain plan into the question.

krokodilko
– krokodilko

2013-09-01 13:50:54 +00:00
Commented Sep 1, 2013 at 13:50 — krokodilko
– krokodilko, Commented Sep 1, 2013 at 13:50
... And the real query.

David Aldridge
– David Aldridge

2013-09-01 18:29:24 +00:00
Commented Sep 1, 2013 at 18:29 — David Aldridge
– David Aldridge, Commented Sep 1, 2013 at 18:29

Gordon Linoff · Accepted Answer · 2013-09-01 18:52:40Z

1

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.

edited Sep 1, 2013 at 18:52

answered Sep 1, 2013 at 16:24

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

David Aldridge Over a year ago

With the select * getting all columns from both tables, the index on t1 might as well be (c1, c2) I think.

Gordon Linoff Over a year ago

@DavidAldridge . . . That is an interesting point. It is possible (but I do not know) that with the id in the index, the index alone would be used for the join with the additional columns coming later. However, Oracle might fetch the pages first and then do the join.

David Aldridge Over a year ago

I'm pretty sure it would access the table first, but whichever way it does I think that the net work would be lower without the id on the index -- you'd also get a more compact index, and COMPRESS might be useful as well.

Collectives™ on Stack Overflow

Oracle Select Query optimization

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related