1 2 Previous Next 23 Replies Latest reply: Aug 23, 2012 8:08 AM by Ric79 Go to original post RSS
      • 15. Re: Referencing a node
        Ric79
        Thanks,
        I will try tomorrow and I will confirm your excellent results

        Where did you add "xdb:defaultTable" in the xsd?

        Riccardo
        • 16. Re: Referencing a node
          odie_63
          See Oracle XML Schema Annotations :
          xdb:defaultTable – Name of the default table generated for each global element when parameter GENTABLES is TRUE. Setting this to the empty string, "", prevents a default table from being generated for the element in question.
          In the present case :
          <element name="measCollecFile" xdb:defaultTable="MEAS_COLLEC_XML">
          This annotation also allows a schema-based XML file to be automatically loaded into the default table when it is dropped into the XML DB repository via FTP, WebDAV or PL/SQL.
          • 17. Re: Referencing a node
            Ric79
            Ok. We are near the end (I hope)

            So
            1) in XSD I added
            xmlns:xdb="http://xmlns.oracle.com/xdb" xdb:storeVarrayAsTable="true"
            ..
              <element name="measCollecFile" xdb:defaultTable="XML_A_MEASCOLLECFILE">
            ..
            ... xdb:SQLType="TIMESTAMP WITH TIME ZONE" ..
            ... xdb:defaultTable="" .. for all <complexType> <element>
            2) The renaming is
            SELECT LEVEL, TABLE_NAME, TABLE_TYPE_NAME, PARENT_TABLE_NAME, PARENT_TABLE_COLUMN
                  FROM USER_NESTED_TABLES
            CONNECT BY PRIOR TABLE_NAME = PARENT_TABLE_NAME
            START WITH PARENT_TABLE_NAME = 'CKGNT_XML_LOADING';
            
            LEVEL     TABLE_NAME             TABLE_TYPE_NAME        PARENT_TABLE_NAME        PARENT_TABLE_COLUMN
            1         XML_A_MEAS_DATA_TAB    measData1857_COLL      CKGNT_XML_LOADING        "SYS_NC00006$"."measData"
            2         XML_A_MEAS_INFO_TAB    measInfo1856_COLL      XML_A_MEAS_DATA_TAB      measInfo
            3         XML_A_MEAS_TYPE_TAB    measType1851_COLL      XML_A_MEAS_INFO_TAB      measType
            3         XML_A_MEAS_VALUE_TAB   measValue1855_COLL     XML_A_MEAS_INFO_TAB      measValue
            4         XML_A_MEAS_R_TAB       r1854_COLL             XML_A_MEAS_VALUE_TAB     r
            3) Added also the index: CREATE INDEX IX_XML_A_MEAS_VALUE_ON XML_A_MEAS_VALUE_TAB("measObjLdn");

            4) The query
            WITH T
                 AS (        SELECT /*+ no_merge */
                                   X1.COLUMN_NAME, SUBSTR(X2.NATIVE_OBJ_IDT, LENGTH('ggsnApnStats,ggsnApnName=') + 1) NATIVE_OBJ_IDT, X3.R_VAL
                               FROM CKGNT_XML_LOADING T,
                                    XMLTABLE(XMLNAMESPACES(DEFAULT 'http://www.3gpp.org/ftp/specs/archive/32_series/32.435#measCollec'), '/measCollecFile/measData/measInfo'
                                             PASSING T.XML_DOC_A) X0,
                                    XMLTABLE(XMLNAMESPACES(DEFAULT 'http://www.3gpp.org/ftp/specs/archive/32_series/32.435#measCollec'), '/measInfo/measType'
                                             PASSING X0.COLUMN_VALUE
                                             COLUMNS COLUMN_NAME VARCHAR2(300) PATH '.', COLUMN_ID NUMBER PATH '@p') X1,
                                    XMLTABLE(XMLNAMESPACES(DEFAULT 'http://www.3gpp.org/ftp/specs/archive/32_series/32.435#measCollec'), '/measInfo/measValue'
                                             PASSING X0.COLUMN_VALUE
                                             COLUMNS NATIVE_OBJ_IDT VARCHAR2(4000) PATH '@measObjLdn', RSET_XML XMLTYPE PATH 'r') X2,
                                    XMLTABLE(XMLNAMESPACES(DEFAULT 'http://www.3gpp.org/ftp/specs/archive/32_series/32.435#measCollec'), '/r'
                                             PASSING X2.RSET_XML
                                             COLUMNS R_VAL NUMBER PATH '.', R_P_ID NUMBER PATH '@p') X3
                              WHERE X3.R_P_ID = X1.COLUMN_ID AND X2.NATIVE_OBJ_IDT LIKE 'ggsnApnStats,ggsnApnName=%' AND T.FILE_NAME = 'A20120822.1230+0200-1245+0200_GMI05U.xml')
              SELECT NATIVE_OBJ_IDT,
                     MAX(CASE WHEN COLUMN_NAME = 'ggsnApnActivePdpContextCount' THEN R_VAL END) ACTIVE_PDP_CONTEXT_COUNT,
                     MAX(CASE WHEN COLUMN_NAME = 'ggsnApnAttemptedActivation' THEN R_VAL END) ATTEMPTED_ACTIVATION,
                     MAX(CASE WHEN COLUMN_NAME = 'ggsnApnAttemptedDynActivation' THEN R_VAL END) ATTEMPTED_DYN_ACTIVATION,
                     MAX(CASE WHEN COLUMN_NAME = 'ggsnApnAttemptedDeactivation' THEN R_VAL END) ATTEMPTED_DEACTIVATION,
                     MAX(CASE WHEN COLUMN_NAME = 'ggsnApnAttemptedSelfDeactivation' THEN R_VAL END) ATTEMPTED_SELF_DEACTIVATION,
                     MAX(CASE WHEN COLUMN_NAME = 'ggsnApnCompletedActivation' THEN R_VAL END) COMPLETED_ACTIVATION,
                     MAX(CASE WHEN COLUMN_NAME = 'ggsnApnCompletedDynActivation' THEN R_VAL END) COMPLETED_DYN_ACTIVATION
                FROM T
            GROUP BY NATIVE_OBJ_IDT;
            5) Your query has TABLE ACCESS FULL, even if there are "automatic" index in the table (ie: CREATE UNIQUE INDEX XML_A_MEAS_TYPE_TAB_MEMBERS ON XML_A_MEAS_INFO_TAB ("measType"))
            Plan hash value: 1487829580
             
            ------------------------------------------------------------------------------------------------------------------
            | Id  | Operation                          | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
            ------------------------------------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT                   |                             |   548 |  1159K|    49   (7)| 00:00:01 |
            |   1 |  HASH GROUP BY                     |                             |   548 |  1159K|    49   (7)| 00:00:01 |
            |   2 |   VIEW                             |                             |   548 |  1159K|    48   (5)| 00:00:01 |
            |*  3 |    HASH JOIN                       |                             |   548 |  3351K|    48   (5)| 00:00:01 |
            |   4 |     TABLE ACCESS FULL              | XML_A_MEAS_TYPE_TAB         |   431 |   852K|     3   (0)| 00:00:01 |
            |*  5 |     HASH JOIN                      |                             |  5246 |    21M|    45   (5)| 00:00:01 |
            |*  6 |      HASH JOIN                     |                             |   123 |   265K|    11  (10)| 00:00:01 |
            |*  7 |       HASH JOIN                    |                             |    33 |  6270 |     6  (17)| 00:00:01 |
            |   8 |        NESTED LOOPS                |                             |       |       |            |          |
            |   9 |         NESTED LOOPS               |                             |     1 |   160 |     3   (0)| 00:00:01 |
            |  10 |          TABLE ACCESS FULL         | XML_A_MEAS_DATA_TAB         |     1 |    20 |     2   (0)| 00:00:01 |
            |* 11 |          INDEX UNIQUE SCAN         | XML_A_MEAS_DATA_TAB_MEMBERS |     1 |       |     0   (0)| 00:00:01 |
            |* 12 |         TABLE ACCESS BY INDEX ROWID| CKGNT_XML_LOADING           |     1 |   140 |     1   (0)| 00:00:01 |
            |  13 |        TABLE ACCESS FULL           | XML_A_MEAS_INFO_TAB         |    33 |   990 |     2   (0)| 00:00:01 |
            |* 14 |       TABLE ACCESS FULL            | XML_A_MEAS_VALUE_TAB        |   123 |   242K|     5   (0)| 00:00:01 |
            |  15 |      TABLE ACCESS FULL             | XML_A_MEAS_R_TAB            | 21327 |    41M|    33   (0)| 00:00:01 |
            ------------------------------------------------------------------------------------------------------------------
             
            Predicate Information (identified by operation id):
            ---------------------------------------------------
             
               3 - access("SYS_ALIAS_14"."p"="SYS_ALIAS_12"."p" AND 
                          "NESTED_TABLE_ID"="SYS_ALIAS_8"."SYS_NC0001700018$")
               5 - access("NESTED_TABLE_ID"="SYS_ALIAS_10"."SYS_NC0000700008$")
               6 - access("NESTED_TABLE_ID"="SYS_ALIAS_8"."SYS_NC0001900020$")
               7 - access("NESTED_TABLE_ID"="SYS_ALIAS_6"."SYS_NC0001000011$")
              11 - access("NESTED_TABLE_ID"="T"."SYS_NC0001700018$")
              12 - filter("T"."FILE_NAME"='A20120822.1230+0200-1245+0200_GMI05U.xml')
              14 - filter("SYS_ALIAS_10"."measObjLdn" LIKE 'ggsnApnStats,ggsnApnName=%')
             
            Note
            -----
               - dynamic sampling used for this statement (level=2)
            Any idea for forcing to use index? However the query run nicely, but sometimes is really slow!

            Riccardo

            Edited by: Ric79 on Aug 23, 2012 11:52 AM
            • 18. Re: Referencing a node
              odie_63
              ... xdb:defaultTable="" .. for all <complexType> <element>
              xdb:defaultTable is used on globally-defined elements, so in this case only the root element is concerned.
              It has no effect (or sense) on complexTypes.
              Any idea for forcing to use index?
              Which index do you want to force and why do you think it's a good idea?

              Did you gather statistics on the table?
              • 19. Re: Referencing a node
                Ric79
                Ok,
                1) I removed the defaultTable=""
                2) Re-executed before running the query
                EXEC DBMS_STATS.GATHER_SCHEMA_STATS('CK6EPM1');
                
                or 
                EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'CK6EPM1', TABNAME => 'XML_A_MEAS_DATA_TAB', CASCADE => TRUE);
                EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'CK6EPM1', TABNAME => 'XML_A_MEAS_INFO_TAB', CASCADE => TRUE);
                EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'CK6EPM1', TABNAME => 'XML_A_MEAS_R_TAB', CASCADE => TRUE);
                EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'CK6EPM1', TABNAME => 'XML_A_MEAS_TYPE_TAB', CASCADE => TRUE);
                EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'CK6EPM1', TABNAME => 'XML_A_MEAS_VALUE_TAB', CASCADE => TRUE);
                EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'CK6EPM1', TABNAME => 'XML_A_MEASCOLLECFILE', CASCADE => TRUE);
                3) The explain plan is stilll the same. I'm wondering why in your case the indexes are used and in mine are not.
                Let us suppose that I want to force the use of:
                CREATE INDEX IX_XML_A_MEAS_VALUE_TAB
                   ON XML_A_MEAS_VALUE_TAB("measObjLdn");
                where do I put the hint /*+ INDEX( XML_A_MEAS_VALUE_TAB IX_XML_A_MEAS_VALUE_TAB) */ in the query?


                You support is really nice!!

                Riccardo
                • 20. Re: Referencing a node
                  odie_63
                  Before forcing anything, you have to understand why the index is not used.

                  How many different "measObjLdn" values are there?

                  Based on the statistics gathered, if the CBO estimates that the selectivity of the index is not worth using it then it will go for a FULL SCAN. Forcing the index in this case will just make things worse.
                  • 21. Re: Referencing a node
                    Ric79
                    Now I'm testing 1000 measObjLdn and statistics are not used.

                    However select needs only 4 seconds to run.

                    Riccardo
                    • 22. Re: Referencing a node
                      Ric79
                      -

                      Edited by: Ric79 on Aug 23, 2012 3:08 PM
                      • 23. Re: Referencing a node
                        Ric79
                        -

                        Edited by: Ric79 on Aug 23, 2012 3:08 PM
                        1 2 Previous Next