2009-02-16

unindex



###########
# updated link to Tom Kyte blog.
# Comment about Toms script in this post is not valid anymore.
# The script itself is valid.
###########

Until now Tom Kytes unindex has been satisfying my needs on searching unindexed foreign key constraints. There came a day when there became need to improve the query. I came across "enq: TM - contention" wait events - cause there was no fk indexing. After a sneak overview on other indexing and queries on the table I figured that it would be nice to have indexing in other order than the constraint is defined.

--example

create table a(a1 number(8)
, a2 number(8)
, x varchar2(2)
, primary key (a1,a2));

create table b(b number(8) primary key
, a1 number(8)
, a2 number(8)
, foreign key (a1,a2) references a);

create index fk_idx on b(a2,a1);

Toms unindex is giving me false negative four stars.
In my opinion fk_idx is satisfying the unindex need. At least TM Enq Wait events dismished from the environment after creating the index with different order of columns than fk.

Here is an alternative approach to search for unindexed foreign keys implemented in a single sql clause. It is operational at least with Oracle 10.2.0.1 and 11.1.0.7. There exists a bug involved with usage of connect_by_root in versions 10.2.0.3, 10.2.0.4 and 11.1.0.6. For those versions you need to set the _optimizer_connect_by_cost_based parameter to false.

alter session set "_optimizer_connect_by_cost_based" = false;


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 table_name, constraint_name
, max(sys_connect_by_path(column_name, ' ' )) cols
from (
select a.table_name
, b.constraint_name
, column_name
, position
, row_number() over
(partition by b.constraint_name
order by column_name) rn
from user_constraints a, user_cons_columns b
where a.constraint_name = b.constraint_name
and a.constraint_type = 'R'
)
start with rn = 1
connect by prior rn = rn-1
and prior constraint_name = constraint_name
group by table_name, constraint_name
) c
left outer join
(
select table_name
, index_name
, cr
, max(sys_connect_by_path(column_name, ' ' )) cols
from (
select table_name
, index_name
, column_position
, column_name
, cr
, row_number() over (partition by index_name, cr
order by column_name) rn
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
)
)
start with rn = 1
connect by prior rn = rn-1
and prior index_name = index_name
and prior cr = cr
group by table_name, index_name, cr
) i on c.cols = i.cols and c.table_name = i.table_name
;

3 comments:

  1. Yup, replicated @10.2.0.3:

    ERROR at line 1:
    ORA-00600: internal error code, arguments: [qctcte1], [0], [], [], [], [], [],
    []

    cheers
    Mateo

    ReplyDelete
  2. Workaround;
    Set "_optimizer_connect_by_cost_based" = false;

    ReplyDelete
  3. alter session set "_optimizer_connect_by_cost_based" = false;

    And the same goes with all 10.2.0.3, 10.2.0.4 and 11.1.0.6 versions. I guess that 10.2.0.2 needs that too, because seen similar issues with that version.

    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.