About Moving, DeDuplication, SecureFile Storage and Encryption of Binary XMLType Tables

In this post I wanted to share some methods you can use while manipulating XML data. My XMLType Table example is based on my last posts about XMLIndex and (binary) XML Storage. So if you want some more information I suggest reading up on those as well.

Triggered by a great example from Jonathan Gennick (“Faster, Safer, and Smaller LOBs”) in the September issue of Oracle Magazine.

I used this as a base, manipulating XMLType tables and see what happens if I would do the same, but now on XML objects.


Although I had a very nice test / demonstration SQL script, I can’t post this yet, because I encountered some features that are currently dealt with by Oracle Support.

Anyway, I hereby also recommend reading Jonathan’s test documents embedded in the sample code zip-file (“domains_06.doc“). I have no idea if this article (mentioned in “domains_06”) already was posted, but as said, it is a very nice article.

Environment

Below the environment I am working with and the examples given here are based upon most of the objects. The XML schema used was registered for binary use (see former posts regarding Binary XML Storage how to realize this)


SQL> select schema_url, binary from user_xml_schemas;

SCHEMA_URL                                         BIN
-------------------------------------------------- ---
http://localhost/public/xsd/binxsd.xsd             YES

SQL> CREATE TABLE TEST of XMLTYPE
  2  XMLTYPE STORE AS SECUREFILE BINARY XML
  3  XMLSCHEMA "http://localhost/public/xsd/binxsd.xsd" ELEMENT "ROOT"
  4  ;

Table created.

SQL> select dbms_metadata.get_ddl('TABLE','TEST') from dual;

DBMS_METADATA.GET_DDL('TABLE','TEST')
-------------------------------------------------------

  CREATE TABLE "TEST" OF "SYS"."XMLTYPE"
  XMLTYPE STORE AS SECUREFILE BINARY XML  (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
  NOCACHE LOGGING  DECRYPT  NOCOMPRESS  KEEP_DUPLICATES
  STORAGE(INITIAL 106496 NEXT 1048576 
          MINEXTENTS 1 MAXEXTENTS 2147483645
          PCTINCREASE 0 BUFFER_POOL DEFAULT))
 XMLSCHEMA "http://localhost/public/xsd/binxsd.xsd" ELEMENT "ROOT" 
 ID 4486 DISALLOW NONSCHEMA 
 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 
          MINEXTENTS 1 MAXEXTENTS 2147483645
          PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 
          BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"

-- Simple generated rows

SQL> DECLARE
  2     XMLData xmlType := xmlType(
  3     '< ?xml version="1.0" encoding="UTF-8"?>
  4     
  5     
  6        0
  7        
  8           0
  9           Text
 10        
 11     ');
 12  BEGIN
 13    for i in 1..100000
 14    loop
 15        insert into TEST
 16        VALUES
 17        (XMLData);
 18    end loop;
 19  END;
 20  /

PL/SQL procedure successfully completed.

Elapsed: 00:02:36.12

SQL> col segment_name for a40
SQL> comp sum of bytes on report
SQL> break on report

SQL> select segment_name
  2  ,      segment_type
  3  ,      bytes
  4  from user_segments
  5  order by segment_type;


SEGMENT_NAME                             SEGMENT_TYPE            BYTES
---------------------------------------- ------------------ ----------
SYS_C008601                              INDEX                 3145728
SYS_IL0000067788C00003$$                 LOBINDEX                65536
SYS_LOB0000067788C00003$$                LOBSEGMENT             131072
TEST                                     TABLE                22020096
                                                            ----------
sum                                                           25362432

SQL> select count(*) from TEST;

  COUNT(*)
----------
    100000

SQL> select dbms_metadata.get_ddl('TABLESPACE','USERS')
  2  from dual;

DBMS_METADATA.GET_DDL('TABLESPACE','USERS')
------------------------------------------------------

  CREATE TABLESPACE "USERS" DATAFILE SIZE 5242880
  AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL 
  AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO

   ALTER DATABASE DATAFILE
  '/oracle/oradata/TEST/datafile/o1_mf_users_3d5xl73m_.dbf' 
  RESIZE 1614675968

OK, as said, a simple environment, we are now can use this environment for some DDL statements and see the effect.

Moving and Renaming XMLType Lob Segments

As demonstrated in an old post and even better described in a post from Jeff Hunter in “DDL Commands for LOBs” (not XMLType specific, but the DDL code can be deducted from this; indirect we are dealing with LOB objects).

The LOBSEGMENT and LOBINDEX of the Binary XML Table can be moved to a different tablespace, but be aware that Oracle advises that both are located on the same tablespace. You are not able to rename the LOBINDEX. The LOBSEGMENT can be renamed and moved via the following statement.

SQL> alter table TEST
  2  move tablespace USERS
  3  LOB (XMLDATA) store as XMLTEST_LOBSEGMENT
  4  (tablespace USERS
  5   INDEX (TABLESPACE USERS))
  6  -- the lobindex should always remain in the
  7  -- same tablespace as the lobsegment
  8  -- this accoording to the Oracle documentation
  9 ;

Table altered.

Elapsed: 00:00:04.50

SQL> select segment_name
  2  ,      segment_type
  3  ,      bytes
  4  from user_segments
  5  order by segment_type;

SEGMENT_NAME                             SEGMENT_TYPE            BYTES
---------------------------------------- ------------------ ----------
SYS_C008601                              INDEX                 3145728
SYS_IL0000067788C00003$$                 LOBINDEX                65536
XMLTEST_LOBSEGMENT                       LOBSEGMENT             131072
TEST                                     TABLE                22020096
                                                            ----------
sum                                                           25362432

Deduplicating XMLType Securefile Storage

The syntax for XMLType table storage is a little bit different to the ones demonstrated by Jonathan in his Oracle Magazine post. For XMLType Binary SECURE storage it is demonstrated in the following DDL statements (DEDUPLICATE and restore via the KEEP_DUPLICATES parameter). Maybe overkill but be aware that this method can only be used with SECUREFILE storage (the new lob storage).

The following ORA error message (ORA-43854) will be raised if you try to apply the DEDUPLICATION method on a BASICFILE storage object.

SQL> ALTER TABLE BASICFILE_TABLE
  2  MODIFY LOB(XMLDATA)
  3  (DEDUPLICATE);
ALTER TABLE BASICFILE_TABLE
*
ERROR at line 1:
ORA-43854: use of a BASICFILE LOB where a SECUREFILE LOB was expected

The following demonstrates the syntax for XMLType table objects.

SQL> alter table TEST
  2  MODIFY LOB(XMLDATA)
  3  (DEDUPLICATE);

Table altered.

Elapsed: 00:01:44.89

SQL> select segment_name
  2  ,      segment_type
  3  ,      bytes
  4  from user_segments
  5  order by segment_type;

SEGMENT_NAME                             SEGMENT_TYPE            BYTES
---------------------------------------- ------------------ ----------
SYS_C008601                              INDEX                 3145728
SYS_IL0000067788C00003$$                 LOBINDEX                65536
XMLTEST_LOBSEGMENT                       LOBSEGMENT             131072
TEST                                     TABLE                22020096
                                                            ----------
sum                                                           25362432

Elapsed: 00:00:00.05

SQL> alter table TEST
  2  MODIFY LOB(XMLDATA)
  3  (KEEP_DUPLICATES)
  4  ;

Table altered.

Elapsed: 00:00:51.99

SQL> select segment_name
  2  ,      segment_type
  3  ,      bytes
  4  from user_segments
  5  order by segment_type;

SEGMENT_NAME                             SEGMENT_TYPE            BYTES
---------------------------------------- ------------------ ----------
SYS_C008601                              INDEX                 3145728
SYS_IL0000067788C00003$$                 LOBINDEX                65536
XMLTEST_LOBSEGMENT                       LOBSEGMENT             131072
TEST                                     TABLE                22020096
                                                            ----------
sum                                                           25362432

XML Securefile – What About Compression?

Compressing the XMLType table is shown in the following example. Four statements are applicable: COMPRESS MEDIUM, COMPRESS HIGH, COMPRESS or NOCOMPRESS. Seen the syntax used with DEDUPLICATION, figuring out the “XMLDATA” piece in the DEDUPLICATE statement, the following is now very simple. NOCOMPRESS is the default when creating tables. The demonstrated ORA-43854 error message is raised again if you are trying to apply this method on a BASICFILE storage LOB / object.

As said by Jonathan in his article, compression can be interesting if used, for example, on data like XML. I noticed (as said by Jonathan) that it is CPU intensive. Or at least, I noticed increased CPU time, during the statements described below on my small Linux laptop environment.

SQL> alter table TEST
  2  MODIFY LOB(XMLDATA)
  3  (COMPRESS HIGH);

Table altered.

SQL> alter table TEST
  2  MODIFY LOB(XMLDATA)
  3  (COMPRESS MEDIUM);

Table altered.

SQL> alter table TEST
  2  MODIFY LOB(XMLDATA)
  3  (COMPRESS);

Table altered.


SQL> alter table TEST
  2  MODIFY LOB(XMLDATA)
  3  (NOCOMPRESS);

Table altered.

B.t.w. here I encountered one of the features (6364855).

Encryption of XML Securefile storage

As described by Jonathan, after you create a wallet and open it for use in the database (I did it via the DBConsole, as once described on the AMIS Technology Blog site) you are now also able to encrypt your data. If the database doesn’t have an wallet opened for use, you will encounter an ORA-28365 error message (“ORA-28365: wallet is not open”).

SQL> ALTER TABLE TEST
  2  MODIFY LOB(XMLDATA)
  3  (ENCRYPT USING 'AES256');  

ALTER TABLE TEST
*
ERROR at line 1:
ORA-28365: wallet is not open
           
-- After the wallet has be opened for use...

SQL> ALTER TABLE TEST
  2  MODIFY LOB(XMLDATA)
  3  (ENCRYPT USING 'AES256');  

SQL> ALTER TABLE TEST
  2  MODIFY LOB(XMLDATA)
  3  (ENCRYPT USING 'AES256');

Table altered.

-- Decrypting it again as it was from the start

SQL> ALTER TABLE TEST
  2  MODIFY LOB(XMLDATA)
  3  (DECRYPT);

Table altered.

-- Encrypting it again with a little bit more spice ;-)

SQL> ALTER TABLE TEST
  2  MODIFY LOB(XMLDATA)
  3  (ENCRYPT USING 'AES256' IDENTIFIED BY 'Encrypted?')
  4  ;

Table altered.

I currently can’t demonstrate the real differences, because I am a little bit handicapped by the environment, but I hope that showing the syntax for a XMLType Securefile Binary XML table is helpful anyway. As said, for “standard” LOB objects, I really recommend that you read Jonathan’ s great Oracle Magazine article.

Related Posts

 
Marco Gralike Written by:

2 Comments

  1. Zashkaser
    August 5

    i found you by link from the Directory Listing Script from Ash.. Nice to read your blog ^.^

Comments are closed.