La mejor manera de explicar éste problema, es realizando un ejemplo.
La tabla de prueba que vamos a utilizar en nuestro ejemplo, contiene los siguientes registros:
SQL> SELECT * FROM test ;
ID NIVEL SALARIO
---------- ---------- ----------
10 1 2500
20 2 3000
30 1 3500
40 2 4000
50 1 4500
60 2 5000
70 1 5500
80 2 6000
90 1 6500
100 2 7000
10 rows selected.
Primero vamos a realizar el ejemplo en una base de datos 8.1.7.4.0
SQL_8i> SELECT id, nivel, salario, DENSE_RANK() OVER (PARTITION BY nivel ORDER BY salario DESC) AS rank
2 FROM test ;
ID NIVEL SALARIO RANK
---------- ---------- ---------- ----------
90 1 6500 1
70 1 5500 2
50 1 4500 3
30 1 3500 4
10 1 2500 5
100 2 7000 1
80 2 6000 2
60 2 5000 3
40 2 4000 4
20 2 3000 5
10 rows selected.
Como podemos observar, si ejecutamos esa consulta con Analytic Functions en SQL*Plus, funciona a la perfección. Veamos qué sucede si intentamos ejecutar la consulta dentro de PL/SQL:
SQL_8i> CREATE PROCEDURE pr_test
2 IS
3 BEGIN
4 --
5 FOR cur IN ( SELECT id, nivel, salario, DENSE_RANK() OVER (PARTITION BY
nivel ORDER BY salario DESC) AS rank
6 FROM test ) LOOP
7 --
8 dbms_output.put_line('ID: '||cur.id||
9 ' - NIVEL: '||cur.nivel||
10 ' - SALARIO: '||cur.salario||
11 ' - RANK: '||cur.rank) ;
12 --
13 END LOOP ;
14 --
15 END pr_test ;
16 /
Warning: Procedure created with compilation errors.
SQL_8i> show errors
Errors for PROCEDURE PR_TEST:
LINE/COL ERROR
----------- ------------------------------------------------------------------------
5/63 PLS-00103: Encountered the symbol "(" when expecting one of the following:
, from
Este error se debe a que según la Nota 147808.1, no podemos utilizar Analytic Functions dentro de PL/SQL en versiones anteriores a 9i. Podemos utilizar éste tipo de funciones en SQL, pero no en PL/SQL. Desde las versiones de la 9i en adelante, podemos utilizar Analytic Functions tanto en SQL como en PL/SQL.
Hay 2 formas de solucionar éste problema:
- Crear vistas utilizando Analytic Function y luego hacer referencia a esas vistas dentro de PL/SQL.
- Usar Dynamic SQL.
Veamos la implementación de esas 2 soluciones:
Utilizando una vista...
SQL_8i> CREATE VIEW pr_test_view AS
2 SELECT id, nivel, salario, DENSE_RANK() OVER (PARTITION BY nivel ORDER BY s
alario DESC) AS rank
3 FROM test ;
View created.
SQL_8i> CREATE OR REPLACE PROCEDURE pr_test
2 IS
3 BEGIN
4 --
5 FOR cur IN ( SELECT id, nivel, salario, rank
6 FROM pr_test_view ) LOOP
7 --
8 dbms_output.put_line('ID: '||cur.id||
9 ' - NIVEL: '||cur.nivel||
10 ' - SALARIO: '||cur.salario||
11 ' - RANK: '||cur.rank) ;
12 --
13 END LOOP ;
14 --
15 END pr_test ;
16 /
Procedure created.
SQL_8i> EXEC pr_test
ID: 90 - NIVEL: 1 - SALARIO: 6500 - RANK: 1
ID: 70 - NIVEL: 1 - SALARIO: 5500 - RANK: 2
ID: 50 - NIVEL: 1 - SALARIO: 4500 - RANK: 3
ID: 30 - NIVEL: 1 - SALARIO: 3500 - RANK: 4
ID: 10 - NIVEL: 1 - SALARIO: 2500 - RANK: 5
ID: 100 - NIVEL: 2 - SALARIO: 7000 - RANK: 1
ID: 80 - NIVEL: 2 - SALARIO: 6000 - RANK: 2
ID: 60 - NIVEL: 2 - SALARIO: 5000 - RANK: 3
ID: 40 - NIVEL: 2 - SALARIO: 4000 - RANK: 4
ID: 20 - NIVEL: 2 - SALARIO: 3000 - RANK: 5
PL/SQL procedure successfully completed.
Utilizando Dynamic SQL...
SQL_8i> CREATE OR REPLACE PROCEDURE pr_test
2 IS
3 --
4 TYPE mi_cursor IS REF CURSOR ;
5 cur mi_cursor ;
6 --
7 l_consulta VARCHAR2(1000) ;
8 l_id test.id%TYPE ;
9 l_nivel test.nivel%TYPE ;
10 l_salario test.salario%TYPE ;
11 l_rank NUMBER ;
12 --
13 BEGIN
14 --
15 l_consulta := 'SELECT id, nivel, salario, DENSE_RANK() OVER (PARTITION
BY nivel ORDER BY salario DESC) AS rank FROM test' ;
16 --
17 OPEN cur FOR l_consulta ;
18 --
19 LOOP
20 --
21 FETCH cur INTO l_id, l_nivel, l_salario, l_rank ;
22 --
23 EXIT WHEN cur%NOTFOUND ;
24 --
25 dbms_output.put_line('ID: '||l_id||
26 ' - NIVEL: '||l_nivel||
27 ' - SALARIO: '||l_salario||
28 ' - RANK: '||l_rank) ;
29 --
30 END LOOP ;
31 --
32 CLOSE cur ;
33 --
34 END pr_test ;
35 /
Procedure created.
SQL_8i> EXEC pr_test
ID: 90 - NIVEL: 1 - SALARIO: 6500 - RANK: 1
ID: 70 - NIVEL: 1 - SALARIO: 5500 - RANK: 2
ID: 50 - NIVEL: 1 - SALARIO: 4500 - RANK: 3
ID: 30 - NIVEL: 1 - SALARIO: 3500 - RANK: 4
ID: 10 - NIVEL: 1 - SALARIO: 2500 - RANK: 5
ID: 100 - NIVEL: 2 - SALARIO: 7000 - RANK: 1
ID: 80 - NIVEL: 2 - SALARIO: 6000 - RANK: 2
ID: 60 - NIVEL: 2 - SALARIO: 5000 - RANK: 3
ID: 40 - NIVEL: 2 - SALARIO: 4000 - RANK: 4
ID: 20 - NIVEL: 2 - SALARIO: 3000 - RANK: 5
PL/SQL procedure successfully completed.
Ahora vamos a realizar el ejemplo anterior pero en una base de datos 9.2.0.8.0
SQL_9i> CREATE PROCEDURE pr_test
2 IS
3 BEGIN
4 --
5 FOR cur IN ( SELECT id, nivel, salario, DENSE_RANK() OVER (PARTITION BY
nivel ORDER BY salario DESC) AS rank
6 FROM test ) LOOP
7 --
8 dbms_output.put_line('ID: '||cur.id||
9 ' - NIVEL: '||cur.nivel||
10 ' - SALARIO: '||cur.salario||
11 ' - RANK: '||cur.rank) ;
12 --
13 END LOOP ;
14 --
15 END pr_test ;
16 /
Procedure created.
SQL_9i> EXEC pr_test
ID: 90 - NIVEL: 1 - SALARIO: 6500 - RANK: 1
ID: 70 - NIVEL: 1 - SALARIO: 5500 - RANK: 2
ID: 50 - NIVEL: 1 - SALARIO: 4500 - RANK: 3
ID: 30 - NIVEL: 1 - SALARIO: 3500 - RANK: 4
ID: 10 - NIVEL: 1 - SALARIO: 2500 - RANK: 5
ID: 100 - NIVEL: 2 - SALARIO: 7000 - RANK: 1
ID: 80 - NIVEL: 2 - SALARIO: 6000 - RANK: 2
ID: 60 - NIVEL: 2 - SALARIO: 5000 - RANK: 3
ID: 40 - NIVEL: 2 - SALARIO: 4000 - RANK: 4
ID: 20 - NIVEL: 2 - SALARIO: 3000 - RANK: 5
PL/SQL procedure successfully completed.
Realizamos el ejemplo anterior en una base de datos 10.1.0.2.0, veremos el mismo resultado que en 9i...
SQL_10g> CREATE PROCEDURE pr_test
2 IS
3 BEGIN
4 --
5 FOR cur IN ( SELECT id, nivel, salario, DENSE_RANK() OVER (PARTITION BY
nivel ORDER BY salario DESC) AS rank
6 FROM test ) LOOP
7 --
8 dbms_output.put_line('ID: '||cur.id||
9 ' - NIVEL: '||cur.nivel||
10 ' - SALARIO: '||cur.salario||
11 ' - RANK: '||cur.rank) ;
12 --
13 END LOOP ;
14 --
15 END pr_test ;
16 /
Procedure created.
SQL_10g> EXEC pr_test
ID: 90 - NIVEL: 1 - SALARIO: 6500 - RANK: 1
ID: 70 - NIVEL: 1 - SALARIO: 5500 - RANK: 2
ID: 50 - NIVEL: 1 - SALARIO: 4500 - RANK: 3
ID: 30 - NIVEL: 1 - SALARIO: 3500 - RANK: 4
ID: 10 - NIVEL: 1 - SALARIO: 2500 - RANK: 5
ID: 100 - NIVEL: 2 - SALARIO: 7000 - RANK: 1
ID: 80 - NIVEL: 2 - SALARIO: 6000 - RANK: 2
ID: 60 - NIVEL: 2 - SALARIO: 5000 - RANK: 3
ID: 40 - NIVEL: 2 - SALARIO: 4000 - RANK: 4
ID: 20 - NIVEL: 2 - SALARIO: 3000 - RANK: 5
PL/SQL procedure successfully completed.
Como pudimos observar, de la versión 9i en adelante, podemos utilizar Analytic Functions tanto en SQL como en PL/SQL.
1 comentario:
Leo que alegría verte en tu blog! espero estés bien.
Te encontré buscando info en google sobre funciones analíticas en tablas particionadas oara oracle 8, y me alegro verificar que la solución que encontré en forma "casera" para trabajar con funciones de cubo en oracle 8 (o sea, meterla dentro de una vista) coincida con tu recomendación.
El problema que tenía es que anduvo muy bien en los tres ambientes de testing y al llegar a producción, la consulta tenía un rendimiento espantoso.
En principio es un insert select donde el select tiene sobre la función de cubo cuatro o cinco consultas pesadas que contienen reglas de negocio; sobre el insert tengo un hint parallel 4 , el 4 lo puse medio a ojo, pero sobre el select que contiene la funcion analitica no tengo ningun hint; ya vi tu consejo que no es necesario hacer ningun hint, pero en este caso ya no sé que inventar para que el insert-select en producción se comporte igual que en los distintos ambientes de testing.
Bueno Leo sigo experimentando, y te mando un fuerte abrazo avellanediano.
Alejandro - TP.
Publicar un comentario