Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Javier.

Asked: February 18, 2005 - 8:30 pm UTC

Last updated: February 21, 2005 - 11:58 am UTC

Version: 8.0.5

Viewed 1000+ times

You Asked

Hi Tom,

Two questions:

1) In "Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2)", Example 2-45, an example of a nonmergeable view that uses the UNION set operator is presented.
I don't understand why it is a nonmergeable view.
In fact, after transforming the query by pushing the predicate, why cannot it be further transformed (by using the rule "distribution of projection over union" in relational algebra) into the following query?

SELECT employee_id, last_name
FROM emp1
WHERE department_id = 50
UNION
SELECT employee_id, last_name
FROM emp2
WHERE department_id = 50

2) I have a Pro*C program using bind variables and recently created a histogram over a highly skewed column used in the program (like "where <column> = :variable"). What is the best way to modify my program in order for the CBO to use the histogram? Can I use dynamic SQL? I'm using version 8.0.5

Thanks a lot!


and Tom said...

how about a link into otn.oracle.com, the documentation?

but in any case it is non-mergable, but that does NOT mean "not subject to predicate pushing"!

it is non mergable since the union operation implies a sort/distinct step. So, we cannot take that view (call it V) and just "merge it into a larger query", since we have to sort/distinct it before say joining it to something else.


We can (and will) push a predicate, so that:

select * from V where deptno = 50;

will be what you want - however,

select * from V where v.deptno = 50 and <whatever other join>

will not be merged into a single "query" -- the view must be preserved -- so we can sort/distinct it AND THEN join.

so, predicate pushing (different from merging) will be done...

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:3561623983484#29347346730613 <code>

2) you would have to "not bind" that column - meaning you would be using dynamic sql - probably "method 3"

2)

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you Tom !

Javier Diaz, February 21, 2005 - 11:58 am UTC

You are great !