Skip to main content

Search

Welcome to Tibero GTS!

Table Size Estimation with Null in Middle Column (Block Dump)

Document Type | Technical Information

CategoryㅣTuning

Document Number | TTUTI022

 

Overview

When a table contains null columns, the table size changes depending on the position of the null columns.
This document explains how to estimate the table size when the null column is in the middle. 
(Space efficiency is better when the last column is NULL, allowing more efficient use of table space.)
 

Method

Query Table Size with Null in Middle Column

1. Create Test Table

Create a test table with a null column in the middle.
CREATE TABLE TEST_MID_NULL
(COL1 VARCHAR2(3) NOT NULL,
COL2 VARCHAR2(4000) 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) NOT NULL);

 

2. Load Data

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

 

3. Query Block Dump Target Information

SELECT COUNT(*),
DBMS_ROWID.ROWID_TO_RELATIVE_FNO(ROWID) FILE_NO, 
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK_NO 
FROM TEST_MID_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 69119
  • The retrieved ROWID information varies by environment, so specify the block number according to your actual environment.
  • The generated block dump file can be found at the following path.
$TB_HOME/instance/$TB_SID/dump/tracedump

 

5. Check Dump

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

**Dump start at 2017-04-02 10:17:16
data block Dump[dba=00_00069119(69119),tsn=0000.00e4421d,type=13,seqno =1] 
--------------------------------------------------------------
sgmt_id=33066  cleanout_tsn=0000.00e4421d  btxcnt=2 
l1dba=00_00069111(69111), offset_in_l1=8
btx      xid                undo          fl  tsn/credit
00  0017.47.1454  01_00048404.00028.00001  E  0000.00e44192
01  0000.00.0000  00_00000000.00000.00000  I  0000.00000000 
--------------------------------------------------------------
Data block dump:
	dlhdr_size=774  freespace=819  freepos=1677  symtab_offset=0  rowcnt=383 
Row piece dump:
rp 0 1677:  [17] flag=--H-FL--  itlidx=0    colcnt=10
col 0: [4]
0000: 03 30 30 31                                     .001 
col 1: [1]
0000: 00                                              . 
col 2: [1]
0000: 00                                              . 
col 3: [1]
0000: 00                                              . 
col 4: [1]
0000: 00                                              . 
col 5: [1]
0000: 00                                              . 
col 6: [1]
0000: 00                                              . 
col 7: [1]
0000: 00                                              . 
col 8: [1]
0000: 00                                              .
col 9: [2]
0000: 01 41                                           .A 
rp 1 1694:  [17] flag=--H-FL--  itlidx=0    colcnt=10
col 0: [4]
0000: 03 30 30 32                                     .002 
col 1: [1]
0000: 00                                              . 
col 2: [1]
0000: 00                                              .
.
.
.
//Omitted for brevity
.
.
.
//Because the null column is located in the middle, information about the null column is not omitted and is included

 

 

 

Related to