Indexing Operation flow structure in ORACLE SQL

Indexing in Oracle SQL is a performance optimization technique used to speed up data retrieval from database tables. An index is a database object that stores the values of one or more columns in a separate structure, which allows Oracle to locate and access data more quickly without having to scan the entire table.

let's continue the explanation step by step regarding what happens when a new entry is inserted into a table with an indexed column.

1. Main Table Insertion:

When we execute the insert statement:

INSERT INTO employees 
(employee_id, first_name, last_name, department_id, salary)
VALUES (101, 'John', 'Doe', 10, 5000);

In the Main Table:

The database adds the new row (101, 'John', 'Doe', 10, 5000) directly to the employees table.
This involves placing the data into the appropriate storage location (page or block) within the table structure.

2. Index Table Update:

If there is an index on the last_name column, let's assume we created it as follows:

CREATE INDEX idx_employees_last_name ON employees(last_name);

In the Index Table:

The index needs to reflect the addition of the new last_name value, which in this case is 'Doe'.

Here’s what happens in the index:

Locate Position: The database determines where the new entry for 'Doe' should be inserted in the B-tree structure of the index. This involves traversing the tree structure to find the correct leaf node or position where the new entry should go.
Insert Entry: The new entry is then added to the index. The index entry typically contains:
1) The value of the indexed column (in this case, 'Doe').
2) A pointer or reference to the actual row in the main table (the
employee_id or the physical location in the table where the record
can be found).

Example of the Index Structure Update:

Let's visualize how the B-tree index looks before and after the insertion:

Before Insertion:

         [Johnson]
        /         \
    [Adams]       [Smith]

After Insertion of last_name = 'Doe':

         [Johnson]
        /         \
    [Adams]       [Doe]
                  /   \
             [Smith] [Taylor]

3. Summary of Actions:

In Main Table: A new row is added with the specified values.

In Index Table: The new last_name value is inserted into the index, with a reference back to the row in the main table.

4. Performance Considerations:

Overhead: Every time an insert occurs, both the main table and the index need to be updated, leading to additional processing time and overhead.

Locking: During this operation, the index might also require locks to ensure data integrity while being updated.

Conclusion:

In summary, when a new entry is inserted into a table with an indexed column, the database must simultaneously update both the main table and the corresponding index. This dual update is what allows the index to maintain its efficiency in speeding up lookups and queries, but it also adds complexity and overhead to data modification operations (inserts, updates, deletes).