Indexes and Functions - 01

The user said me that the table has a index in the date column that he used in his query SQL but is too slow.

That is true, this table (TB_EVENTS) has an index in date type column (EVNT_PK). 

SQL> SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE FROM DBA_TAB_COLUMNS WHERE TABLE_NAME='TB_EVENTS' AND COLUMN_NAME='EVENT_DATE';

TABLE_NAME                     COLUMN_NAME                      DATA_TYPE

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

TB_EVENTS                      EVENT_DATE                       DATE

SQL> SELECT COL.INDEX_NAME,   COL.COLUMN_NAME   FROM SYS.DBA_IND_COLUMNS COL   WHERE OWNER='PSP' AND INDEX_NAME='EVNT_PK';

INDEX_NAME                     COLUMN_NAME

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

EVNT_PK                        EVENT_DATE

Ok, He use EVENT_DATE column in the WHERE but also use TO_CHAR function in order to select all rows from yesterday.  When we use functions in column of an index, in certain cases we kill the index and then the database does a full scan.

SQL> set autot trace exp stat

SQL>  select Event_Status

from PSP.TB_Events

WHERE TO_CHAR (Event_Date,'DDMMYYYY') = TO_CHAR(SYSDATE-1,'DDMMYYYY');

no rows selected

Elapsed: 00:04:18.75

Execution Plan

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

   0      SELECT STATEMENT Optimizer=RULE

   1    0   TABLE ACCESS (FULL) OF 'TB_EVENTS'  <-- Full scan, didn’t use the index

Statistics

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

          0  recursive calls

          0  db block gets

     651601  consistent gets

     586208  physical reads   <-- performance more physical reads.

        356  redo size

        308  bytes sent via SQL*Net to client

        461  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          0  rows processed

We rewrite the WHERE condition so that don’t use TO_CHAR function and continue to obtain the same rows. And then... Database can use the index.

SQL> SELECT EVENT_STATUS

FROM PSP.TB_EVENTS

WHERE EVENT_DATE  >= TRUNC (SYSDATE-1) AND EVENT_DATE  < TRUNC (SYSDATE);

no rows selected

Elapsed: 00:02:08.14

Execution Plan

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

   0      SELECT STATEMENT Optimizer=RULE

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TB_EVENTS'

   2    1     INDEX (RANGE SCAN) OF 'EVNT_PK' (UNIQUE) <-- Used the index.

Statistics

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

          0  recursive calls

          0  db block gets

     732782  consistent gets

      20769  physical reads   <-- performance less physical reads.

          0  redo size

        308  bytes sent via SQL*Net to client

        461  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          0  rows processed

Comparison table

	TO_CHAR (Event_Date,'DDMMYYYY') = TO_CHAR(SYSDATE-1,'DDMMYYYY');	EVENT_DATE  >= TRUNC (SYSDATE-1) AND EVENT_DATE  < TRUNC (SYSDATE)
Optimizer	TABLE ACCESS (FULL)	INDEX (RANGE SCAN)
Elapsed	00:04:18.75	00:02:08.14
physical reads	586208	20769

Eini Trujillo