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.

Unique index candidates

Jonathan Lewis is blogging about indexes that could benefit from rebuild and Richard Foote is pointing out that preferably use unique indexes if possible. Putting these together. Indexes, that could be unique and are not, should be rebuild. Here is a way to find candidates.

SELECT DISTINCT table_name, index_name AS unique_index_candidate
              , constraint_name AS based_on_constraint
              , status AS constraint_status
           FROM (SELECT con.table_name, con.constraint_name, con.m
                      , ind.index_name, con.status
                      , COUNT (con.column_name) 
                         OVER (PARTITION BY con.table_name
                                          , con.constraint_name
                                          , ind.index_name) n
                   FROM (SELECT c.table_name, c.constraint_name
                              , o.column_name
                              , c.status
                              , MAX (o.POSITION) 
                                 OVER (PARTITION BY c.owner
                                                  , c.table_name
                                                  , c.constraint_name) m
                           FROM user_cons_columns o 
                             INNER JOIN user_constraints c
                                ON o.constraint_name = c.constraint_name
                          WHERE c.constraint_type IN ('P', 'U')
                            AND c.DEFERRABLE = 'NOT DEFERRABLE') con
                        INNER JOIN
                        (SELECT i.table_name, i.index_name, n.column_name
                           FROM user_ind_columns n INNER JOIN user_indexes i
                                ON n.index_name = i.index_name
                          WHERE i.uniqueness = 'NONUNIQUE') ind
                        ON con.table_name = ind.table_name
                      AND con.column_name = ind.column_name
                        )
          WHERE n = m
       ORDER BY table_name, index_name
;

Idiocy

People are idiots, some more than others. At least people are idiots in different aspects of life and issues. A person is an idiot differently each day. At least I feel like that. Other days it is quite obvious to see things exists and other days you cannot figure out a simplest case. Well maybe it is not idiotism to be clueless about a thing that one has never figured out before. Most often it is really nice to be working with persons that are less idiots than me. At least in a issue that one tried to figure out and the other has a ready answer to.

I have set up a 10g data quard manually and tried to register the databases to Grid Control. 

Stand by instance seemed to be quite hard to get registered to the agent. I did not figure out that the standby node is in mount state and dbsnmp monitoring user is trying to reach the database as a normal user. Just change the monitoring user to connect to the database as sysdba and voila grid control is showing roles of the instances correctly. Creating a stand by instance with Grid Control handles that ok. 

This week I have had a pleasure to work with a person who could figure out thigs while problems arise and a oraganization that has scheduled HA evironment testing well before going to production. After setting up Data Guard environment we had plenty of time to try out different kinds of switchower and failover situations and there is still time in the project plan to try out even more complex situations. This possibility and capability should exist in every project having a HA environment. 

unindex

###########

# updated link to Tom Kyte blog.

# Comment about Toms script in this post is not valid anymore.

# The script itself is valid.

# More about it and a 11.2 version available. And a cross schema version of the unindex query.

# Postgresql version.

###########

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

;