Skip to Main Content
Forums

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

took 6 hours to complete xquery!!! help me.

587246Aug 2 2007 — edited Mar 26 2012
I got a xml schema which has more than 3 thousands of elements.

I created xml table and tried to insert elements of the schema into the table.
It took about 6 hours completed.
What a performance !

CREATE TABLE "FINESUSER"."TSCHEMA" OF SYS.XMLTYPE XMLTYPE STORE AS CLOB

INSERT INTO TSCHEMA SELECT HTTPURIType('http://www.xbrl.or.kr/kr/fr/common/pte/2006-05-31/krfr-pte-2006-05-31.xsd').getXML() FROM dual;

below XQuery took almost 6 hours (about 18,000 seconds)
Is there any idea to take not much time ?

SELECT id,concat(concat(prefix,':'),name) as name,tp,grp,abstract,nillable,period
FROM TSCHEMA t,
XMLTABLE(
XMLNAMESPACES(
'http://www.w3.org/1999/xlink' AS "xlink",
'http://www.xbrl.org/2003/linkbase' AS "link",
'http://www.xbrl.org/2003/instance' AS "xbrli",
'http://www.xbrl.org/2003/iso4217' AS "iso4217",
'http://www.xbrlkorea.com/kr/kisinfo/fr/gaap/ci/2007-02-09' AS "kisinfo-ci",
'http://www.xbrlkorea.com/kr/kisinfo/fr/gcd/2007-02-09' AS "kisinfo-gcd",
'http://www.xbrlkorea.com/kr/kisinfo/fr/profile/2007-02-09' AS "kisinfo-profile",
'http://www.xbrlkorea.com/kr/kisinfo/fr/ratio/2007-02-09' AS "kisinfo-ratio",
'http://www.xbrlkorea.com/kr/kisinfo/fr/common/scenario' AS "kisinfo-scenario",
'http://www.xbrl.or.kr/kr/fr/gaap/ci/2006-05-31' AS "kr-gaap-ci",
'http://www.xbrl.or.kr/kr/fr/common/pte/2006-05-31' AS "krfr-pte",
'http://www.xbrl.or.kr/kr/fr/common/ptr/2006-05-31' AS "krfr-ptr",
'http://www.xbrl.or.kr/2006/role/subitem-notes' AS "p0",
'http://xmlns.oracle.com/xdb' AS "ora",
DEFAULT 'http://www.w3.org/2001/XMLSchema'),
'

let $ns := $doc/schema/@targetNamespace
for $prefix in $doc/schema/@xmlns:*[.=$ns]/name()
where contains($prefix,":")
return
let $pn := substring-after($prefix,":")


for $e in $doc/schema/element
return <item prefix="{$pn}">{$e}</item>'

PASSING t.OBJECT_VALUE as "doc"
COLUMNS
id varchar2(128) PATH 'element/@id',
prefix varchar2(128) PATH '@prefix',
name varchar2(128) PATH 'element/@name',
tp varchar2(16) PATH 'element/@type',
grp varchar2(16) PATH 'element/@substitutionGroup',
abstract varchar2(16) PATH 'element/@abstract',
nillable varchar2(16) PATH 'element/@nillable',
period varchar2(16) PATH 'element/@xbrli:periodType'
) xt;

Comments

783956
EDIT: I just realized that this query probably doesn't give you what you are looking for after all. While it will display the leaf values, it does not display their root parent. I left the post here in case it may provide a starting point for an alternate solution that does everything you want.

Hi,

Since you only want the leaf values and those values cannot be parents, the following query will give you the results you want without traversing the hierarchy at all:
select child
  from test_hierarchy
minus
select parent p
  from test_hierarchy;
A word of caution, I have no idea if this will be any faster than the query you posted but, by definition, it should give you the results you are looking for. It is possible that a variation of this query (using a correlated query for instance instead of minus) may provide the performance you are seeking.

HTH,

John.

Edited by: 440bx - 11gR2 on Jul 26, 2010 5:57 PM added the EDIT at the top.
422652
In my case there can be more than one root.

Let's say A and 1 are root nodes and I want to select all the leaf children for A and all the leaf children for 1 along with the information as to which leaves are children for A and which are children for 1. So I want the information for parents also along with their leaf children
Frank Kulash
Hi,

Welcome to the forum!

Sorry, I think SELECT DISTINCT is the best that you can do in SQL. I've never heard of any built-in feature that checks for distinct nodes as it traverses the graph.
You could write something in PL/SQL (a pipelined fucntion, perhaps) that checks each new node against a list of nodes already found. There's no guarantee it will be faster, though.

Thanks for supplying the CREATE TABLE and INSERT statements; that's very hepful.
783956
Hi Frank,

This is a "wild" guess on my part.

I thought that the number of traversals thought the hierarchy would be lower going from leaf to parent instead of parent to leaf. Of course, the downside is that there is a potentially significant cost to determine the leaves to start with.

I tried testing this thought but, I am not well versed enough (yet) in hierarchy traversal to test what I've just mentioned.

Does this thought have any merit ?

thanks,

John.
Frank Kulash
Hi, John,
440bx - 11gR2 wrote:
Hi Frank,

This is a "wild" guess on my part.

I thought that the number of traversals thought the hierarchy would be lower going from leaf to parent instead of parent to leaf. Of course, the downside is that there is a potentially significant cost to determine the leaves to start with.

I tried testing this thought but, I am not well versed enough (yet) in hierarchy traversal to test what I've just mentioned.

Does this thought have any merit ?
Sorry, I don't understand. Could you describe in more detail, with examples from OP's sample data, what you mean?

It's true that bottom-up queries (from leaves to roots) can be faster in trees, because there is no branching in that direction: nodes in a tree can have (at most) only one parent.
The graph in this problem is not a tree: nodes can have two or more parents, so a bottom-up query could branch just as much as a top-down query.
Jaydip Bosamiya
Dear,

you can use DISTINCT in your query to get your desired result,

so, use following query,

Select DISTINCT connect_by_root(parent), child from test_hierarchy where connect_by_isleaf = 1
start with parent = 'a'
connect by prior child = parent

Thanks,
Jaydip
Aketi Jyuuzou
Ladies and gentleman.
I say again and again that I like recursive with clause B-)

create table test_hierarchy(parent,child) as
select 'a','b' from dual union all
select 'a','c' from dual union all
select 'a','d' from dual union all
select 'b','c' from dual union all
select 'b','d' from dual union all
select 'c','d' from dual union all
select 'd','e' from dual union all
select 'd','f' from dual;
with rec(rootParent,parent,child,Lv) as(
select parent,parent,child,1
  from test_hierarchy
 where parent = 'a'
union all
select a.rootParent,b.parent,b.child,a.Lv+1
  from rec a Join test_hierarchy b
    on a.child = b.parent
 where (b.parent,b.child)
 not in(select c.parent,c.child
          from test_hierarchy c
        start with c.parent = 'a'
        connect by prior c.child = c.parent
               and Level <= a.Lv))
select rootParent,parent,child,LV
  from rec a
 where not exists(select 1 from rec b
                   where a.child = b.parent);

R  P  C  LV
-  -  -  --
a  d  e   2
a  d  f   2
Actually I suppose above SQL is bad performance :8}
Then I recommend using PL/SQL like my homepage
http://www.geocities.jp/oraclesqlpuzzle/plsql-5.html
1 person found this helpful
John Spencer
Are you sure that your sample data is actually representitive of you real data?

Your hierarchy appears to have significant overlap and/or duplication of paths.
SQL> WITH test_hierarchy as(
  2     SELECT 'a' parent, 'b' child FROM dual UNION ALL
  3     SELECT 'a','c' FROM dual UNION ALL
  4     SELECT 'a','d' FROM dual UNION ALL
  5     SELECT 'b','c' FROM dual UNION ALL
  6     SELECT 'b','d' FROM dual UNION ALL
  7     SELECT 'c','d' FROM dual UNION ALL
  8     SELECT 'd','e' FROM dual UNION ALL
  9     SELECT 'd','f' FROM dual)
 10  Select LPAD(' ', level*2, ' ')||parent||'-'||child hier,
 11         connect_by_root(parent) root, level lv
 12  from test_hierarchy
 13  -- where connect_by_isleaf = 1
 14  start with parent = 'a'
 15  connect by prior child = parent;
 
HIER            R         LV
--------------- - ----------
  a-b           a          1
    b-c         a          2
      c-d       a          3
        d-e     a          4
        d-f     a          4
    b-d         a          2
      d-e       a          3
      d-f       a          3
  a-c           a          1
    c-d         a          2
      d-e       a          3
      d-f       a          3
  a-d           a          1
    d-e         a          2
    d-f         a          2
Their are clearly 4 seperate paths to the leaves, all with the root at A.

John
422652
Thanks ... but as far as I know the recursive with clause is supported only in Oracle 11g. And my client is using Oracle 10g. Also, I have not tried this on oracle 11g instance but I am assuming that the output and query you wrote below is for oracle only.

Also, as per Franks suggestion; I did some reading on pipelined functions. Basically, I tried creating a function (not pipelined) that will take a collection and a string. The function would update the collection to have this string if it is not present already in the collection. And would return true if the collection gets modified, false otherwise.

But now am stuck because i cannot call this function in sql query as it has an out parameter.

Is the only option left for me is to persist the collection and then query?

Lastly, if I can keep everything in memory then I do not think i require a pipeline function. Please let me know if I am correct or not.

Below is the function

create type stringCollection as table of varchar2(100);

create or replace function addStringInCollection (inputString IN String, collectedParents IN stringCollection )
return stringCollection AS
returnCollection stringCollection;
BEGIN
--Add element into the collection
returnCollection := collectedParents;
returnCollection.EXTEND;
returnCollection(returnCollection.count + 1) := inputString;
return returnCollection;
END addStringInCollection;
Frank Kulash
Hi,
sarora@informatica.com wrote:
Thanks ... but as far as I know the recursive with clause is supported only in Oracle 11g. And my client is using Oracle 10g. Also, I have not tried this on oracle 11g instance but I am assuming that the output and query you wrote below is for oracle only.

Also, as per Franks suggestion; I did some reading on pipelined functions. Basically, I tried creating a function (not pipelined) that will take a collection and a string. The function would update the collection to have this string if it is not present already in the collection. And would return true if the collection gets modified, false otherwise.

But now am stuck because i cannot call this function in sql query as it has an out parameter.
Functions that have OUT parameters, or that return data types not found in SQL (even BOOLEAN), can only be called from PL/SQL. They can not be used in SQL statements, even SQL statements embedded in PL/SQL.
I suggest a function that takes one IN-argument, a string (such as 'A'), that returns a delimited string (such as 'E,F'). You could split that string into parts after it was returned, if necessary.
So you might call the function in a SQL statment like this: 
SELECT  get_leaves ('A')
FROM    my_table;
and it would display 
GET_LEAVES
--------------------
E,F
Inside get_leaves, you can use whatever other functions yu want, including functions with user-defined arguments and return values. (OUT arguments in functions are not very good programming, but they would be allowed, if you really want them.)
It's only inside get_leaves that you need to keep track of what nodes have been visited already; the calling program doesn't need to see that part of the job at all.
422652
But then I wont be able to use connect by. Correct? And connect by is performing well in most cases except when i have a scenario where a node gets repeated.
Frank Kulash
Hi,
sarora wrote:
But then I wont be able to use connect by. Correct? And connect by is performing well in most cases except when i have a scenario where a node gets repeated.
Having an alternative to CONNECT BY won't keep you from using CONNECT BY. You'll have your choice of which to use.

As I said in my first message, I don't think Oracle SQL is capable of anything better that CONNECT BY and DISTINCT in this case. If it's important enough to you, try PL/SQL. A user-defined PL/SQL function might be faster than CONNECT BY and SELECT DISTINCT, but I can't guarantee it.
Aketi Jyuuzou
My homepage mentions similar problem.
http://www.geocities.jp/oraclesqlpuzzle/10-334.html
1 - 13
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 23 2012
Added on Aug 2 2007
#performance-availability, #xml-db
8 comments
2,696 views