Query mess
- From: "Oracle" <Oracle_list@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 19 Mar 2004 17:13:21 -0000
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
