What Are a Table's Indexes? JDBC

According to Wikipedia, “an index is a feature in a database that allows quick access to the rows in a table. The index is created using one or more columns of the table.” You can use the DatabaseMetaData interface’s getIndexInfo() method to find the indexes for a specified table. This section illustrates how you’d use getIndexInfo(); we define a couple of indexes and then run our solution against a sample table called ACCOUNT. The signature of getIndexInfo() is

public ResultSet getIndexInfo(String catalog, String schema, String table, boolean unique, boolean approximate) throws SQLException;

The method’s parameters are

  • catalog: A catalog name. It must match the catalog name as it is stored in this database."" retrieves those without a catalog; null means that the catalog name should not be used to narrow the search.
  • schema: A schema name. It must match the schema name as it is stored in this database. "" retrieves those without a schema; null means that the schema name should not be used to narrow the search.
  • table: A table name; must match the table name as it is stored in this database.
  • unique: When true, returns only indexes for unique values; when false, returns indexes regardless of whether or not values are unique.
  • approximate: When true, the result is allowed to reflect approximate or out-of-date values; when false, the results are requested to be accurate (all table statistics are exact). Some drivers (such as the MiniSoft JDBC Driver) ignore this parameter and ensure that all table statistics are exact.

This method retrieves a ResultSet object containing information about the indexes or keys for the table. The returned ResultSet is ordered by NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION.

Result Columns for Invoking getIndexInfo()

Result Columns for Invoking getIndexInfo()

This method returns ResultSet, in which each row is an index column description. If a database access error occurs, it throws SQLException. As you can see from the returned ResultSet, it contains a lot of information. The best way to represent that information is XML, which may be used by any type of client.

The Solution: getIndexInformation()

The index information can be useful in sending proper SQL queries to the database. During runtime, for better response from the database, in formulating SQL’s SELECT statement you can use the index columns in the WHERE clauses (otherwise, the database tables will be scanned sequentially). In passing actual parameters to the DatabaseMetaData.getIndexInfo() method, try to minimize passing null and empty values (passing null values might slow down your metadata retrieval).

Oracle Database Setup

For testing, let’s create an ACCOUNT table and a couple of indexes:

Next, let’s create some indexes on the ACCOUNT table using the Oracle database. Since id is a primary key, Oracle will automatically create a unique index for this column; we define three additional indexes.

Client 1:Oracle

Output 1:Oracle

Note that when the index type is tableIndexStatistic, the index name will be null. When the schema does not assign a proper index name(for example, for primary keys), the database server will assign a generated name.

Client 2:Oracle

Output 2:Oracle

MySQL Database Setup

For testing, let’s create an ACCOUNT table and a couple of indexes:

Next, let’s create some indexes on the ACCOUNT table using the MySQL database. Because id is a primary key, MySQL will automatically create a unique index for this column; we define three additional indexes.

Output 1:MySQL

Client 2:MySQL

Output 2:MySQL


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

JDBC Topics