Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Query mess

RE: Query mess

From: Powell, Mark D <>
Date: Fri, 19 Mar 2004 12:42:08 -0500
Message-ID: <>

There does not appear to be an ORDER BY, GROUP BY, or DISTINCT clause so the error is probably due to the choice by the optimizer to use a sort/merge join somewhere along the line. This is assuming none of the FROM clause tables are really views. Run an explain plan and if you can replace the sort/merge with a nested loop or hash join.

The choice of a sort/merge join could indicate that a join condition is missing from the where clause or that no index exists to support one of the joins. The wrong join order could result in the expected index being unavailable when the optimizer attempts to join to the target. Missing or bad statistics could be to blame.

Run an explain plan, update the statistics, and re-run the plan may be a good starting point.

HTH -- Mark D Powell --

-----Original Message-----

[]On Behalf Of Oracle Sent: Friday, March 19, 2004 12:13 PM
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?


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
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
(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?


Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.

Archives are at
FAQ is at

Please see the official ORACLE-L FAQ:
To unsubscribe send email to:
put 'unsubscribe' in the subject line.

Archives are at FAQ is at
Received on Fri Mar 19 2004 - 11:39:31 CST

Original text of this message