Query optimization

[kkpareek]

I have a query like select .... from a,b where a.col=b.col
and a.col1=xxx. Both tables are very large.

My problem is that oracle CBO is first doing join then applies filter. I want it to do other way round. I can do it by re-phrasing the query.

Am I missing some settings in init.ora? Why is CBO always doing hash join on tables and then filtering. The query takes over & over and as per its estimate will take 120 hours!

Please help.

Kailash pareek

[tycho]

Hi Kkpareek,

Can you create an index on a.col1 (or is there already one there)?

Greetings,

Tycho

[vinit]

Hi,
Can you try an interchange.. where a.col = and a.col = b.col

Cheers

[chrisrlong]

Please post the actual query and actual plan. If there is an index on a.col1, then the CBO would definitely hit that first.

- Chris