Excessive parsing of queries involving views

From: Ana Ribeiro <ana.ribeiro_at_reflective.com>
Date: 26 Sep 2006 07:07:24 -0700
Message-ID: <1159279644.594393.292550@h48g2000cwc.googlegroups.com>

I have a view

VIEW CLASSIFICATIONOBJECT ( ID, DESCRIPTORID ) AS select ob.id, iv1.stringvalue DescriptorId from objects ob, simpleassociations sa1, orderedassociations oa, instancevalues iv1,

     attributenames an1, modeltypes_tab mt1, modeltypes_tab mt2, objects ob2
where ob.id = sa1.end1 and sa1.id = mt1.id and mt1.typename =
'A3.ObjectDescriptorGroup' and ob2.id = sa1.end2
 and sa1.end2 = oa.end1 and oa.id = mt2.id and mt2.typename =
'RAS.ClassificationSchema.DescriptorValues'
 and iv1.object = oa.end2 and iv1.attribute = an1.attrnameid and an1.attrname = 'identifier'
 and exists (select connection from perspective where connection = userenv('sessionid') and repid = ob.repid and version = ob.startv)  and not exists(select connection from perspective where connection = userenv('sessionid') and repid = ob.repid and version in (select distinct endv from vend where vid = ob.vid))  and exists (select connection from perspective where connection = userenv('sessionid') and repid = sa1.repid and version = sa1.startv)  and not exists(select connection from perspective where connection = userenv('sessionid') and repid = sa1.repid and version in (select distinct endv from vend where vid = sa1.vid))  and exists (select connection from perspective where connection = userenv('sessionid') and repid = oa.repid and version = oa.startv)  and not exists(select connection from perspective where connection = userenv('sessionid') and repid = oa.repid and version in (select distinct endv from vend where vid = oa.vid))  and exists (select connection from perspective where connection = userenv('sessionid') and repid = ob2.repid and version = ob2.startv)  and not exists(select connection from perspective where connection = userenv('sessionid') and repid = ob2.repid and version in (select distinct endv from vend where vid = ob2.vid)) with read only

which I use as part of a query

e.g.

select id
  from indObj
 where lower(attrname) = 'name'
   and id in (select id

                from classificationObject
               where contains(descriptorid, '12610') > 0
              intersect
              select distinct id
                from indexedObject
               where contains(value, 'Enterprise') > 0)

 order by stringvalue

When this is run (On Oracle 10.2.0.1.0) I experience about 580 parses/second. This seems very high to me. I am wondering whether the use of literals in the view definition would cause the view to be reparsed (frequently) during the query evaluation? Received on Tue Sep 26 2006 - 09:07:24 CDT