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)
catalog– a catalog name; must match the catalog name as it is stored in this database; “” retrieves those without a catalog;
nullmeans 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;
nullmeans 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 184.108.40.206 & 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 220.127.116.11 JDBC driver.