how to improve varchar2 query performance in case the column has xml content
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?