Hello, Ask TOM Team.
I am creating a data model and thinking about create some XMLType columns in Oracle Database 19c because we receive the data in that format and we want to take advantage of this Oracle feature.
I have some questions:
1. Developers say that if I create a XMLType column to store the XML data it will have no performance while the data increases. Because of that, they tell me that I should place some columns of the XML in relational model in order to gain performance when querying through the app because XML query is "slow". I say that we can query the XMLType column using XMLQuery and indexing the relevant columns.
For example:
If I have XML:
<maintag>
<Version>1.0</Version>
<column1>value</column1>
<column2>value</column2>
<column3>value</column3>
<column4>value</column4>
</maintag>
They want:
create table A
column1 varchar(x),
column2 varchar(x),
XML XMLType
What do you think?
2. Are identity columns still useful as PK in tables with XMLType columns?
3. What else should I take into account using XMLType columns?
Thanks in advanced.
Regards,
You need to think about the trade-offs you're making here.
Taking the XML and slapping it straight into an XMLType column makes the insert code much easier. But it pushes complexity to querying. While you can use XMLQuery, this can be fiddly to work with, particularly if your dev team is unfamiliar with it.
Remember typically you only have a handful of insert statements on a table but could easily have hundreds or even thousands of different queries!
Is that trade-off worth it?
Then you have to bear in mind what to do if the contents of the XML changes. If you're dumping it straight in an XMLType, there's little to do if elements are added/removed. But when shredding it into a relational structure, you'll have to update your tables and code appropriately.
If the XML changes regularly, this could generate lots of work.
Is that trade-off worth it?
You're correct, you can index XML, either with a (structured or unstructured) XMLIndex, Oracle Text index or targeted function-based indexes (though these are deprecated).
The docs discuss the differences between these in detail.
https://docs.oracle.com/en/database/oracle/oracle-database/19/adxdb/indexes-for-XMLType-data.html#GUID-AEF30229-B377-4479-9627-45494B772392 Will these give good enough performance? How does it compare to regular table indexes? You'll have to test to find out!
Are identity columns still useful as PK in tables with XMLType columns?Probably? Again, it depends on why you're adding these.
If you want to have foreign keys pointing to this table, then it's highly likely you'll want to add these (assuming there are no other obvious PK candidates).
It also makes it easier to find specific rows, which can make some searches and debugging easier.
If you want to know more about working with XML, the XDB Developer's Guide discusses the ins-and-outs of working with XML in detail.
https://docs.oracle.com/en/database/oracle/oracle-database/19/adxdb/index.html