Index on XML Type column

Toad Data Modeler
1

Hi,

(TDM 3.4.15.10, Oracle 10g r2)

I have a table with a schema based XMLType column, which I create an index on, for example;

CREATE TABLE data_table(
…
data_xml XMLType,
…
)
TABLESPACE TBSP
XMLType “data_xml” STORE AS CLOB
XMLSCHEMA “data.xsd”
ELEMENT “XXXX”;

CREATE INDEX data_num_idx
ON data_table (
SUBSTR( EXTRACTVALUE (data_xml, ‘/XXXX//Number’),1,4),
SUBSTR( EXTRACTVALUE (data_xml, '/XXXX//Series’),1,1),
SUBSTR( EXTRACTVALUE (data_xml, ‘/XXXX/*/Year’),1,4),
SUBSTR( EXTRACTVALUE (data_xml, ‘//DataId’),1,4)
);

After RE the index script is generated as;
CREATE INDEX DATA_NUM_IDX ON DATA_TABLE
(SUBSTR(EXTRACTVALUE(SYS_MAKEXML(‘8C23A8A6D38831CEE092909853804B18’,4815,“SYS_NC00003$”),’/XXXX//Number’),1,4),
SUBSTR(EXTRACTVALUE(SYS_MAKEXML(‘8C23A8A6D38831CEE092909853804B18’,4815,“SYS_NC00003$”),’/XXXX//Series’),1,1),
SUBSTR(EXTRACTVALUE(SYS_MAKEXML(‘8C23A8A6D38831CEE092909853804B18’,4815,“SYS_NC00003$”),’/XXXX/*/Year’),1,4),
SUBSTR(EXTRACTVALUE(SYS_MAKEXML(‘8C23A8A6D38831CEE092909853804B18’,4815,“SYS_NC00003$”),’//DataId’),1,4))
LOGGING
TABLESPACE TBSP
NOPARALLEL
/

I think this is a correct representation of the index as Oracle has created it (as shown in TOAD). However it is not transportable. If the schema is destroyed and reloaded (or a different target db is used) the magic numbers change.
RE needs to somehow extract the original SQL text.

Also the field in the index dialog is a single line text field. A Multiline field would make something this long much more understandable.

Regards
Malcolm

2

Hi Malcolm,

Just a quick note that we will be dealing with it. I need to ask you for patience.
Thanks a lot.

Regards,

Vladka + TDM Team

3

I think this is a correct representation of the index as Oracle has created it (as shown in TOAD). However it is not transportable. If the schema is destroyed and reloaded (or a different target db is used) the magic numbers change.

RE needs to somehow extract the original SQL text.

Also the field in the index dialog is a single line text field. A Multiline field would make something this long much more understandable.

Hi Malcolm,

Here I’m with the reply.

Unfortunately, we’re not able to solve it now. We’re sorry.

We will see what we can do about it. CR # 71 818.

Thanks.

Regards,

Vladka + TDM Team

4

Hi Vladka,

Unfortunately, we’re not able to solve it now.
Please explain.

I think the required info can be extracted as follows;
It looks like the first parameter in SYS_MAKEXML is the SchemaOid (see sys.ku$_xmlschema_view). This gives the schema URL. SYS.user_xml_tab_cols will then show the column name of the table that maps to that schema.
So the SYS_MAKEXML(…) could be replaced with the column name which will give the required SQL.

I guess if the table had multiple xmltype columns based on the same schema this would fail to resolve the correct column name, in which case you could leave the SYS_MAKEXML() in.

HTH

Regards
Malcolm

5

Hello Malcolm,

We can’t find out if user set e.g. the following on his own and in this format (and wanted to load it the same way during RE):
SUBSTR(EXTRACTVALUE(SYS_MAKEXML(‘8C23A8A6D38831CEE092909853804B18’,4815,“SYS_NC00003$”),’/XXXX/*/Number’),1,4)

We will look into the matter later. In any case, we don’t plan any modifications in the near future.

Please find attached a script that should do what you described. Please run it in Scripting Window. Set appropriate model in the script.
The script will go through all entities with XMLType attributes. In entities that have only one XMLType attribute, it will go through all indexes and replace SYS_MAKEXML by XMLType attribute name.

If you have any questions, please write us back. Thanks.

Regards,

Vladka + Mario
OR_XMLType_Index.txt (2 KB)