viernes, 27 de julio de 2007

SQL*loader: Field in data file exceeds maximum length

Recientemente tuve la oportunidad de ver un problema en un aplicativo que utilizaba SQL*loader 9.2.0.6 para cargar datos de un archivo plano en una tabla, originalmente separados por pipes y eventualmente conteniendo espacios en uno de sus campos.

Un ejemplo del contenido del archivo de datos (los pipes van en color para identificar mejor los campos):

A1|3|02/12/98|NN|A Suspender |34
A2|2|02/12/98|NS|No se presentó, fue enviado nuevo aviso         (**)      |27
A3|3|02/14/98|NS||9

(**) La segunda línea de datos contiene luego del texto y antes del siguiente pipe, más de 300 espacios.

El control file utilizado para cargar ese archivo tenía esta forma:

LOAD DATA
APPEND
INTO TABLE TMP_SUSPENSION
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
SEGMENTO,
SUBSEGMENTO,
FECHA_VENCIMIENTO DATE "MM/DD/RRRR",
ESTADO_FINANCIERO,
DESCRIPCION "TRIM(:DESCRIPCION)",
CODIGO_CATEGORIA
)

Al campo descripcion se le había agregado la función TRIM para eliminar los espacios sobrantes, sin embargo, cuando se ejecutaba SQL*loader la carga fallaba y parecía ignorar la función:

Record 2: Rejected - Error on table TMP_SUSPENSION, column DESCRIPCION.
Field in data file exceeds maximum length

Table TMP_SUSPENSION:
2 Rows successfully loaded.
1 Row not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

La causa del problema no era que la función TRIM no estaba cortando los caracteres ni que el campo en la tabla no tuviera el largo suficiente, sino la mala práctica de no especificar tipos en los campos del control file. Recordemos que los campos que se definen allí no corresponden a la tabla destino sino a lo que se espera encontrar en el archivo fuente. Cuando no se especifica tipo y largo en un campo del control file, Oracle asume el largo por defecto de 255 caracteres. Por lo tanto, al encontrar mas de 255 caracteres entre dos pipes, se rechaza el mismo antes siquiera de aplicar la función TRIM. De la forma que opera SQL*loader, primero se validan los tipos en el archivo, y luego se aplican las funciones sobre los campos identificados.

Como solución, se arregló el control file para que tuviera el tipo especificado de un largo mayor al previsto. Esta simple buena práctica es muy recomendada para SQL*loader.

LOAD DATA
APPEND
INTO TABLE TMP_SUSPENSION
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
SEGMENTO,
SUBSEGMENTO,
FECHA_VENCIMIENTO DATE "MM/DD/RRRR",
ESTADO_FINANCIERO,
DESCRIPCION CHAR(600) "TRIM(:DESCRIPCION)",
CODIGO_CATEGORIA
)

domingo, 22 de julio de 2007

Sobre estimar estadísticas

¿Que estadísticas conviene tomar? ¿La tabla completa o un porcentaje? ¿Qué valor uso?

Esta pregunta es relevante para tablas muy grandes o esquemas populosos donde el tiempo de extracción debe ser optimizado. Obviamente tomar las estadísticas completas es lo mejor por precisión, y en esquemas chicos nos puede llevar algunos pocos minutos, pero algunas veces tenemos ciertos requerimientos de performance o de utilización de recursos ya que es una operación que realiza mucha lectura física (I/O).

Para tablas y esquemas grandes yo suelo tomar un 20 por ciento para el estimado, con lo cual logro mis estadísticas de 'buena calidad'.

Oracle recomienda utilizar el paquete dbms_stats para tomar estadísticas de tablas, índices, esquemas o de la base de datos completa. Extrae más información que el comando ANALIZE además de otras características que podemos aprovechar como el procesamiento en paralelo.

Se puede invocar de la siguiente manera:

SQL> exec dbms_stats.gather_table_stats(ownname => user, tabname => 'OUT_TAB',estimate_percent => 20, method_opt => 'FOR TABLE FOR ALL COLUMNS FOR ALL INDEXES', cascade => true );

Algo interesante de este paquete es que nos permite que Oracle calcule un valor adecuado para el porcentaje estimado, y esto se hace poniendo dbms_stats.auto_sample_size en estimate_percent.

Si hacemos la prueba podemos constatar observando la tabla user_tables que Oracle toma las estadísticas al 100 por ciento hasta cierta cantidad de filas que depende de cada base de datos.

A tales efectos puede utilizarse una consulta como ésta:

SELECT table_name, num_rows, sample_size, TRUNC(sample_size*100/DECODE(num_rows,0,1,num_rows),2) porciento
FROM user_tables;

A modo de ejemplo, en una base 9iR2 sobre un esquema de desarrollo con estadísticas estimadas al 20 por ciento, constaté que hasta las tablas con 12500 filas Oracle tomaba el 100 por ciento de las filas como muestra, y luego a partir de ahí tomaba el 20 por ciento que yo le había solicitado.

También se puede comprobar que usando dbms_stats.auto_sample_size para ciertos casos, Oracle calcula la tabla entera sin importar la cantidad de tuplas.
Esto se debe a que Oracle utiliza un algoritmo para obtener un número adecuado: parte de cierta cantidad de tuplas y en base a ellas analiza si la calidad de las mismas es buena, si no lo es toma un nuevo porcentaje y así sucesivamente hasta llegar a un 25 por ciento, luego del cual decide computar la tabla entera.
Cuando esto sucede, los requerimientos de performance o recursos utilizados sufren; por este motivo en esos casos es mejor especificar el valor fijo.

Ver también:
Falla de estadísticas con ORA-00933
Error ORA-942 while gathering statistics

jueves, 19 de julio de 2007

Variables en SQL*Plus (parte 2)

Hay tres tipos de variables en SQL*Plus y suelen causar confusión.
Estas son: variables de usuario, variables de sustitución y bind variables.
En la primera parte repasamos las variables de usuario, aquí van las variables de sustitución.

VARIABLES DE SUSTITUCION

Las variables de sustitución suelen confundirse con las variables de usuario, más que nada porque ambas aparecen precedidas con un símbolo ampersand (&). Sin embargo, hay diferencias en la definición, asignación y uso de las mismas. Como vimos en la parte 1, las variables de usuario se declaran explícitamente con el comando DEFINE, tienen asociado un tipo y se les pueden asignar valores en el script como lo hacemos en cualquier lenguaje de programación.
Las variables de sustitución en cambio cuando están indefinidas, solicitan el valor al usuario cada vez que aparecen, y el resultado es exactamente el mismo que si escribiéramos el texto directamente.

Por ejemplo: en la siguiente consulta en cada aparición de variable será solicitado el valor al usuario. Notar que aunque la variable COLUMNA aparece repetida, se trata como si fueran diferentes y el valor es solicitado dos veces.

SELECT &COLUMNA, SUM(MONTO)
FROM cuentas
GROUP BY &COLUMNA;

Enter value for columna: ID_REGION
old 1: SELECT &COLUMNA,
new 1: SELECT ID_REGION,
Enter value for columna: ID_REGION
old 1: GROUP BY &COLUMNA
new 1: GROUP BY ID_REGION

Mientras que el símbolo ampersand (&) declara una variable sustitución temporal (vale una única vez), dos ampersand consecutivos (&&) definen una variable permanente evitando que el usuario deba ingresar nuevamente su valor.

Scripts parametrizados

Las variables de sustitución se utilizan típicamente para pasar parámetros a un script a través del comando START. Dentro del script las variables deben enumerarse secuencialmente &1, &2, etc, y éstas serán asignadas en orden con los parámetros pasados en la llamada.

Ejemplo: Un script al que le pasamos dos parámetros, un nombre de columna y la tabla

programa.sql
SET VERIFY OFF
SELECT &&1, SUM(MONTO)

FROM &2
GROUP BY &1;

La llamada se realiza de la siguiente manera:

SQL> START programa.sql ID_REGION cuentas

  • La variable 1 tiene dos ampersand por lo que queda definida a la primera vez que aparece.
  • SET VERIFY OFF elimina el feedback de old y new values.
  • Las variables de sustitución 1 y 2, al igual que las variables de usuario también se pueden borrar de memoria, por ejemplo incluyendo la línea UNDEFINE 1 en el script.

Ver también:
Variables en SQLPlus (parte 1) - Sobre variables de usuario
Variables en SQLPlus (parte 3) - Sobre variables bind

sábado, 14 de julio de 2007

lunes, 9 de julio de 2007

Cuestiones de idioma

Muchas veces necesitamos comparar días de la semana con SQL y usamos funciones como to_char, to_date, next_day, y otras. Con ellas podemos obtener el número de día de la semana o el nombre del día o del mes, y usarlo para evaluar alguna condición.
En estos casos deberíamos preguntarnos si nuestro código depende del idioma en que esté configurado el cliente o la base de datos. Si esto es así, estamos haciendo que nuestras aplicaciones sean dependientes del lugar donde se instalen y eso no es deseable.

Recordando que el orden de preferencia para los seteos del NLS es (de mayor a menor):

1) NLS explicitado en función
2) NLS configurado en la sesión del usuario
3) NLS configurado en el cliente
4) NLS configurado en la base de datos

...es una buena práctica 'asegurar' el idioma o territorio a utilizar cuando usamos una función que depende de eso.
Yo prefiero que mis aplicaciones dependan lo menos posible de una configuración especial en el cliente o la base de datos, por lo tanto siempre voy a aplicar 1) o 2).

Ejemplo 1: Días de la semana como palabra

Obtener las marcas de los empleados los días sábados:

SELECT timestamp, employee
FROM timer_tbl
WHERE to_char(timestamp,'DAY') = 'SATURDAY';

En este caso la consulta depende del idioma que este configurado en la sesión o en el cliente o la base de datos, y rezaría para que siempre fuera inglés!
Para evitar ese tipo de ligaduras con la región, puedo pasarle a to_char el parámetro NLS_DATE_LANGUAGE y entonces estoy en el caso 1) de máxima prioridad.
Me quedaría:

SELECT timestamp, employee
FROM timer_tbl
WHERE to_char(timestamp,'DAY','NLS_DATE_LANGUAGE=AMERICAN') = 'SATURDAY';

Ejemplo 2: Número de día de la semana

En algunos territorios como Portugal y USA el primer día de la semana es el domingo, en otros como España y América del Sur es el lunes. Esto hace que la máscara 'D' de to_char sea diferente según el territorio.
El número de dia de la semana depende del parámetro NLS_TERRITORY, pero no lo puedo pasar como tercer parámetro, no es válido.

En ese caso tendré que alterar la sesión para asegurar que el día 1 sea el lunes para mi aplicación.

SQL> alter session set nls_territory='AMERICA';

Session altered.

SQL> select to_char(sysdate,'D DAY') HOY from dual;

HOY
-----------------------------
1 MONDAY

Ver también:
Acentos del idioma español

jueves, 5 de julio de 2007

INNER/OUTER JOINS (ANSI)

La pregunta del día es:
¿Usando la notación ANSI para joins, es lo mismo agregar condiciónes adicionales al where que en la cláusula ON?

Si no se entendió la pregunta, pongamos un ejemplo...

¿Son estas dos consultas equivalentes?
SELECT d.departamento_id, e.nombre
FROM (departamento d INNER JOIN empleado e
ON d.departamento_id = e.departamento_id)
WHERE d.seccion_id = e.seccion;

SELECT d.departamento_id, e.nombre
FROM (departamento d INNER JOIN empleado e
ON d.departamento_id = e.departamento_id
AND d.seccion_id = e.seccion);
Pensemos esto, si usáramos el viejo estilo de join (la sintaxis ANSI apareció en 9i), tendríamos todas las condiciones juntas, sin orden específico ni marca especial del tipo "esta condición es de join".
Sucede que luego que Oracle parsea la consulta, analiza todas las condiciones que haya encontrado y según lo que tiene sobre la mesa decide cuál aplicar primero. El hecho de que nosotros incluyamos una condición en el ON o en el where, no va a ser un factor decisivo sino una diferencia documental que aporta claridad. Hagamos sino la prueba de dejar una de las cláusulas de un join compuesto fuera del ON, y colocarla en el where.... el resultado va a ser el mismo!

Si alguien quiere pruebas experimentales concretas podrá comparar los planes de ejecución y notar que internamente es exactamente lo mismo.