SQL: Performance: Índice único NUMBER vs índice único VARCHAR2

¿Que es más eficiente en términos de velocidad de ejecución en consultas SQL? ¿Usar índices únicos del tipo number o usar varchar2? Hace unos días hice unas pruebas en una base de datos Oracle 10g R2 sobre CentOS 5 para ver que es más eficiente en términos de tiempos de búsqueda evaluando los tipos number versus varchar2. Al hacer búsquedas simples (usando solo una tabla) los planes de ejecución son equivalentes al usar índices únicos con una columna NUMBER(10) o si la columna indexada es VARCHAR2(10). Para preparar un ambiente de ejecución usé como base la tabla objetos (imagen de la tabla all_objects) creando dos columnas ID_OBJETO y COD_OBJETO e indexandolas separadamente con una clave única

Varios select usando la columna number

ALTER SESSION SET sql_trace = TRUE;
SELECT id_objeto, cod_objeto, object_name, object_type
FROM   mherrera.objetos
WHERE  id_objeto = 22000;
.....
ALTER SESSION SET sql_trace=FALSE;

Varios select usando la columna varchar2

ALTER SESSION SET sql_trace = TRUE;
SELECT id_objeto, cod_objeto, object_name, object_type
FROM   mherrera.objetos
WHERE  cod_objeto = '0000022000';
.....
ALTER SESSION SET sql_trace=FALSE;

Usando tkprof para evaluar la ejecución:

Select usando NUMBER

SELECT id_objeto, cod_objeto, object_name, object_type
FROM   mherrera.objetos
WHERE  id_objeto = 22000

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        4      0.00       0.00          0          6          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      0.00       0.00          0          6          0           2

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 65  (MHERRERA)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID OBJETOS (cr=3 pr=0 pw=0 time=62 us)
      1   INDEX UNIQUE SCAN UK01_OBJETOS (cr=2 pr=0 pw=0 time=33 us)(object id 68777)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      1   TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 'OBJETOS' (TABLE)
      1    INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 'UK01_OBJETOS' (INDEX(UNIQUE))

Select usando VARCHAR2

SELECT id_objeto, cod_objeto, object_name, object_type
FROM   mherrera.objetos
WHERE  cod_objeto = '0000022000'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        4      0.00       0.00          0          6          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      0.00       0.00          0          6          0           2

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 65  (MHERRERA)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID OBJETOS (cr=3 pr=0 pw=0 time=46 us)
      1   INDEX UNIQUE SCAN UK02_OBJETOS (cr=2 pr=0 pw=0 time=25 us)(object id 68778)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      1   TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 'OBJETOS' (TABLE)
      1    INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 'UK02_OBJETOS' (INDEX (UNIQUE))

Los planes son equivalentes y el uso de recursos y lecturas son los mismos, por lo tanto, la conclusión es que desde el punto de vista de velocidad de ejecución de una query, es lo mismo indexar por NUMBER o por VARCHAR2

Para ejecutar estas pruebas usé los siguientes scripts:

Crear escenario base

Varios select usando Number

Varios select usando Varchar2

Después, ejecute unas queries con un join entre dos tablas (Tipos_Objetos y Objetos)y los resultados son equivalentes. Para complementar el escenario anterior:

Complementa escenario con columnas Number

Select con Number

Complementa escenario con columnas Varchar2

Select con Varchar2

Los resultados generados por TRACE + TKPROF en este nuevo escenario:

Select con Number

Select con Varchar2

Contexto: Oracle 10g R2; SO: Centos 5.2

PL/SQL: Performance: Cuando usar BULK

En varias oportunidades me he encontrado con alguien que me consulta cual es la mejor forma de utilizar las operaciones Bulk en PL/SQL. En general, la receta es: Si la operación se puede realizar en el motor SQL, hágalo allá; si necesita traer los registros al motor PL/SQL, use Bulk. Por ejemplo, si solo necesita hacer una copia de registros de una tabla a otra, usando algunas operaciones básicas, la operación INSERT/SELECT es mucho más eficiente que llevar los registros al motor PL/SQL y hacer un bulk insert:

Caso 1: Insert / Select

INSERT INTO resumen_creaciones
SELECT   TRUNC (fecha_creacion, 'MONTH') AS mes,
    tipo_objeto,
    SUM (total_objetos) AS total_mensual
FROM     objetos_creados
GROUP BY TRUNC (fecha_creacion, 'MONTH'), tipo_objeto;

Caso 2: Bulk Select / Bulk Insert

.....
SELECT   TRUNC (fecha_creacion, 'MONTH'), tipo_objeto, SUM (total_objetos)
BULK COLLECT INTO v_mes, v_tipo_objeto, v_total_mensual
FROM     objetos_creados
GROUP BY TRUNC (fecha_creacion, 'MONTH'), tipo_objeto;

FORALL i IN v_mes.FIRST .. v_mes.LAST
INSERT INTO resumen_creaciones
       (mes, tipo_objeto, total_mensual
       )
VALUES      (v_mes (i), v_tipo_objeto (i), v_total_mensual (i)
       );
....

Para el caso 1:

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.01       0.00          0          0          0           0
Execute      2      0.00       0.00          3          8         25          38
Fetch        1      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.01       0.00          3         11         25          39

Para el caso 2:

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute      3      0.00       0.01          3          5         25          38
Fetch        2      0.00       0.00          0          6          0          39
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      0.00       0.01          3         11         25          77

Beneficios de operaciones SQL:

1. Generalmente son mucho más simples de implementar
2. Producen menos parsing
3. El motor SQL implementa estas operaciones con muchos menos pasos
4. No hay intercambio de registros entre los motores PL/SQL y SQL
5. Más fáciles de mantener

Cuando usar PL/SQL+Colecciones+Procesos Bulk

1. Si es necesario tener el control de errores por registro (p.e para ETL)
2. Es muy grande el volumen de datos a procesar, mejor, dividir para conquistar. En este caso se puede usar BULK con LIMIT y procesar en grupos limitados de registros
3. La query o DML es demasiado compleja para mantenerla como una sola instrucción
4. Si la operación no se puede implementar solo con SQL ya que necesita instrucciones procedurales

Los ejemplos completos se pueden ver en

• Crear escenario
• eInsert_SQL.sql
• eInsert_Bulk.sql

Contexto: Oracle 10g R2 en Centos 5.2