Como evitar join 2000 veces

Hace un par de semanas se me quejaron los usuarios que les iba a pedales la aplicacion, un ERP que lee datos de otro CRM.

Esta era la consulta:

 
SELECT COUNT(*)
FROM VSA_PARTIES_V PARTIES,
     VGIA_SOLICITUDES_V SOLICITUDES
WHERE PARTIES.CUST_ACCOUNT_ID = SOLICITUDES.CLIENTE_ID
AND   PARTIES.JGZZ_FISCAL_CODE = :b1
AND SOLICITUDES.SOL_ESTADO NOT IN ('Cerrada','Anulada' )

15   14  NESTED LOOPS (Cost=19 Card=1 Bytes=61)
16   15    VIEW OF ‘VSA_PARTIES_V’ (Cost=13 Card=2 Bytes=50)
17   16      SORT (GROUP BY) (Cost=13 Card=2 Bytes=200)
18   17        FILTER
19   18          NESTED LOOPS (OUTER)
20   19            NESTED LOOPS (Cost=7 Card=2 Bytes=176)
21   20              TABLE ACCESS (BY INDEX ROWID) OF ‘HZ_PARTIES’ (Cost=4 Card=1 Bytes=65)
22   21                INDEX (RANGE SCAN) OF ‘HZ_PARTIES_N10' (NON-UNIQUE) (Cost=3 Card=1)
23   20              TABLE ACCESS (BY INDEX ROWID) OF ‘CS_INCIDENTS_ALL_B’ (Cost=3 Card=707915 Bytes=16282045)
24   23                INDEX (RANGE SCAN) OF ‘CS_INCIDENTS_N2' (NON-UNIQUE) (Cost=2 Card=707915)
25   19            TABLE ACCESS (BY INDEX ROWID) OF ‘HZ_CUST_ACCOUNTS’ (Cost=2 Card=2447425 Bytes=29369100)
26   25              INDEX (UNIQUE SCAN) OF ‘HZ_CUST_ACCOUNTS_U1' (UNIQUE) (Cost=1 Card=2447425)
27   15    TABLE ACCESS (BY INDEX ROWID) OF ‘CS_INCIDENTS_ALL_B’ (Cost=3 Card=707915 Bytes=25484940)

Las dos tablas del from son en efecto vistas, bastante complejas que no voy a colocar las definicion aqui porque ocupan mazo de sitio. El problema estaba en el paso 15. La condicion de join.

PARTIES.CUST_ACCOUNT_ID = SOLICITUDES.CLIENTE_ID

Resulta que cuando el result set de la vista VSA_PARTIES_V (devuelve 2000 registros) hace join con la vista de VGIA_SOLICITUDES_V, el valor de PARTIES.CUST_ACCOUNT_ID no varia en los 2000 registros, es decir que hace la join con Nested Loop 2000 veces con el mismo CUST_ACCOUNT_ID. Cada busqueda por Nested Loop eran 70000 consistent gets, la query en si leia 140 millones de consistent gets y tardaba 1 hora aproximadamente. Consegui reducir a 1.5 segundos, reduciendo de 2000 busquedas a una con funciones analiticas.

 
SELECT COUNT (*)
  FROM (SELECT ROW_NUMBER () OVER (PARTITION BY party_id, jgzz_fiscal_code,
                                                party_name, cust_account_id, orig_system_reference,
                                                codigo_empresa ORDER BY jgzz_fiscal_code) rn,
               x.*
          FROM vsa_parties_v x) parties,
       apps.vgia_solicitudes_v solicitudes
 WHERE parties.cust_account_id = solicitudes.cliente_id
   AND parties.jgzz_fiscal_code = :b1
   AND solicitudes.sol_estado NOT IN (’cerrada’, ‘anulada’)
   AND rn = 1

Trabajando con las vistas hay que tener mucho cuidado con el uso de ROWNUM, Funciones de Agrupacion y Funciones Analiticas por posible reescritura de Query por Oracle (complex view merging, push predicates etc.). La otra solucion era modificar la definicion de vista para que haga un distinct CUST_ACCOUNT_ID sin embargo la opcion no era viable porque yo no desarrolle la aplicacion y no sabia en donde mas se utilizaba estas vistas, cambiando la vista podria afectar a otras funcionalidades.

One thought on “Como evitar join 2000 veces

  1. Karla

    Hola ….quisiera preguntarte del uso del OVER (PARTITION BY ), ya que yo solo se usar el gruop by …y no se bien cual es la diferencia, y que beneficios tiene este primero,…..

    Gracias por tu ayuda

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s