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.