4 Replies Latest reply: Jan 3, 2013 10:21 PM by user8941550 RSS

    Table in Schema1 using XMLIndexes But not in View- Schema2

    user8941550
      Hi,

      We are using :
      Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit

      I have a view with following query:

      CREATE OR REPLACE FORCE VIEW VIEW_TNAME
      (
      XML
      )
      AS
      SELECT Column_Name
      FROM TNAME
      WHERE XMLEXISTS (
      'declare namespace Namesp1 ="Abc:Set"; declare Namespace Namesp2 ="XYZ:Set"; declare namespace Namesp3 ="DEF:Set"; /Namesp1:Tag1/Namesp2:Tag2/Namesp3:Tag3[@id="ID"][Namesp2:Tag5[@Scope="SCOPE"] = ("1000", "2000") ]'
      PASSING Column_Name);

      Table TNAME is in Schema1.
      View VIEW_TNAME is in Schema2.

      When I execute the query in Schema1(without the view. i.e. Just execute underlying query).

      Plan is like:

      Plan
      SELECT STATEMENT ALL_ROWSCost: 86 Bytes: 124 Cardinality: 1                                                        
           20 NESTED LOOPS Cost: 86 Bytes: 124 Cardinality: 1                                                   
                18 VIEW VIEW SYS.VW_SQ_1 Cost: 84 Bytes: 25 Cardinality: 1                                              
                     17 HASH UNIQUE Bytes: 190 Cardinality: 1                                         
                          16 NESTED LOOPS Cost: 84 Bytes: 190 Cardinality: 1                                    
                               13 NESTED LOOPS Cost: 84 Bytes: 143 Cardinality: 1                               
                                    9 NESTED LOOPS Cost: 65 Bytes: 96 Cardinality: 1                          
                                         6 HASH JOIN RIGHT SEMI Cost: 64 Bytes: 490 Cardinality: 10                     
                                              1 COLLECTION ITERATOR PICKLER FETCH PROCEDURE SYS.XQSEQUENCEFROMXMLTYPE Cost: 35 Bytes: 32,720 Cardinality: 16,360                
                                              5 PX COORDINATOR                
                                                   4 PX SEND QC (RANDOM) PARALLEL_TO_SERIAL SYS.:TQ10000 :Q1000Cost: 28 Bytes: 442,599 Cardinality: 9,417           
                                                        3 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1000Cost: 28 Bytes: 442,599 Cardinality: 9,417 Partition #: 11 Partitions accessed #1 - #282     
                                                             2 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT Schema_Name.TNAME_RANGE_PT :Q1000Cost: 28 Bytes: 442,599 Cardinality: 9,417 Partition #: 11 Partitions accessed #1 - #282
                                         8 PARTITION SYSTEM ITERATOR Cost: 1 Bytes: 47 Cardinality: 1 Partition #: 13 Partitions determined by Key Values                    
                                              7 TABLE ACCESS FULL TABLE Schema_Name.TNAME_RANGE_PT Cost: 1 Bytes: 47 Cardinality: 1 Partition #: 13 Partitions determined by Key Values               
                                    12 PARTITION SYSTEM ALL Cost: 20 Bytes: 47 Cardinality: 1 Partition #: 15 Partitions accessed #1 - #282                         
                                         11 TABLE ACCESS BY LOCAL INDEX ROWID TABLE Schema_Name.TNAME_RANGE_PT Cost: 20 Bytes: 47 Cardinality: 1 Partition #: 15 Partitions accessed #1 - #282                    
                                              10 INDEX RANGE SCAN INDEX Schema_Name.SYS803074_I_XPATH_T_PIKEY_IX Cost: 20 Cardinality: 1 Partition #: 15 Partitions accessed #1 - #282               
                               15 PARTITION SYSTEM ITERATOR Cost: 1 Bytes: 47 Cardinality: 1 Partition #: 18 Partitions determined by Key Values                              
                                    14 TABLE ACCESS FULL TABLE Schema_Name.TNAME_RANGE_PT Cost: 1 Bytes: 47 Cardinality: 1 Partition #: 18 Partitions determined by Key Values                         
                19 TABLE ACCESS BY USER ROWID TABLE Schema_Name.TNAME Cost: 1 Bytes: 99 Cardinality: 1 Partition #: 20 Partition access computed by row location               


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

      Query uses the XMLIndexes and gets executed in ~20 seconds.

      BUT

      When I execute the same query in Schema2 or query the view which is built in Schema2. Explain Plan is like:

      <ExplainPlan>
      - <PlanElement id="0" operation="SELECT STATEMENT" optimizer="ALL_ROWS" search_columns="0" cost="48,865">
      - <PlanElements>
      - <PlanElement id="1" operation="FILTER" search_columns="0" filter_predicates="IS NOT NULL">
      - <PlanElements>
      - <PlanElement id="2" operation="PARTITION RANGE" option="ALL" search_columns="0" cost="48,805" cardinality="1,269,600" bytes="1,274,678,400" partition_id="2" partition_start="1" partition_stop="282" cpu_cost="1,932,647,487" io_cost="48,744" time="684">
      - <PlanElements>
      <PlanElement object_ID="0" id="3" operation="TABLE ACCESS" option="FULL" object_owner="Schema_Name" object_name="TNAME" object_type="TABLE" search_columns="0" cost="48,805" cardinality="1,269,600" bytes="1,274,678,400" partition_id="2" partition_start="1" partition_stop="282" cpu_cost="1,932,647,487" io_cost="48,744" time="684" />
      </PlanElements>
      </PlanElement>
      - <PlanElement id="4" operation="HASH JOIN" option="RIGHT SEMI" search_columns="0" cost="60" cardinality="1" bytes="18" cpu_cost="27,206,479" io_cost="59" access_predicates="<not feasible>" time="1">
      - <PlanElements>
      <PlanElement object_ID="1" id="5" operation="COLLECTION ITERATOR" option="PICKLER FETCH" object_owner="SYS" object_name="XQSEQUENCEFROMXMLTYPE" object_type="PROCEDURE" search_columns="0" cost="35" cardinality="16,360" bytes="32,720" cpu_cost="3,428,288" io_cost="35" time="1" />
      - <PlanElement id="6" operation="NESTED LOOPS" search_columns="0" cost="24" cardinality="100" bytes="800" cpu_cost="5,560,801" io_cost="24" time="1">
      - <PlanElements>
      <PlanElement id="7" operation="XPATH EVALUATION" search_columns="0" />
      <PlanElement id="8" operation="XPATH EVALUATION" search_columns="0" />

      (Sorry about the tags in the Plan).

      As shown in the plan, XMLIndexes are not used and query goes into forever execution as there are millions of rows in table TNAME.

      - Why this difference of behaviour in two schemas. It happens on some views like this one and not on others.
      - How can I correct this. ( I changed parameter OPTIMIZER_SECURE_VIEW_MERGING to FALSE. It looks like that solved the problem in our environment. But getting it done in PRODUCTION looks like Very Difficult and Process Oriented unless there is no other way).

      Kindly suggest. Thanks.
        • 1. Re: Table in Schema1 using XMLIndexes But not in View- Schema2
          user8941550
          No. I think that was a temporary behaviour.
          Now the QUERY is NOT USING INDEX at all.
          Even when I try the HINT :

          select /*+ index(vf Index_Name) */ XML from VIEW_TNAME

          I think XML Indexes are not getting used because of XQuery code. IS there anyway I can force the Indexes otherwise. Please suggest.

          I created Index in the following way

          CREATE INDEX Index_Name ON "TNAME" (COLUMNNAME)
          INDEXTYPE IS XDB.XMLINDEX PARAMETERS ( 'PARAM Param_Name' )
          LOCAL

          And in the Parameter I specify the Path table with different XPaths.
          Indexes work fine as they are used in the SCHEMA1.

          Edited by: user8941550 on Jan 2, 2013 12:21 AM
          • 2. Re: Table in Schema1 using XMLIndexes But not in View- Schema2
            odie_63
            A test case would help identifying the issue : table and index DDLs + sample data.

            The explain plans are not readable like that, at least use DBMS_XPLAN functionalities to get them and use
             tags to post them here.                                                                                                                                                                                                                                                                                                                                                                                                                                                            
            • 3. Re: Table in Schema1 using XMLIndexes But not in View- Schema2
              Marco Gralike
              If I have a look at your initial plan then it doesn't look good at all. It is traversing all partitions... As A Non said, we would need the create table (DDL) etc statements to make better suggestions...
              SELECT STATEMENT  ALL_ROWSCost: 86  Bytes: 124  Cardinality: 1                                                                                  
                      20 NESTED LOOPS  Cost: 86  Bytes: 124  Cardinality: 1                                                                           
                              18 VIEW VIEW SYS.VW_SQ_1 Cost: 84  Bytes: 25  Cardinality: 1                                                                    
                                      17 HASH UNIQUE  Bytes: 190  Cardinality: 1                                                              
                                              16 NESTED LOOPS  Cost: 84  Bytes: 190  Cardinality: 1                                                   
                                                      13 NESTED LOOPS  Cost: 84  Bytes: 143  Cardinality: 1                                           
                                                              9 NESTED LOOPS  Cost: 65  Bytes: 96  Cardinality: 1                                     
                                                                      6 HASH JOIN RIGHT SEMI  Cost: 64  Bytes: 490  Cardinality: 10                           
                                                                              1 COLLECTION ITERATOR PICKLER FETCH PROCEDURE SYS.XQSEQUENCEFROMXMLTYPE Cost: 35  Bytes: 32,720  Cardinality: 16,360                    
                                                                              5 PX COORDINATOR                        
                                                                                      4 PX SEND QC (RANDOM) PARALLEL_TO_SERIAL SYS.:TQ10000 :Q1000Cost: 28  Bytes: 442,599  Cardinality: 9,417                
                                                                                              3 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1000Cost: 28  Bytes: 442,599  Cardinality: 9,417  Partition #: 11  Partitions accessed #1 - #282    
                                                                                                      2 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT Schema_Name.TNAME_RANGE_PT :Q1000Cost: 28  Bytes: 442,599  Cardinality: 9,417  Partition #: 11  Partitions accessed #1 - #282
                                                                      8 PARTITION SYSTEM ITERATOR  Cost: 1  Bytes: 47  Cardinality: 1  Partition #: 13  Partitions determined by Key Values                          
                                                                              7 TABLE ACCESS FULL TABLE Schema_Name.TNAME_RANGE_PT Cost: 1  Bytes: 47  Cardinality: 1  Partition #: 13  Partitions determined by Key Values                  
                                                              12 PARTITION SYSTEM ALL  Cost: 20  Bytes: 47  Cardinality: 1  Partition #: 15  Partitions accessed #1 - #282                                   
                                                                      11 TABLE ACCESS BY LOCAL INDEX ROWID TABLE Schema_Name.TNAME_RANGE_PT Cost: 20  Bytes: 47  Cardinality: 1  Partition #: 15  Partitions accessed #1 - #282                              
                                                                              10 INDEX RANGE SCAN INDEX Schema_Name.SYS803074_I_XPATH_T_PIKEY_IX Cost: 20  Cardinality: 1  Partition #: 15  Partitions accessed #1 - #282                    
                                                      15 PARTITION SYSTEM ITERATOR  Cost: 1  Bytes: 47  Cardinality: 1  Partition #: 18  Partitions determined by Key Values                                         
                                                              14 TABLE ACCESS FULL TABLE Schema_Name.TNAME_RANGE_PT Cost: 1  Bytes: 47  Cardinality: 1  Partition #: 18  Partitions determined by Key Values                                 
                              19 TABLE ACCESS BY USER ROWID TABLE Schema_Name.TNAME Cost: 1  Bytes: 99  Cardinality: 1  Partition #: 20  Partition access computed by row location     
              • 4. Re: Table in Schema1 using XMLIndexes But not in View- Schema2
                user8941550
                Hi,

                Table Structure is as follows:

                CREATE TABLE TNAME
                (
                DB_ID VARCHAR2(10 BYTE),
                DATA_ID VARCHAR2(10 BYTE),
                DATA_ID2 VARCHAR2(10 BYTE),
                IDENTIFIER1 NUMBER(19) NOT NULL,
                ID1 NUMBER(10) NOT NULL,
                STATUS1 NUMBER(10) NOT NULL,
                TIME_STAMP NUMBER(19) NOT NULL,
                OBJECT_ID VARCHAR2(40 BYTE) NOT NULL,
                OBJECT_NAME VARCHAR2(80 BYTE) NOT NULL,
                UNIQUE_ID VARCHAR2(255 BYTE),
                DATA_LIVE CHAR(1 BYTE) NOT NULL,
                XML_MESSAGE SYS.XMLTYPE,
                ID2 VARCHAR2(255 BYTE) NOT NULL,
                FLAG1 CHAR(1 BYTE) NOT NULL,
                KEY1 VARCHAR2(255 BYTE),
                HEADER1 VARCHAR2(2000 BYTE) NOT NULL,
                VERSION2 VARCHAR2(255 BYTE) NOT NULL,
                TYPE1 VARCHAR2(15 BYTE),
                LOAD_TIMESTAMP TIMESTAMP(6),
                SOURCE_NUMBER NUMBER
                )
                XMLTYPE XML_MESSAGE STORE AS BINARY XML (
                TABLESPACE TSNAME)
                TABLESPACE TSNAME
                PARTITION BY RANGE (LOAD_TIMESTAMP)
                (
                PARTITION MAX VALUES LESS THAN (MAXVALUE)
                TABLESPACE TSNAME
                )
                NOCOMPRESS
                NOCACHE
                ENABLE ROW MOVEMENT
                /


                Indexes are:

                CREATE UNIQUE INDEX "TNAME_UQI"
                ON "TNAME" (" DATA_ID", "IDENTIFIER1", "ID1")
                PCTFREE 10
                INITRANS 2
                MAXTRANS 255
                COMPUTE STATISTICS
                TABLESPACE "TSNAME"
                /


                CREATE INDEX "TNAME_I"
                ON "TNAME" ("SOURCE_NUMBER")
                PCTFREE 10
                INITRANS 2
                MAXTRANS 255
                COMPUTE STATISTICS
                TABLESPACE "TSNAME"
                /


                -- XMLType Index is created as Register Parameter and then....

                BEGIN
                DBMS_XMLINDEX.REGISTERPARAMETER(
                'TNAME_PAR',
                'PATH TABLE TNAME_PT
                PATHS (INCLUDE ( /abc:Msg/xyz:product/def:productType/def:SubProductType
                /abc:Msg/xyz:Products/xyz:Owner
                ......................................................
                ......................................................
                )
                NAMESPACE MAPPING ( xmlns:abc="Abc:Set";
                xmlns:xyz ="XYZ:Set"
                xmlns:def="DEF:Set"
                ))
                ');
                END;
                /


                CREATE INDEX I_XPATH_TNAME
                ON "TNAME" (XML_MESSAGE)
                INDEXTYPE IS XDB.XMLINDEX PARAMETERS ( 'PARAM TNAME_PAR',)
                local
                /


                Now the view is like:

                CREATE OR REPLACE FORCE VIEW VIEW_TNAME
                (
                XML
                )
                AS
                SELECT XML_MESSAGE
                FROM TNAME
                WHERE XMLEXISTS (
                'declare namespace abc ="Abc:Set"; declare Namespace xyz ="XYZ:Set"; declare namespace def ="DEF:Set"; /abc:Tag1/xyz:Tag2/def:Tag3[@id="ID"][EntityID:Tag5[@Scope="SCOPE"] = ("1000", "2000") ]'
                PASSING XML_Message);

                THROUGH this view we are filtering only the EntityID in 1000 or 2000 which is unique in the XML Message, based on certain crietrias as mentioned in the Path Expression.

                Due to confidentiality I can't share the data here. :-(
                But it's like..(XML File size is around 250 rows in every XML_Message) and around .2 million rows in the table in environment where I am testing.
                But it will grow upto some millions later.


                NOW I have table in SCHEMA1.
                There if I execute this underlying query to view, plan is like:
                ---------------------------------------------------------------------------------------------------------------------
                | Id  | Operation                                | Name                         | E-Rows |  OMem |  1Mem | Used-Mem |
                ---------------------------------------------------------------------------------------------------------------------
                |   0 | SELECT STATEMENT                         |                              |        |       |    |     |
                |   1 |  NESTED LOOPS                            |                              |      1 |       |    |     |
                |   2 |   VIEW                                   | VW_SQ_1                      |      1 |       |    |     |
                |   3 |    HASH UNIQUE                           |                              |      1 |   926K|   926K| 1263K (0)|
                |   4 |     NESTED LOOPS                         |                              |      1 |       |    |     |
                |   5 |      NESTED LOOPS                        |                              |      1 |       |    |     |
                |   6 |       NESTED LOOPS                       |                              |      1 |       |    |     |
                |*  7 |        HASH JOIN RIGHT SEMI              |                              |     52 |   733K|   733K|  497K (0)|
                |   8 |         COLLECTION ITERATOR PICKLER FETCH| XQSEQUENCEFROMXMLTYPE        |  16360 |       |    |     |
                |   9 |         PX COORDINATOR                   |                              |        |       |    |     |
                |  10 |          PX SEND QC (RANDOM)             | :TQ10000                     |  59638 |       |    |     |
                |  11 |           PX BLOCK ITERATOR              |                              |  59638 |       |    |     |
                |* 12 |            TABLE ACCESS FULL             | TNAME_PT                     |  59638 |       |    |     |
                |  13 |        PARTITION SYSTEM ITERATOR         |                              |      1 |       |    |     |
                |* 14 |         TABLE ACCESS FULL                | TNAME_PT                     |      1 |       |    |     |
                |  15 |       PARTITION SYSTEM ALL               |                              |      1 |       |    |     |
                |* 16 |        TABLE ACCESS BY LOCAL INDEX ROWID | TNAME_PT                     |      1 |       |    |     |
                |* 17 |         INDEX RANGE SCAN                 | SYS803074_I_XPATH_T_PIKEY_IX |      1 |       |    |     |
                |  18 |      PARTITION SYSTEM ITERATOR           |                              |      1 |       |    |     |
                |* 19 |       TABLE ACCESS FULL                  | TNAME_PT                     |      1 |       |    |     |
                |* 20 |   TABLE ACCESS BY USER ROWID             | TNAME                        |      1 |       |    |     |
                ---------------------------------------------------------------------------------------------------------------------
                So I can see PATH Tables being used on which Index is registered and Execution is very fast.

                Other PLAN when I execute the query from SCHEMA2, is going for FTS on table TNAME as:
                Plan
                SELECT STATEMENT  ALL_ROWSCost: 39,887                      
                     8 FILTER                 
                          2 PARTITION RANGE ALL  Cost: 39,827  Bytes: 447,179,616  Cardinality: 193,752  Partition #: 2  Partitions accessed #1 - #282          
                               1 TABLE ACCESS FULL TABLE TNAME Cost: 39,827  Bytes: 447,179,616  Cardinality: 193,752  Partition #: 2  Partitions accessed #1 - #282     
                          7 HASH JOIN RIGHT SEMI  Cost: 60  Bytes: 18  Cardinality: 1            
                               3 COLLECTION ITERATOR PICKLER FETCH PROCEDURE SYS.XQSEQUENCEFROMXMLTYPE Cost: 35  Bytes: 32,720  Cardinality: 16,360       
                               6 NESTED LOOPS  Cost: 24  Bytes: 800  Cardinality: 100       
                                    4 XPATH EVALUATION  
                                    5 XPATH EVALUATION  
                fIRST THINGS FIRST- So is there any way I can make this view always use this index. ?
                I have tried several ways including hints for Indexes but they simply don't seem to help.
                Same query takes 15 secs in one schema and 2 hours in another.


                Is there any way we can make the query through view always use these XMLType Indexes and not Inconsistently. It's a lot faster when XMLType Indexes are used. In fact it does not work in Production if XMLIndex is not used.
                Kindly suggest..

                Edited by: user8941550 on Jan 3, 2013 8:20 PM