DatabaseMetaData#getIndexInfo()

If you were an Oracle developer and you were asked to implement a small part of the JDBC driver functionality – the getIndexInfo() method of the java.sql.DatabaseMetaData interface – how’d you do that?

The API states following requirements:

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

Retrieves a description of the given table’s indices and statistics. They are ordered by NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION.

Each index column description has the following columns: (snipped)

Parameters:
catalog – a catalog name; 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; 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, return only indices for unique values; when false, return indices regardless of whether unique or not
approximate – when true, result is allowed to reflect approximate or out of data values; when false, results are requested to be accurate

The description is relatively clear – the method should return index(es) information. Interesting parameter is approximate. Based on it’s value, the data retrieved via ResultSet (simply put, a cursor) should contain statistical information of the index(es) – either approximate or accurate. Well, how a normal developer would implement such a requirement? For appoximate==true, return currently available index statistics; for appoximate==false, throw an SQLException saying something like “the feature is not implemented”. An exception would be absolutely appropriate in this case, since accurately calculated statistics require tons of work for the database.

I don’t know what Oracle developers were smoking while developing this method; they implemented it as following (pseudo code):

if (approximate) then
    analyze table schema.table estimate statistics;
else
    analyze table schema.table compute statistics;
end if;
construct & execute a query to data dictionary and return ResultSet

You can imagine disastrous consequences of analyzing the base table in a production. This is actually how I found out this nasty behavior couple of years ago, when calls with approximate==true to that method provoked several queries execution plans changes and the users were unhappy due to application slow-down.

A little bit good news is getIndexInfo() implementation has been changed in 11.1.0.6 & 10.2.0.4 JDBC drivers – but not completely (pseudo code):

if (approximate == false) then
    analyze table schema.table compute statistics;
end if;
construct & execute a query to data dictionary and return ResultSet

- still it contains a call to the analyze table compute statistics, even in 11.2.0.1 JDBC driver.

About these ads

4 Comments

  1. Posted January 12, 2010 at 01:35 | Permalink | Reply

    Interesting. A good reason why the end user app should not connect as the schema owner and, preferably, should only have access to view and stored procedures.

    • Posted January 12, 2010 at 12:39 | Permalink | Reply

      Hello, Gary,

      I didn’t think of this as a security issue – now I think it is; thanks.

  2. UNV
    Posted October 14, 2010 at 22:27 | Permalink | Reply

    Ha-ha, this is my case. Really disastrous implementation. Though there were also good consequences – version of db client was updated almost on every server.

  3. Posted October 16, 2011 at 10:15 | Permalink | Reply

    hi……….. i want to initiate a discussion:
    if i have metadata of table or tables.and i want to show it to user but not all attributes . i want to filter out some attribute which are have less priority or less important then other attributes . so on what parameter i can filter out some attributes of table. i don’t no what data tables has. i know only metadata, such as datatype, size and constrains.

    please reply me on my mail id . if you have something idea about that.
    anikeshharan@yahoo.in

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 289 other followers