Query mess

From: Oracle <Oracle_list_at_hotmail.com>
Date: Fri, 19 Mar 2004 17:13:21 -0000
Message-ID: <BAY15-DAV8hxvzSoBJH00027590@hotmail.com>

Hi, i have the following query which causes error: ORA-01652: unable to extend temp segment by 129 in tablespace TEMP. The tablespace has been increased but same result. Can i re write this query to improve performance so that it does less processing?

SELECT

a.memo_text NCLINE_MEMO_TEXT,a.text_order,n.loginid NCLINE_LOGINID,
b.memo_text CAUSE_MEMO_TEXT,b.text_order, c.memo_text CORRECT_MEMO_TEXT,
c.text_order, ncl_c.loginid NCLINE_C_LOGINID,
d.memo_text NCLINE_D_MEMO_TEXT,d.text_order, ncl_d.loginid NCLINE_D_LOGINID,
e.memo_text NCLINE_CLS_MEMO_TEXT,e.text_order, ncl_cls.loginid

NCLINE_CLS_LOGINID

FROM nc_memo_text a,
nc_memo_text b,
nc_memo_text c,
nc_memo_text d,
nc_memo_text e,

ncline n,

ncline_c ncl_c,
ncline_d ncl_d,
ncline_cls ncl_cls,

nonconform nc,
part_table pt,
op_no op,
nc_serial_create ncs
WHERE a.mtsn = n.ncline_mtsn

AND b.mtsn = ncl_c.cause_mtsn
AND c.mtsn = ncl_c.correct_mtsn
AND d.mtsn = ncl_d.disp_mtsn
AND e.mtsn = ncl_cls.close_mtsn
AND nc.ncsn = n.ncsn
AND nc.ncsn = ncl_c.ncsn
AND nc.ncsn = ncl_d.ncsn

AND nc.ncsn = ncl_cls.ncsn
AND nc.nc_pn = pt.nc_pn
AND ncs.ncsn = nc.ncsn

AND nc.operation_code = op.operation_code (+)
AND nc.nc_type not in ('QU', 'PO', 'QY')
AND nc.nc_status = 'CLS'
AND nc.ncsn = 1649

I'm trying to re write the query to eliminate some of the tables or reduce full table scans

Ive got this which runs quick, no probs: SELECT NCM.MTSN, NCM.TEXT_ORDER,NCM.MEMO_TEXT, NCM.R_STATUS FROM nc_memo_text ncm,
(SELECT mtsn

FROM
(SELECT ncsn, ncline_mtsn as mtsn FROM ncline
UNION
SELECT ncsn, correct_mtsn as mtsn FROM ncline_c UNION
SELECT ncsn, cause_mtsn as mtsn FROM ncline_c UNION
SELECT ncsn, disp_mtsn as mtsn FROM ncline_d UNION
SELECT ncsn, close_mtsn FROM ncline_cls) a, nonconform nc,nc_serial_create nsc, part_table pt, op_no op

WHERE a.ncsn = nc.ncsn
AND nc.ncsn = nsc.ncsn
AND nc.nc_pn = pt.nc_pn (+)
AND nc.operation_code = op.operation_code (+)
AND nc.ncsn = 1649

AND nc.nc_type NOT in ('QU', 'PO', 'QY') AND nc.nc_status = 'CLS') b
WHERE b.mtsn = ncm.mtsn);

But i cant figure out how to get the results like the first query (which fails 01652) i.e. a.memo_text NCLINE_MEMO_TEXT,a.text_order,n.loginid NCLINE_LOGINID,
b.memo_text CAUSE_MEMO_TEXT,b.text_order etc

Any ideas anyone?

Cheers

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Mar 19 2004 - 11:10:47 CST