Carl's Oracle

I was inspired by Tom Kytes Blog Entries about NULLs in indexes. Only if all Columns of an index are set to NULL they are not part of the index;

Very often processing rows with a state information is done via workflow. For example an Activity once opened will be finished or if not will be set to overdue. A Table holding Activities will always have a very small count of open activities.

The SQL which should process open aged out activities (1) to set the overdue status (2);

SQL> SELECT *

  2   FROM Activity Act

  3  WHERE Activitystate = 1

  4    AND Activitydate < SYSDATE;

The distribution of the Activity states;

SQL> SELECT Act.Activitystate,

  2         COUNT(*)

  3    FROM Activity Act

  4   GROUP BY Act.Activitystate;

 

ACTIVITYSTATE   COUNT(*)

------------- ----------

            1        118

            2         27

            3       9019

            4       3077

            5         15

 

SQL>

A normal index ...

create index ACTIVITY_IX01 on ACTIVITY (ACTIVITYSTATE, ACTIVITYDATE)

  tablespace OASISIXL

;

... had following stats

  index_name                       : ACTIVITY_IX01

  index_type                       : NORMAL

  uniqueness                       : NONUNIQUE

  blevel                           : 1

  leaf_blocks                      : 38

  distinct_keys                    : 12144

  avg_leaf_blocks_per_key          : 1

  avg_data_blocks_per_key          : 1

  clustering_factor                : 12101

  status                           : VALID

  num_rows                         : 12256

  sample_size                      : 12256

  last_analyzed                    : 30.01.06

  duration                         :

  column_name 1                    : ACTIVITYSTATE

  column_name 2                    : ACTIVITYDATE

Every Row was put in the index, only the rows with Actvitystate = 1 would be interesting for processing. So 95% of the index data are not interesting for the Query. Would it be possible to have a small, never growing  index only with the value Activitystates = 1?

And this worked :

create index ACTIVITY_FX01 on ACTIVITY (DECODE(ACTIVITYSTATE,1,1,NULL), DECODE(ACTIVITYSTATE,1,ACTIVITYDATE,NULL))

  tablespace OASISIXS

;

This index definition allows only Activity state values = 1 all other values are not stored in the index because the Activitydate/ Activitystate is set to null if Activitystate <> 1; Let see the stats.

  index_name                       : ACTIVITY_FX01

  index_type                       : FUNCTION-BASED NORMAL

  uniqueness                       : NONUNIQUE

  blevel                           : 0

  leaf_blocks                      : 1

  distinct_keys                    : 60

  avg_leaf_blocks_per_key          : 1

  avg_data_blocks_per_key          : 1

  clustering_factor                : 85

  status                           : VALID

  num_rows                         : 118

  sample_size                      : 118

  last_analyzed                    : 30.01.06

  duration                         :

  column_name 1                    : SYS_NC00020$

  column_name 2                    : SYS_NC00021$

And the autotrace output :

SQL> SELECT *

  2    FROM Activity Act

  3   WHERE Decode(Activitystate,

  4                1,

  5                1,

  6                NULL) = 1

  7     AND Decode(Activitystate,

  8                1,

  9                Activitydate,

 10                NULL) < SYSDATE;

 

11 Zeilen ausgewõhlt.

 

 

Ausf³hrungsplan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=6 Bytes=954)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'ACTIVITY' (Cost=3 Card=6 Bytes=954)

   2    1     INDEX (RANGE SCAN) OF 'ACTIVITY_FX01' (NON-UNIQUE) (Cost=2 Card=1)

 

 

Statistiken

----------------------------------------------------------

          0  recursive calls

          0  db block gets

         10  consistent gets

          0  physical reads

          0  redo size

       3093  bytes sent via SQL*Net to client

        499  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         11  rows processed

 

The classical way with ‘full’ index over all Activitystates  and histogram did not work with Oracle Server 9.2.0.4 because the open range scan of the Activitiydate (< SYSDATE ). Only a hint helped :

 

SQL> SELECT /*+ INDEX ( Act ACTIVITY_IX01 ) */ *

  2   FROM Activity Act

  3  WHERE Activitystate = 1

  4    AND Activitydate < SYSDATE;

 

11 Zeilen ausgewõhlt.

 

Ausf³hrungsplan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1364 Card=1370 Bytes=220570)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'ACTIVITY' (Cost=1364 Card=1370 Bytes=220570)

   2    1     INDEX (RANGE SCAN) OF 'ACTIVITY_IX01' (NON-UNIQUE) (Cost=7 Card=1370)

 

 

Statistiken

----------------------------------------------------------

          0  recursive calls

          0  db block gets

         11  consistent gets

          0  physical reads

          0  redo size

       3093  bytes sent via SQL*Net to client

        499  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         11  rows processed

 

SQL>

Karl