when switch nullif example ejemplo con sql oracle plsql oracle11g sequence

switch - sql coalesce ejemplo



La evaluación de cortocircuito de CASE y COALESCE funciona con secuencias en PL/SQL pero no en SQL (2)

¿La evaluación de cortocircuito descrita en la documentación de CASE y COALESCE() aplica a las secuencias cuando se utiliza en SQL? Esto no parece estar sucediendo.

La documentación de Oracle en CASE establece que:

La base de datos Oracle utiliza la evaluación de cortocircuito. Para una expresión CASE simple ... Oracle nunca evalúa una comparación_expr si una comparación_expr anterior es igual a expr. Para una expresión CASE buscada, la base de datos ... nunca evalúa una condición si la condición anterior era verdadera.

Del mismo modo para COALESCE() la documentación establece que:

La base de datos Oracle utiliza la evaluación de cortocircuito. La base de datos evalúa cada valor expr y determina si es NULL, en lugar de evaluar todos los valores expr antes de determinar si alguno de ellos es NULL.

Cuando se llama a una secuencia desde SQL, este no parece ser el caso; Como puede ver, no se producen cortocircuitos y la secuencia se incrementa.

SQL> create sequence tmp_test_seq start with 1 increment by 1; SQL> select tmp_test_seq.nextval from dual; NEXTVAL ---------- 1 SQL> select tmp_test_seq.currval from dual; CURRVAL ---------- 1 SQL> select coalesce(1, tmp_test_seq.nextval) from dual; COALESCE(1,TMP_TEST_SEQ.NEXTVAL) -------------------------------- 1 SQL> select tmp_test_seq.currval from dual; CURRVAL ---------- 2 SQL> select case when 1 = 1 then 1 else tmp_test_seq.nextval end as s from dual; S ---------- 1 SQL> select tmp_test_seq.currval from dual; CURRVAL ---------- 3

El violín de SQL .

Sin embargo, al llamar desde PL / SQL, la secuencia no se incrementa:

SQL> create sequence tmp_test_seq start with 1 increment by 1; SQL> declare 2 i number; 3 begin 4 i := tmp_test_seq.nextval; 5 dbms_output.put_line(tmp_test_seq.currval); 6 i := coalesce(1, tmp_test_seq.nextval); 7 dbms_output.put_line(i); 8 dbms_output.put_line(tmp_test_seq.currval); 9 i := case when 1 = 1 then 1 else tmp_test_seq.nextval end; 10 dbms_output.put_line(i); 11 dbms_output.put_line(tmp_test_seq.currval); 12 end; 13 / 1 1 1 1 1 SQL> select tmp_test_seq.nextval from dual; NEXTVAL ---------- 2

Al llamar a la secuencia en SQL desde PL / SQL, se obtienen los mismos resultados que con SQL:

SQL> create sequence tmp_test_seq start with 1 increment by 1; SQL> declare 2 i number; 3 begin 4 select tmp_test_seq.nextval into i from dual; 5 dbms_output.put_line(tmp_test_seq.currval); 6 select coalesce(1, tmp_test_seq.nextval) into i from dual; 7 dbms_output.put_line(i); 8 dbms_output.put_line(tmp_test_seq.currval); 9 select case when 1 = 1 then 1 else tmp_test_seq.nextval end into i 10 from dual; 11 dbms_output.put_line(i); 12 dbms_output.put_line(tmp_test_seq.currval); 13 end; 14 / 1 1 2 1 3

No parece haber nada en la documentación sobre esto; la guía del administrador para la gestión de secuencias , la referencia del lenguaje SQL en las columnas de columnas , la referencia del lenguaje PL / SQL en CURRVAL y NEXTVAL o el resumen de conceptos de la base de datos de las secuencias .

¿Se produce la evaluación de cortocircuito de CASE y COALESCE() para las secuencias cuando se utiliza en SQL? ¿Está esto documentado?

Estamos en 11.2.0.3.5 si es de interés.

Explicación de por qué la evaluación de cortocircuito no se aplica a las secuencias puede ser la siguiente. ¿Qué es una secuencia? Dejando a un lado las partes internas, es una combinación de definición de secuencia (registro en la seq$ tabla del diccionario de datos) y algún componente SGA interno, no es una función y podría considerarse, aunque la documentación no lo indica directamente (pero sí lo hace el plan de ejecución) como una fila fuente. Y cada vez que se hace referencia a una secuencia directamente en la lista de selección de una consulta, el optimizador debe evaluarla cuando busca el plan de ejecución óptimo. Durante el proceso de formación de un plan de ejecución óptimo, una secuencia se incrementa si se hace referencia a la pseudocolumna nextval :

SQL> create sequence seq1; Sequence created

Aquí está nuestra secuencia:

SQL> select o.obj# 2 , o.name 3 , s.increment$ 4 , s.minvalue 5 , s.maxvalue 6 , s.cache 7 from sys.seq$ s 8 join sys.obj$ o 9 on (o.obj# = s.obj#) 10 where o.name = ''SEQ1'' 11 ; OBJ# NAME INCREMENT$ MINVALUE MAXVALUE CACHE ---------- ------- ---------- ---------- ---------- ---------- 94442 SEQ1 1 1 1E28 20

Permite rastrear a continuación la consulta, y también echar un vistazo a su plan de ejecución

SQL> ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''; Session altered SQL> select case 2 when 1 = 1 then 1 3 when 2 = 1 then seq1.nextval 4 end as res 5 from dual; RES ---------- 1 /* sequence got incremented by 1 */ SQL> select seq1.currval from dual; CURRVAL ---------- 3

Información del archivo de seguimiento:

ESTADO # 1016171528 id = 1 cnt = 1 pid = 0 pos = 1 obj = 94442 op = ''SEQUENCE SEQ1 ...
ESTADO # 1016171528 id = 2 cnt = 1 pid = 1 pos = 1 obj = 0 op = ''FAST DUAL ...
CIERRE # 1016171528: c = 0, e = 12, dep = 0, tipo = 0, tim = 12896600071500 / * cerrar el cursor * /

El plan de ejecución nos mostrará básicamente lo mismo:

SQL> explain plan for select case 2 when 1 = 1 then 1 3 else seq1.nextval 4 end 5 from dual 6 / Explained Executed in 0 seconds SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT --------------------------------------------------------------- Plan hash value: 51561390 ----------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ----------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 | | 1 | SEQUENCE | SEQ1 | | | | | 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 | ----------------------------------------------------------------- 9 rows selected Executed in 0.172 seconds

En términos de evaluación, hacer referencia a una secuencia directamente en una consulta, aproximadamente lo mismo que incluir una subconsulta correlacionada. Esa subconsulta correlacionada siempre será evaluada por el optimizador:

SQL> explain plan for select case 2 when 1 = 1 then 1 3 when 2 = 1 then (select 1 4 from dual) 5 end as res 6 from dual; Explained Executed in 0 seconds SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT ----------------------------------------------------------------- Plan hash value: 1317351201 ----------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ----------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 (0)| 00:00:01 | | 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 | | 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 | ----------------------------------------------------------------- 9 rows selected Executed in 0.063 seconds

Podemos ver que dual tabla dual se ha incluido en el plan de ejecución dos veces.

La analogía con una sub-consulta se hizo en un apuro. Hay más diferencias que similitudes, por supuesto. Las secuencias son mecanismos absolutamente diferentes. Sin embargo, el optimizador considera que las secuencias son una fuente de fila, y mientras no vea la nextval pseudocolumna de la secuencia a la que se hace referencia directamente en la lista de select de una consulta de nivel superior, no evaluará la secuencia. De lo contrario, la secuencia se incrementará, ya sea que se use o no una lógica de evaluación de cortocircuito. El motor PL / SQL, obviamente, (a partir de Oracle 11g r1) tiene una forma diferente de acceder a un valor de secuencia. Debe tenerse en cuenta que en las versiones 11gR1 anteriores de RDBMS deberíamos escribir una consulta para hacer referencia a una secuencia en el bloque PL / SQL, cuyo motor PL / SQL se envió directamente al motor SQL.

La respuesta a la pregunta " por qué una secuencia se incrementa durante la generación de un plan de ejecución por parte del optimizador " se encuentra en la implementación interna de las secuencias.

Para PL / SQL, Oracle asegura que utilizará la evaluación de cortocircuito:

Al evaluar una expresión lógica, PL / SQL utiliza la evaluación de cortocircuito. Es decir, PL / SQL deja de evaluar la expresión tan pronto como puede determinar el resultado. Por lo tanto, puede escribir expresiones que de lo contrario podrían causar errores.

Desde: 2 Fundamentos del lenguaje PL / SQL

Cuando usa el siguiente nextval en el código SQL, tenemos una situación diferente.

En primer lugar, debemos tener en cuenta que currval y nextval son pseudocolumns:

Una pseudocolumna se comporta como una columna de tabla, pero en realidad no se almacena en la tabla. Puede seleccionar entre pseudocolumnas, pero no puede insertar, actualizar o eliminar sus valores. Una pseudocolumna también es similar a una función sin argumentos (consulte el Capítulo 5, "Funciones". Sin embargo, las funciones sin argumentos generalmente devuelven el mismo valor para cada fila en el conjunto de resultados, mientras que las pseudocolumnas generalmente devuelven un valor diferente para cada fila.

A partir de: 3 pseudocolumnas .

La pregunta ahora es: ¿por qué Oracle evalúa nextval ? o ¿Está este comportamiento establecido en algún lugar?

Dentro de una sola instrucción SQL que contiene una referencia a NEXTVAL, Oracle incrementa la secuencia una vez:

  • Para cada fila devuelta por el bloque de consulta externo de una instrucción SELECT. Dicho bloque de consulta puede aparecer en los siguientes lugares:

    1. Una instrucción SELECT de nivel superior
    2. Una instrucción INSERT ... SELECT (ya sea de tabla única o multitable). Para un inserto multitable, la referencia a NEXTVAL debe aparecer en la cláusula VALUES, y la secuencia se actualiza una vez para cada fila devuelta por la subconsulta, aunque NEXTVAL puede ser referenciado en múltiples ramas del inserto multitable.
    3. Una sentencia CREATE TABLE ... AS SELECT
    4. Una sentencia CREATE MATERIALIZED VIEW ... AS SELECT

  • Para cada fila actualizada en una sentencia UPDATE

  • Para cada sentencia INSERT que contiene una cláusula VALUES

  • Para cada fila fusionada por una sentencia MERGE. La referencia a NEXTVAL puede aparecer en merge_insert_clause o merge_update_clause o ambos. El valor de NEXTVALUE se incrementa para cada fila actualizada y para cada fila insertada, incluso si el número de secuencia no se usa realmente en la actualización o la operación de inserción. Si NEXTVAL se especifica más de una vez en cualquiera de estas ubicaciones, entonces la secuencia se incrementa una vez para cada fila y devuelve el mismo valor para todas las apariciones de NEXTVAL para esa fila.

De: Secuencia Pseudocolumns

Su caso es claramente "1. Una declaración SELECT de nivel superior", pero no significa que la lógica de cortocircuito no esté en su lugar, sino que nextval se evalúa siempre el siguiente valor.

Si está interesado en la lógica de cortocircuito, entonces es mejor eliminar el siguiente nextval de la ecuación.

Una consulta como esta no evalúa la subconsulta:

select 6 c from dual where ''a'' = ''a'' or ''a'' = (select dummy from dual)

Pero si intentamos hacer algo similar con la coalesce o el case , veremos que Oracle Optimizer decide ejecutar las subconsultas:

select 6 c from dual where ''a'' = coalesce(''a'', (select dummy from dual) )

Creé pruebas anotadas en esta demostración en SQLFiddle para mostrar esto.

Parece que Oracle aplica la lógica de cortocircuito solo si con la condición OR, pero con la coalesce y el case tiene que evaluar todas las ramas.

Creo que sus primeras pruebas en PL / SQL muestran que la coalsce y el case utilizan una lógica de cortocircuito en PL / SQL, como afirma Oracle. Su segunda prueba, incluida la secuencia en las sentencias de SQL, muestra que, en ese caso, el siguiente nextval se evalúa de todos modos, incluso si no se usa el resultado, y Oracle también lo documenta.

Reunir las dos cosas parece un poco extraño, porque la coalesce y el comportamiento del case parecen ser realmente inconsistentes para mí, pero también debemos tener en cuenta que la implementación de esa lógica depende de la implementación ( aquí mi fuente )