LOB objects and table partitioning – how this works together

Suppose we have some table what contains LOB column and at the same time is partitioned by range (say, by some INT key using INTERVAL Oracle 11GR2 option):

SQL> create table clob_tab (x int, y int, z clob)
  2  partition by range(y)
  3  interval(1)
  4  (
  5  partition p_1 values less than (0)
  6  )
  7  /

Table created.

OK, we got it. Now we should notice what CLOB column is being supported by reference – “Z” column contains “reference to LOB index entry” what (LOB index) in turn contains the chain to read CLOB content.

SQL> declare
  2    l_lob clob;
  3  begin
  4    insert into clob_tab
  5    values(1,1, empty_clob())
  6    returning z into l_lob;
  7    dbms_lob.writeappend(l_lob,32767,lpad('0',32767,'0'));
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select dbms_lob.getlength(z) from clob_tab;

DBMS_LOB.GETLENGTH(Z)                                                           
---------------------                                                           
                32767

SQL> select segment_name, index_name from user_lobs
  2  where table_name = 'CLOB_TAB';

SEGMENT_NAME                   INDEX_NAME                                       
------------------------------ ------------------------------                   
SYS_LOB0000078579C00003$$      SYS_IL0000078579C00003$$

If we study Oracle metadata we should notice 2 things – LOB index has 2 partitions
and is UNIQUE:

SQL> select object_id from user_objects
  2  where object_name = 'SYS_IL0000078579C00003$$';

 OBJECT_ID                                                                      
----------                                                                      
     78584                                                                      
     78587                                                                      

SQL> select partitioned, uniqueness
  2  from user_indexes
  3  where index_name = 'SYS_IL0000078579C00003$$';

PAR UNIQUENES                                                                   
--- ---------                                                                   
YES UNIQUE

The first fact is the indicator of that LOB index is LOCAL, and it’s really reflected in metadata dictionary:

SQL> select locality, partitioning_type
  2  from user_part_indexes
  3  where index_name = 'SYS_IL0000078579C00003$$'
  4  /

LOCALI PARTITION                                                                
------ ---------                                                                
LOCAL  RANGE
SQL> select object_id, object_type from user_objects where object_name = 'SYS_IL0000078579C00003$';

OBJECT_ID OBJECT_TYPE
---------- -------------------
78584 INDEX PARTITION
78587 INDEX PARTITION

SQL> select subobject_name, object_type, data_object_id
2 from user_objects where object_name = 'SYS_LOB0000078579C00003$$'
3 /

SUBOBJECT_NAME OBJECT_TYPE DATA_OBJECT_ID
------------------------------ ------------------- --------------
SYS_LOB_P361 LOB PARTITION 78582
SYS_LOB_P364 LOB PARTITION 78586
LOB 78581

We see what not only the index is partitioned – LOB segment is also partitioned !
And finally we should also notice the fact what Oracle dictuionary reflects the fact
what CLOB_TAB and LOB index are partitioned but LOB segment itself is missing !

SQL> select name, object_type, column_name
  2  from user_part_key_columns
  3  /

NAME                           OBJEC COLUMN_NAME                                
------------------------------ ----- --------------------                       
CLOB_TAB                       TABLE Y                                          
SYS_IL0000078579C00003$$       INDEX Y

Now let’s try to track what happens when we exchange partition in such table (with CLOB) and corresponding plain table.

SQL> CREATE TABLE exch_tab (x int, y int, z clob)
  2  /

Table created.

SQL> select segment_name, index_name from user_lobs
  2  where table_name = 'EXCH_TAB';

SEGMENT_NAME                   INDEX_NAME                                       
------------------------------ ------------------------------                   
SYS_LOB0000078588C00003$$      SYS_IL0000078588C00003$$

We must take into account what …_OBJECTS views don’t reflect LOB indexes themselves (sic !), so
we have to query SYS.OBJ$ in direct way to get object_id and data_object_id of LOB index:

SQL> select object_id, data_object_id
  2  from user_objects
  3  where object_name = 'SYS_LOB0000078588C00003$$';

 OBJECT_ID DATA_OBJECT_ID                                                       
---------- --------------                                                       
     78589          78589                                                       

SQL> select object_id, data_object_id
  2  from user_objects
  3  where object_name = 'SYS_IL0000078588C00003$$';

no rows selected

SQL> select obj#, dataobj#
  2  from sys.obj$
  3  where name = 'SYS_IL0000078588C00003$$';

      OBJ#   DATAOBJ#                                                           
---------- ----------                                                           
     78590      78590

Good, we have got all metadata values we should. Now let’s exchange ordinar partition in CLOB_TAB
with EXCH_TAB table.

SQL> select partition_name from user_tab_partitions
  2  where table_name = 'CLOB_TAB';

PARTITION_NAME                                                                  
------------------------------                                                  
P_1                                                                             
SYS_P363                                                                        

SQL> select x,y from clob_tab partition(sys_p363);

         X          Y                                                           
---------- ----------                                                           
         1          1                                                           

SQL> insert into exch_tab values(1,1,empty_clob());

1 row created.

SQL> commit;

Commit complete.

SQL> alter table clob_tab exchange partition sys_p363
  2  with table exch_tab;

Table altered.

Now let’s look at object identifiers – what has happend ?
Well,
a) ordinar partition data segment was exchanged with table data segment

SQL> select object_id, data_object_id
  2  from user_objects where subobject_name = 'SYS_P363';

 OBJECT_ID DATA_OBJECT_ID                                                       
---------- --------------                                                       
     78585          78588

b) LOB partition data segment was exchanged with table LOB data segment:

SQL> select object_id, data_object_id
  2  from user_objects where subobject_name = 'SYS_LOB_P364';

 OBJECT_ID DATA_OBJECT_ID                                                       
---------- --------------                                                       
     78586          78589                                                       

SQL> select object_id, data_object_id
  2  from user_objects where object_name = 'SYS_LOB0000078588C00003$$'
  3  /

 OBJECT_ID DATA_OBJECT_ID                                                       
---------- --------------                                                       
     78589          78586

c) LOB index partition was exchanged with LOB index

SQL> select obj#, dataobj#
  2  from sys.obj$
  3  where name = 'SYS_IL0000078588C00003$$';

      OBJ#   DATAOBJ#                                                           
---------- ----------                                                           
     78590      78587                                                           

SQL> select lob_indpart_name, lob_partition_name from user_lob_partitions
  2  where table_name = 'CLOB_TAB';

LOB_INDPART_NAME               LOB_PARTITION_NAME                               
------------------------------ ------------------------------                   
SYS_IL_P362                    SYS_LOB_P361                                     
SYS_IL_P365                    SYS_LOB_P364  

SQL> select obj#,dataobj# from sys.obj$
  2  where subname = 'SYS_IL_P365';

      OBJ#   DATAOBJ#                                                           
---------- ----------                                                           
     78587      78590

OK, so we got all we had to expect – data segnent references are exchanged for correspondent partitions -ordinar, LOB and LOB index ones.
What happens when we change the value of partition key column in the row (and hence we get row movement
between partitions) ?
Oracle obviousely should move not only row (and corresponding LOB locator) but also
should move data from one LOB partition to another and change LOB locator to point to another LOB index partition row. Let’s track ?

  
SQL> select dbms_rowid.rowid_relative_fno(rowid),
  2  dbms_rowid.rowid_block_number(rowid)
  3  from clob_tab;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)       
------------------------------------ ------------------------------------       
                                   4                                 3148       
SQL> ALTER SYSTEM DUMP DATAFILE 4 BLOCK MIN 3148 BLOCK MAX 3148;

System altered.

Now we have the following LOB locator content (from block dump):

block_row_dump:
tab 0, row 0, @0x1f64
tl: 2 fb: --HDFL-- lb: 0x2 
tab 0, row 1, @0x1f36
tl: 46 fb: --H-FL-- lb: 0x1  cc: 3
col  0: [ 2]  c1 02
col  1: [ 2]  c1 02
col  2: [36]
 00 54 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 00 0d 4a be 00 10 09 00 00
 00 00 00 00 00 00 00 00 00 00 00
LOB
Locator:
  Length:        84(36)
  Version:        1
  Byte Length:    2
  LobID: 00.00.00.01.00.00.00.0d.4a.be
  Flags[ 0x02 0x0c 0x80 0x00 ]:
    Type: CLOB 
    Storage: BasicFile
    Enable Storage in Row 
    Characterset Format: IMPLICIT
    Partitioned Table: No
    Options: VaringWidthReadWrite 
  Inode: 
    Size:     16
    Flag:     0x09 [ Valid DataInRow ]
    Future:   0x00 (should be '0x00')
    Blocks:   0
    Bytes:    0
    Version:  00000.0000000000
    Inline data[0]

Now we can make changes in partition key:

SQL> alter table clob_tab enable row movement;

Table altered.

SQL> update clob_tab set y = 2 where x = 1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select dbms_rowid.rowid_relative_fno(rowid),
  2  dbms_rowid.rowid_block_number(rowid)
  3  from clob_tab
  4  /

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)       
------------------------------------ ------------------------------------       
                                   4                                 3172       

SQL> alter system dump datafile 4 block min 3172 block max 3172;

System altered.

As we can see LOB locator value was changed:

block_row_dump:
tab 0, row 0, @0x1f6a
tl: 46 fb: --H-FL-- lb: 0x1  cc: 3
col  0: [ 2]  c1 02
col  1: [ 2]  c1 03
col  2: [36]
 00 54 00 01 02 0c 00 00 00 02 00 00 00 01 00 00 00 0d 4a c1 00 10 09 00 00
 00 00 00 00 00 00 00 00 00 00 00
LOB
Locator:
  Length:        84(36)
  Version:        1
  Byte Length:    2
  LobID: 00.00.00.01.00.00.00.0d.4a.c1
  Flags[ 0x02 0x0c 0x00 0x00 ]:
    Type: CLOB 
    Storage: BasicFile
    Enable Storage in Row 
    Characterset Format: IMPLICIT
    Partitioned Table: No
    Options: ReadWrite 
  Inode: 
    Size:     16
    Flag:     0x09 [ Valid DataInRow ]
    Future:   0x00 (should be '0x00')
    Blocks:   0
    Bytes:    0
    Version:  00000.0000000000
    Inline data[0]

The new LOB partition and new LOB index partition were created (as expected):

SQL> select lob_partition_name, lob_indpart_name from dba_lob_partitions
  2  where table_name = 'CLOB_TAB';

LOB_PARTITION_NAME             LOB_INDPART_NAME                                 
------------------------------ ------------------------------                   
SYS_LOB_P361                   SYS_IL_P362                                      
SYS_LOB_P364                   SYS_IL_P365                                      
SYS_LOB_P442                   SYS_IL_P443

And finally, let’s make a row movement for a big,big LOB data (say, 32Mb).

SQL> DECLARE
  2      l_lob CLOB;
  3      buffer VARCHAR2(32767);
  4  BEGIN
  5      buffer := LPAD('0',32767,'0');
  6      SELECT z INTO l_lob FROM clob_tab WHERE x = 1 FOR UPDATE;
  7      FOR i IN 1..1024 LOOP
  8          DBMS_LOB.WRITEAPPEND(l_lob,32767,buffer);
  9      END LOOP;
 10      COMMIT;
 11  END;
 12  /

PL/SQL procedure successfully completed.

SQL> select partition_name, blocks from dba_segments
  2  where segment_name = 'SYS_LOB0000078579C00003$$';

PARTITION_NAME                     BLOCKS                                       
------------------------------ ----------                                       
SYS_LOB_P361                            8                                       
SYS_LOB_P364                            8                                       
SYS_LOB_P442                        10240                                       

SQL> select partition_name, blocks from dba_segments
  2  where segment_name = 'SYS_IL0000078579C00003$$';

PARTITION_NAME                     BLOCKS                                       
------------------------------ ----------                                       
SYS_IL_P362                             8                                       
SYS_IL_P365                             8                                       
SYS_IL_P443                            32                                       

SQL> set timing on
SQL> update clob_tab SET y = 1 WHERE x = 1;

1 row updated.

Elapsed: 00:00:16.65
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> set timing off
SQL> select partition_name, blocks from dba_segments
  2  where segment_name = 'SYS_LOB0000078579C00003$$';

PARTITION_NAME                     BLOCKS                                       
------------------------------ ----------                                       
SYS_LOB_P361                            8                                       
SYS_LOB_P364                         9216                                       
SYS_LOB_P442                        10240                                       

SQL> select partition_name, blocks from dba_segments
  2  where segment_name = 'SYS_IL0000078579C00003$$';

PARTITION_NAME                     BLOCKS                                       
------------------------------ ----------                                       
SYS_IL_P362                             8                                       
SYS_IL_P365                            16                                       
SYS_IL_P443                            48

Are you guys still sure RDBMS rather than OS is a relevant place to keep LOBs ?😉

About dnikiforov

I'm writing here about everything what is in my interest - Java, Oracle, MySQL and other things. These are just remarks about things I'm working with, and if this helps you, guys, in your work - this makes me happy. ;)
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s