Re: Large Index organized tables: just a marketing feature ?! (for experts)

From: Sergey Adamenko <asv._n_o__.s_p_a_m_at_softline.kiev.ua>
Date: Tue, 5 Aug 2003 10:02:58 +0300
Message-ID: <3f2f569f$1@softline.kiev.ua>

Hi!  

I also had problems with filling/loading IOTs.  

IMO, the fastest way to accomplish this task is: 1. Get HEAP table filled
2. Use CTAS with STORAGE parameters set to avoid space management and index node splits.    

It seems that CTAS is more efficient for IOT then INSERT-INTO­-SELECT because it generates zero 'leaf node splits' or 'branch node splits' for the statement.    

Here is an example:  

SQL> 
SQL> 
SQL> COL NAME FORMAT A20
SQL> 
SQL> SELECT COUNT(*) FROM cdc_unitfamily;
 
  COUNT(*)                                                                      
----------                                                                      
     16222                                                                      
 

SQL>
SQL> DROP TABLE cdc_unitfamily1;  

Table dropped.  

SQL> 
SQL> 
SQL> SELECT
  2    n.name,

  3 s.value,
  4 n.class
  5 FROM
  6 v$mystat s,
  7 v$statname n
  8 WHERE

  9    n.name IN ('leaf node splits',
 10               'branch node splits',
 11               'consistent gets',
 12               'db block gets',
 13               'db block changes') AND

 14 n.statistic# = s.statistic#;  

NAME                      VALUE      CLASS                                      


-------------------- ---------- ----------                                      

db block gets                91          8                                      
consistent gets             322          8                                      
db block changes            102          8                                      
leaf node splits              0        128                                      
branch node splits            0        128                                      
 
SQL> 
SQL> 

SQL>
SQL> CREATE TABLE cdc_unitfamily1
  2 (
  3 child_id,
  4 parent_id,
  5 CONSTRAINT pk_cdc_unitfamily1 PRIMARY KEY(child_id, parent_id)   6 ) ORGANIZATION INDEX STORAGE (INITIAL 256K NEXT 256K PCTINCREASE 0)   7 AS
  8 SELECT

  9      child_id,
 10      PARENT_ID
 11    FROM
 12      CDC_UNITFAMILY;
 

Table created.  

SQL>
SQL> SELECT

  2    n.name,
  3    s.value,
  4    n.class

  5 FROM
  6 v$mystat s,
  7 v$statname n
  8 WHERE

  9    n.name IN ('leaf node splits',
 10               'branch node splits',
 11               'consistent gets',
 12               'db block gets',
 13               'db block changes') AND

 14 n.statistic# = s.statistic#;  

NAME                      VALUE      CLASS                                      


-------------------- ---------- ----------                                      

db block gets               229          8                                      
consistent gets             517          8                                      
db block changes            265          8                                      
leaf node splits              0        128                                      
branch node splits            0        128                                      
 
SQL> 
SQL> 
SQL> 

SQL>
SQL> DROP TABLE cdc_unitfamily1;  

Table dropped.  

SQL>
SQL> CREATE TABLE cdc_unitfamily1
  2 (
  3 child_id number,
  4 parent_id number,
  5 CONSTRAINT pk_cdc_unitfamily1 PRIMARY KEY(child_id, parent_id)   6 ) ORGANIZATION INDEX
  7 STORAGE (INITIAL 256K NEXT 256K PCTINCREASE 0);   Table created.  

SQL> 
SQL> 
SQL> 
SQL> SELECT
  2    n.name,

  3 s.value,
  4 n.class
  5 FROM
  6 v$mystat s,
  7 v$statname n
  8 WHERE

  9    n.name IN ('leaf node splits',
 10               'branch node splits',
 11               'consistent gets',
 12               'db block gets',
 13               'db block changes') AND

 14 n.statistic# = s.statistic#;  

NAME                      VALUE      CLASS                                      


-------------------- ---------- ----------                                      

db block gets               412          8                                      
consistent gets             756          8                                      
db block changes            509          8                                      
leaf node splits              0        128                                      
branch node splits            0        128                                      
 
SQL> 
SQL> 

SQL>
SQL> INSERT INTO cdc_unitfamily1 (child_id, parent_id)   2 SELECT
  3 child_id,
  4 PARENT_ID
  5 FROM
  6 CDC_UNITFAMILY;   16222 rows created.  

SQL> 
SQL> 
SQL> SELECT
  2    n.name,

  3 s.value,
  4 n.class
  5 FROM
  6 v$mystat s,
  7 v$statname n
  8 WHERE

  9    n.name IN ('leaf node splits',
 10               'branch node splits',
 11               'consistent gets',
 12               'db block gets',
 13               'db block changes') AND

 14 n.statistic# = s.statistic#;  

NAME                      VALUE      CLASS                                      


-------------------- ---------- ----------                                      

db block gets              1740          8                                      
consistent gets            1007          8                                      
db block changes           2062          8                                      
leaf node splits             55        128                                      
branch node splits            0        128                                      
 

SQL>
SQL> EXIT Differencial results

                                      INSERT-SELECT                 CTAS 
db block gets                        1740                                138                                                
consistent gets                     1007                                 195                                      
db block changes                2062                                 163                                        
leaf node splits                       55                                      0                            
branch node splits                   0                                       0
 

 
 

Note, execution of INSERT-INTO­-SELECT statement caused 55 leaf node splits while CTAS none.           

Best regards,
Sergey Adamenko Received on Tue Aug 05 2003 - 02:02:58 CDT