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_fin_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 = 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_vigencia <= to_date('01/' || to_char(sysdate, 'MM/YYYY'), 'DD/MM/YYYY') ) select ps.tag, ps.nu_comercial, ps.nu_comercial_aso, ps.co_prse, 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, 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) from paquetes ps2 where ps2.tag = ps.tag ) PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 5fnqd00pn9ata -------------------- with lineas as ( select /*+ materialize */ 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 /*+ materialize */ 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_fin_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 = 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_vigencia <= to_date('01/' || to_char(sysdate, 'MM/YYYY'), 'DD/MM/YYYY') ) select count(*) from paquetes Plan hash value: 1198046889 ------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | | 1055K(100)| | | | | 1 | TEMP TABLE TRANSFORMATION | | | | | | | | | | 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D854F_D5882CDC | | | | | | | | | 3 | HASH UNIQUE | | 61M| 5602M| 6640M| 1022K (1)| 00:00:40 | | | | 4 | VIEW | VW_ORE_4FEB68FB | 61M| 5602M| | 36339 (7)| 00:00:02 | | | | 5 | UNION-ALL | | | | | | | | | |* 6 | FILTER | | | | | | | | | |* 7 | HASH JOIN RIGHT OUTER | | 61M| 6185M| 127M| 25806 (5)| 00:00:02 | | | | 8 | TABLE ACCESS STORAGE FULL | TE_OGN_ADMIPTV_1 | 3101K| 91M| | 126 (12)| 00:00:01 | | | | 9 | TABLE ACCESS STORAGE FULL | TE_QYPSNCMD | 61M| 4376M| | 10040 (8)| 00:00:01 | | | |* 10 | FILTER | | | | | | | | | |* 11 | HASH JOIN OUTER | | 7394 | 895K| | 10533 (11)| 00:00:01 | | | | 12 | JOIN FILTER CREATE | :BF0000 | 7394 | 671K| | 10400 (11)| 00:00:01 | | | |* 13 | TABLE ACCESS STORAGE FULL | TE_QYPSNCMD | 7394 | 671K| | 10400 (11)| 00:00:01 | | | | 14 | JOIN FILTER USE | :BF0000 | 3101K| 91M| | 126 (12)| 00:00:01 | | | |* 15 | TABLE ACCESS STORAGE FULL | TE_OGN_ADMIPTV_1 | 3101K| 91M| | 126 (12)| 00:00:01 | | | | 16 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D8550_D5882CDC | | | | | | | | | 17 | HASH UNIQUE | | 1 | 298 | | 33364 (16)| 00:00:02 | | | |* 18 | HASH JOIN | | 1 | 298 | | 33363 (16)| 00:00:02 | | | |* 19 | HASH JOIN | | 1 | 189 | | 33349 (16)| 00:00:02 | | | | 20 | JOIN FILTER CREATE | :BF0000 | 1 | 147 | | 20980 (14)| 00:00:01 | | | |* 21 | HASH JOIN | | 1 | 147 | | 20980 (14)| 00:00:01 | | | | 22 | VIEW | VW_SQ_1 | 1 | 51 | | 11254 (20)| 00:00:01 | | | | 23 | HASH GROUP BY | | 1 | 51 | | 11254 (20)| 00:00:01 | | | | 24 | VIEW | | 61M| 2976M| | 9582 (6)| 00:00:01 | | | | 25 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9D854F_D5882CDC | 61M| 5602M| | 9582 (6)| 00:00:01 | | | | 26 | VIEW | | 61M| 5602M| | 9582 (6)| 00:00:01 | | | | 27 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9D854F_D5882CDC | 61M| 5602M| | 9582 (6)| 00:00:01 | | | | 28 | JOIN FILTER USE | :BF0000 | 48M| 1937M| | 12256 (19)| 00:00:01 | | | | 29 | PARTITION RANGE ALL | | 48M| 1937M| | 12256 (19)| 00:00:01 | 1 | 121 | |* 30 | TABLE ACCESS STORAGE FULL | TE_QYPSCOMD | 48M| 1937M| | 12256 (19)| 00:00:01 | 1 | 121 | | 31 | VIEW | VI_REF_TV_PAQUETES_EQU | 1240 | 131K| | 14 (29)| 00:00:01 | | | | 32 | SORT UNIQUE | | 1240 | 177K| | 14 (29)| 00:00:01 | | | | 33 | UNION-ALL | | | | | | | | | | 34 | TABLE ACCESS STORAGE FULL | REF_TV_PAQUETES | 914 | 63980 | | 2 (0)| 00:00:01 | | | |* 35 | HASH JOIN | | 163 | 13366 | | 4 (0)| 00:00:01 | | | | 36 | TABLE ACCESS STORAGE FULL | REF_TV_PS_EQUIVALENTES | 162 | 1944 | | 2 (0)| 00:00:01 | | | | 37 | TABLE ACCESS STORAGE FULL | REF_TV_PAQUETES | 914 | 63980 | | 2 (0)| 00:00:01 | | | |* 38 | HASH JOIN | | 163 | 13366 | | 4 (0)| 00:00:01 | | | | 39 | TABLE ACCESS STORAGE FULL | REF_TV_PS_EQUIVALENTES | 162 | 1944 | | 2 (0)| 00:00:01 | | | | 40 | TABLE ACCESS STORAGE FULL | REF_TV_PAQUETES | 914 | 63980 | | 2 (0)| 00:00:01 | | | | 41 | SORT AGGREGATE | | 1 | | | | | | | | 42 | VIEW | | 299K| | | 144 (4)| 00:00:01 | | | | 43 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9D8550_D5882CDC | 299K| 75M| | 144 (4)| 00:00:01 | | | ------------------------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 6 - filter(CASE WHEN ROWID IS NOT NULL THEN DECODE("COD_APLIPROV",'T00',1,0) ELSE NULL END IS NULL) 7 - access("NUM_ADMINISTRATIVO"=TRIM("P"."NU_COMERCIAL")) 10 - filter((CASE WHEN ROWID IS NOT NULL THEN DECODE("COD_APLIPROV",'T00',1,0) ELSE NULL END =0 AND LNNVL(CASE WHEN ROWID IS NOT NULL THEN DECODE("COD_APLIPROV",'T00',1,0) ELSE NULL END IS NULL))) 11 - access("NUM_ADMINISTRATIVO"=TRIM("P"."NU_COMERCIAL")) 13 - storage(("P"."CO_TP_NU_COMER_ASO"='18' AND "P"."CO_UNI_SERVIC_ASO"='00000229' AND "P"."CO_TP_NU_COMER"='02' AND INTERNAL_FUNCTION("P"."CO_TIPO_RELA"))) filter(("P"."CO_TP_NU_COMER_ASO"='18' AND "P"."CO_UNI_SERVIC_ASO"='00000229' AND "P"."CO_TP_NU_COMER"='02' AND INTERNAL_FUNCTION("P"."CO_TIPO_RELA"))) 15 - storage(SYS_OP_BLOOM_FILTER(:BF0000,"NUM_ADMINISTRATIVO")) filter(SYS_OP_BLOOM_FILTER(:BF0000,"NUM_ADMINISTRATIVO")) 18 - 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")) 19 - access("PSNC"."NU_SECUE_PSCO_ASO"="PSCO"."NU_SECUE_PSCO_PADR") 21 - access("PSNC"."FX_FIN_VIGENCIA"="MAX(L.FX_FIN_VIGENCIA)" AND "ITEM_1"="PSNC"."NU_COMERCIAL") 30 - storage(("PSCO"."FX_INI_VIGENCIA"<=TO_DATE('01/'||TO_CHAR(SYSDATE@!,'MM/YYYY'),'DD/MM/YYYY') AND SYS_OP_BLOOM_FILTER(:BF0000,"PSCO"."NU_SECUE_PSCO_PADR"))) filter(("PSCO"."FX_INI_VIGENCIA"<=TO_DATE('01/'||TO_CHAR(SYSDATE@!,'MM/YYYY'),'DD/MM/YYYY') AND SYS_OP_BLOOM_FILTER(:BF0000,"PSCO"."NU_SECUE_PSCO_PADR"))) 35 - access("P2"."CO_EQUIPO"="E1"."CO_EQUIPO_EQU") 38 - access("P3"."CO_EQUIPO"="E2"."CO_EQUIPO") 103 filas seleccionadas.