AnySQL.net English

    I have nothing to do at last weekend, so I decided to make some changes to the MyLOG utility, I had added the search by redo layer/opcode feature two weeks ago, so I tried my best to add the search by object id feature. However the search by object id can only be performed on redo record of layer 11, which is the table data operations. It's a big improve for me, for the MyLOG utility.

    If I focus on the layer 11 and master all the details, I should be able to write a logical replication utility like Quest SharePlex or GoldenGate. The fact is that I have to spend a lot of time in making money for the whole family by doing something not relative to this subject. Following is the search command syntax :

SEARCH option value [option value]
TAIL   option value [option value]
DUMP   option value [option value]
   OPTION
       OP       layer
       SUBOP    opcode
       START    start redo block
       END      end redo block
       BLOCK    in given block
       OBJECT   object id

    Let's try each of these command with the enhanced search feature!

LOG> tail object 61 start 2
Start tailing redo operation ...

0x00005e30.00003eb8.0010 2006-08-23 19:08:58
          [05.02] [Trans Undo ] Update rollback segment header - KTURDH
          [05.01] [Trans Undo ] Undo block or under segment header - KTURDB
          [11.05] [Row Access ] Update Row Piece
          [05.19] [Trans Undo ] Transaction start audit log record
LOG> search object 61 start 2
Start search opcode = [00.00] ...
Seq=24112, Start=16056, Offset=0x0010=16, End=16057, Time=2006-08-23 19:08:58
LOG> dump object 61 start 2
Start dump redo operation ...
REDO RECORD -- RBA=0x00005e30.00003eb8.0010 LEN=0x0284 VLD=0x01 SCN=0x031f.05c0098c
  CHANGE#=0x01 OP=5.2 SEQ=1 TYP=0 CLS=43 OFFS=0x000c LEN=0x003c DBA=0x6280b402 ...
    PIECE#=2 OFFS=0x001c LEN=0x0020 SIZE=0x0020
    ktudh redo: slt=0x0045 sqn=0x000daa80 flag=0x0412 siz=228 fbi=0
                uba=0x3b41c2dd.3bce.00  pxid=0x0000.000.00000000
  CHANGE#=0x02 OP=5.1 SEQ=8 TYP=0 CLS=44 OFFS=0x0048 LEN=0x0114 DBA=0x3b41c2dd ...
    PIECE#=2 OFFS=0x0038 LEN=0x0014 SIZE=0x0014
    ktudb redo: siz=0x00e4 spc=0x000000ac flag=0x0012 seq=0x00ce rec=0x00
                xid=0x000e.045.000daa80
    PIECE#=3 OFFS=0x004c LEN=0x0030 SIZE=0x0030
    ktubu redo: slt=69 rci=0 opc=11.1 objn=61 objd=61 tns=0
    PIECE#=4 OFFS=0x007c LEN=0x0020 SIZE=0x0020
    ......

    With these powerful command, I can make more improvement in log format research, and more smoothly.

    I wrote the following SQL in my script, before I wrote I feel it not the best, but just cannot think of it.

SELECT
   REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
      REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TABLE_NAME,'0','#'),
         '1','#') ,'2','#') ,'3','#') ,'4','#')
             ,'5','#') ,'6','#') ,'7','#') ,'8','#') ,'9','#')  PATTERN_NAME
FROM USER_TABLES

    When taking metro line to home, I reminded the translate function, it's very very simple.

SELECT
   TRANSLATE(TABLE_NAME,'0123456789','##########') PATTERN_NAME
FROM USER_TABLES

    I want to write the following SQL to find out which tables should have the same structure, and them compare them.

SELECT TABLE_NAME FROM USER_TABLES
WHERE TRANSLATE(TABLE_NAME,'0123456789','##########') IN
(SELECT
   TRANSLATE(TABLE_NAME,'0123456789','##########') PATTERN_NAME
FROM USER_TABLES
GROUP BY TRANSLATE(TABLE_NAME,'0123456789','##########')
HAVING COUNT(*) > 1)

    Choose right way is very important for us. One of my friend said we could use "REGEXP_REPLACE(TABLE_NAME, '[1-9]', '#' )" in Oracle 10g.

    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.