sql tuning

with v as (
select col1, col2 from tab1
)
select tab3.col1,v.col2 from tab3, v
where v.col1 = tab3.col1

The above query uses tab3 as the driving table and does an index range scan on tab1. But,

with v as (
select col1, col2 from tab1
union all
select col1, col2 from tab2
)
select tab3.col1,v.col2 from tab3, v
where v.col1 = tab3.col1

This second query does a full scan on both tab1 and tab2 and then using a hash join to join with tab3.
The second query is much slower than the first one.
Is there any way to avoid the hash join and do a nested loop join on tab3 and v keeping tab3 as the driving table?
The statistics are updated, I tried various hints, tried rewriting the query in various ways.
I realise that it will be a long shot trying to answer this question without having access to the environment
but was trying to understand why should the union all have an impact on the execution plan?
subratocAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Christoffer SwanströmPartnerCommented:
How many rows do you have in each of the three tables? A full table scan is as such not necessarily a bad thing, it is in many cases faster than using an index.
0
subratocAuthor Commented:
10 million rows in tab1 and tab2.
50,000 rows in tab3.
0
Christoffer SwanströmPartnerCommented:
Have you tried this:


select 
  tab3.col1
  ,v.col2 
from 
  tab3
inner join
  (select col1, col2 from tab1) v
on
  v.col1 = tab3.col1

union all

select 
  tab3.col1
  ,v.col2 
from 
  tab3
inner join
  (select col1, col2 from tab2) v
on
  v.col1 = tab3.col1

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

subratocAuthor Commented:
Yes, that will make the query faster but it will make the code less maintainable as I have 30 tables to join using 'union all' so in case any changes are required in tab3 results, that needs to be done 30 times. I was wondering, why does the union all change the execution plan at all?
But otherwise, yes the above will work faster. Thanks, Tosse.
0
Christoffer SwanströmPartnerCommented:
Do you have an index on tab2 on col1 as well?

Also, how many records from tab1 and tab2 have a matching record in tab3?

Depending on these the optimizer might (correctly or incorrectly) decide that a full table scan is faster. Without knowing a bit more about the contents it's difficult to say whether an index scan would actually be faster or not.
0
subratocAuthor Commented:
Yes, I have an index on tab1 and tab2 on col1
and the selectivity is very high.
Full table scan takes hours to return the complete data whereas the index scan takes only seconds to return the complete data.
I have used hints like push_subq but nothing has worked so far.
0
Christoffer SwanströmPartnerCommented:
Have you tried to run the query in parallel, e.g.:
0
Christoffer SwanströmPartnerCommented:
sorry, lost the code, here it is...:
with v as (
select col1, col2 from tab1
union all
select col1, col2 from tab2
)
select /*+ parallel (v, 32) */ tab3.col1,v.col2 from tab3, v
where v.col1 = tab3.col1

Open in new window

0
Christoffer SwanströmPartnerCommented:
Of course ideally you would like to be able to use the indexes, but I don't really have a good idea how to force the optimizer to do that given the information that is at hand here. One possibility would be to materialize v, either as a table or materialized view, and then make an index on that and collect statistics.
0
subratocAuthor Commented:
Still no noticable difference:
 
Description	Object Name	Cost 	Cardinality	Bytes	CPU Cost
SELECT STATEMENT, GOAL = CHOOSE		85127	17813650	2654233850	2957587990
 RESULT CACHE	966aq2bc5sgwy9bamy4p898adj				
  MERGE JOIN		85127	17813650	2654233850	2957587990
   SORT JOIN		83704	17813650	1140073600	2826327408
    VIEW		83704	17813650	1140073600	2826327408
     UNION-ALL					
      TABLE ACCESS FULL	tab1	33054	16958679	678347160	11027494218
      TABLE ACCESS FULL	tab2	3870	854971	26504101	502797025
   SORT JOIN		1423	59958	5096430	131260582
    TABLE ACCESS FULL	tab3	238	59958	5096430	52320584

Open in new window

0
Christoffer SwanströmPartnerCommented:
For which query is that explain plan?
0
subratocAuthor Commented:
The explain plan is for:

with v as (
select col1, col2 from tab1
union all
select col1, col2 from tab2
)
select /*+ parallel (v, 32) */ tab3.col1,v.col2 from tab3, v
where v.col1 = tab3.col1

I was hoping that the individual sql statements in the view will take 'col1' as a join condition before applying the 'union all'.
0
Christoffer SwanströmPartnerCommented:
How does the explain plan look for your original query? And if you try to run the query in parallel how long does it take?

Unfortunately, I think it will not be possible to coax the optimizer to do the join before the union. If you take the approach I suggested before (i.e. do a union of several joins) and you are worried about maintaining the code, you could implement a procedure that dynamically creates a view with your query. Then you would need to make changes to only one part in the procedure and then rerun it to recreate the view with identical changes in all the joins.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.