RE: Query mess
- From: "Carol Bristow" <Carol.Bristow@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 19 Mar 2004 11:39:42 -0600
The first thing that I see is five references to the table nc_memo_text,
with nothing in the where clause indicating how these are being joined.
So Oracle is trying to generate all the combinations of all the rows
(n*n*n*n*n) - that can be an awfully big number even if you've only got
a small number of records in your table!
Carol Bristow
DPRA Inc.
1300 N 17th St Suite 950
Rosslyn, VA 22209
Work: 703-841-8025
Fax: 703-524-9415
-----Original Message-----
From: Oracle [mailto:Oracle_list@xxxxxxxxxxx]
Sent: Friday, March 19, 2004 12:13 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Query mess
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
-----------------------------------------------------------------
----------------------------------------------------------------
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:
