9.2.0.5, views, queries, and pushing predicates.....

  • From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 16 Jul 2004 14:33:15 -0400

So, a while ago, I posted a problem I was having where a predicate was
not getting pushed into a view when the view's SQL was the union of two
SQL statements, each of which utilized an analytical function.  I ended
up opening a TAR w/ Oracle.  They actually owned up to the problem, and
two days ago, I actually got a one-off patch against 9.2.0.5 for 64-bit
Solaris.  Well, first thing I tried was running my test case, and, sure
enough, it worked.  It seems my test case wasn't robust enough, though!
My test case had the following SQL to demonstrate the problem:
select * from xan_view where doc_id = 38943105;
and with the patch applied, this did the right thing.  However, in the
real world, we use little things called BIND VARIABLES.

If you re-write the above query and execute:

variable b1 number;
exec :b1:=38943105;
select * from xan_view where doc_id = :b1;

IT FAILS!!!  ARGH!  I mean, I never considered that the fix would only
address the case where a literal is specified!  So, it took two weeks to
get the patch, and I'm right back where I started from!!!

So, that's my frustration of the week.

-Mark

PS  If anyone is interested in the details (full test case, patch#, etc)
just let me know.


Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
"Post Hoc Ergo Propter Hoc"


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: