Size of CHAR(1) versus VARCHAR2(1)


There was a discussion today about whether CHAR(1) is more space efficient than VARCHAR2(1). My answer below shows that there is no difference in size on disk between the two.

Environment

For this exercise, I am using Oracle RDBMS 12.1.0.2.0 on Windows.

Create Test User

Used the following command to create a test user (called ‘TESTUSER’):

CREATE USER testuser
   IDENTIFIED BY ***********
   DEFAULT TABLESPACE users
   QUOTA UNLIMITED ON users
   TEMPORARY TABLESPACE tempts1;
GRANT CREATE SESSION, CREATE TABLE TO testuser;

Test Cases

There are four (4) test cases:

Test Case # Data Type Nullable ?
1 CHAR Yes
2 CHAR No
3 VARCHAR2 Yes
4 VARCHAR2 No

Implement Test Cases

Used the following commands to create the test cases:

-- ----------------------------------------------------------
-- Connect to test user
-- ----------------------------------------------------------
CONNECT testuser/**********
-- ----------------------------------------------------------
-- Create separate tables for test case. The name includes
--   the test case number.
-- ----------------------------------------------------------
CREATE TABLE t01 (c CHAR(1));
CREATE TABLE t02 (c CHAR(1) NOT NULL);
CREATE TABLE t03 (c VARCHAR2(1));
CREATE TABLE t04 (c VARCHAR2(1) NOT NULL);
-- ----------------------------------------------------------
-- Insert data into the tables so that the tables are
--   materialised on disk.
-- ----------------------------------------------------------
INSERT INTO t01(c) VALUES(' ');
INSERT INTO t02(c) VALUES(' ');
INSERT INTO t03(c) VALUES(' ');
INSERT INTO t04(c) VALUES(' ');
COMMIT;
-- ---------------------------------------------------------
-- Gather table statistics to get the average row length
-- ---------------------------------------------------------
EXEC dbms_stats.gather_schema_stats(USER);

Test Results

At this stage, we have four (4) tables of one (1) row each. Each row has one (1) column with a blank in it. We have collected table statistics of which one is the average row length.

To get the average row length for each table, I ran the following command:

COLUMN table_name FORMAT a10
SELECT
    table_name,
    avg_row_len
  FROM
    user_tables
  ORDER BY
    table_name;

The results are:

TABLE_NAME AVG_ROW_LEN
---------- -----------
T01                  2
T02                  2
T03                  2
T04                  2

Note: Every row has the same length. Therefore, I conclude that neither CHAR(1) or VARCHAR2(1) are more space efficient than the other.

What Happens With NULLs?

What happens when I set the column to NULL? Let;s try with the following:

UPDATE t01 SET c = NULL;
UPDATE t03 SET c = NULL;
COMMIT;
EXEC dbms_stats.gather_schema_stats(USER);

The results are now:

TABLE_NAME AVG_ROW_LEN
---------- -----------
T01                  0
T02                  2
T03                  0
T04                  2

So, it is possible to have zero-length rows in Oracle!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s