Indíces basados en una función Método Tipo ( Function-based Index on a Type Method )

Por lo general, en la mayoría de los casos, utilizamos solamente índexes convencionales, olvidándonos que existen varios tipos ellos.
Tenemos:

• Indexes normales
• Bitmap
• Particionados
• Indexes Basados en una función
• Indexes de Dominio

Aquí un ejemplo práctico de como utilizar los índexes basados en una función, mediante el método de utilización de tipos de dato. Algo que sin lugar a duda, puede ser de gran utilidad.

Para crear un índice basado en función, además de los requisitos previos para crear un índice convencional, este debe ser definido como "Determinista". Además, los usuarios que deseen utilizarlo, deben tener privilegios de ejecución sbore la función definida.

Vamos a crear un tipo de objecto llamado "rectangulo" cuya función será calcular el área de un figura rectangular.

SQL> create type rectangulo as object
2 (largo number,
3 ancho number,
4 member function area return number deterministic
5 );
6 /

Type created.

Vamos con el cuerpo del tipo, el cuál, devolvera como resultado, la múltiplicación de largo por ancho:

SQL> create or replace type body rectangulo as
2 member function area return number is
3 begin
4 return(largo*ancho);
5 end;
6 end;
7 /

Type body created.

Ahora vamos a crear una tabla de tipo rectangulo.

SQL> create table rect_tabla of rectangulo;

Table created.

Ahora como la tabla fue creada basada en el tipo "rentangulo", usted puede crear un índexe basado en una función utilizando el método "area()", como se ve a continuación:

SQL> create index area_idx on rect_tabla x( x.area());

Index created.

Si hacemos una consulta a la tabla, filtrando aquellos registros cuyo valor sea mayor a 100, como producto de la aplicación de la función, tenemos:

SQL> select * from rect_tabla x where x.area() > 100;

no rows selected

Correcto, no hemos ingresando registros.
Antes de hacerlo, veamos el plan de ejecución ( explain plan ), que la base de datos genera para la consulta:

SQL> explain plan for
2 select * from rect_tabla x where x.area() > 100;

Explained.

SQL> select * from table(dbms_xplan.display);





Ahora sí, ingresemos un par de registros, uno que no cumpla con el filtro de la consulta y otro si.

SQL> insert into rect_tabla values(&largo,&ancho);
Enter value for largo: 10
Enter value for ancho: 5
old 1: insert into rect_tabla values(&largo,&ancho)
new 1: insert into rect_tabla values(10,5)

1 row created.

SQL> /
Enter value for largo: 20
Enter value for ancho: 10
old 1: insert into rect_tabla values(&largo,&ancho)
new 1: insert into rect_tabla values(20,10)

1 row created.

SQL> commit;

Commit complete.
Realizamos la consulta sobre la tabla nuevamente, ahora el resultado es:

SQL> select * from rect_tabla x where x.area() > 100;

LARGO ANCHO
---------- ----------
20 10

SQL>

Elaborado en:
SQL> select * from v$version;
BANNER
--------------------------------------------------------
Oracle Database 10g Release 10.2.0.1.0 - Production
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production