Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Excessive parsing of queries involving views

Re: Excessive parsing of queries involving views

From: hpuxrac <johnbhurley_at_sbcglobal.net>
Date: 26 Sep 2006 11:18:32 -0700
Message-ID: <1159294712.748909.246950@i3g2000cwc.googlegroups.com>

Ana Ribeiro wrote:
> 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?

Aside from what you noted and a couple of people commented on, please read what Tom Kyte has to say about bind variables. Not using them can cause huge problems in developing scalable applications.

The types of tables that you have in the view along with the names of them and how you are attempting to join them appears to show some kind of implemented in oracle object oriented design. Many of us have seen designers and developers without enough serious relational experience create design nightmares with these types of constructs.

How much experience do the senior database designers have in this project? What kind of design review process is being followed as your company is developing this application? Received on Tue Sep 26 2006 - 13:18:32 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US