DevOps, CI/CD and Automation

Hello,

I want to perform business intelligence queries over three tables Publication, Author,

Proceedings. Each table is of XMLType with Binary XML storage. The three tables form a star in

the sense that table Publication is the fact and Author and Proceedings are Dimensions. Table Publication has, among others, two XML Elements

- "/publication/relationships/authored_by/author/author_id" plays the role of a foreign key

and references "/author/@author_id" in Author Table,

-"/publication/relationships/publi_conf/proceedings/@proceedings_id" also plays the role of a

foreign key and references "/proceedings/@proceedings_id in Proceedings table.

Now, I want to count the number of publications of a given author by proceedings. I used

XMLTable views so that I can express a Group By clause. The query is the following:

-----------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------
select t_a.author_name, t_r.proceedings_id, count(*) "total"
from author a,
XMLTable ('/author[1]' passing a.object_value columns author_id number path

'@author_id', author_name varchar2(100) path 'author_name') t_a,
publication p,
XMLtable ('/publication[1]' passing p.object_value
columns
publication_id varchar2(100) path '@publication_id',
authors xmltype path 'relationships/authored_by/author',
proceedings_id varchar2(100) path

'relationships/publi_conf/proceedings/@proceedings_id',
typpe varchar2(13) path 'type') t_p,
XMLtable('author' passing authors columns author_id number path

'@author_id') aut,

proceedings r,
XMLTable('/proceedings[1]' passing r.object_value
columns
proceedings_id varchar2(100) path '@proceedings_id',
title varchar2(100) path 'title') t_r

where t_p.typpe='inproceedings'
t_a.author_name ='myname'
and aut.author_id = t_a.author_id
and t_r.proceedings_id=t_p.proceedings_id
group by t_a.author_name , t_r.proceedings_id
-----------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------

---------

I also added XMLIndex indexes on the three tables (with structured components) and then I added

secondary B*Tree indexes on each column of the primary index tables.

Now, when I run the query, I keep waiting for the result in vain...However,

surprisingly, if I replace "proceedings_id" by "title" in the select clause and the group by

clause , I get the result within 80 seconds.

I also tried the same query for the same data but using structured storage instead of Binary

XML. Here, I can get the result quickely since its stored object-relationally, and even when data are grouped by author_id and proceedings_id. So, my questions are:

1. is there any restriction when grouping data using XMLTable? for example the group by clause

mustn't contain an XML Attribute even after projecting it on an XMLTable?

2. Is the problem with regards to binary XML storage?

can anyone help please?

Regards.

D.