CODEWITHSUNDEEP
oracle-databasejoinviewpredicates
Rod Meyer
Rod Meyer

Reputation: 568

Oracle 11 joining with view has high cost

I'm having some difficulty with joining a view to another table. This is on an Oracle RAC system running 11.2

I'll try and give as much detail as possible without going into specific table structures as my company would not like that.

You all know how this works. "Hey, can you write some really ugly software to implement our crazy ideas?"

The idea of what they wanted me to do was to make a view where the end user wouldn't know if they were going after the new table or the old table so one of the tables is a parameter table that will return "ON" or "OFF" and is used in the case statements.

There are some not too difficult but nested case statements in the select clause

I have a view:

create view my_view as 
select t1.a as a, t1.b as b, t1.c as c,
sum(case when t2.a = 'xx' then case when t3.a then ... ,
case when t2.a = 'xx' then case when t3.a then ... ,

from table1 t1 
join table t2 on (t1.a = t2.a etc...)
full outer join t3 on (t1.a = t3.a etc...)
full outer join t4 on (t1.a = t4.a etc...)
group by t1.a, t1.b, t2.c, and all the ugly case statements...

Now, when I run the query

select * from my_view where a='xxx' and b='yyy' and c='zzz'

the query runs great and the cost is 10.

However, when I join this view with another table everything falls apart.

select * from my_table mt join my_view mv on (mt.a = mv.a and mt.b=mv.b and mt.c=mv.c) where ..."

everything falls apart with a cost though the roof.

What I think is happening is the predicates are not getting pushed to the view. As such, the view is now doing full tables scans and joining everything to everything and then finally removing all the rows. Every hint, tweak, or anything I've done doesn't appear to help.

When looking at the plan it looks like it has the predicates.

enter image description here

But this happens after everything is joined.

enter image description here

Sorry if this is cryptic but any help would be greatly appreciated.

Upvotes: 1

Views: 223

Answers (2)

Rod Meyer
Rod Meyer

Reputation: 568

After many keyboard indentations on my forehead I may have tricked Oracle into pushing the predicates. I don't know exactly why this works but simplifying things may have helped.

I changed all my ON clauses to USING clauses and in this way the column names now match the columns from which I'm joining to. On some other predicates that were constants I added in a where clause to the view.

The end result is I can now join this view with another table and the cost is reasonable and the plan shows that the predicates are being pushed. enter image description here

Thank you to everybody who looked at this problem.

Upvotes: 0

BA.
BA.

Reputation: 934

Since you have the view with a "GROUP BY", predicates could not be pushed to the inner query

Also, you have the group by functions in a case statement, which could also make it worse for the optimizer

Oracle introduces enhancements to Optimizer every version/release/patch. It is hard to say what is supported in the version you're running. However, you can try:

  1. See if removing the case from the GROUP BY function will make any difference
  2. Otherwise, you have to take the GROUP BY and GROUP BY functions from the view to the outer most query

Upvotes: 0

Related Questions