La Supresión Involuntaria de Índices

En estos dos ejemplos, verificados en Oracle 10, muestro la supresión involuntaria de un índice:

SQL> alter session set nls_language = 'SPANISH'

  2  /

Sesion modificada.

SQL> create table my_data

  2  as select object_id, object_name

  3  from dba_objects

  4  /

Tabla creada.

SQL>

Para empezar el primer ejemplo, he creado una tabla. Su primera columna se llama object_id y es de tipo number: 

SQL> desc my_data

Nombre                     ?Nulo?   Tipo

-------------------------- -------- ------------------ OBJECT_ID                           NUMBER OBJECT_NAME                         VARCHAR2(128)

SQL>

Entonces creo un índice en la columna object_id: 

SQL> create index my_index

  2  on my_data(object_id)

  3  /

Indice creado.

SQL>

Hago una consulta en la columna object_id que es de tipo number. Normalmente debería compararla con otro campo del mismo tipo, por ejemplo 51526  pero la comparo con ‘51526’. No obstante, el índice funciona todavía: 

SQL> set autotrace on

SQL> select count(*) from my_data

  2  where object_id = '51526'

  3  /

  COUNT(*)

----------

         1

Plan de Ejecucion

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

Plan hash value: 425162582

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

| Id  | Operation         | Name     |  Rows | Bytes | Cost (%CPU)| Time     |   ------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |          |     1 |    13 |     1   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE   |          |     1 |    13 |            |          |

|*  2 |   INDEX RANGE SCAN| MY_INDEX |     1 |    13 |     1   (0)| 00:00:01 |

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

Predicate Information (identified by operation id): ---------------------------------------------------

   2 - access("OBJECT_ID"=51526)

 Note

 -----

    - dynamic sampling used for this statement

Estadisticas

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

         32  recursive calls

          0  db block gets

         79  consistent gets

          1  physical reads

          0  redo size

        429  bytes sent via SQL*Net to client

        385  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL> set autotrace off

SQL> drop table my_data

  2  /

Tabla borrada.

SQL>

El segundo ejemplo es un poco diferente: 

SQL> create table my_data

  2  as select object_id, object_name

  3  from dba_objects where 1 = 2

  4  /

Tabla creada.

SQL> alter table my_data modify

  2  (object_id varchar2(10))

  3  /

Tabla modificada.

SQL>

Esta vez, la columna object_id es de tipo varchar2(10): 

SQL> desc my_data

Nombre                     ?Nulo?   Tipo

-------------------------- -------- ------------------ OBJECT_ID                           VARCHAR2(10) OBJECT_NAME                         VARCHAR2(128)

SQL>

Pero la tabla contiene los mismos datos y el mismo índice: 

SQL> insert into my_data

  2  select object_id, object_name

  3  from dba_objects

  4  /

49752 filas creadas.

SQL> create index my_index

  2  on my_data(object_id)

  3  /

Indice creado.

SQL>

Hago una consulta en la columna object_id que es de tipo varchar2(10). Normalmente debería compararla con otro campo del mismo tipo, por ejemplo ‘51526’ pero la comparo con 51526. Esta vez el índice no funciona: 

SQL> set autotrace on
SQL> select count(*) from my_data
  2  where object_id = 51526
  3  /

  COUNT(*)
----------
         1

Plan de Ejecucion
----------------------------------------------------------
Plan hash value: 2558507361

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |     7 |    57   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE    |         |     1 |     7 |            |          |
|*  2 |   TABLE ACCESS FULL| MY_DATA |     3 |    21 |    57   (4)| 00:00:01 |
------------------------------------------------------------------------------ 

Predicate Information (identified by operation id): ---------------------------------------------------

El problema es que Oracle quiere emplear to_number para cambiar cada registro antes de hacer la consulta: 

   2 - filter(TO_NUMBER("OBJECT_ID")=51526)

Note

-----

   - dynamic sampling used for this statement

Estadisticas

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

         28  recursive calls

          0  db block gets

        303  consistent gets

          0  physical reads

          0  redo size

        429  bytes sent via SQL*Net to client

        385  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL>

Si queremos emplear el índice en este segundo ejemplo, tenemos que cambiar la consulta de la manera siguiente: 

SQL> select count(*) from my_data

  2  where object_id = '51526'

  3  /

  COUNT(*)

----------

         1

Plan de Ejecucion

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

Plan hash value: 425162582

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

| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |          |     1 |     7 |     1   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE   |          |     1 |     7 |            |          |

|*  2 |   INDEX RANGE SCAN| MY_INDEX |     1 |     7 |     1   (0)| 00:00:01 |

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

Predicate Information (identified by operation id): ---------------------------------------------------

    2 - access("OBJECT_ID"='51526')

Note

-----

   - dynamic sampling used for this statement

Estadisticas

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

          9  recursive calls

          0  db block gets

         59  consistent gets

          1  physical reads

          0  redo size

        429  bytes sent via SQL*Net to client

        385  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL>

Si queremos emplear un índice, aquí está una sugerencia que viene de estos ejemplos:

Si tenemos una columna de tipo number, puede ser posible compararla con un número entre comillas por ejemplo ‘123’. Pero si tenemos una columna de tipo varchar2 y queremos compararla con un número, debemos poner el número entre comillas por ejemplo ‘123’.