Create Index in Oracle

here's an example demonstrating the creation, dropping, and altering of an index in Oracle, along with altering a table to add a column:

1. Creation of an index:

-- Creating a table

CREATE TABLE employees (

    employee_id NUMBER,

    first_name VARCHAR2(50),

    last_name VARCHAR2(50),

    hire_date DATE

);

-- Creating an index on the employee_id column

CREATE INDEX idx_employee_id ON employees(employee_id);

2. Dropping an index:

-- Dropping the previously created index

DROP INDEX idx_employee_id;

3. Altering a table to add a column:

-- Altering the table to add a new column

ALTER TABLE employees ADD (email VARCHAR2(100));

4. Recreation of the index with a different name:

-- Recreating the index with a different name

CREATE INDEX idx_employee_id_new ON employees(employee_id);

5. Altering an existing index:

-- Altering the index to add a new column

ALTER INDEX idx_employee_id_new RENAME TO idx_employee_id_renamed;

In this example:

- We create a table employees with columns employee_id, first_name, last_name, and hire_date.

- Then, we create an index named idx_employee_id on the employee_id column.

- We drop the index idx_employee_id.

- We alter the employees table to add a new column email.

- We recreate the index with a new name idx_employee_id_new.

- Finally, we rename the recreated index to idx_employee_id_renamed.

These SQL statements demonstrate how you can create, drop, alter a table, and manage indexes in Oracle.