oracle query optimization

Hi, I have a fairly big xml which happens to reside in an oracle table of structure like 5-6 varchar columns and 1 clob type column.Clob type column contains xml.Below is section of my procedure that retrieves data from the table.Currently its taking around 35-40 secs.I need to understand if there is any more scope for optimization.
The indexes are correctly used.If there is any scope of implementing pipeline processing, which I have just heard from few collegues. here it is:

 SELECT a.aa, a.bb, a.cc, a.dd, a.ee, a.ff, a.gg, x.hh,x.ii,x.jj,x.kk              FROM schema.tablename a,
                   XMLTABLE(
                       xmlnamespaces(
                           'http://www.w3.org/2003/05/soap-envelope' AS "tns",            
                           'urn:com:test:testnmAS "tst",
                           'urn:com:test1:test1nmas "tst1"
                       ),
                       '//tst:element
                       PASSING xmltype(trim(clobxmldata))
                       columns
                       dd varchar2(100) path '//tst:element3//tst:element2//tst:element1,
                       ee varchar2(100) path '//tst:element6//tst:element4//tst:element3,
                       ff varchar2(10) path '//tst:element9//tst:element8//tst:element7,
                       gg varchar2(100) path  '//tst:element11//tst:element10
                                       ) x  WHERE a.aa= 'somevalue'               AND a.bb= 'value'
                            AND a.cc>= start_date AND a.cc< end_date
                AND a.hh BETWEEN AmountFrom AND amount_to
                and rownum < 2001
                            ORDER BY a.cc DESC
DevildibAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
that doesn't look like it should execute at all.  It's missing quotes.

You should be getting an error if you try to run what is posted above.

I know you're masking the objects, columns and paths but the syntax that is left isn't valid.


But, assuming your real query is valid,   converting your clob to an xmltype on the fly will cost you time, especially if it's large.
Then parsing through it afterward will take more time.

Do you have the option to store your data in xmltype instead of text?

A pipelined function is unlikely to help here.  You would still need to read the clob and parse it, nothing in a pipelined function would help that.
0
DevildibAuthor Commented:
Hi thanks for understanding and ignoring the syntactical errors.Actually the table is getting used among multiple systems.So i cant change the type from clob to xmltype..Can you suggest any sort of optimization or way to execute pipeline processing on this query as i asked.since i am not in a position to change the table structure or data properties right now
0
sdstuberCommented:
if it's applicable, try using explicit paths instead of // wildcarding.

pipelining won't help the xml parsing.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

DevildibAuthor Commented:
Any part that could be rewritten for good ?any possibility for betterment of the performance?
0
sdstuberCommented:
did you try this?

>>> if it's applicable, try using explicit paths instead of // wildcarding.

I can't tell you want the explicit paths are, I don't have your xml

Do you have an index on aa,bb,cc,hh  ?
Not 4 indexes, one on each.
I mean  one index on all 4 columns.

Depending on the volume and distribution of data, it's possible you shouldn't use an index at all, but I don't have your table , your data or your indexes to determine what would be optimal.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdstuberCommented:
Another option, assuming the data is fairly static, would be to materialize the results of the query.

That won't actually save any time on processing the data, but it would mean retrieving it would be faster because the work would already be done.
0
DevildibAuthor Commented:
Thanks sdstuber..query which was taking 35 secs to render 2k records is now bringing in results within a variance of 28-29 secs.I applied complete xml path approach as suggested by you.although i had a question.when i run this query again and again 2-3 times or third time in sql developer it shows me a reducing trend in time in takes.like 28 secs gets reduced to upto 20 or 16 secs smtimes.but why do i not get the same experience when calling the same query(used in a stored proc) from UI.I am using c# dot net as my front end.Although Ui must be taking some additional time before showing complete output to user, but it doesnot show the reducing trend in time taken even a bit when called multiple times.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.