lunes, 10 de septiembre de 2007

Comillas simples dentro de cadenas de texto en 10g

En Oracle 10g se agrega una nueva funcionalidad que permite escribir literales en una cadena de texto con comillas simples, sin la necesidad de escribir los literales con dobles, triples, ... comillas. Esta funcionalidad es muy útil cuando los desarrolladores trabajan con sql dinámico, ya que muchas consultas escritas en sql dinámico pueden ser bastante complejas debido a las comillas.
En PL/SQL llamamos a esta funcionalidad de ésta manera q'[.....]'. Podemos reemplazar los [] con cualquiera de éstos caracteres:
[ ]
{ }
( )
< >

Creamos una tabla de ejemplo:

SQL_9iR2> CREATE TABLE emp AS
2 SELECT level id, 'nom_'||level nom
3 FROM dual
4 CONNECT BY level <= 100 ;

Table created.

En Oracle 9iR2 escribiríamos éste código PL/SQL:

SQL_9iR2> DECLARE
2 l_query VARCHAR2(100) ;
3 BEGIN
4 l_query := 'SELECT id FROM emp WHERE nom = ''nom_10''' ;
5 EXECUTE IMMEDIATE l_query ;
6 DBMS_OUTPUT.PUT_LINE(l_query) ;
7 END ;
8 /

SELECT id FROM emp WHERE nom = 'nom_10'

PL/SQL procedure successfully completed.

En 10gR1 podríamos transcribir ese mismo código de la siguiente manera:

SQL_10gR1> DECLARE
2 l_query VARCHAR2(100) ;
3 BEGIN
4 l_query := q'[SELECT id FROM emp WHERE nom = 'nom_10']' ;
5 EXECUTE IMMEDIATE l_query ;
6 DBMS_OUTPUT.PUT_LINE(l_query) ;
7 END ;
8 /

SELECT id FROM emp WHERE nom = 'nom_10'

PL/SQL procedure successfully completed.


SQL_10gR1> DECLARE
2 l_query VARCHAR2(100) ;
3 BEGIN
4 l_query := q'(SELECT id FROM emp WHERE nom = 'nom_10')' ;
5 EXECUTE IMMEDIATE l_query ;
6 DBMS_OUTPUT.PUT_LINE(l_query) ;
7 END ;
8 /

SELECT id FROM emp WHERE nom = 'nom_10'

PL/SQL procedure successfully completed.


SQL_10gR1> DECLARE
2 l_query VARCHAR2(100) ;
3 BEGIN
4 l_query := q'{SELECT id FROM emp WHERE nom = 'nom_10'}' ;
5 EXECUTE IMMEDIATE l_query ;
6 DBMS_OUTPUT.PUT_LINE(l_query) ;
7 END ;
8 /

SELECT id FROM emp WHERE nom = 'nom_10'

PL/SQL procedure successfully completed.

Pueden observar, que con ésta nueva funcionalidad, ya no hace falta preocuparnos por las comillas en las cadenas de texto.

6 comentarios:

mercedes dijo...

hola como estas? tengo una pregunta el max() solo se puede usar para campos numericos? que pasa si lo aplico a un varchar2? lo pregunto porque tengo un campo1 tipo varchar2 pero hasta ahora he guardado solo numeros, si ejecuto la sentencia select max(campo1) el resultado no es el maximo el valor maximo es 201 y me devuelve 61, no se si es porque el campo es varchar2 agradezco tu ayuda por aca o a mi correo mbelandria@gmail.com

Leonardo Horikian dijo...

Hola Mercedes, el problema es debido a que estas guardando números en un campo del tipo VARCHAR2 (osea, estas guardando números que en realidad son caracteres). Las cadenas son comparadas caracter por caracter, y el primer caracter mayor a la cadena anterior... es la 'cadena mayor'. Por ejemplo, si buscas el mayor entre el 201 y 61, te va a mostrar que el mayor es el 61 porque 6 (el primer caracter de la cadena '61') es mayor que 2 (el primer caracter de la cadena '201'). Si buscamos el mayor entre la cadena 99 y 98, te va a mostrar que el mayor es el 99 porque primero compara el primer caracter de las dos cadenas, como son iguales, compara los otros caracteres restantes, y como 9 es mayor que 8, la cadena 99 es la mayor. En tu caso, la forma que tenes de que te muestre el valor correcto es utilizando la función TO_NUMBER como vemos en mi ejemplo. De todas formas te aconsejo que cada campo de tu tabla tenga el tipo correcto para el dato que va a almacenar.

Veamos un ejemplo:

SQL> CREATE TABLE t ( numero VARCHAR2(2) ) ;

Table created.

SQL> INSERT INTO t VALUES('1') ;

1 row created.

SQL> INSERT INTO t VALUES('2') ;

1 row created.

SQL> INSERT INTO t VALUES('3') ;

1 row created.

SQL> INSERT INTO t VALUES('4') ;

1 row created.

SQL> INSERT INTO t VALUES('5') ;

1 row created.

SQL> SELECT MAX(numero) FROM t ;

MA
--
5

1 row selected.

SQL> INSERT INTO t VALUES('0') ;

1 row created.

SQL> SELECT MAX(numero) FROM t ;

MA
--
5

1 row selected.

SQL> INSERT INTO t VALUES('201') ;

1 row created.

SQL> INSERT INTO t VALUES('61') ;

1 row created.

SQL> SELECT MAX(numero) FROM t ;

MAX
---
61

1 row selected.

SQL> INSERT INTO t VALUES('99') ;

1 row created.

SQL> INSERT INTO t VALUES('98') ;

1 row created.

SQL> SELECT MAX(numero) FROM t ;

MAX
---
99

1 row selected.

SQL> SELECT MAX(to_number(numero)) FROM t ;

MAX(TO_NUMBER(NUMERO))
----------------------
201

1 row selected.

mercedes dijo...

Hola leonardo, como estas? bueno anoche pense eso en llevarlo a entero, busque la funcion y lo resolvi. Tienes toda la razon deberia ser un campo int, la cosa es que inicialmente se penso que se necesitaba que fuera cadena de caracteres, se queria que el codigo fuera una combinacion de la fecha con una letra y un autoincremento; pero ya luego se decidio que fuera solo autoincremento (long story). Igual de verdad Muchisimas gracias por la explicación.

Saludos y Exito

Mariano Gecik dijo...

Leonardo tengo la siguiente consulta de performance...

1) Tengo que hacer el siguiente SQL en Oracle Financials en
una session del TOAD

SELECT count(1)
FROM IC_Cycl_Dtl
WHERE Delete_mark = 0
AND cycle_id = 1402

2) Ese query lo tengo que ejecutar por cada registro en un FORM
de Oracle donde hay 50 entradas, por lo tanto necesito que
sea agil. El mismo tarda 12 segundos en la respuesta...

3) Si hago el mismo query sin el campo delete_mark = 0 o sea

SELECT count(1)
FROM IC_Cycl_Dtl
WHERE cycle_id = 1402

El query es rapido y me muestra el siguiente explain plan

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 1 469
SORT AGGREGATE 1 4
INDEX FAST FULL SCAN GMI.IC_CYCL_DTL_PK 1 K 3 K 469


O sea que entra por indice...

4) El problema es que yo necesito si o si filtrar por
el campo delete_mark = 0 tambien...

5) Si yo ejecuto el query como estaba al principio me hace un full scan
anula el indice y no se porque...

SELECT count(1)
FROM IC_Cycl_Dtl
WHERE Delete_mark = 0
AND Cycle_Id = 1402

Explain Plan

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 1 627
SORT AGGREGATE 1 7
TABLE ACCESS FULL GMI.IC_CYCL_DTL 503 3 K 627


6) los indices de la tabla son

Index Name Unique Column Name Order Pos Index Owner

IC_CYCL_DTL_PK Y ITEM_ID ASC 1 GMI
IC_CYCL_DTL_PK Y WHSE_CODE ASC 2 GMI
IC_CYCL_DTL_PK Y LOT_ID ASC 3 GMI
IC_CYCL_DTL_PK Y LOCATION ASC 4 GMI
IC_CYCL_DTL_PK Y CYCLE_ID ASC 5 GMI
IC_CYCL_DTL_PK Y COUNT_NO ASC 6 GMI

7) Me fije y las statistics estan actualizadas a la fecha actual

8) Cree un indice bitmap para probar si eso agilizaba, ya que el campo
delete_mark solo admite valores 0 y 1. Pero no aceleró el proceso.

9) Puse hints para probar si funcionaba, aunque se que no es la mejor solucion...
y nada...

10) Lo solucione todo esto haciendo una tabla temporal para el form, pero
no logre bajar los tiempos del query y me gustaria saber si lo puedo lograr.

Gracias Mariano Gecik

Leonardo Horikian dijo...

Hola Mariano,

Me gustaría que veas el siguiente ejemplo que trata de emular tu caso en particular.

Ejemplo:

Creo una tabla llamada TEST que va a tener el campo cycle_id con valores del 0 al 10,000 los cuales se van a ir repitiendo hasta llegar a 1,000,000. Esto quiere decir, que por cada valor, voy a tener 100 valores iguales (1,000,000/10,000=100). Por otro lado, también agrego el campo delete_mark que va a tener solamente los valores 0 y 1...

SQL_9iR2> CREATE TABLE test
2 PCTFREE 70
3 PCTUSED 10
4 TABLESPACE xxbd AS
5 SELECT mod((level-1),10000) cycle_id, mod(level,2) delete_mark
6 FROM dual
7 CONNECT BY level <= 1000000;

Nota: En la creación de la tabla usé un truco estándar en el cual elijo un valor muy alto para setear el PCTFREE para lograr extender los datos de la tabla en un número mayor de bloques sin tener que generar una gran cantidad de datos. Con éste truco, pude guardar todos los registros de la tabla en 4,783 bloques.

SQL_9iR2> EXEC dbms_stats.gather_table_stats(user, 'TEST');

PL/SQL procedure successfully completed.

SQL_9iR2> SELECT column_name, num_distinct, density
2 FROM user_tab_columns
3 WHERE table_name = 'TEST';

COLUMN_NAME NUM_DISTINCT DENSITY
------------------------------ ------------ ----------
CYCLE_ID 10000 .0001
DELETE_MARK 2 .5

Bien, veamos el plan de ejecución si la creación de índices y haciendo un full scan de la tabla...

SQL_9iR2> SET AUTOTRACE TRACEONLY

SQL_9iR2> SELECT count(*)
2 FROM test
3 WHERE delete_mark = 1
4 AND cycle_id = 1402;

COUNT(*)
----------
100

Elapsed: 00:00:03.06

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=728 Card=1 Bytes=6)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TEST' (Cost=728 Card=50 Bytes=300)

Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
4795 consistent gets
318 physical reads
0 redo size
330 bytes sent via SQL*Net to client
491 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed

Como vemos, estamos leyendo aproximadamente 4,800 bloques de datos para encontrar solamente 100 valores iguales al 1402.
Veamos que sucede si creamos un índice solamente por los campos cycle_id y delete_mark...

SQL_9iR2> CREATE INDEX test_1402_1_idx ON test(cycle_id, delete_mark) TABLESPACE test_ind;

Index created.

SQL_9iR2> EXEC dbms_stats.gather_index_stats(user, 'TEST_1402_1_IDX');

PL/SQL procedure successfully completed.

SQL_9iR2> SELECT count(*)
2 FROM test
3 WHERE delete_mark = 1
4 AND cycle_id = 1402;

COUNT(*)
----------
100

Elapsed: 00:00:03.02

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=6)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'TEST_1402_1_IDX' (NON-UNIQUE) (Cost=3 Card=50 Bytes=300)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
330 bytes sent via SQL*Net to client
491 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

En éste caso, estamos leyendo sólo 3 bloques de datos (realizamos 3 LIO's) para encontrar todos los valores iguales al 1402 ya que en vez de realizar un full scan de la tabla TEST, realizamos un range scan del índice, y como los datos en el índice B*Tree se guardan ordenados, pudimos encontrar los valores necesarios leyendo muy pocos bloques del índice.

Saludos

Mariano Gecik dijo...

Leonardo,
Muchas Gracias , lo probé como vos dijiste con el indice para los 2 campos y funcionó perfecto.
Lo que no entiendo son 2 cosas :

1)En que casos utilizo entonces un indice bitmap?.
2)Esta tabla de financials se usa en muchos lugares, agregar un índice no puede ocasionar problemas en otros lados, por más que en mi caso particular funcione bien? Ya que me dijeron que a veces crear muchos índices sobre una tabla puede degradar la performance.

Muchas Gracias por la ayuda.
Saludos Mariano.-