CODEWITHSUNDEEP
oracle-databaseoracle11g
Jap Evans
Jap Evans

Reputation: 1127

Optimize outer join in oracle view

I am using Oracle 11g.

I have a big table with 1016736 rows and another small table with 2007 rows. I want to join these two tables into a view.

I join these tables like below.

where big_table.id = small_table.id (+)

I assume, this will form the view by looking at the small table 1016736 times. Is there a more optimized way to do this?

The queries are slower with the way it is now and i want to make it faster.

Any idea would be appreciated as i am new to Oracle.

--- EDIT --- This is the script of my view. 

select a.col1, a.col2, a.col100, b.col1, b.col2, b.col3 
from big_table a, small_table b where a.col1 = b.col1 (+)

Upvotes: 0

Views: 1472

Answers (2)

Miguel Veloso
Miguel Veloso

Reputation: 1095

The join is Ok, just create an index on "Id" for each table.

Try updating the statistics:

for each table:

ANALYZE TABLE XXXX_TABLE DELETE STATISTICS;

ANALYZE TABLE XXXX_TABLE COMPUTE STATISTICS;

Upvotes: 0

David Aldridge
David Aldridge

Reputation: 52356

If you're going to select from the view without any predicates placed on either table then Oracle is going to estimate the join cardinality based on the number of distinct values and the maximum and minimum values in the join columns, and any histograms present -- the number of rows projected could be anywhere from zero to 1016736 x 2007 (when the join columns each have the same single value for every row)

The join type will then depend on this estimation.

Typically you would see a hash outer join for this type of query, which is very efficient and barely more cost than reading the data required by the query projection.

For a simple count(*), if you have indexes on the join columns and the large table's join column is constrained to NOT NULL (or you use some method to ensure that the null values are included in the index) then they could each be fast full scanned and the results hash outer joined. Performance would be extremely good.

If you have particular queries that are slow then an execution plan is required to help in optimisation.

Upvotes: 2

Related Questions