12 Replies Latest reply on Jul 17, 2012 8:52 PM by Marco Gralike

    Range partitioning on virtual column based on binary xmltype column

    Michiel Weggen
      Alright, our DBA finally got around to upgrading to 11.2.0.2. Now I'm running into another issue:
      CREATE TABLE USER.DI_D2
        (
          ID NUMBER(19, 0) NOT NULL ,
          XML SYS.XMLTYPE ,
          PRIMARY KEY ( ID )
        )
        XMLTYPE XML STORE AS SECUREFILE BINARY XML
        VIRTUAL COLUMNS
        (
          ts AS (TO_TIMESTAMP(extractvalue(xml,'/d:d/c:dHeader/c:creationTime',
              'xmlns:d="http://www.example.com/m/d/schema/di"
               xmlns:c="http://www.example.com/m/schema/common"'),'YYYY-MM-DD"T"HH24:MI:SS'))
        )
        PARTITION BY RANGE (ts)
        (
          PARTITION d_p2012_07 VALUES LESS THAN (TO_DATE('1-8-2012','DD-MM-YYYY')),
          PARTITION d_px VALUES LESS THAN (MAXVALUE)
        );
      On our old 11.2.0.1 install this command works fine (tho due to other issues 11.2.0.1 doesn't work for our search queries)

      On our 11.2.0.2 install, I get the following error:
      Error at Command Line:10 Column:37
      Error report:
      SQL Error: ORA-14513: Partitiekolom mag niet van het gegevenstype object zijn.
      14513. 00000 -  "partitioning column may not be of object datatype"
      *Cause:    Partitioning column specified by the user was an object datatype
                 (object, REF, nested table, array) which is illegal.
      *Action:   Ensure that no partitioning column is an object datatype.
      Anyone know what's up with that? What changed between the 2 DB versions that could cause this to fail?
        • 1. Re: Range partitioning on virtual column based on binary xmltype column
          user503699
          Michiel Weggen wrote:
          Anyone know what's up with that? What changed between the 2 DB versions that could cause this to fail?
          No idea and this may not be relevant to your question but I believe EXTRACTVALUE function has been deprecated in 11.2.0.2.
          Does anything change if you replace it with XMLCAST/XMLQUERY, which is the recommended approach?
          • 2. Re: Range partitioning on virtual column based on binary xmltype column
            Michiel Weggen
            In 11.2.0.2 I get:
            CREATE TABLE USER.DI_D2
              (
                ID NUMBER(19, 0) NOT NULL ,
                XML SYS.XMLTYPE ,
                PRIMARY KEY ( ID )
              )
              XMLTYPE XML STORE AS SECUREFILE BINARY XML
              VIRTUAL COLUMNS
              (
                TS AS (XMLCAST(XMLQUERY('declare default element namespace "http://www.example.com/m/d/schema/di";declare namespace c="http://www.example.com/m/schema/common";/d/c:dHeader/c:creationTime' passing xml returning content) as timestamp))
              )
              PARTITION BY RANGE (ts)
              (
                PARTITION d_p2012_07 VALUES LESS THAN (TO_DATE('1-8-2012','DD-MM-YYYY')),
                PARTITION d_px VALUES LESS THAN (MAXVALUE)
              );
            
            Error at Command Line:10 Column:242
            Error report:
            SQL Error: ORA-14513: Partitiekolom mag niet van het gegevenstype object zijn.
            14513. 00000 -  "partitioning column may not be of object datatype"
            *Cause:    Partitioning column specified by the user was an object datatype
                       (object, REF, nested table, array) which is illegal.
            *Action:   Ensure that no partitioning column is an object datatype.
            In 11.2.0.1 I get
            Error at Command Line:1 Column:0
            Error report:
            SQL Error: ORA-54002: In de uitdrukking van een virtuele kolom kunnen alleen zuivere functies worden opgegeven.
            • 4. Re: Range partitioning on virtual column based on binary xmltype column
              Michiel Weggen
              It looks like a limitation was added in 11.2.0.2 to disallow partitioning on xmltype columns, I can only get it to work with xmltype tables now.
              CREATE TABLE "USER"."DI_D" OF XMLTYPE
                (
                  PRIMARY KEY ("ID")
                )
                XMLTYPE STORE AS SECUREFILE BINARY XML
                VIRTUAL COLUMNS
                (
                  "ID" AS (XMLCAST(XMLQUERY('declare default element namespace "http://www.example.com/m/d/schema/i";declare namespace c="http://www.example.com/m/schema/common";/d/c:dHeader/c:identifier' PASSING OBJECT_VALUE RETURNING CONTENT) AS NUMBER(19))),
                  "CREATION_TIME" AS (XMLCAST(XMLQUERY('declare default element namespace "http://www.example.com/m/d/schema/i";declare namespace c="http://www.example.com/m/schema/common";/d/c:dHeader/c:creationTime' PASSING OBJECT_VALUE RETURNING CONTENT) AS DATE))
                )
                PARTITION BY RANGE
                (
                  "CREATION_TIME"
                )
                (
                  PARTITION "d_p2011_07" VALUES LESS THAN (TO_DATE('1-8-2011', 'DD-MM-YYYY')),
                  PARTITION "d_p2011_08" VALUES LESS THAN (TO_DATE('1-9-2011', 'DD-MM-YYYY')),
                  PARTITION "d_p2011_09" VALUES LESS THAN (TO_DATE('1-10-2011', 'DD-MM-YYYY')),
                  PARTITION "d_p2011_10" VALUES LESS THAN (TO_DATE('1-11-2011', 'DD-MM-YYYY')),
                  PARTITION "d_p2011_11" VALUES LESS THAN (TO_DATE('1-12-2011', 'DD-MM-YYYY')),
                  PARTITION "d_p2011_12" VALUES LESS THAN (TO_DATE('1-1-2012', 'DD-MM-YYYY')),
                  PARTITION "d_p2012_01" VALUES LESS THAN (TO_DATE('1-2-2012', 'DD-MM-YYYY')),
                  PARTITION "d_px" VALUES LESS THAN (MAXVALUE)
                ) ;
              
              table "USER"."DI_D" created.
              I'm running into a new problem now; when viewing the SQL of the created table, it shows as using BasicFile instead of SecureFile, no matter how I specify the XMLType storage in the create query. It would be quite a shame to lose SecureFile functionality at this point. :(
              • 5. Re: Range partitioning on virtual column based on binary xmltype column
                odie_63
                Michiel Weggen wrote:
                It looks like a limitation was added in 11.2.0.2 to disallow partitioning on xmltype columns, I can only get it to work with xmltype tables now.
                It is documented in the 11.2 manual, but not sure which version it really applies on :

                http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb03usg.htm#ADXDB0310
                You can partition an XMLType table using a virtual column.
                You cannot partition a relational table that has an XMLType column, using that column to define virtual columns of XML data.
                I'm running into a new problem now; when viewing the SQL of the created table, it shows as using BasicFile instead of SecureFile, no matter how I specify the XMLType storage in the create query.
                Really?

                What are you using to "view" the SQL?

                What does the following return?
                select table_name, column_name, securefile 
                from user_lobs 
                where table_name = 'YOUR_TABLE_NAME';
                • 6. Re: Range partitioning on virtual column based on binary xmltype column
                  MGralike
                  Please show us the output of DBMS_METADATA for the created objects... Securefile should be default as a XMLType storage type in 11.2.0.2.0

                  ...and as mentioned, do not use stuff like EXTRACTVALUE; performance is in these versions optimized using the XQuery engine and as said those XML/SQL operators will, are deprecated.

                  Edited by: MGralike on Jul 14, 2012 11:30 AM
                  • 7. Re: Range partitioning on virtual column based on binary xmltype column
                    Michiel Weggen
                    Alright, seems that's just a display issue then.

                    Looking in user_lobs like suggested above gives
                    TABLE_NAME COLUMN_NAME SECUREFILE 
                    ---------- ----------- ---------- 
                    DI_D       XMLDATA     YES        
                    I was opening the table in SQL Developer and then looking in the tab SQL (12th tab); with a XmlType table it seems to always show Basicfile even if it's actually a Securefile.

                    I'd like to use the suggested xmlcast/xmlquery solution, however it doesn't seem to play well with our custom timestamp format.
                    "CREATION_TIME" AS (XMLCAST(XMLQUERY('declare default element namespace "http://www.example.com/m/d/schema/i";declare namespace c="http://www.example.com/m/schema/common";/d/c:dHeader/c:creationTime' PASSING OBJECT_VALUE RETURNING CONTENT) AS TIMESTAMP))
                    
                    Error at Command Line:1 Column:0
                    Error report:
                    SQL Error: ORA-54002: In de uitdrukking van een virtuele kolom kunnen alleen zuivere functies worden opgegeven.
                    "CREATION_TIME" AS (TO_TIMESTAMP(XMLQUERY('declare default element namespace "http://www.example.com/m/d/schema/i";declare namespace c="http://www.example.com/m/schema/common";/d/c:dHeader/c:creationTime' PASSING OBJECT_VALUE RETURNING CONTENT),'YYYY-MM-DD"T"HH24:MI:SS'))
                    
                    Error at Command Line:9 Column:45
                    Error report:
                    SQL Error: ORA-00932: inconsistente gegevenstypen: - verwacht, - gekregen
                    00932. 00000 -  "inconsistent datatypes: expected %s got %s"
                    *Cause:    
                    *Action:
                    "CREATION_TIME" AS (TO_TIMESTAMP(EXTRACTVALUE("OBJECT_VALUE",'/di:d/c:dHeader/c:creationTime','xmlns:di="http://www.example.com/m/d/schema/i" xmlns:c="http://www.example.com/m/schema/common"'),'YYYY-MM-DD"T"HH24:MI:SS'))
                    
                    table "USER"."DI_D" created.
                    • 8. Re: Range partitioning on virtual column based on binary xmltype column
                      Michiel Weggen
                      odie_63 wrote:
                      Michiel Weggen wrote:
                      It looks like a limitation was added in 11.2.0.2 to disallow partitioning on xmltype columns, I can only get it to work with xmltype tables now.
                      It is documented in the 11.2 manual, but not sure which version it really applies on :

                      http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb03usg.htm#ADXDB0310
                      You can partition an XMLType table using a virtual column.
                      You cannot partition a relational table that has an XMLType column, using that column to define virtual columns of XML data.
                      I read that as well, however it does work in 11.2.0.1 and I was expecting the functionality to also be present in 11.2.0.2. I built a solution that used this structure and after upgrading the database to resolve the empty-column problem it seems I have to start over from scratch...

                      I can probably get it to work with the xmltype table eventually, but my Hibernate mappings will try to write to the virtual column so I'll have to find a solution to that, which would not be needed with an xmltype column.

                      Edited by: Michiel Weggen on 16-jul-2012 5:24
                      • 9. Re: Range partitioning on virtual column based on binary xmltype column
                        Marco Gralike
                        Please don't use SQL Developer to prove your point. Besides the version, which method you are using to connect, the only way to make sure if something works or not is using a SQLPlus connection which version matches the database version. SQL Developer has too many issues when dealing with the XMLDB functionality and until know I couldn't find the proper channels too push those issues so they could be dealt with. I had a contact, Sue Harper, but she isn't working for Oracle anymore.

                        So please use SQLPlus, so we don't have to deal with SQL Developer bugs here n this forum.
                        • 10. Re: Range partitioning on virtual column based on binary xmltype column
                          Marco Gralike
                          Als je wil kan ik je over een week, ben dan terug van vakantie, direct helpen via email : marco[dot]gralike[at]amis[dot]nl
                          • 11. Re: Range partitioning on virtual column based on binary xmltype column
                            Michiel Weggen
                            Marco Gralike wrote:
                            Please don't use SQL Developer to prove your point. Besides the version, which method you are using to connect, the only way to make sure if something works or not is using a SQLPlus connection which version matches the database version. SQL Developer has too many issues when dealing with the XMLDB functionality and until know I couldn't find the proper channels too push those issues so they could be dealt with. I had a contact, Sue Harper, but she isn't working for Oracle anymore.

                            So please use SQLPlus, so we don't have to deal with SQL Developer bugs here n this forum.
                            Unfortunately I don't have the luxury / time to switch to SQLPlus at this point. SQL Developer has been working great for us the past years and this is probably the first time it didn't work properly. I hope you'll bear with me on this :)

                            So if I understand correctly, XmlType column partitioning working properly in 11.2.0.1 was a bug and it got fixed in 11.2.0.2 by removing that functionality?
                            • 12. Re: Range partitioning on virtual column based on binary xmltype column
                              Marco Gralike
                              Might be.

                              Regarding SQL Developer, I am just asking to double check via SQLPlus so we don't go all out on a wild goose chase. Some of the stuff you mentioned in the first threads where bugs related to SQL Developer, the basicfile XMLType issue...