I have a table with an XMLColumn:
CREATE TABLE xml_data_table
(
id number PRIMARY KEY,
xml_column XMLType
) XMLType xml_column
STORE AS SECUREFILE BINARY XML;
Then I would like to insert big xml files:
INSERT INTO xml_data_table
VALUES (3,
XMLType(
'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Report>
<Content>
<Dogs>
<Dog>
<id>1</id>
<name>Harry</name>
</Dog>
<Dog>
<id>2</id>
<name>Peter</name>
</Dog>
</Dogs>
<Shelters>
<Shelter>
<id>1</id>
<city>NYC</city>
</Shelter>
<Shelter>
<id>2</id>
<city>Washington DC</city>
</Shelter>
</Shelters>
<Connections>
<Connection>
<dogid>1</dogid>
<shelterid>1</shelterid>
</Connection>
</Connections>
</Content>
</Report>'
));
The XML files contains three lists: Dogs, Shelters, Connections. Between dogs and shelters there is an m:n connection and the connection is made in the Connections list.
I know I can make relational views over the data and query the fields like that.
CREATE OR REPLACE VIEW dog_view
AS
SELECT dog.*
FROM xml_data_table tbl,
XMLTable(
'/Report/Content/Dogs/Dog' PASSING tbl.xml_column
COLUMNS
ID VARCHAR2(30) PATH 'id',
NAME VARCHAR2(30) PATH 'name') dog;
SELECT *
FROM dog_view
WHERE id = '1'
I can also create relational views for shelters and connections if I want to.
I can even index the view:
CALL DBMS_XMLINDEX.registerParameter(
'dog_view',
DBMS_XMLSTORAGE_MANAGE.getSIDXDefFromView('DOG_VIEW'));
CREATE INDEX dog_view_idx on xml_data_table tbl (tbl.xml_column) INDEXTYPE IS XDB.XMLIndex
PARAMETERS ('PARAM dog_view');
And then when querying the data the select by id are noticeably faster. Remember the XML files can be potentially huge.
What I want to do now, is also having indices on the shelter ids. If those would be relational tables, I would index all the id columns, however, when trying to create more indices, I get the following error:
An attempt was made to define multiple domain indexes on the same column list using identical indextypes.
How can I query data across all the tables with a lot of data quickly? Is there a way to index just the columns I require?