title: Hibernate Index Query Slow
body: <p>My question is similar to the one posed in this thread:
  <a href="https://stackoverflow.com/questions/2630613/how-to-avoid-this-very-heavy-query-that-slows-down-the-application">How to avoid this very heavy query that slows down the application?</a></p>
  
  <p>We checked for missing indexes on foreign keys and found some. Adding the missing indexes actually had the opposite effect in that it slowed the query even more. One important piece of information is that our customer has a single Oracle install with our schema replicated on it 21 times. Each schema has just shy of 1,000 tables in it. Are we asking too much of Oracle with such a large number of tables (and of course indexes)? I don't know what their hardware is but my question is whether this is a reasonable approach or would it be be better to break up the users to different SIDs?</p>
  
  <p>Below is the query that is being executed by Hibernate. The customer is telling us that this query is consuming about 45% of the processor when it is being executed (though I don't know for how long).</p>
  
  <p>Any suggestions are appreciated,
  Steve</p>
  
  <pre><code>SELECT NULL AS table_cat,
         owner AS table_schem,
         table_name,
         0 AS non_unique,
         NULL AS index_qualifier,
         NULL AS index_name,
         0 AS TYPE,
         0 AS ordinal_position,
         NULL AS column_name,
         NULL AS asc_or_desc,
         num_rows AS CARDINALITY,
         blocks AS pages,
         NULL AS filter_condition
    FROM all_tables
   WHERE table_name = 'BOOKING'
         AND owner = 'FORWARD_TN'
  UNION
  SELECT NULL AS table_cat,
         i.owner AS table_schem,
         i.table_name,
         DECODE (i.uniqueness, 'UNIQUE', 0, 1),
         NULL AS index_qualifier,
         i.index_name,
         1 AS TYPE,
         c.column_position AS ordinal_position,
         c.column_name,
         NULL AS asc_or_desc,
         i.distinct_keys AS CARDINALITY,
         i.leaf_blocks AS pages,
         NULL AS filter_condition
    FROM all_indexes i,
         all_ind_columns c
   WHERE     i.table_name = 'BOOKING'
         AND i.owner = 'FORWARD_TN'
         AND i.index_name = c.index_name
         AND i.table_owner = c.table_owner
         AND i.table_name = c.table_name
         AND i.owner = c.index_owner
  ORDER BY non_unique,
           TYPE,
           index_name,
           ordinal_position
  </code></pre>