Людвиг Аристархович
Новичок
Товарищи, помогите. Медленно работает SQL-запрос. Что делать, не знаю.
Код:
SELECT region_key AS region
,NAME(containedobjectsbasement_key) AS building
,container_node_id
,containedobjectsexchange_key
,object_name AS ats
,number_ranges AS diapazon
,exchange_type_name AS tip_ats
,exchange_mount_capacity AS emkost
,operator_licence_number AS license
,trace_line_relay_type_name AS tip_sl
,COUNT(trace_line_id) AS count5
,otkuda_nax AS otkuda_ats
,kuda_blya AS kuda_ats
,seven_nation_army
,ROWNUM
,cable_type_name
,logical_cable_length
,ATS_NAME
FROM (SELECT DISTINCT r.object_id AS region_key
,n.node_id AS containedobjectsbasement_key
,n1.container_node_id AS container_node_id
,e.node_id AS containedobjectsexchange_key
,CAST(name2(e.node_id, e.exchange_class_id) AS VARCHAR2(128) ) AS object_name
,e.exchange_mount_capacity AS exchange_mount_capacity
,CAST( (SELECT stragg(first_number || '-' || last_number || '
' )
FROM number_interval
WHERE exchange_id = e.node_id) AS VARCHAR2(1024) ) AS number_ranges
,(SELECT e1.exchange_type_name
FROM exchange_tl e1
WHERE e1.exchange_type_id = e.exchange_type_id) AS exchange_type_name
,CAST( (SELECT s3.licence_number
FROM service_operator_type s3
WHERE s3.service_operator_type_id = e.operator_id) AS VARCHAR2(64) ) AS operator_licence_number
,(SELECT t.trace_line_relay_type_name
FROM trace_line_relay_type t
WHERE t.trace_line_relay_type_id = s.trace_line_relay_type_id) AS trace_line_relay_type_name
,s.object_id trace_line_id
,NAME(s1.exchange_id) otkuda_nax
,NAME(trace_line.exchange_id) kuda_blya
,(SELECT SUM(TO_NUMBER(n.last_number) - TO_NUMBER(n.first_number) + 1) AS s
FROM number_interval n, region_l r
WHERE LENGTH(r.region_add_code || n.last_number) = 10
AND LENGTH(r.region_add_code || n.first_number) = 10
AND n.region_id = r.object_id
AND n.exchange_id = e.node_id) AS seven_nation_army
,ROWNUM
FROM region_l r, node n, exchange_l e, node n1, service_trace_line s, trace_line, service_l s1, node n2, OBJECT o
WHERE s.object_id = s1.object_id
AND s1.exchange_id = e.node_id
AND trace_line.service_id = s1.object_id
AND e.node_id = n1.node_id
AND n1.container_node_id = n.node_id
AND e.exchange_class_id = 100
AND n2.container_node_id = n.node_id
AND n2.entity_id = 108
AND n.region_id = r.object_id
AND n.node_type_id = 115
AND r.object_id = :region_key
AND n1.node_id = o.object_id
AND o.object_owner_type_id = 3) sel
,(SELECT r.object_id AS region_key2
,c.node_id AS containedcoppercross_key
,s2.linkedobjectsinterexchange_key AS linkedobjectsinterexchange_key
,s2.logical_cable_length AS logical_cable_length
,s2.cable_type_name AS cable_type_name
,NAME(sl.exchange_id) AS ATS_NAME
,NAME(el.node_id) AS el
FROM region_l r
,CROSS c
,exchange_l el
,node n2
,service_l sl
,trace_line tl
,(SELECT l.node_id AS node_id
,l.logical_cable_id AS linkedobjectsinterexchange_key
,l.logical_cable_length AS logical_cable_length
,(SELECT c.type_name
FROM cable_type_name c
WHERE c.cable_type_name_id = l.cable_type_id) AS cable_type_name
FROM logical_cable l
WHERE l.logical_cable_type_id = 455) s2
WHERE s2.node_id(+) = c.node_id
AND c.node_id = n2.node_id
AND n2.entity_id = 108
AND c.node_id = el.cross_id
AND sl.exchange_id = el.node_id
AND tl.exchange_id = el.node_id
AND sl.entity_id = 156
AND r.object_id = :region_key) sel2
WHERE NAME(kuda_blya) = ATS_NAME
GROUP BY region_key, containedobjectsbasement_key, container_node_id, containedobjectsexchange_key, number_ranges, object_name, exchange_type_name, operator_licence_number, trace_line_relay_type_name, otkuda_nax, kuda_blya, exchange_mount_capacity
,operator_licence_number, seven_nation_army, ROWNUM, cable_type_name, logical_cable_length,ATS_NAME
ORDER BY otkuda_ats
