Query too slow... how can I optimize ?

From: jacquesh <jhullu_at_gmail.com>
Date: Tue, 24 Jul 2007 21:02:52 -0000
Message-ID: <1185310972.900144.133860@w3g2000hsg.googlegroups.com>

hello,

I need to join 2 tables ; 1 VARCHAR2 column and a node IN XMLTYPE COLUMN. Here my query :

select

    XMLELEMENT(FIELD,

        XMLATTRIBUTES(
            f.LABEL AS "LABEL",
            f.NOM   AS "NOM",
            u.code,
            extractValue(VALUE(aclmode1),'XMLNAME/profile_in') AS
ACLMODE
        )

    ) AS FINAL
FROM
    utilisateurs u,
    tfields f,
    table(XMLSequence(extract(u.CUSTOM_ACL,'/FIELDS/XMLNAME'))) aclmode1,

    table(XMLSequence(extract(f.ACL_DEF,'/FIELDS/XMLNAME'))) aclmode2 WHERE
    extractValue(VALUE(aclmode1),'XMLNAME/@ID') = extractValue(VALUE(aclmode2),'XMLNAME/@ID') and

    u.code = '2134357689'
;

have you got a new way to rewrite this query ?

thanks !! Received on Tue Jul 24 2007 - 16:02:52 CDT