Message-Id: <10568.112741@fatcity.com> From: Philip West Date: Mon, 24 Jul 2000 13:42:04 +0100 Subject: RE: Explain Plan Hangs. DBA on 3 week holiday, and his name is Mu You say it works fine, does that mean you get an output? If so what is the output? how long does it take to run successfully? What does the explain plan look like for the successful run? The extra group by will involve extra work for the db and the query will run faster. Phil West Unix Sys Admin and Oracle Financials DBA +44 (0) 20 7623 5222 x5221 I have a query which did nothing over the entire weekend. When I try to see what it is doing the explain plan hangs. So now I understand (I think) why the query does nothing - oracle doesn't even manage to get past the query optimizer or whatever it is that does the query plans. No errors or anything, it just sits there for days... Oracle 8.0.5.0.0 running on NT4 sp3. Removing a single column from the results set changes everything. Though this column is in a table from which other columns are already returned, so it is difficult to understand why it should change the explain plan!!! For example (this is an example the full sql is a bit bigger): select sum(trans_amt),account_code,rcpt.short_dsc,rcpt.region from glm ,rcpt where glm.rcpt_code = rcpt.rcpt_code group by account_code,rcpt.short_dsc,rcpt.region wont work, but if I remove rcpt.region from the sql it works fine. What's the deal here then?. Thanks PAtrick Mullen Data Warehouse Officer United Nations World Food Programme -- Author: Patrick Mullen INET: Patrick.Mullen@wfp.org Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ******************************************************************************* The information in this Internet e-mail is confidential and may be legally privileged. It is intended solely for the addressee. Access to this Internet e-mail by anyone else is unauthorised and any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited and may be unlawful. When addressed to our clients any opinions or advice contained in this Internet e-mail are subject to the terms and conditions expressed in any applicable documentation or market practices governing the relationship between Garban Intercapital plc and its clients. Any views expressed in this message are those of the individual sender except where they are stated to be the views of Garban Intercapital plc. *******************************************************************************