Apache Derby - Índices Derby

Um índice em uma tabela nada mais é que um ponteiro para seus dados. Eles são usados para acelerar a recuperação de dados de uma tabela.

Se usarmos índices, as instruções INSERT e UPDATE são executadas em uma fase mais lenta. Enquanto SELECT e WHERE são executados em menos tempo.

Criação de um índice

A instrução CREATE INDEX é usada para criar um novo Índice em uma tabela no banco de dados Derby.

Sintaxe

A seguir está a sintaxe da instrução CREATE INDEX -

CTREATE INDEX index_name on table_name (column_name);

Exemplo

Suponha que tenhamos criado uma tabela chamada Funcionários no Apache Derby, conforme mostrado abaixo.

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

A seguinte instrução SQL cria um índice na coluna denominada Salário na tabela Funcionários.

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

Criando um índice UNIQUE

No Apache Derby, os índices UNIQUE são usados para integração de dados. Depois de criar um índice UNIQUE em uma coluna de uma tabela, ele não permite valores duplicados.

Sintaxe

A seguir está a sintaxe para criar um índice exclusivo.

CREATE UNIQUE INDEX index_name on table_name (column_name);

Exemplo

O exemplo a seguir cria um índice UNIQUE na coluna Id da tabela Employee.

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

Depois de criar um índice exclusivo em uma coluna, você não pode inserir os mesmos valores para essa coluna em outra linha. Resumindo, uma coluna que possui um índice UNIQE não permite valores duplicados.

Insira uma linha na tabela Emp como mostrado abaixo

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

Como criamos um índice único na coluna Phone_No, se você digitar o mesmo valor do registro anterior, ocorrerá um erro.

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'.

Criação de um índice COMPOSTO

Você pode criar um único índice em duas linhas e é chamado de índice composto.

Sintaxe

A seguir está a sintaxe do índice composto.

CREATE INDEX index_name on table_name (column_name1, column_name2);

Exemplo

O índice a seguir cria um índice composto nas colunas Nome e Local.

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

Exibindo os índices

A consulta SHOW INDEXES exibe a lista de índices em uma tabela.

Sintaxe

A seguir está a sintaxe da instrução SHOW INDEXES -

SHOW INDEXES FROM table_name;

Exemplo

A seguir o exemplo, i exibe os índices na tabela Funcionários.

ij> SHOW INDEXES FROM Emp;

Isso produz o seguinte 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

Queda de índices

A instrução Drop Index exclui / descarta o índice fornecido em uma coluna.

Sintaxe

A seguir está a sintaxe da instrução DROP INDEX.

DROP INDEX index_name;

Exemplo

O exemplo a seguir elimina os índices chamados composite_index e unique_index criados acima.

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

Agora, se você verificar a lista de índices, poderá ver o índice em uma coluna, pois excluímos o restante.

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

Manipulação de índices usando o programa JDBC

O programa JDBC a seguir demonstra como criar índices de eliminação em uma coluna de uma tabela.

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");
   }
}

Resultado

Ao executar, isso gera o seguinte 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