SQL Performance (MOSC)

MOSC Banner

how to improve varchar2 query performance in case the column has xml content

edited Aug 8, 2013 7:15AM in SQL Performance (MOSC) 4 commentsAnswered
Hi Community  ,

We have a  table  that contains  a varchar2(4000) column and this colummn stores an  xml  data. This  table is  queried  usually by searching a number inside  the  varchAr2:

tableA ( col1 varchar2(4000))

col1 sample data: 

<?xml version="1.0" encoding="iso-8859-9"?><XmlString><SONUC>1</SONUC><DURUM>0</DURUM><ACIKLAMA></ACIKLAMA><TUKETICILIST><TUKETICI><YETKILI>KS VERİ GİRİŞ 2</YETKILI><VERDEKOD>832134312</VERDEKOD><HESAPNO>323243254</HESAPNO><KARTTIP>0</KARTTIP> ............

and sql: 

select   * from  tableA where col1  like '%832134312%'

(we want to  search the xml content  based on  its tags, in this case  we want to find every  data that has  <verdekod> equals to  832134312)

Since  the table has lots of rows  ,  the query performance is  poor.  And since  we use like  ,  indexing col1 has no benefit.   How can  we increase the  performance  of this  query?  we are thinking of storing this xml conrent inside an xml db . Will it help to improve the performance?

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center