AnySQL.net English

    We could add enable or disable storage in row attribute for Oracle CLOB/BLOB columns, by default it's enabled. If the LOB value is less than 4000 bytes, the LOB value will be stored inline the row piece, else both of these type will store the value in LOB segment. Is there any difference between them when LOB value is stored out of row? Let's start a demo, I create this demo on a 10g database, create a demo table contains two CLOB columns, and populate them with the same value, the table just contains one row, as following :

SQL> create table t_lobtest (col1 clob, col2 clob)
  2  lob (col2) store as (disable storage in row);

Table created.

ASQL> SELECT DBMS_LOB.GETLENGTH(COL1) COL1_LEN,
    2        DBMS_LOB.GETLENGTH(COL2) COL2_LEN
    3 FROM T_LOBTEST;

COL1_LEN COL2_LEN
-------- --------
    7989     7989

1 rows returned.

    The LOB value length is greater than 4000, so the value are stored out of row for both columns, then let's select them in SQL*Plus and do a statistics trace, you will see that consistent gets for the two LOB columns are different.

SQL> select col1 from t_lobtest;

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          4  physical reads
          0  redo size
       1213  bytes sent via SQL*Net to client
        829  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select col2 from t_lobtest;

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         19  consistent gets
          4  physical reads
          0  redo size
       1141  bytes sent via SQL*Net to client
        781  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

    However the physical reads are both 4 due to direct path read of LOB, but for consistent gets, the disabled column has more gets than the enabled column. According to my research of LOB value, for LOB columns with enable storage in row attribute, if the chunks are less than 12, then the chunk address will be stored in row piece ( LOB_ID, CHUNK1, CHUNK2, ..., CHUNK12 ), and the LOB index will not store the chunk address which already stored in the row piece. for LOBs with disable storage in row attribute, only LOB_ID is stored in the row piece, every chunk addresses are stored in the LOB index. For this case, when accessing column "COL1", no LOB index access is required, but for column "COL2", oracle have to touch LOB index's block, that's where the extra consistent gets come from.

    For high execution frequency SQLs, take this into account when tuning. You can define the proper chunk size to reduce the entries in LOB index for performance improvement.

    Some of my friends write some articles of hash cluster table, about the lowest cost of the equal access. I want to tell you how to enhance the range access of the hash cluster key column. The demo scripts listed as following :

CREATE CLUSTER HASH_C_DEMO (OBJECT_ID NUMBER)
SIZE 50 SINGLE TABLE HASHKEYS 1000;
CREATE TABLE T_C_DEMO (OBJECT_ID NUMBER, OBJECT_NAME VARCHAR2(30))
CLUSTER HASH_C_DEMO (OBJECT_ID);
CREATE INDEX IDX_T_C_DEMO ON T_C_DEMO (OBJECT_ID);
INSERT INTO T_C_DEMO SELECT OBJECT_ID, OBJECT_NAME FROM USER_OBJECTS;

    Let's test the equal access :

SQL> SELECT OBJECT_NAME FROM T_C_DEMO WHERE OBJECT_ID=621150;

OBJECT_NAME
------------------------------
P_DEMO

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   TABLE ACCESS (HASH) OF 'T_C_DEMO'

    Let's test the range access :

SQL> SELECT OBJECT_NAME FROM T_C_DEMO WHERE OBJECT_ID BETWEEN 611851 AND 611853;

OBJECT_NAME
------------------------------
SYS_LOB0000609516C00004$$
T_IOT

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T_C_DEMO'
   2    1     INDEX (RANGE SCAN) OF 'IDX_T_C_DEMO' (NON-UNIQUE)

    I will tell you how to choose the SIZE and HASHKEYS option in later articles.

    Hash cluster table can get much performance improve for equal access, however proper SIZE and HASHKEYS option is required. In OLTP, we always have some tables, the query use equal condition and with unique or very effective index on it, but the execution frequency is really high, half of the total consistent gets is due to these simple queries.

    I will first analyze the table and get the current blocks with rows :

SQL> SELECT BLOCKS FROM USER_TABLES WHERE TABLE_NAME='T_OBJECTS';

    BLOCKS
----------
       118

    For this table, data distribution is even enough, so I set the SIZE option to data block size, and set the HASHKEYS a value larger than the data blocks, in this case it's 144.

SQL> CREATE CLUSTER C_T_OBJECTS (OBJECT_ID NUMBER(38,0))
  2  SIZE 8192 SINGLE TABLE HASHKEYS 144;

Cluster created.

    Now create a hash cluster table CT_OBJECTS with the same structure as T_OBJECTS, and insert the rows from T_OBJECTS. I run the following PL/SQL to access all the rows in hash cluster table to detect the performance improve.

SQL> DECLARE
  2    TEMP NUMBER:=0;
  3  BEGIN
  4    FOR REC IN (SELECT OBJECT_ID FROM T_OBJECTS) LOOP
  5       SELECT DATA_OBJECT_ID INTO TEMP FROM CT_OBJECTS
  6              WHERE OBJECT_ID=REC.OBJECT_ID;
  7    END LOOP;
  8  END;
  9  /

    Find out the hash value of the simple query, and get the statistics from V$SQL view.

SQL> SELECT HASH_VALUE, BUFFER_GETS, EXECUTIONS
  2  FROM V$SQL WHERE HASH_VALUE=3267226907;

HASH_VALUE BUFFER_GETS EXECUTIONS
---------- ----------- ----------
3267226907        9464       9462

    You can see that the consistent gets for each row is almost one, the lowest cost.

    HASH is verify effective for locate one row (for equal access). Oracle have very famious hash join method, and hash cluster table to get higher performance. The B*Tree index on some busy system may not be effective enough, we have hit availability problems caused by index root block split or branch block split, and the cost of get one row is still too high for B*Tree index by navigating from root-branch-leaf-data blocks.

    Oracle have support the hash cluster table, so hash cluster index is not a problem at all, because index is also a table contains the key columns and the ROWID value of the table rows. We could eliminate the root block split and branch block split, and get the cost of single row with 2 logical gets. Why not hash cluster table directory, because table is hard to move to reorganize the storage, while index can be rebuild online to reorganize the storage for better performance. So hash cluster index is worthy of it in some cases.

    Now we can write our own context index to simulate it, but I never write my own context index, any one have a sample?

    In Oracle, the hash cluster column type are not limited to number only, so it not so hard to support it? It's likely a specially shadow table of the master table, we can use trigger to simulate it currently.

SQL> create cluster c_hashidx (col1 varchar2(30)) hashkeys 128;

Cluster created.

    Is there other DBMS system which support this kind of index? In IBM DB2, when creating an index you can specify some including columns for access filter, but not affect the row order of the index, if oracle can add this feature too, it will be another great thing.

    I have a table named "T_OBJECTS" and one index on it named "T_OBJECTS_IX1". They are not analyzed now:

TABLE_NAME       NUM_ROWS LAST_ANALYZED
-------------- ---------- ---------------
T_OBJECTS
T_OBJECTS_IX1

    Now I receive a task to create a second index on this table, I add the compute statistics option :

SQL> CREATE INDEX T_OBJECTS_IX2 ON T_OBJECTS
  2  ( OBJECT_NAME ) compute statistics;

Index created.

    Let's query the table's statistics data again :

TABLE_NAME       NUM_ROWS LAST_ANA
-------------- ---------- --------
T_OBJECTS             555 00:30:54
T_OBJECTS_IX1
T_OBJECTS_IX2         555 00:30:54

    Because of the table is analyzed, but another index is not analyzed, so the optimizer choose the wrong execution plan, and get the server in performance trouble. I later test it on 10g version, the table will not be analyzed. So when you create index with compute statistics option in 9i, you should be really carefully, because table will be analyzed too.