PL/SQL Index-By Tables

PL/SQL Index-By Tables

There exist several types of collections that can be used in PL/SQL for working with datasets, but one of the most common types is the PL/SQL Index-By Table, often known as an Associa

tive Array. This type of table allows you to have elements indexed via a primary key, which can be either a string or an integer, so it is very flexible and powerful for most database applications. This article will Creating Index-By Tables, how they are structured, how to declare and work with them, Using Index-By Tables in Oracle, and their benefits. We also include examples to illustrate the differences between index-by tables and other collections in PL/SQL.

Introduction to PL/SQL Collections

PL/SQL Collections are data structures that allow you to store multiple values in a single unit. Collections are useful when working with large sets of data, especially when the exact number of elements is not known beforehand.

There are three main types of collections in PL/SQL:

• Associative Arrays (Index-By Tables)
• Nested Tables
• VARRAYs (Variable-Sized Arrays)

Each type of collection has its unique advantages and limitations, and choosing the right type depends on the specific use case.

Collection Type	Description
Associative Arrays	Unbounded collections indexed by integers or strings.
Nested Tables	Unbounded collections that allow for dynamic storage and retrieval.
VARRAYs	Bounded collections with a predefined maximum size.

What is an Index-By Table in PL/SQL?

Index-By Table, more commonly called Associative Array, is an unbounded collection of elements. That is, each element of the collection has a unique index that allows it to be identified uniquely. This index may take either numeric data type or string data type. While doing computations with large data sets, this flexibility definitely comes in handy.

Index-By Tables Structure

Elements of Index-By Table are stored as key-value pairs. In this case, index itself is a unique key referencing some value. Such tables do not need to be continuous in their index range; it is absolutely fine if indices skip some index numbers without a problem. That’s why they are the best choice for all those scenarios where keys or indices are not continuous.

Key Features

Main characteristics of PL/SQL Index-By Tables are as under:

• They expand in size when more entries are required to be stored.
• Indexed by integers or strings; This makes them very flexible and useful with a wide range of applications.
• Fast lookup and access; Index-By Tables are designed to offer fast access through the index.

Creating Index-By Tables in PL/SQL

In PL/SQL, Index-By Tables are declared using the following syntax:

TYPE type_name IS TABLE OF element_type INDEX BY index_type;

Where:

• type_name: The name of the Index-By Table type.
• element_type: The data type of the elements stored in the table.
• index_type: The data type of the index, which can be either BINARY_INTEGER or VARCHAR2.

Example: Creating an Index-By Table

Let’s create an Index-By Table that stores employee salaries, indexed by employee IDs.

DECLARE
    TYPE emp_salary_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
    emp_salaries emp_salary_table;
BEGIN
    -- Adding elements to the Index-By Table
    emp_salaries(101) := 50000;
    emp_salaries(102) := 55000;
    emp_salaries(103) := 60000;
    
    -- Accessing and printing values from the Index-By Table
    DBMS_OUTPUT.PUT_LINE('Salary of Employee 101: ' || emp_salaries(101));
    DBMS_OUTPUT.PUT_LINE('Salary of Employee 102: ' || emp_salaries(102));
    DBMS_OUTPUT.PUT_LINE('Salary of Employee 103: ' || emp_salaries(103));
END;

In this example:

• We define an Index-By Table called emp_salary_table to store employee salaries.
• We use employee IDs as the index and assign salary values to each employee.
• Finally, we access and print the salaries using the indices.

Using Index-By Tables in Oracle

Once an Index-By Table has been declared and populated, we can perform several operations on it, including retrieving, updating, and deleting elements.

1. Inserting Values into Index-By Tables

Adding values to an Index-By Table is straightforward. We simply use the index to assign a value.

emp_salaries(104) := 65000;

This statement adds a new entry for employee ID 104 with a salary of 65000.

2. Accessing Values in Index-By Tables

To retrieve a value from the table, use the index in the same way:

DBMS_OUTPUT.PUT_LINE('Salary of Employee 104: ' || emp_salaries(104));

3. Updating Values in Index-By Tables

Updating a value in an Index-By Table involves reassigning a new value to the same index.

emp_salaries(104) := 70000;
DBMS_OUTPUT.PUT_LINE('Updated Salary of Employee 104: ' || emp_salaries(104));

4. Deleting Entries in Index-By Tables

To remove an element from the table, use the DELETE statement with the index:

emp_salaries.DELETE(104);

This will delete the entry for employee ID 104. If you want to delete all entries from the table, use:

emp_salaries.DELETE;

This will clear all entries in the Index-By Table.

Associative Arrays in PL/SQL

Associative Arrays in PL/SQL are a powerful feature that allows developers to store data in key-value pairs. This data structure is particularly useful for scenarios where you need fast access to elements without the overhead of traditional database operations.

Definition and Characteristics

An associative array is a one-dimensional, unbounded, and sparse collection of homogeneous elements. Each element in an associative array is accessed via a unique key, which can be either a string or an integer. This makes associative arrays in PL/SQL flexible and efficient for temporary data storage during program execution. Unlike other collections, associative arrays do not require a predefined size, allowing them to grow dynamically as needed.

Sparse Nature: Associative arrays can have gaps between their elements, meaning not every index needs to be populated. This characteristic allows developers to utilize memory efficiently when dealing with datasets that may not be sequentially filled.

Homogeneous Elements: All elements in an associative array must be of the same data type. This uniformity ensures that operations performed on the elements are consistent and predictable.

Key-Value Pair Storage: Data is stored in pairs where the key serves as an index to retrieve the corresponding value. This structure facilitates quick lookups and updates, making associative arrays in PL/SQL particularly useful for scenarios requiring fast access to data without the overhead of traditional database operations.

Syntax for Declaring Associative Arrays

To declare an associative arrays in PL/SQL, you use the following syntax:

TYPE array_name IS TABLE OF element_datatype [NOT NULL] INDEX BY index_datatype;

For example, you might declare an associative array that holds book IDs indexed by their titles as follows:

DECLARE
    TYPE book_array IS TABLE OF NUMBER INDEX BY VARCHAR2(50);
    books book_array;
BEGIN
    books('Oracle Database') := 1234;
    books('MySQL') := 9876;
END;

Inserting and Updating Values

Inserting values into an associative array is straightforward. You assign values to keys using the assignment operator (:=). For example:

books('Oracle Database') := 1234;  -- Insert value
books('MySQL') := 9876;            -- Insert another value

Updating values is equally simple; you can reassign a new value to an existing key:

books('MySQL') := 1010;  -- Update value for 'MySQL'

Accessing Elements

To access elements in an associative array, you use the key within parentheses:

DBMS_OUTPUT.PUT_LINE(books('Oracle Database'));  -- Outputs: 1234

Iterating Over Associative Arrays

Associative arrays come with built-in methods to facilitate iteration. The FIRST and NEXT methods allow you to traverse through the keys:

DECLARE
    key VARCHAR2(50);
BEGIN
    key := books.FIRST;  -- Get the first key
    WHILE key IS NOT NULL LOOP
        DBMS_OUTPUT.PUT_LINE(key || ': ' || books(key));
        key := books.NEXT(key);  -- Get the next key
    END LOOP;
END;

PL/SQL Associative Arrays (Index-By Tables) vs. Other PL/SQL Collections

PL/SQL provides other collection types like Nested Tables and VARRAYs. Let’s compare Index-By Tables with these collections to understand when to use each type.

Feature	Index-By Table	Nested Table	VARRAY
Size	Unbounded	Unbounded	Bounded (fixed maximum size)
Indexing	Indexed by integers or strings	Indexed by integers	Indexed by integers
Order	No guaranteed order	No guaranteed order	Ordered by index
Performance	Fast lookups using index	Slightly slower due to nested nature	Fast but limited by fixed size
Use Case	Ideal for dynamic, unbounded data sets	Best for sparse data sets	Best when a fixed number of elements exist

As we can see, Index-By Tables offer more flexibility in terms of indexing and size compared to other collection types. They are ideal for scenarios where you need fast lookups and the data set is dynamic and unbounded.

Practical Example: Using Index-By Tables to Track Inventory

Let’s look at a practical example where we use an Index-By Table to manage the inventory of a store. Each product is identified by a unique product ID, and we need to track the stock quantity for each product.

Example Code

DECLARE
    TYPE inventory_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
    product_stock inventory_table;
BEGIN
    -- Adding stock for products
    product_stock(1001) := 50;
    product_stock(1002) := 75;
    product_stock(1003) := 120;

    -- Updating stock for a product
    product_stock(1001) := product_stock(1001) - 10; -- 10 units sold

    -- Displaying current stock levels
    DBMS_OUTPUT.PUT_LINE('Stock for Product 1001: ' || product_stock(1001));
    DBMS_OUTPUT.PUT_LINE('Stock for Product 1002: ' || product_stock(1002));
    DBMS_OUTPUT.PUT_LINE('Stock for Product 1003: ' || product_stock(1003));

    -- Removing a product from the inventory
    product_stock.DELETE(1002);
    DBMS_OUTPUT.PUT_LINE('Product 1002 removed from inventory');
END;

Output of Example Code

Stock for Product 1001: 40
Stock for Product 1002: 75
Stock for Product 1003: 120
Product 1002 removed from inventory

This is an example that shows how Index-By Tables might be used for real time tracking and updating in inventory applications. This is also one of the reasons why they make an excellent choice for such applications due to flexible indexing and dynamic sizing.

Advantages of PL/SQL Index-By Tables

PL/SQL index-by tables, also known as associative arrays, offer several advantages that enhance performance, efficiency, and ease of use when working with collections in PL/SQL. Here’s a detailed look at the benefits of using index-by tables:

1. Fast Access

• Direct Indexing: Index-by tables provide constant-time access to elements since they allow direct indexing. This means you can retrieve or update values quickly without needing to traverse the collection.
• Efficient Lookups: The use of numeric or string keys for indexing allows for rapid lookups, making them ideal for scenarios where performance is critical.

2. Dynamic Sizing

• Flexible Resizing: Index-by tables can grow and shrink dynamically, allowing for flexible memory management. You can easily add or remove elements without needing to declare a fixed size at the outset, which helps in efficiently utilizing memory.

3. Simplified Code

• Ease of Use: The syntax for working with index-by tables is straightforward, allowing for simpler and more readable code. This simplicity can lead to easier maintenance and fewer bugs.
• Straightforward Iteration: Iterating over index-by tables is easy, as you can use a loop to access elements directly via their indexes.

4. Support for Sparse Data

• Sparse Collections: Index-by tables can efficiently handle sparse data, where not all indexes need to be populated. This is particularly useful for datasets where the number of valid entries is significantly smaller than the potential index range.

5. Strong Typing

• Type Safety: When declaring an index-by table, you can define a specific data type for the elements, ensuring type safety. This helps catch errors at compile-time, reducing runtime issues related to type mismatches.

6. No Limit on Key Types

• Versatile Indexing: Index-by tables can be indexed by any scalar data type, including strings and numbers, allowing for versatile key definitions based on the requirements of the application.

7. Ease of Data Manipulation

• Manipulation of Collections: Index-by tables provide powerful methods for data manipulation, including the ability to bulk collect data and return it in an easily manageable format. This can significantly streamline data processing tasks.

8. Enhanced Performance with Bulk Operations

• Bulk Processing: Using index-by tables in conjunction with bulk processing features (e.g., FORALL and BULK COLLECT) can greatly enhance performance by reducing context switches between PL/SQL and SQL engines, leading to more efficient data handling.

9. No Limitations on Size (Per Session)

• Session-Scoped: The size of index-by tables is limited only by the available memory of the session, allowing for handling large datasets without predefined constraints, provided that the system resources are adequate.

10. Easier Integration with SQL

• Seamless SQL Interaction: Index-by tables can easily interface with SQL queries, allowing you to pass data between PL/SQL and SQL, which can enhance application performance and reduce complexity in handling data across different layers of the application.

Disadvantages of PL/SQL Index-By Tables

While PL/SQL index-by tables have their own advantages, they also have disadvantages that the developers should know. Knowing about them will best be aided in decisions when using them in applications.

1. Limited Scope

• PL/SQL Only: Index-by tables are specific to PL/SQL and can’t be used directly within the SQL statement. This limitation prevents data in index-by tables from being accessed using SQL commands; therefore this leads to complications if it is applied in SQL-based operations.

2. Memory Consumption

• Memory Usage: Since index-by tables are stored in memory, the same table can consume considerably more memory if the dataset is large, which causes a decrease in performance and resource constraints in limited memory environments.

3. SQL Doesn’t Support Bulk Operations

• Bulk Collect Limitations: One of the major limitations of index-by tables is that they are not supported with direct bulk SQL operations like other types of collections, such as nested tables. In fact, this makes the performance worse on large datasets when using index-by tables as against nested tables .

4. No Intrinsic Procedures

• Limited Functionality: Index-by tables do not have built-in methods for data manipulation and retrieval except for primary indexing. This implies that developers have to do the code individually, especially in specific situations, which may raise the complexity of the code.

5. The possibility of handling sparse data issues

• Sparse Data Management: While index-by tables can operate with sparse data, this has the capacity for confusion and complexity in the retrieval of data because managing gaps in indexes might be getting more confusing and complex. Developers need to make sure proper handling of noncontinuous keys which often complicate logic.

6. Complexity in Index Management

• Manual Index Handling: Developers have to do index handling manually. Manual handling will result in errors if not done properly. For instance, when an index is accidentally deleted or modified, there might be a possibility of unexpected behavior in the application.

7. Overhead of Performance in Large Collections

• Overhead in Access Time: Though index-by tables access individual elements very quickly, their performance degrades with very large collections because memory overhead increases and more extensive index ranges take more time to access.

8. Unsuitable for Multi-User Applications

• Session-Specific: As an index-by table is session-specific, they do not adapt well to multi-user applications where data needs to be shared across sessions, so their usability remains restricted in some applications.

9. Incompatibility with Certain PL/SQL Features

• Restrictions on Features of PL/SQL: By way of using index-by tables, features and functionalities of PL/SQL in general can only be partially used-for example, to be used as parameters in stored procedures-as such a restriction could be unworkable in many highly complex application scenarios.

10. Logical Error Possibility

• Index Mismanagement: The more customised the logic of dealing with index management, the greater is the possibility for logical errors that seem to emerge as developers overlook the state of indexes or fail to handle particular conditions that might have remained unseen and developed into bugs and are hard to pin down.