optimizar query
Publicado por
alex (1 intervención) el 19/09/2008 17:50:08
Hola necesito que me sugieran como optimizar un query que dura 4 horas y medias....
SELECT B.IDCOMPONENTE as IDCOMPONENTE,
B.IDFUENTE,
B.IDREGION,
e.ETIQUETA AS ETIQUETA,
e.NOMBRE,
e.IDCONTROL
FROM HCOMPONENTES@TRAF B, control@traf e
WHERE not exists
(SELECT TRUNC(cell_id / 10) IDCELDA,
cell_id CELL_ID,
la_id_lac,
segment_id,
segment_name,
o_bsc.object_instance BSC_ID,
o_bsc.name BSC_NAME,
TO_NUMBER(o_bcf.object_instance) BCF_ID,
o_bcf.name BCF_NAME,
TO_NUMBER(o_bts.object_instance) BTS_ID,
o_bts.name BTS_NAME,
count(o_trx.object_instance) TRX_ID1,
((count(o_trx.object_instance) * 7) - 2) TRX_ID,
decode(o_bcf_st.stateinmode1,
'0',
'N',
'1',
'U',
'2',
'S',
'3',
'L') BCF_STATE,
decode(o_bts_st.stateinmode1,
'0',
'N',
'1',
'U',
'2',
'S',
'3',
'L') BTS_STATE
from c_trx@oss_ccs1,
c_bts@oss_ccs1,
c_bcf@oss_ccs1,
objects@oss_ccs1 o_trx,
objects@oss_ccs1 o_bts,
objects@oss_ccs1 o_bcf,
objects@oss_ccs1 o_bsc,
object_modestates@oss_ccs1 o_bcf_st,
object_modestates@oss_ccs1 o_bts_st,
object_modestates@oss_ccs1 o_trx_st
WHERE c_trx.conf_name = '<ACTUAL>'
and c_bts.conf_name = '<ACTUAL>'
and c_bcf.conf_name = '<ACTUAL>'
and o_trx.int_id = c_trx.int_id
and o_bts.int_id = c_bts.int_id
and o_bcf.int_id = c_bcf.int_id
and o_trx.parent_int_id = o_bts.int_id
and o_bts.parent_int_id = o_bcf.int_id
and o_bcf.parent_int_id = o_bsc.int_id
and o_bcf_st.int_id = o_bcf.int_id
and o_bts_st.int_id = o_bts.int_id
and o_trx_st.int_id = o_trx.int_id
and o_trx_st.stateinmode1 = 1
and o_bsc.name is not null
and o_bcf.name is not null
and o_bts.name is not null
and o_bcf.name = e.NOMBRE
GROUP BY cell_id,
la_id_lac,
segment_id,
segment_name,
o_bsc.object_instance,
o_bsc.name,
o_bcf.object_instance,
o_bcf.name,
o_bts.object_instance,
o_bts.name,
o_bcf_st.stateinmode1,
o_bts_st.stateinmode1) and B.Idred = 23 and
e.idcontrol = b.idcontrol and B.fechadesactivacion is null and
b.idtipocomponente in (1);