RE: Query mess

From: Carol Bristow <Carol.Bristow_at_dpra.com>
Date: Fri, 19 Mar 2004 11:39:42 -0600
Message-ID: <710C39AC5BC90648B3CA31DC47D1111601753891@mhk02.ad.dpra.com>

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_at_hotmail.com] Sent: Friday, March 19, 2004 12:13 PM
To: oracle-l_at_freelists.org
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_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


-----------------------------------------------------------------

----------------------------------------------------------------

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:36:14 CST