Usefull indexes

Do we actually need such indexes?

select owner,table_name,index_name 
  from all_indexes 
 where distinct_keys < 2
   and num_rows > 100;

locator index size

Just dealing a situation with 10.2.0.4 locator index. Size of a index had grown to 1.3G. After rebuilding it had size less than 50M.
Maybe there is going on something similar than Richard Foote is talking about bitmap indexes behaving in 9.2 and 10.2.
Trying a similar test with a locator index.

CREATE TABLE foo AS 
 SELECT mod(ROWNUM,1000)+1 ID
     , SYSDATE+mod(ROWNUM,1000)+1 fro
     , SYSDATE+mod(ROWNUM,1000)+2 til
     , 'FOO' NAME 
  FROM (SELECT NULL A FROM
(SELECT NULL A FROM dual CONNECT BY LEVEL <= 1000),
(SELECT NULL A FROM dual CONNECT BY LEVEL <= 1000));

CREATE OR REPLACE FUNCTION TF(FRO DATE, TIL DATE) RETURN SDO_GEOMETRY deterministic as 
BEGIN
RETURN MDSYS.SDO_GEOMETRY(2002,NULL, NULL, SDO_ELEM_INFO_ARRAY (1,2,1),
    SDO_ORDINATE_ARRAY(to_number(to_char(FRO,'j')),0,to_number(to_char(TIL,'j')),0));
END;
/

select min(to_number(to_char(FRO,'j'))),max(to_number(to_char(TIL,'j'))) from foo;

2455373 2456373

INSERT INTO USER_SDO_GEOM_METADATA(TABLE_NAME,COLUMN_NAME,DIMINFO)
  VALUES (
  'FOO',
  'RAFU.TF(FRO,TIL)',
  SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('X', 2455373, 2456373, 0.5),
    SDO_DIM_ELEMENT('Y', 0, 0, 0.5)
     )
  )
;

commit;

CREATE INDEX FOO_LOCATOR_IDX ON FOO(TF(FRO,TIL))
  INDEXTYPE IS mdsys.spatial_index
;

analyze index FOO_LOCATOR_IDX compute statistics;

SELECT index_name, blevel, leaf_blocks, num_rows FROM user_indexes WHERE index_name = 'FOO_LOCATOR_IDX';

"INDEX_NAME" "BLEVEL" "LEAF_BLOCKS" "NUM_ROWS"
"FOO_LOCATOR_IDX" "" "" ""

Nothing there. It is a domain index and storing its structures in a table.

select segment_name,bytes,blocks from user_segments where segment_name like 'MDRT_%';

MDRT_11976$ 92274688 11264

SELECT count(*) FROM MDRT_11976$;

33342

And now populating in a loop as Richard did with bitmap index.

CREATE TABLE bar AS 
 SELECT * FROM foo 
  WHERE 0=1
;

INSERT INTO USER_SDO_GEOM_METADATA(TABLE_NAME,COLUMN_NAME,DIMINFO)
  VALUES (
  'BAR',
  'RAFU.TF(FRO,TIL)',
  SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('X', 2455373, 2456373, 0.5),
    SDO_DIM_ELEMENT('Y', 0, 0, 0.5)
     )
  )
;

commit;

CREATE INDEX BAR_LOCATOR_IDX ON BAR(TF(FRO,TIL))
  INDEXTYPE IS mdsys.spatial_index
;

begin
 FOR i IN 1..1000 loop
  FOR j IN 1..1000 loop
   INSERT INTO bar VALUES (j, SYSDATE+j, SYSDATE+j, 'FOO');
   COMMIT;
  END loop;
 end loop;
end;
/



select segment_name,bytes,blocks from user_segments where segment_name like 'MDRT_%';

MDRT_11976$  92274688      11264
MDRT_119DF$ 125829120      15360


SELECT count(*) FROM MDRT_11976$;

33342

select count(*) from MDRT_119DF$;

58084

Well that does not explain the 1.3G size. Similar result with 11.2.0.1, 11.1.0.7 and 10.2.0.4.

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
;

A week on the other side (part3 DB2)

Missing Oracle function based indexes. Got involved in a project having a DB2 database environment. There is a need for case insensitive predicate in a where clause. In Oracle it is possible to create function based index:

create table t(n number(8) primary key, last_name varchar(100));

insert into t (n,last_name)

select rownum, object_name

from all_objects;

select * from t where upper(last_name) = :ln;

Performs a full table scan to table t.

create index t_last_name_upper on t(upper(last_name));

select * from t where upper(last_name) = :ln;

And the index is used if the table is big enough.

In DB2 there is no such thing as a function based index. But in a article on IBM site there is described alternatives to do this. Generated columns and index extensions. Generated columns seems like a valid approach. Even though data is duplicated on a row. No need for triggers seems like a good thing and the original query do not need any modifications.

This was about the first time that I am using db2cc Control Center to do things in DB2 environment. Execution plans were found easily from Access Plan page. At least with a small table implementation and tests went ok. Allthough adding a column in a table was not so straight forward because the column was a GENERATED ALLWAYS AS column.

SET INTEGRITY FOR t OFF;

alter table t
add column last_name_u varchar(100)
GENERATED ALWAYS AS ( UPPER(last_name));

SET INTEGRITY FOR t IMMEDIATE CHECKED FORCE GENERATED;

create index t_lastname_upper_idx on t(last_name_u);


How much easier would that have been in Oracle 11g. Just add a virtual column...

alter table t add last_name_u as (upper(last_name));

create index t_last_name_upper on t(last_name_u);

select * from t where upper(last_name) = :ln;


Similarily no need to change the query. Index is used. And the column is virtual, no need for storage. The index extensions approach might be the way to avoid unneeded storing of the data in DB2.

Needed also reduce a size of column and got surpriced how often REORG TABLE is needed in DB2. Or did I miss something in the documentation.

A week on the other side (part1 PostgreSQL)

For a Oracle oriented person like me, it was nice to see that indexing in PostgreSql environment has the same kind of possibility to boost performance of a query. In a OLTP environment the first star was the most meaningfull this time. Query execution time deminisched from minutes to part of a second. Talking about stars I mean the stars described in a book Relational Database Index Design and the Optimizers writen by Tapio Lahdenmäki. Recommended reading, at least the chapter DERIVING THE IDEAL INDEX FOR A SELECT. There was three separately indexed foreign key columns in a half a million rows table. All those columns were predicates in a query. All that was needed was to create a single index having all those three columns in it.

Dropping an index in postgre was not an online operation. Need to figure out why.

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

;