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:
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 (
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
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
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.
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.