SQL> with lineas as ( select distinct p.nu_comercial, p.nu_comercial_aso, p.nu_secue_psco_aso, p.co_client_com, p.fx_ini_vigencia, p.fx_fin_vigencia from te_qypsncmd p left outer join ( select num_administrativo, es_telco from aux_telco_admin ) t on trim(p.nu_comercial) = t.num_administrativo where p.co_tp_nu_comer_aso = '18' and p.co_tp_nu_comer = '02' and p.co_uni_servic_aso = '00000229' and p.co_tipo_rela in ('SP', 'HE') and (t.es_telco = 0 OR t.es_telco is null) ), paquetes as ( select distinct trim(psnc.nu_comercial)||'|'||r.co_equipo tag, psnc.nu_comercial, psnc.nu_comercial_aso, psco.co_prse, psco.nu_contratabl, r.descripcion, r.co_equipo, psco.co_reg_comerc, r.des_reg_co, psco.co_cli_tarifa, psnc.co_client_com, psco.nu_secue_psco_padr, psnc.nu_secue_psco_aso, psnc.fx_ini_vigencia fx_ini_vig_psnc, psnc.fx_fin_vigencia fx_fin_vig_psnc, psco.fx_ini_vigencia fx_ini_vig_psco, psco.fx_f 2 in_vigencia fx_fin_vig_psco from lineas psnc, te_qypscomd psco, vi_ref_tv_paquetes_equ r where psnc.fx_fin_vigencia = ( select max(l.fx_fin_vigencia) from lineas l where l.nu_comercial = psnc.nu_comercial ) and psnc.nu_secue_psco_aso 3 = psco.nu_secue_psco_padr and psco.co_prse = r.co_prse and psco.nu_contratabl = r.nu_contratabl and psco.co_reg_comerc = r.co_reg_co and psco.fx_ini_v 4 igencia <= to_date('01/' || to_char(sysdate, 'MM/YYYY'), 'DD/MM/YYYY') ) select ps.tag, ps.nu_comercial, ps.nu_comercial_aso, ps.co_prs 5 e, ps.nu_contratabl, ps.descripcion, ps.co_equipo, ps.co_reg_comerc, ps.des_reg_co, ps.co_cli_tarifa, ps.co_client_com, ps.nu_secue_psco_padr, ps.nu_secue_psco_aso, 6 ps.fx_ini_vig_psnc, ps.fx_fin_vig_psnc, ps.fx_ini_vig_psco, ps.fx_fin_vig_psco from paquetes ps where ps.fx_fin_vig_psco = ( select max(ps2.fx_fin_vig_psco) 7 from paquetes ps2 where ps2.tag = ps.tag ) 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 / 975574 filas seleccionadas. Transcurrido: 00:00:38.93 Plan de Ejecucion ---------------------------------------------------------- Plan hash value: 2385804196 ------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 361 | | 23085 (16)| 00:00:01 | | | | 1 | TEMP TABLE TRANSFORMATION | | | | | | | | | | 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D85DD_D5882CDC | | | | | | | | | 3 | HASH UNIQUE | | 7394 | 895K| 1000K| 10687 (11)| 00:00:01 | | | |* 4 | FILTER | | | | | | | | | |* 5 | HASH JOIN OUTER | | 7394 | 895K| | 10533 (11)| 00:00:01 | | | |* 6 | TABLE ACCESS STORAGE FULL | TE_QYPSNCMD | 7394 | 671K| | 10400 (11)| 00:00:01 | | | | 7 | TABLE ACCESS STORAGE FULL | TE_OGN_ADMIPTV_1 | 3101K| 91M| | 126 (12)| 00:00:01 | | | | 8 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D85DE_D5882CDC | | | | | | | | | 9 | HASH UNIQUE | | 1 | 256 | | 12393 (20)| 00:00:01 | | | |* 10 | HASH JOIN | | 1 | 256 | | 12392 (20)| 00:00:01 | | | |* 11 | HASH JOIN | | 1 | 147 | | 12377 (20)| 00:00:01 | | | |* 12 | HASH JOIN | | 1 | 105 | | 8 (25)| 00:00:01 | | | | 13 | VIEW | VW_SQ_1 | 7393 | 216K| | 4 (25)| 00:00:01 | | | | 14 | SORT GROUP BY | | 7393 | 216K| | 4 (25)| 00:00:01 | | | | 15 | VIEW | | 7394 | 216K| | 3 (0)| 00:00:01 | | | | 16 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9D85DD_D5882CDC | 7394 | 541K| | 3 (0)| 00:00:01 | | | | 17 | VIEW | | 7394 | 541K| | 3 (0)| 00:00:01 | | | | 18 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9D85DD_D5882CDC | 7394 | 541K| | 3 (0)| 00:00:01 | | | | 19 | PARTITION RANGE ALL | | 48M| 1937M| | 12256 (19)| 00:00:01 | 1 | 121 | |* 20 | TABLE ACCESS STORAGE FULL | TE_QYPSCOMD | 48M| 1937M| | 12256 (19)| 00:00:01 | 1 | 121 | | 21 | VIEW | VI_REF_TV_PAQUETES_EQU | 1240 | 131K| | 14 (29)| 00:00:01 | | | | 22 | SORT UNIQUE | | 1240 | 90712 | | 14 (86)| 00:00:01 | | | | 23 | UNION-ALL | | | | | | | | | | 24 | TABLE ACCESS STORAGE FULL | REF_TV_PAQUETES | 914 | 63980 | | 2 (0)| 00:00:01 | | | |* 25 | HASH JOIN | | 163 | 13366 | | 5 (20)| 00:00:01 | | | | 26 | TABLE ACCESS STORAGE FULL | REF_TV_PS_EQUIVALENTES | 162 | 1944 | | 2 (0)| 00:00:01 | | | | 27 | TABLE ACCESS STORAGE FULL | REF_TV_PAQUETES | 914 | 63980 | | 2 (0)| 00:00:01 | | | |* 28 | HASH JOIN | | 163 | 13366 | | 5 (20)| 00:00:01 | | | | 29 | TABLE ACCESS STORAGE FULL | REF_TV_PS_EQUIVALENTES | 162 | 1944 | | 2 (0)| 00:00:01 | | | | 30 | TABLE ACCESS STORAGE FULL | REF_TV_PAQUETES | 914 | 63980 | | 2 (0)| 00:00:01 | | | |* 31 | HASH JOIN | | 1 | 361 | | 6 (34)| 00:00:01 | | | | 32 | VIEW | VW_SQ_2 | 1 | 54 | | 3 (34)| 00:00:01 | | | | 33 | HASH GROUP BY | | 1 | 54 | | 3 (34)| 00:00:01 | | | | 34 | VIEW | | 1 | 54 | | 2 (0)| 00:00:01 | | | | 35 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9D85DE_D5882CDC | 1 | 245 | | 2 (0)| 00:00:01 | | | | 36 | VIEW | | 1 | 307 | | 2 (0)| 00:00:01 | | | | 37 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9D85DE_D5882CDC | 1 | 245 | | 2 (0)| 00:00:01 | | | ------------------------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter(CASE WHEN ROWID IS NOT NULL THEN DECODE("COD_APLIPROV",'T00',1,0) ELSE NULL END =0 OR CASE WHEN ROWID IS NOT NULL THEN DECODE("COD_APLIPROV",'T00',1,0) ELSE NULL END IS NULL) 5 - access("NUM_ADMINISTRATIVO"(+)=TRIM("P"."NU_COMERCIAL")) 6 - storage("P"."CO_TP_NU_COMER_ASO"='18' AND "P"."CO_UNI_SERVIC_ASO"='00000229' AND "P"."CO_TP_NU_COMER"='02' AND ("P"."CO_TIPO_RELA"='HE' OR "P"."CO_TIPO_RELA"='SP')) filter("P"."CO_TP_NU_COMER_ASO"='18' AND "P"."CO_UNI_SERVIC_ASO"='00000229' AND "P"."CO_TP_NU_COMER"='02' AND ("P"."CO_TIPO_RELA"='HE' OR "P"."CO_TIPO_RELA"='SP')) 10 - access("PSCO"."CO_PRSE"="R"."CO_PRSE" AND "PSCO"."NU_CONTRATABL"="R"."NU_CONTRATABL" AND "R"."CO_REG_CO"=TO_NUMBER("PSCO"."CO_REG_COMERC")) 11 - access("PSNC"."NU_SECUE_PSCO_ASO"="PSCO"."NU_SECUE_PSCO_PADR") 12 - access("PSNC"."FX_FIN_VIGENCIA"="MAX(L.FX_FIN_VIGENCIA)" AND "ITEM_0"="PSNC"."NU_COMERCIAL") 20 - storage("PSCO"."FX_INI_VIGENCIA"<=TO_DATE('01/'||TO_CHAR(SYSDATE@!,'MM/YYYY'),'DD/MM/YYYY')) filter("PSCO"."FX_INI_VIGENCIA"<=TO_DATE('01/'||TO_CHAR(SYSDATE@!,'MM/YYYY'),'DD/MM/YYYY')) 25 - access("P2"."CO_EQUIPO"="E1"."CO_EQUIPO_EQU") 28 - access("P3"."CO_EQUIPO"="E2"."CO_EQUIPO") 31 - access("PS"."FX_FIN_VIG_PSCO"="MAX(PS2.FX_FIN_VIG_PSCO)" AND "ITEM_1"="PS"."TAG") Estadisticas ---------------------------------------------------------- 53 recursive calls 2 db block gets 1696704 consistent gets 1696463 physical reads 420 redo size 135224876 bytes sent via SQL*Net to client 718997 bytes received via SQL*Net from client 65040 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 975574 rows processed