Oracle VSIZE Function: A Simple Guide

The VSIZE function in Oracle SQL is a simple utility function that tells you the number of bytes used to store a specific expression.

This is different from the LENGTH function, which tells you the number of characters. VSIZE is useful for understanding the actual storage size of your data, which is especially important in multibyte character sets (like UTF-8) where a single character can take up more than one byte.

What is the VSIZE Function in Oracle?

The VSIZE(expr) function takes any expression (expr) and returns a NUMBER indicating how many bytes Oracle is using to store its internal representation.

• For a NUMBER, it tells you how many bytes are used to store that number.
• For a VARCHAR2 string, it tells you the total bytes of all characters in the string.
• If the expression is NULL, VSIZE returns NULL.

VSIZE Function Syntax

The syntax for VSIZE is very simple:

VSIZE(expr)

Let's break that down:

• expr: The value or column you want to measure (e.g., 'Hello', last_name, salary).

Oracle VSIZE Function Examples

Here are two practical examples of how to use VSIZE.

Example 1: Comparing LENGTH and VSIZE using VSIZE

This example clearly shows the difference between the character length (LENGTH) and the byte size (VSIZE) using a name with a non-ASCII character. In many Unicode character sets, é takes 2 bytes.

Query:

-- In this example, 'José' has 4 characters
-- But 'J', 'o', 's' are 1 byte each, and 'é' is 2 bytes.
-- Total bytes = 1 + 1 + 1 + 2 = 5
SELECT 
  'José' AS "Name",
  LENGTH('José') AS "Character_Length",
  VSIZE('José') AS "Byte_Size"
FROM DUAL;

Result: (The exact VSIZE result depends on your database's character set)

Name Character_Length  Byte_Size
---- ---------------- -----------
José                4           5

Example 2: Finding the Byte Size of a Table Column using VSIZE

This example returns the number of bytes used to store each last_name in the employees table for a specific department.

Query:

SELECT 
  last_name, 
  VSIZE(last_name) AS "Bytes"
FROM 
  employees
WHERE 
  department_id = 10
ORDER BY 
  employee_id;

Result:

LAST_NAME                 Bytes
------------------------- ----------
Whalen                   6