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