| Joining Two View [message #640537] |
Thu, 30 July 2015 08:49  |
 |
samyms
Messages: 18
Registered: May 2015
Location: Chennai
|
Junior Member |
|
|
Hi
I have problem when joining two 2 views.
ASP_RP_SP_V is view which is doing product value calculation.
when we pass the value directly to this view. it resulting properly (like below)
Example: (working fine)
select * from ASP_RP_SP_V t
where t.lbl='00123' and
t.prod = '0111' and
t.pckg = '01' and
t.id = 140
select * from INP_SP_V --- Only one row its having
output:
lbl prod pckg id
00123 0111 01 140
When i join with other view(INP_SP_V ), then its throwing divide by zero error. but if we pass the value directly then its showing the proper result.
select * from ASP_RP_SP_V t1 , INP_SP_V t2
Where t1.lbl=t2.lbl
and t1.prod=t2.prod
and t1.pckg=t2.pckg
and t1.id=t2.id
Can you please help me how oracle joining these views in background and throwing the error.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Joining Two View [message #640554 is a reply to message #640553] |
Thu, 30 July 2015 10:07  |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
If the division is in the where clause then a change in access path can cause it in some cases and not others.
Say you're looking for rows where col1 is A and rows where col1 is not A have the divisor column set to 0.
An index scan of an index on col1 will avoid the problem data but a full table scan will pick it up.
Bill B's suggestion will avoid the error.
|
|
|
|
| Re: Joining Two View [message #640555 is a reply to message #640554] |
Thu, 30 July 2015 10:20 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Depending on the case, you can also use the NO_MERGE hint:
select /*+ no_merge(t1) no_merge(t2) */ * from ASP_RP_SP_V t1 , INP_SP_V t2...
Or in the opposite force the merge:
select /*+ merge(t2) */ * from ASP_RP_SP_V t1 , INP_SP_V t2...
Or materialize one view:
with v as (select lbl, prod, pckg, id from INP_SP_V where ... and rownum > 0),
select * from ASP_RP_SP_V t1 , v t2 where ...
Or rewrite it with a subquery:
select * from ASP_RP_SP_V t1 where (lbl, prod, pckg, id) in (select lbl, prod, pckg, id from ... )
Or...
--moderator edit: added code tags 
[Updated on: Thu, 30 July 2015 11:22] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: Joining Two View [message #640593 is a reply to message #640589] |
Fri, 31 July 2015 07:15 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
1) You should always select only the columns you need.
2) Reducing the select list increases the chances of oracle using index scans rather than full table scans. So the problem is probably what I said above. What you've done can not be considered a permanent fix - a change in stats can cause a change in the plan and make the error reappear. To be sure you need you fix the division code in the view as Bill B suggested above
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
