Left Join on small table against large table - slow

[cmmrfrds]

I am doing a left join from a small table with 2000 records against a large table with 30 million records. The reason is that the small table has codes that may not appear in the large table and I want these codes to appear in a report along with summary amounts from the large table on the other codes (the report is a graph). The small table has 2 fields joined to 2 NON indexed fields in the large table. According to the DBA the large table is doing a nested loop table scan on the large table and the query is not processing in a reasonable amount of time. Will adding indexes to the 2 fields in the large table make this query significantly faster or is there a better solution?

 

[sem]

In general it may depend, but creating index has a good chance to improve performance significantly. Don't forget to gather statistics and verify that the index is used.

Regards, Dima

 

[Cagliostro]

By the way, this often happens when you forget do specify how to join tables

select * from table1, table2
--no problems, but join not specified
--and execution could be very very slow

select * from table1, table2
where table1.x = table2.y (+) --secify join criteria
--no problems, fase execution

if there are many tables and complicated joins, is very difficult to find this error.

Ion Filipski

 

[cmmrfrds]

Thank you for your input. I have run into some problems with the way I was going to implement this and have decided to create a View for the right side of the join.