1

Which is better in terms of performance: xmltable or xmlsequence in Oracle?

John Bensin
  • 301
  • 2
  • 5
  • 20
abhi
  • 621
  • 1
  • 6
  • 17
  • Why don't you try both on your `DEV` or `TEST` environment? That way you'll have the answer tailored specifically to your exact requirements and hardware/network etc. – Ollie Apr 23 '12 at 08:02

2 Answers2

3

I think internally the execution plan is the same. Test the resource usage and execution plan using "set autotrace on"

Check out this article: http://www.liberidu.com/blog/2007/07/10/about-tablexmlsequence-and-xmltable/

Rob van Laarhoven
  • 8,737
  • 2
  • 31
  • 49
  • I have used both approaches , using the xmltable is more clean and you can specify the data type of each column you are extracting , whereas in xmlsequence you will have to extract and do like "TO_DATE(EXTRACTVALUE (T2.column_value, 'ROW/LAST_UPDATING_DATE'),'DD-MON-YYYY') LAST_UPDATING_DATE".I have already checked the link http://www.liberidu.com/blog/2007/07/10/about-tablexmlsequence-and-xmltable/, i am looking for more factual inputs – abhi Apr 23 '12 at 09:40
  • One more difference is that xmlsequence is deprecated in oracle 11g r2 – abhi Apr 23 '12 at 10:00
  • There you are! Fact: don't use xmlsequence ! – Rob van Laarhoven Apr 23 '12 at 14:53
0

With Respect to the question, Please find some Factual points

  1. Deprecated in 12c+
  2. Performance wise XMLTABLE Is Good, if you have 2 or more PATH Expressions to be retrieved,
  3. Parsing for each Column takes time, instead you can create view using XML TABLE and XMLTYPE Refer this Link xmlTable
  4. Additional Reference - https://www.igorkromin.net/index.php/2016/03/15/oracle-database-xmltable-vs-extractvalue-performance/
Karthik_Rajendiran
  • 43
  • 8