Problems with very slow QUERY
From: EliasFigueroa <Eliasfimo_at_gmail.com>
Date: Wed, 27 Aug 2008 13:49:55 -0700 (PDT)
Message-ID: <f519b589-596b-4e43-9dba-7577aee7b2a0@x35g2000hsb.googlegroups.com>
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
OBJECT_INSTANCE OBJECT_TYPE OPTIMIZER ID --------------- ------------- --------------
Date: Wed, 27 Aug 2008 13:49:55 -0700 (PDT)
Message-ID: <f519b589-596b-4e43-9dba-7577aee7b2a0@x35g2000hsb.googlegroups.com>
How to improve THIS QUERY is very slow
/
*------------------------------------------------------------------------------------------------------------------------------------ */ OPERATION OPTIONS OBJECT_NAME ----------------------------------------------------- SELECT STATEMENT SORT GROUP BY
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID REG_TMPTRAFICO INDEX RANGE SCAN INDEX_PERIODO TABLE ACCESS BY INDEX ROWID ZONAL INDEX UNIQUE SCAN ZONALIX_CODZONATIS TABLE ACCESS BY INDEX ROWID SEGMENTO INDEX RANGE SCAN INSEG2 TABLE ACCESS BY INDEX ROWID SUBTIPOPC INDEX RANGE SCAN IXNSUBTIPO
/
*-------------------------------------------------------------------------------------------------------------------*/
OBJECT_INSTANCE OBJECT_TYPE OPTIMIZER ID --------------- ------------- --------------
CHOOSE 0 1 2 3 4 3 5 NON-UNIQUE 6 6 ANALYZED 7 UNIQUE 8 5 ANALYZED 9 NON-UNIQUE 10 4 ANALYZED 11 NON-UNIQUE ANALYZED 12 SELECT tab2.cliente, tab2.segmento, tab2.servicio, tab2.trafico, tab2.operador, tab2.zonal, tab2.horario, tab2.Denominacion, lpad(to_char(count(*)),15,0)llamadas, lpad(to_char(sum(substr(tab2.Duracion,1,2)*3600+substr(tab2.Duracion,3,2)*60+ substr(tab2.Duracion,5,2))), 15,0)segundos,
lpad(to_char(sum(substr(tab2.Duracion, 1,2)*60+substr(tab2.Duracion,3,2)+decode(substr(tab2.Duracion, 5,2),'00',0,1))),9,0)minutos,
to_char(sum(to_number(tab2.soles)),'0999999.09') soles
FROM
(SELECT /*+index(TAB1 INDEX_TREE_TRAFICO) index(sg INSEG2)*/
sp.des_stip_pc AS cliente, sg.nom_seg_cta AS segmento, f_obtienetiposervicio (tab1.tipoorigen, tab1.tipodestino, tab1.callclas, tab1.bparty ) AS servicio, f_obtienetipotrafico (tab1.tipotelefono, tab1.tipodestino, 1) AS trafico, f_obteneroperador (tab1.aparty) AS operador, zn.descripcion AS zonal, tab1.codzon AS codzonal, tab1.banda AS horario, valorfacialnumtarjeta_w(tab1.refid) AS Denominacion, tab1.soles AS soles, tab1.stardate AS Fecha, tab1.duracion AS Duracion, tab1.rao AS Negocio, tab1.expiredate AS Agencia, tab1.tipollamada AS Indrural -- tab1.periodo AS periodo, -- tab1.tipotelefono AS tipotelefono FROM (SELECT rg.cod_stip_pc AS subtipo, rg.cod_seg_cta AS segcta, rg.cod_tipopc AS tipopc, rg.cod_nat_cta AS codnat, rg.tipotelefonoorigen AS tipoorigen, rg.tipotelefonodestino AS tipodestino, rg.callclass AS callclas, rg.bparty AS bparty, rg.aparty AS aparty, rg.tipollamada AS tipollamada, rg.codigozonal AS codzon, rg.startdatetime AS stardate, rg.rao AS rao, rg.DURATION AS duracion, rg.expiredate AS expiredate, rg.refid AS refid, rg.periodo AS periodo, rg.tipotelefono AS tipotelefono, rg.bandahoraria AS banda, rg.callcharge AS soles FROM reg_tmptrafico rg WHERE (rg.periodo=20080602||'10') ) tab1, subtipopc sp, segmento sg, zonal zn WHERE tab1.tipopc = sp.cod_tipopc AND tab1.subtipo = sp.cod_stip_pc AND tab1.segcta = sg.cod_seg_cta AND tab1.codnat = sg.cod_nat_cta AND tab1.codzon = zn.cod_zonal_atis)tab2 GROUP BY tab2.cliente, tab2.segmento, tab2.servicio, tab2.trafico, tab2.operador, tab2.zonal, tab2.horario, tab2.DenominacionReceived on Wed Aug 27 2008 - 15:49:55 CDT