Запрос на выборку и сортировку данных работает медленно. В init файле есть параметры SORT_AREA_RETAINED_SIZE и SORT_AREA_SIZE. Если их размер увеличить будет ли запрос работать быстрее??? И какие их размеры надо устанавливать????

Ссылка не работает.

Если меняю в запросе

where (batch.mtl_kind_oid, batch.phys_form_oid,
batch.chem_form_oid,
batch.irrad_pur_oid) in
(select nc.mtl_kind_oid, nc.phys_form_oid,
nc.chem_form_oid,
nc.irrad_pur_oid from nomen_cmpo)

на

AND nc.mtl_kind_oid = batch.mtl_kind_oid
AND nc.phys_form_oid = batch.phys_form_oid
AND nc.chem_form_oid = batch.chem_form_oid
AND nc.irrad_pur_oid = batch.irrad_pur_oid

то но работаетна много быстрее. Почему????

Вот оба запроса

SELECT n.name_mtl, n.enrich_descr, n.sort_id, nc.enrich_descr, prg.OID,
batch.mtl_kind_oid, batch.phys_form_oid, batch.chem_form_oid,
batch.irrad_pur_oid, prd.end_prd_dt,
SUM (
DECODE (
typ.elem_unit,
'кг', mtl.elem_mass,
'г', mtl.elem_mass * 0.001
)
)
sum_elem_mass,
DECODE (
typ.elem_unit,
'кг', 'кг', 'г', 'кг'
)
elem_unit
FROM nomen n,
nomen_cmpo nc,
cd_prg prg,
ac_batch batch,
ac_mtl mtl,
mtl_typ typ,
prd,
inv
WHERE
batch.mtl_kind_oid = nc.mtl_kind_oid
AND batch.phys_form_oid = nc.phys_form_oid
AND batch.chem_form_oid = nc.chem_form_oid
AND batch.irrad_pur_oid = nc.irrad_pur_oid
AND check_pct (
batch.enrichmnt,
nc.down_sign,
nc.up_sign,
nc.down_pct,
nc.up_pct
) IS NULL
AND nc.nomen_oid = n.OID
AND batch.cd_prg_oid = prg.OID
AND mtl.ac_batch_oid = batch.OID
AND mtl.mtl_typ_oid = typ.OID
AND batch.inv_oid = inv.OID
AND inv.mba_oid IN (SELECT OID
FROM mba
WHERE code = 'ОЗО')
AND inv.prd_oid = prd.OID
AND inv.prd_oid IN (SELECT prd.OID
FROM prd
WHERE TO_CHAR (prd.end_prd_dt, 'DD.MM') = '31.12')
GROUP BY prg.OID,
n.name_mtl,
n.enrich_descr,
n.sort_id,
nc.enrich_descr,
batch.mtl_kind_oid,
batch.phys_form_oid,
batch.chem_form_oid,
batch.irrad_pur_oid,
prd.end_prd_dt,
typ.elem_unit;





SELECT n.name_mtl, n.enrich_descr, n.sort_id, nc.enrich_descr, prg.OID,
batch.mtl_kind_oid, batch.phys_form_oid, batch.chem_form_oid,
batch.irrad_pur_oid, prd.end_prd_dt,
SUM (
DECODE (
typ.elem_unit,
'кг', mtl.elem_mass,
'г', mtl.elem_mass * 0.001
)
)
sum_elem_mass,
DECODE (
typ.elem_unit,
'кг', 'кг', 'г', 'кг'
)
elem_unit
FROM nomen n,
nomen_cmpo nc,
cd_prg prg,
ac_batch batch,
ac_mtl mtl,
mtl_typ typ,
prd,
inv
WHERE
batch.mtl_kind_oid = nc.mtl_kind_oid
AND batch.phys_form_oid = nc.phys_form_oid
AND batch.chem_form_oid = nc.chem_form_oid
AND batch.irrad_pur_oid = nc.irrad_pur_oid
AND check_pct (
batch.enrichmnt,
nc.down_sign,
nc.up_sign,
nc.down_pct,
nc.up_pct
) IS NULL
AND nc.nomen_oid = n.OID
AND batch.cd_prg_oid = prg.OID
AND mtl.ac_batch_oid = batch.OID
AND mtl.mtl_typ_oid = typ.OID
AND batch.inv_oid = inv.OID
AND inv.mba_oid IN (SELECT OID
FROM mba
WHERE code = 'ОЗО')
AND inv.prd_oid = prd.OID
AND inv.prd_oid IN (SELECT prd.OID
FROM prd
WHERE TO_CHAR (prd.end_prd_dt, 'DD.MM') = '31.12')
GROUP BY prg.OID,
n.name_mtl,
n.enrich_descr,
n.sort_id,
nc.enrich_descr,
batch.mtl_kind_oid,
batch.phys_form_oid,
batch.chem_form_oid,
batch.irrad_pur_oid,
prd.end_prd_dt,
typ.elem_unit;

Ошибся второй

SELECT
n.name_mtl, n.enrich_descr,n.sort_id, nc.enrich_descr,
prg.oid, batch.mtl_kind_oid, batch.phys_form_oid,
batch.chem_form_oid, batch.irrad_pur_oid, prd.end_prd_dt,
sum(DECODE(typ.elem_unit, 'кг', mtl.elem_mass, 'г',
mtl.elem_mass*0.001)) sum_elem_mass,
DECODE(typ.elem_unit, 'кг','кг', 'г','кг') elem_unit
FROM nomen n,
nomen_cmpo nc,
cd_prg prg,
ac_batch batch,
ac_mtl mtl,
mtl_typ typ,
prd, inv
WHERE (batch.mtl_kind_oid, batch.phys_form_oid,
batch.chem_form_oid, batch.irrad_pur_oid) in
(SELECT nc.mtl_kind_oid, nc.phys_form_oid,
nc.chem_form_oid, nc.irrad_pur_oid
FROM nomen_cmpo)
AND check_pct(batch.enrichmnt, nc.down_sign, nc.up_sign,
nc.down_pct, nc.up_pct) IS NULL
AND nc.nomen_oid = n.oid
AND batch.cd_prg_oid = prg.oid
AND mtl.ac_batch_oid = batch.oid
AND mtl.mtl_typ_oid = typ.oid
AND batch.inv_oid = inv.oid
AND inv.mba_oid in (SELECT oid
FROM mba
WHERE code = 'ОЗО')
AND inv.prd_oid = prd.oid
AND inv.prd_oid in (SELECT prd.oid
FROM prd
WHERE to_char(prd.end_prd_dt,'DD.MM') = '31.12')
GROUP BY prg.oid,
n.name_mtl,
n.enrich_descr,
n.sort_id,
nc.enrich_descr,
batch.mtl_kind_oid,
batch.phys_form_oid,
batch.chem_form_oid,
batch.irrad_pur_oid,
prd.end_prd_dt,
typ.elem_unit;