Consultas SQL con la utilidad OVER(PARTITION BY)

Se han preguntado cómo puedo obtener el porcentaje de una fila con relación al gran total?

Bueno, para esto podemos utilizar varias técnicas como realizar sub-consultas, realizar un proceso que llene una temporal y realice los cálculos, pero existe una forma muy sencilla y no compleja de usar que es el comando OVER.

Con el comando OVER podemos obtener resultados de funciones de grupo de una determinada partición de datos.

Como podemos usar el comando OVER?

Para contestar esta pregunta mejor veamos un ejemplo:

EJEMPLO USANDO EL COMANDO OVER

Tenemos información de almacenes vs ventas y deseamos obtener la siguiente información: Almacen, ventas, cantidades y % de Ventas con relación al total.

ALMACEN_ID	VALOR_VENTAS	CANTIDAD_VENTAS	% VENTAS
ALM_30	150.000	15	33,33%
ALM_10	100.000	10	22,22%
ALM_20	200.000	20	44,44%
TOTAL	450.000	45	100,00%

Ahora veamos cómo podemos obtener esta información desde una consulta SQL.

• Creamos la tabla
  
  Create Table Almacenes_Ventas
  (
  Almacen_IdVarchar2 (10),
  Valor_Ventas Number,
  Cantidad_Ventas Number
  )
  
• Insertamos los datos
  
  Insert Into Almacenes_Ventas
  (Almacen_Id,Valor_Ventas, Cantidad_Ventas)
  Values ('ALM_30', 150000, 15);
  Insert Into Almacenes_Ventas
  (Almacen_Id, Valor_Ventas, Cantidad_Ventas)
  Values('ALM_10', 100000, 10);
  Insert Into Almacenes_Ventas
  (Almacen_Id,Valor_Ventas, Cantidad_Ventas)
  Values ('ALM_20',200000, 20);
  Commit;
  
• Realizamos la consulta
  
  Select v.*,
  v.valor_ventas/sum(valor_ventas)over()*100"% Ventas",
  sum(valor_ventas)over()gran_total
  
  From Almacenes_Ventas V
  

    --Resultado

ALMACEN_ID	VALOR_VENTAS	CANTIDAD_VENTAS	% Ventas	GRAN_TOTAL
ALM_30	150.000	15	33,33333333	450.000
ALM_10	100.000	10	22,22222222	450.000
ALM_20	200.000	20	44,44444444	450.000

Vemos que la columna GRAN_TOTAL de la consulta, es el mismo valor en las tres filas ya que indicamos por medio del OVER que es la suma del total de registros en el campo VALOR_VENTAS. Ya con lo definido en con el OVER podemos obtener el campo "% Ventas".

Fíjense que aunque usamos la función de grupo SUM no estamos usando declaración GROUP BY en nuestra consulta SQL.

EJEMPLO USAANDO EL COMANDO OVER (PARTITION BY)

Al ejemplo anterior agreguemos un campo periodo para tener las ventas de los almacenes por mes.

PERIODO	ALMACEN_ID	VALOR_VTA	% VTA PERIODO	% VTA
201101	ALM_10	10.000	22,22%	9,52%
201101	ALM_20	15.000	33,33%	14,29%
201101	ALM_30	20.000	44,44%	19,05%
Total 201101		45.000	100,00%	42,86%
201102	ALM_10	20.000	33,33%	19,05%
201102	ALM_20	40.000	66,67%	38,10%
Total 201102		60.000	100,00%	57,14%
GRAN TOTAL		105.000		100,00%

Ahora vemos que la información que necesitamos mostrar es más compleja. "% VTA Periodo" es el porcentaje de ventas con relación al total del periodo y "% VTA" es el % del total de las ventas.

Ahora veamos como lo podemos resolver con una consulta SQL.

• Creamos la tabla
  
  Create Table Almacenes_Ventas_Periodo
  (
  Periodo varchar2(6),
  Almacen_Id Varchar2 (10),
  Valor_Ventas Number,
  Cantidad_Ventas Number
  )
  
• Insertamos los registros
  
  Insert into ALMACENES_VENTAS_PERIODO
  (PERIODO,ALMACEN_ID, VALOR_VENTAS, CANTIDAD_VENTAS)
  Values ('201101', 'ALM_30', 20000, 30);
  Insert into  ALMACENES_VENTAS_PERIODO
  (PERIODO, ALMACEN_ID, VALOR_VENTAS, CANTIDAD_VENTAS)
  Values ('201101','ALM_10', 10000, 10);
  Insert into ALMACENES_VENTAS_PERIODO
  (PERIODO, ALMACEN_ID, VALOR_VENTAS, CANTIDAD_VENTAS)
  Values ('201101','ALM_20', 15000,20);
  Insert into ALMACENES_VENTAS_PERIODO
  (PERIODO, ALMACEN_ID, VALOR_VENTAS, CANTIDAD_VENTAS)
  Values ('201102','ALM_10', 20000, 20);
  Insert into ALMACENES_VENTAS_PERIODO
  (PERIODO, ALMACEN_ID, VALOR_VENTAS, CANTIDAD_VENTAS)
  Values ('201102','ALM_20', 40000, 40);
  COMMIT;
  
• Realizamos la consulta
  
  SelectV.*,
  Round(Valor_Ventas/Sum(Valor_Ventas) Over (Partition By Periodo) * 100, 2)
  "%Periodo",
  Round(Valor_Ventas/Sum(Valor_Ventas) Over() * 100, 2) "%Total",
  Sum(Valor_Ventas) Over (Partition By Periodo) Periodo_Total,
  Sum(Valor_Ventas) Over () Gran_Total
  From Almacenes_Ventas_Periodo V
  Order By Periodo, Almacen_Id
  
  --Resultado
  

PERIODO	ALMACEN_ID	VALOR_VENTAS	%Periodo	%Total	PERIODO_TOTAL	GRAN_TOTAL
201101	ALM_10	10000	22,22	9,52	45.000	105.000
201101	ALM_20	15000	33,33	14,29	45.000	105.000
201101	ALM_30	20000	44,44	19,05	45.000	105.000
201102	ALM_10	20000	33,33	19,05	60.000	105.000
201102	ALM_20	40000	66,67	38,1	60.000	105.000

 El campo GRAN_TOTAL es el total de todos los registros del campo VALOR_VENTAS y PERIODO_TOTAL es el total del campo VALOR_VENTAS pero de la agrupación PERIODO. Aquí vemos que agregamos al OVER la clausula PARTITION BY que no indica sobre que partición de datos deseamos realizar la operación de grupo (SUM, AVG, MAX, MIN, ETC.).

Para obtener la fila "%Periodo" debemos dividir VALOR_VENTAS para el total de ventas del grupo periodo "Sum(Valor_Ventas) Over (Partition By Periodo)" y oara obtener el "%Total" debemos dividir VALOR_VENTAS para el gran total "Sum (Valor_Ventas)Over ()".

CONCLUSION:

Es claro que la utilidad OVER nos ayuda a simplificar la generación de consultas complejas y elaborar mejor nuestros cubos de datos. Solo hay que ponerle cabeza para utilizar de la mejor manera estas clausulas.

ORA-02354: error in exporting/importing data

Saludos, este blog tiene como objetivo explicar el error presentado el momento de realizar imports con la utilidad Imp. El error presentado se los muestro a continuación:

ORA-02354: error in exporting/importing data
ORA-39776:
ORA-00600: internal error code, arguments: [klaevcnulcolnum], [], [], [], [], [], [], []
ORA-31693: 

Consultando en el metalink de Oracle esto es un error típico para las veresiones 10gR2 e infereiores, y no existe un parche para solventar este error. El origen del error es por la posible conversión de caracteres que se genera al realizar la importación.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)
Note: grants on tables/views/sequences/roles will not be exported
About to export specified users ...

Como observamos en el mensaje del import nos indica que hay una posible conversión.

Pero la buena noticia es que para versiones 11gR2 ya esta solucionado, pero eso quiere decir que debemos cambiarnos a la versión 11g para que se solucione el problema?

La respuesta es no, para solucionarlo solo debemos de generar de nuevo el export, pero antes debemos setear la variable de ambiente NLS_LANG con el juego de caracteres que tiene la base de datos origen para evitar la conversión durante la importación.

SET NLS_LANG= AMERICAN_AMERICA.WE8MSWIN1252
EXP '/ as sysdba' FILE='MI_EXPORT.DMP' LOG='MI_EXPORT_LOG.LOG' CONSISTENT=Y OWNER=SISOWN

Ahora si, ya podemos realizar la importación sin problemas

ora-06508: PL/SQL: could not find program unit being called

Este error se presenta si se realizan cambios en los objetos de base de datos, procedimientos almacenados y sobre todo si estos procedimientos u objetos tiene relación de dependencia por otros objetos.


Cuando se realiza una modificación en cualquier objeto de base de datos, Oracle automáticamente invalida el objeto en la librería de objetos y si existen sesiones de usuarios trabajando con estos objetos, a estas sesiones se le presentara mensaje de error de objetos invalidos.


Aunque durante esta modificación de tabla, vista, sequencia, procedicmiento, etc no quede invalido podría aun persistir su invelidez en la librería de objetos de la sesion de los usuarios. Que podriamos hacer para que esta libreria se actualice?


1.- Lo que primero que debemos hacer es confirmar que no existan objetos invalidos.
--Revisión de objetos invalidos de un esquema en específico.
--Si deseamos de culquier esquema solo debemos quitar la condición OWNER
select object_type,count(*) from all_objects
where owner='SISOWN'
and status = 'INVALID'
group by object_type;


2.- Si existen objetos invalidos debemos de recompilarlos y luego realizar de nuevo el paso 1 para comprobar si no existen objetos discompilados. Si aun  siguen descompilados habra que revisar la lógica dentro del código o definición del objeto para ver el error.
--Utilidad del DBM_UTILITY para la recompilación de los objetos de un esquema.
call dbms_utility.compile_schema('SISOWN')


3.-Si ya no existen y siguen presentadose errores debemos de vaciar la SHARED_POOL de la SGA. Con esto logramos liberar la memoria de la librería de objetos y la libreria SQL
--Vaciado de la SHARED_POOL
alter system flush shared_pool;

Para entender mejor a continuación una gráfica de la arquitectura del Oracle Database Server donde revisaremos el componente SHARED_POOL de la SGA. No voy a explcar detenidamente cada uno de los componentes pero si el que se vió afectado.

Estructura del Oracle Server Database

Veamos los compnntes de la SHARED POOL que son los que mas tiene relación con los problemas de descompilación.

Me ha pasado que aun no existiendo objetos descompilados y no se han realizado alteraciones en la base de datos  se siguen presentando en ciertos momentos errores de ora-06508: PL/SQL: could not find program unit being called. Este error deja de aparecer realizado un vaciado de la SHARED_POOL(alter system flush shared_pool) pero es una solución momentanea.

Una solución momentanea fué programar un job para que realice un vaciado de la SHARED_POOL cada 10 minutos (Es como si tuvieramos una gotera y lo que hacemos es poner un recipiente para que no se riegue en el piso, pero lo que realmente tenemos que hacer es sellar los huecos por donde hay filtraciones). Esto no es era una solución coherente por lo que se investigó en el metalink de oracle en donde describian dos posibles causas:


CAUSA 1.
Shared pool se esta quedando corta en la SGA. Esto es por la administración automática de la SAGA no esta asignado valores correctos a los diferentes componentes de memoria po lo que hay que cambiar a gestión manual.

Recomendaciones:
Aumentara el tamaño de la shared pool y configurar el parametro de base de datos¨"shared pool reserved size" a un 10% al tamaño asignado a la shared pool.

Aciones:
Se aumento a 5gb el tamaño para la shared pool
alter system set shared_pool_size=5G  scope=BOTH;


Se deja el 10% de 5G es  decir 500Mb para reservar la shared pool.Con esto se logra que cuando el tamaño disponible de los 5G llegue a 500Mb la shared pool se autolibere y no realizar un flush a la shared pool.
alter system set shared_pool_reserved_size=500M scope=both;


CAUSA 2
Existen diferencias en la fecha de compilación en los objetos relacionados. Adjunto la consulta mágica que nos lo dirá.
select
 do.name dname,
 od.object_type dtipoob ,
 po.name pname,
 op.object_type ptipoob,
 p_timestamp,
 po.stime p_stime,
 do.owner# do_owner_id,
 po.owner# po_owner_id
from    sys.obj$ do,
  sys.dependency$ d,
  sys.obj$ po,
  all_objects od,
  all_objects op
where p_obj#=po.obj#(+)
and d_obj#=do.obj#
and do.status=1 /*dependent is valid*/
and po.status=1 /*parent is valid*/
and po.stime!=p_timestamp /*parent timestamp does not match*/
and do.type# not in (28,29,30) /*dependent type is not java*/
and po.type# not in (28,29,30) /*parent type is not java*/
and do.OBJ#=od.OBJECT_ID
and po.OBJ#=op.OBJECT_ID
  --and do.owner#=845
order by 2,1;

Recomendación:
Recompilar todos los objetos que estan en el lado DNAME y realizar la consulta. Repetir este proceso hasta que la consulta no de mas resultados.

Acciones:
Se realizó script para la recompilación de los objetos que aparecen en la subconsulta. Se filtran solo los objetos del esquema SISOWN.
begin
    for v in (
      select do.name dname, od.object_type dtipoob , po.name pname, op.object_type ptipoob, p_timestamp, po.stime p_stime
      from sys.obj$ do, sys.dependency$ d, sys.obj$ po,
      all_objects od, all_objects op
      where p_obj#=po.obj#(+)
      and d_obj#=do.obj#
      and do.status=1 /*dependent is valid*/
      and po.status=1 /*parent is valid*/
      and po.stime!=p_timestamp /*parent timestamp does not match*/
      and do.type# not in (28,29,30) /*dependent type is not java*/
      and po.type# not in (28,29,30) /*parent type is not java*/
      and do.owner# in (select USER_ID from dba_users where username in ('SISOWN'))
      and do.OBJ#=od.OBJECT_ID
      and po.OBJ#=op.OBJECT_ID
      order by 2,1
    )loop
        case
        when v.dtipoob like 'PACKAGE%' then
            execute immediate 'alter package '||v.dname||' compile';
        when v.dtipoob like 'PROCEDURE%' then
            execute immediate 'alter procedure '||v.dname||' compile';
        when v.dtipoob like 'FUNCTION%' then
            execute immediate 'alter function '||v.dname||' compile';
        when v.dtipoob like 'VIEW%' then
            execute immediate 'alter view '||v.dname||' compile';
        else
            dbms_output.put_line(v.dname);
        end case;
    end loop;
end;

De las dos causas expuesta aplicando la segunda fue la que funciono.

Ya con esto implementamos como política que todo pase a producción que intervenga alteraciones en la base debe de realizarse las revisiones de diferencias en fechas.

Espero que esto les sirva para solucionar los problemas que se me presentaron.

Sentencia WITH para simplicar SQL complejos

Las consultas SQL complejas que usan subquerys pueden ejecutarse de manera mas rapida con el uso de tablas globales temporales.

Desde las versiones de Oracle 9i R2 ya se implemento una utilidad que ayuda a simplificar las subconsultas complejas con el comando WITH. El comando WITH es una utilidad que materializa las subconsultas(Genera una temporal)

Consideraciones:

• Se recomienda su uso si la subconsulta se ejecuta varias veces
• Se crea automaticamente una temporal
• No funciona en consultas recursivas

Estructura:
WITH
   nommbre_subconsulta
AS
  (El sql que se materializará)
SELECT
  (SQL en el que se usará la subconsulta);

 

Veamos el siguiente ejemplo:



Modelo ER

 Tenemos la tabla de clientes y la tabla de ventas, deseamos obtener por cliente el total vendido, cantidad de facuras, total de montos pendientes, cantidad de facturas pendientes y que el promedio de ventas sea mayor a 15000.

SQL implementado con subconsultas
Select C.Cliente_Id,C.Nombre_Completo,
(Select Sum(V.Capital) From  Ventas V Where V.Cliente_Id=C.Cliente_Id) Monto_Ventas,
(Select Count(*) From  Ventas V Where V.Cliente_Id=C.Cliente_Id) Cantidad_Ventas,
(Select Avg(V.Capital) From  Ventas V Where V.Cliente_Id=C.Cliente_Id) Promedio_Ventas,
(Select Sum(V.Capital) From  Ventas V Where V.Cliente_Id=C.Cliente_Id And Pagada='N') Monto_Pendiente,
(Select Count(*) From  Ventas V Where V.Cliente_Id=C.Cliente_Id And Pagada='N') Cantidad_Pendiente
From Clientes C
Where (Select Avg(V.Capital) From  Ventas V Where V.Cliente_Id=C.Cliente_Id)>15000;



Tiempo y resultado de la consulta



Ahora vemos el ejemplo con la clausula WITH

With T_Ventas --Nombre de la subconsulta
As( --Seccion de la subconsulta
    Select  V.Cliente_Id,
            Sum(V.Capital)  Monto_Ventas,
            Count(*)        Cantidad_Ventas,
            Avg(V.Capital)  Promedio_Ventas,
            Sum(V.Capital)  Monto_Pendiente,
            Count(*)        Cantidad_Pendiente   
    From Ventas V
    Group By V.Cliente_Id
)
--Sección en donde hacemos uso de la subconsulta
Select  C.Cliente_Id,
        C.Nombre_Completo,
        Vt.Monto_Ventas,
        Vt.Cantidad_Ventas,
        Vt.Promedio_Ventas,
        Vt.Monto_Pendiente,
Vt.Cantidad_Pendiente
From Clientes C
Inner Join T_Ventas Vt On C.Cliente_Id=Vt.Cliente_Id
Where Vt.Promedio_Ventas>15000;

Tiempo y resultado de la consulta con clausula WITH

Lo único nuevo es el uso de la cláusula WITH por que de ahí el join y condición con T_Ventas se maneja como si fuera una tabla.

Conculsión:
Con la introducción de la cláusula WITH simplificamos el uso de consultas SQL que usan subconsultas, ademas de que los tiempos de respuesta de la consulta son mejores con WITH que con el uso de subsonsultas.