| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Query mess
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-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Oracle
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.loginidNCLINE_CLS_LOGINID
FROM nc_memo_text a, nc_memo_text b, nc_memo_text c, nc_memo_text d, nc_memo_text e,
ncline_c ncl_c, ncline_d ncl_d, ncline_cls ncl_cls,
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.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
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
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
 |
 |