Create an index on multiple 'fields' in an XMLColumn

36 views Asked by At

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?

0

There are 0 answers