2009-11-03

unindex 11.2

Tom Kyte has updated his unindex documentation. It is good to review your work every now and then. I guessed he had a valid version of unindex somewhere. Just did not find it at the moment.

Jonathan Lewis talking about the foreign key indexing issue in OTN. It might be that not all foreign keys need an index in your schema.

11.2 new function listagg is useful also in unindex. Here is a listagg version of unindex for the new Oracle version.

select case when i.index_name is not null
            then 'OK'
            else '****'
       end ok
     , c.table_name
     , c.constraint_name
     , c.cols
     , i.index_name
from (
  select a.table_name
       , a.constraint_name
       , listagg(b.column_name, ' ' ) 
          within group (order by column_name) cols
      from user_constraints a, user_cons_columns b
     where a.constraint_name = b.constraint_name
       and a.constraint_type = 'R'
  group by a.table_name, a.constraint_name
 ) c
 left outer join
 (
  select table_name
       , index_name
       , cr
       , listagg(column_name, ' ' ) 
          within group (order by column_name) cols
    from (
        select table_name
             , index_name
             , column_position
             , column_name
             , connect_by_root(column_name) cr
          from user_ind_columns
       connect by prior column_position-1 = column_position
              and prior index_name = index_name
         )
    group by table_name, index_name, cr
) i on c.cols = i.cols and c.table_name = i.table_name
;



Even thou unindex query is not the kind of query that is run several times a day, I measured execution times from different versions. The test schema contains 1700 foreign keys. Performance comparison
"col_cnt > ALL" 13 sec
"connect by"     3 sec 
"listagg"        1 sec


Foreign keys may point also to and from another schema. Here you can find a version using ALL_CONSTRAINTS and ALL_CONS_COLUMNS views.


2 comments:

  1. Your SQL doesn’t report concatenated keys and indexes created in wrong order of columns.
    ExaMPLE:
    Parent_table, PK= col1, col2
    Child_table, FK=col1,col2 index= col2,col1

    ReplyDelete
  2. @Darko

    As it should not report those. index=col2,col1 satisfies the need of foreign key indexing. Did you read unindex? You can get rid of TM Enq Wait events even if the order of the foreign key columns is not the same than the index. Only the group of columns in beginning of the index should match the group of columns in foreign key .

    ReplyDelete

About Me

My Photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.