Apache Derby - Índices de Derby

Un índice en una tabla no es más que un puntero a sus datos. Se utilizan para acelerar la recuperación de datos de una tabla.

Si usamos índices, las instrucciones INSERT y UPDATE se ejecutan en una fase más lenta. Mientras que SELECT y WHERE se ejecutan en menos tiempo.

Crear un índice

La instrucción CREATE INDEX se utiliza para crear un nuevo índice en una tabla en la base de datos Derby.

Sintaxis

A continuación se muestra la sintaxis de la instrucción CREATE INDEX:

CTREATE INDEX index_name on table_name (column_name);

Ejemplo

Supongamos que hemos creado una tabla llamada Empleados en Apache Derby como se muestra a continuación.

CREATE TABLE Emp ( Id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
   Name VARCHAR(255),
   Salary INT NOT NULL,
   Location VARCHAR(255),
   Phone_Number BIGINT
);

La siguiente instrucción SQL crea un índice en la columna denominada Salario en la tabla Empleados.

ij> CREATE INDEX example_index on Emp (Salary);
0 rows inserted/updated/deleted

Creando un índice ÚNICO

En Apache Derby, los índices UNIQUE se utilizan para la integración de datos. Una vez que crea un índice ÚNICO en una columna de una tabla, no permite valores duplicados.

Sintaxis

A continuación se muestra la sintaxis de la creación de un índice único.

CREATE UNIQUE INDEX index_name on table_name (column_name);

Ejemplo

El siguiente ejemplo crea un índice ÚNICO en la columna Id de la tabla Empleado.

ij> CREATE UNIQUE INDEX unique_index on Emp (Phone_Number);
0 rows inserted/updated/deleted

Una vez que haya creado un índice único en una columna, no puede ingresar los mismos valores para esa columna en otra fila. En resumen, una columna que tiene un índice UNIQE no permitirá valores duplicados.

Inserte una fila en la tabla Emp como se muestra a continuación

ij> INSERT INTO Emp(Name, Salary, Location, Phone_Number) VALUES ('Amit',
45000, 'Hyderabad', 9848022338);
1 row inserted/updated/deleted

Dado que hemos creado un índice único en la columna Phone_No, si escribe el mismo valor que en el registro anterior, muestra un error.

ij> INSERT INTO Emp(Name, Salary, Location, Phone_Number) VALUES ('Sumit',
35000, 'Chennai', 9848022338);
ERROR 23505: The statement was aborted because it would have caused a duplicate
key value in a unique or primary key constraint or unique index identified by
'UNIQUE_INDEX' defined on 'EMP'.

Crear un índice COMPUESTO

Puede crear un solo índice en dos filas y se denomina índice compuesto.

Sintaxis

A continuación se muestra la sintaxis del índice compuesto.

CREATE INDEX index_name on table_name (column_name1, column_name2);

Ejemplo

El siguiente índice crea un índice compuesto en las columnas Nombre y Ubicación.

ij> CREATE INDEX composite_index on Emp (Name, Location);
0 rows inserted/updated/deleted

Visualización de índices

La consulta SHOW INDEXES muestra la lista de índices en una tabla.

Sintaxis

A continuación se muestra la sintaxis de la instrucción SHOW INDEXES:

SHOW INDEXES FROM table_name;

Ejemplo

En el siguiente ejemplo, visualizo los índices en la tabla Empleados.

ij> SHOW INDEXES FROM Emp;

Esto produce el siguiente resultado.

ij> SHOW INDEXES FROM Emp;
TABLE_NAME |COLUMN_NAME |NON_U&|TYPE|ASC&|CARDINA&|PAGES
----------------------------------------------------------------------------
EMP |PHONE_NUMBER |false |3 |A |NULL |NULL
EMP |NAME |true |3 |A |NULL |NULL
EMP |LOCATION |true |3 |A |NULL |NULL
EMP |SALARY |true |3 |A |NULL |NULL
4 rows selected

Eliminando índices

La declaración Drop Index elimina / suelta el índice dado en una columna.

Sintaxis

A continuación se muestra la sintaxis de la instrucción DROP INDEX.

DROP INDEX index_name;

Ejemplo

El siguiente ejemplo arroja un índice llamado composite_index y unique_index creado anteriormente.

ij> DROP INDEX composite_index;
0 rows inserted/updated/deleted
ij>Drop INDEX unique_index;
0 rows inserted/updated/deleted

Ahora, si verifica la lista de índices, puede ver el índice en una columna ya que hemos eliminado el resto.

ij> SHOW INDEXES FROM Emp;
TABLE_NAME |COLUMN_NAME |NON_U&|TYPE|ASC&|CARDINA&|PAGES
----------------------------------------------------------------------------
EMP |SALARY |true |3 |A |NULL |NULL
1 row selected

Manejo de índices usando el programa JDBC

El siguiente programa JDBC demuestra cómo crear índices de caída en una columna de una tabla.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class IndexesExample {
   public static void main(String args[]) throws Exception {

      //Registering the driver
      Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
      //Getting the Connection object
      String URL = "jdbc:derby:MYDATABASE;create=true";
      Connection conn = DriverManager.getConnection(URL);

      //Creating the Statement object
      Statement stmt = conn.createStatement();

      //Creating the Emp table
      String createQuery = "CREATE TABLE Emp( "
         + "Id INT NOT NULL GENERATED ALWAYS AS IDENTITY, "
         + "Name VARCHAR(255), "
         + "Salary INT NOT NULL, "
         + "Location VARCHAR(255), "
         + "Phone_Number BIGINT )";
      stmt.execute(createQuery);
      System.out.println("Table created");
      System.out.println(" ");

      //Creating an Index on the column Salary
      stmt.execute("CREATE INDEX example_index on Emp (Salary)");
      System.out.println("Index example_index inserted");
      System.out.println(" ");

      //Creating an Unique index on the column Phone_Number
      stmt.execute("CREATE UNIQUE INDEX unique_index on Emp (Phone_Number)");
      System.out.println("Index unique_index inserted");
      System.out.println(" ");

      //Creating a Composite Index on the columns Name and Location
      stmt.execute("CREATE INDEX composite_index on Emp (Name, Location)");
      System.out.println("Index composite_index inserted");
      System.out.println(" ");

      //listing all the indexes
      System.out.println("Listing all the columns with indexes");

      //Dropping indexes
      System.out.println("Dropping indexes unique_index and, composite_index ");
      stmt.execute("Drop INDEX unique_index");
      stmt.execute("DROP INDEX composite_index");
   }
}

Salida

Al ejecutar, esto genera el siguiente resultado

Table created
Index example_index inserted

Index unique_index inserted

Index composite_index inserted

Listing all the columns with indexes
Dropping indexes unique_index and, composite_index