Oracle Smallint Datatype

In this  Oracle tutorial, we will learn about the smallint datatype in the oracle database. This article will provide an overview of the SMALLINT data type in Oracle Database 21c, its advantages, and how it can be used.

Introduction to Oracle Smallint Datatype

The SMALLINT data type is a commonly used data type in database management systems. It is used to store small integers, typically within the range of -32,768 to 32,767.

Oracle Database 21c supports the SMALLINT data type, which is a 16-bit signed integer.

Also, check: How to drop database in Oracle

Advantages of Smallint datatype

• Storage Effectiveness: The SMALLINT data type requires less storage space—2 bytes—than the INTEGER data type, which needs 4 bytes. Because of this, it can use less storage and have smaller databases, which is very useful when working with big datasets.
• SMALLINT data types can be handled more quickly than larger integer data types, like INTEGER because they are 16-bit integers. This leads to improved query performance. Applications that use huge datasets and complicated queries may benefit from enhanced query performance as a result.
• Reduced Memory Usage: Using SMALLINT data types can drastically reduce memory usage when working with huge datasets. This is particularly crucial for memory-constrained applications, like those found in mobile or embedded systems.

Oracle Smallint datatype Syntax

Here is the syntax for declaring a Smallint column in a table Oracle 21c:

column_name SMALLINT;

For example, if you want to create a table named my_table with a SMALLINT column named my_smallint, you can use the following syntax:

CREATE TABLE employees (
  emp_id SMALLINT
);

In this example, emp_id is a column of the SMALLINT data type.

Now, we will insert the values into the following given table

INSERT INTO employees (emp_id) VALUES (562);

select * from employees;

In the given example, the value of 562 is inserted into the emp_id column.

Note that the SMALLINT data type is a 16-bit signed integer that can store values in the range of -32,768 to 32,767. If you need to store larger integers, you may need to use a larger integer data type, such as INTEGER or BIGINT.

Read: Oracle Blob Datatype

Example:

Now, we will discuss how to use the SMALLINT datatype in the Oracle developer tool.

Now we will insert the values into the following given table

INSERT INTO customer (cust_id, product_price) VALUES (623, 6752);
INSERT INTO customer (cust_id, product_price) VALUES (854, 9765);

select * from customer;

Storage and Performance of Smallint Datatype in Oracle 21c

The integer values that can be stored by the Oracle Smallint data type range from -32768 to 32767. It is a subtype of the Integer data type. It uses 2 bytes of storage space and is commonly used in applications that require small integer values.

Smallint is a fast data type that is useful for storing small integer values in Oracle databases, in terms of both performance and efficiency. Since it requires less storage space than larger integer types such as Integer or BigInt, it can improve the performance of database queries that involve the Smallint column.

Also, check: Real Datatype Oracle

Conclusion

So, In this Oracle tutorial, we understood how to define and use the Smallint Datatype in Oracle Database. And we have also covered a few sample examples related to it.

Also, take a look at some more Oracle tutorials.

• Oracle raw Datatype
• Oracle long raw Datatype
• Oracle Timestamp Datatype