001: /*
002: * OracleIndexReader.java
003: *
004: * This file is part of SQL Workbench/J, http://www.sql-workbench.net
005: *
006: * Copyright 2002-2008, Thomas Kellerer
007: * No part of this code maybe reused without the permission of the author
008: *
009: * To contact the author please send an email to: support@sql-workbench.net
010: *
011: */
012: package workbench.db.oracle;
013:
014: import java.sql.PreparedStatement;
015: import java.sql.ResultSet;
016: import java.sql.SQLException;
017: import java.sql.Statement;
018: import java.util.Collection;
019: import java.util.HashMap;
020: import java.util.Map;
021: import workbench.db.DbMetadata;
022: import workbench.db.DbSettings;
023: import workbench.db.IndexDefinition;
024: import workbench.db.JdbcIndexReader;
025: import workbench.db.TableIdentifier;
026: import workbench.log.LogMgr;
027: import workbench.util.SqlUtil;
028:
029: /**
030: *
031: * @author support@sql-workbench.net
032: */
033: public class OracleIndexReader extends JdbcIndexReader {
034: private PreparedStatement indexStatement;
035:
036: /** Creates a new instance of OracleMetaData */
037: public OracleIndexReader(DbMetadata meta) {
038: super (meta);
039: }
040:
041: public void indexInfoProcessed() {
042: try {
043: this .indexStatement.close();
044: this .indexStatement = null;
045: } catch (Throwable th) {
046: }
047: }
048:
049: /**
050: * Replacement for the DatabaseMetaData.getIndexInfo() method.
051: * Oracle's JDBC driver does an ANALYZE INDEX each time an indexInfo is
052: * requested which slows down the retrieval of index information.
053: * (and is not necessary at all for the Workbench, as we don't use the
054: * cardinality field anyway)
055: */
056: public ResultSet getIndexInfo(TableIdentifier table, boolean unique)
057: throws SQLException {
058: if (this .indexStatement != null) {
059: LogMgr.logWarning("OracleIndexReader.getIndexInfo()",
060: "getIndexInfo() called with pending results!");
061: indexInfoProcessed();
062: }
063:
064: TableIdentifier tbl = table.createCopy();
065: tbl.adjustCase(this .metaData.getWbConnection());
066:
067: StringBuilder sql = new StringBuilder(200);
068: sql
069: .append("SELECT null as table_cat, "
070: + " i.owner as table_schem, "
071: + " i.table_name, "
072: + " decode (i.uniqueness, 'UNIQUE', 0, 1) as non_unique, "
073: + " null as index_qualifier, "
074: + " i.index_name, "
075: + " i.index_type as type, "
076: + " c.column_position as ordinal_position, "
077: + " c.column_name, "
078: + " null as asc_or_desc, "
079: + " i.distinct_keys as cardinality, "
080: + " i.leaf_blocks as pages, "
081: + " null as filter_condition, "
082: + " i.index_type "
083: + "FROM all_indexes i, all_ind_columns c "
084: + "WHERE i.table_name = ? \n");
085:
086: if (tbl.getSchema() != null) {
087: sql.append(" AND i.owner = ? \n");
088: }
089: if (unique) {
090: sql.append(" and i.uniqueness = 'UNIQUE'\n");
091: }
092: sql.append(" and i.index_name = c.index_name "
093: + " and i.table_owner = c.table_owner "
094: + " and i.table_name = c.table_name "
095: + " and i.owner = c.index_owner ");
096: sql
097: .append("ORDER BY non_unique, type, index_name, ordinal_position ");
098:
099: this .indexStatement = this .metaData.getWbConnection()
100: .getSqlConnection().prepareStatement(sql.toString());
101: this .indexStatement.setString(1, table.getTableName());
102: if (table.getSchema() != null)
103: this .indexStatement.setString(2, table.getSchema());
104: ResultSet rs = this .indexStatement.executeQuery();
105: return rs;
106: }
107:
108: /**
109: * Read the definition for function based indexes into the Map provided.
110: * The map should contain the names of the indexes as keys, and an List
111: * as elements. Each Element of the list is one part (=function call to a column)
112: * of the index definition.
113: */
114: public void processIndexList(TableIdentifier tbl,
115: Collection<IndexDefinition> indexDefs) {
116: if (indexDefs.size() == 0)
117: return;
118:
119: Map<String, String> result = new HashMap<String, String>();
120:
121: String base = "SELECT i.index_name, e.column_expression, e.column_position \n"
122: + "FROM all_indexes i, all_ind_expressions e \n"
123: + " WHERE i.index_name = e.index_name \n"
124: + " and i.owner = e.index_owner \n"
125: + " and i.table_name = e.table_name \n"
126: + " and e.index_owner = i.owner \n "
127: + " and i.index_type like 'FUNCTION-BASED%' ";
128: StringBuilder sql = new StringBuilder(300);
129: sql.append(base);
130: String schema = tbl.getSchema();
131:
132: if (schema != null && schema.length() > 0) {
133: sql.append(" AND i.owner = '" + schema + "' ");
134: }
135: boolean found = false;
136:
137: sql.append(" AND i.index_name IN (");
138: for (IndexDefinition def : indexDefs) {
139: String type = def.getIndexType();
140: if (type == null)
141: continue;
142: if (type.startsWith("FUNCTION-BASED")) {
143: if (found)
144: sql.append(',');
145: found = true;
146: sql.append('\'');
147: sql.append(def.getName());
148: sql.append('\'');
149: }
150: }
151: sql.append(") ");
152:
153: if (!found)
154: return;
155:
156: ResultSet rs = null;
157: Statement stmt = null;
158: try {
159: stmt = this .metaData.getWbConnection()
160: .createStatementForQuery();
161: rs = stmt.executeQuery(sql.toString());
162: while (rs.next()) {
163: String name = rs.getString(1);
164: String exp = rs.getString(2);
165: result.put(name, exp);
166: }
167:
168: for (IndexDefinition def : indexDefs) {
169: String exp = result.get(def.getName());
170: if (exp != null) {
171: def.setExpression(exp);
172: }
173: String type = def.getIndexType();
174: if (type.startsWith("FUNCTION-BASED")) {
175: def.setIndexType(type
176: .replace("FUNCTION-BASED ", ""));
177: } else if (type.indexOf(' ') > -1
178: || type.indexOf('-') > -1) {
179: def.setIndexType(DbSettings.IDX_TYPE_NORMAL);
180: }
181:
182: }
183: } catch (Exception e) {
184: LogMgr.logWarning("OracleMetaData.processIndexList()",
185: "Error reading function-based index definition", e);
186: LogMgr.logDebug("OracleMetaData.processIndexList()",
187: "Using sql: " + sql.toString());
188: } finally {
189: SqlUtil.closeAll(rs, stmt);
190: }
191: return;
192: }
193:
194: }
|