Skip to main content

Search

Welcome to Tibero GTS!

Table Size Estimation When the Last Column is Null (Block Dump)

Document Type | Technical Information

Category | Tuning

Document Number | TTUTI021

 

Overview

When a table includes null columns, the table size changes depending on the position of the null columns. Especially, if the last column is NULL, the table space can be used more efficiently.
This document guides you on how to estimate the size of a table when the last column is Null.
 

Method

Check Table Size When the Last Column is Null

1. Create Test Table

Create a table where the last column is NULL.
CREATE TABLE TEST_END_NULL
(COL1 VARCHAR2(3) NOT NULL,
COL2 VARCHAR2(4000) NOT NULL,
COL3 VARCHAR2(4000) NULL,
COL4 VARCHAR2(4000) NULL,
COL5 VARCHAR2(4000) NULL,
COL6 VARCHAR2(4000) NULL,
COL7 VARCHAR2(4000) NULL,
COL8 VARCHAR2(4000) NULL,
COL9 VARCHAR2(4000) NULL,
COL10 VARCHAR2(4000) NULL);

 

2. Load Data

INSERT INTO TEST_END_NULL
SELECT LPAD(LEVEL,3,'0'),'A',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL FROM DUAL
CONNECT BY LEVEL < 1000;

 

3. Query Information for Block Dump

SELECT COUNT(*),
DBMS_ROWID.ROWID_TO_RELATIVE_FNO(ROWID) FILE_NO, 
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK_NO 
FROM TEST_END_NULL
GROUP BY 
DBMS_ROWID.ROWID_TO_RELATIVE_FNO(ROWID), 
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID);

 

4. Perform Block Dump

ALTER SYSTEM DUMP DATAFILE 0 BLOCK 69135;
  • The ROWID information retrieved varies by environment, so the block number must be specified according to your actual environment.
  • The generated block dump file can be found in the following path.
$TB_HOME/instance/$TB_SID/dump/tracedump

 

5. Check Dump

Open the generated dump file to check the table size and how NULL columns are handled.
 
**Dump start at 2017-04-02 10:16:56
DUMP of BLOCK file #0 block #69135

**Dump start at 2017-04-02 10:16:56
data block Dump[dba=00_00069135(69135),tsn=0000.00e441b4,type=13,seqno =1] 
--------------------------------------------------------------
sgmt_id=33067  cleanout_tsn=0000.00e441b4  btxcnt=2
l1dba=00_00069127(69127), offset_in_l1=8
btx      xid                undo           fl  tsn/credit
00  0003.32.1549  01_00037288.00021.00015  E  0000.00e441b0
01  0000.00.0000  00_00000000.00000.00000  I  0000.00000000 
--------------------------------------------------------------
Data block dump:
dlhdr_size=1330  freespace=825  freepos=2239  symtab_offset=0  rowcnt=661 
Row piece dump:
rp 0 2239:  [9] flag=--H-FL--  itlidx=0    colcnt=2
col 0: [4]
0000: 03 30 30 31                                     .001 
col 1: [2]
0000: 01 41                                           .A 
rp 1 2248:  [9] flag=--H-FL--  itlidx=0    colcnt=2
col 0: [4]
0000: 03 30 30 32                                     .002
col 1: [2]
0000: 01 41                                           .A 
rp 2 2257:  [9] flag=--H-FL--  itlidx=0    colcnt=2
col 0: [4]
0000: 03 30 30 33                                     .003 
col 1: [2]
0000: 01 41        
rp 3 2266:  [9] flag=--H-FL--  itlidx=0    colcnt=2 
col 0: [4]
0000: 03 30 30 34                                     .004 
col 1: [2]
0000: 01 41                                           .A 
rp 4 2275:  [9] flag=--H-FL--  itlidx=0    colcnt=2
col 0: [4]
0000: 03 30 30 35                                     .005 
col 1: [2]
0000: 01 41                                           .A 
rp 5 2284:  [9] flag=--H-FL--  itlidx=0    colcnt=2
col 0: [4]
0000: 03 30 30 36                                     .006 
.
.
.
//Omitted
.
.
.
//Information about null columns is omitted

 

 

 

Related to