This query make sense. It should not use full table scan. Denham, did you try that, I am curious to know the answer.
Joan > "Toepke, Kevin M" wrote: > > Ouch! I hate to see queries written this way...the query should answer > the question you are trying to ask. It appears you are checking for > the existance of a record in fwepcode1 that matches the criteria. > > If I understand the query correctly, you want to return 1 row if one > of 2 conditions is met. > 1) A row in fwepcode1 that matches 1 of the 3 where clauses > 2) A row does not exist in valuelist with the below conditions. > > My suggestion for rewriting the query is below. > > Kevin > > > SELECT 1 > FROM fwepcode1 > WHERE wotype = 'TST' > AND func = 'C0NEPRF' > AND EXP = '22222' > AND rownum = 1 > UNION > SELECT 1 > FROM dual > WHERE NOT EXISTS ( > SELECT 1 > FROM valuelist > WHERE listname = 'STATUS' > AND maxvalue = 'AAAAA' > AND value = 'INPRG') > )l > > > -----Original Message----- > From: Denham Eva [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, January 14, 2003 6:29 AM > To: Multiple recipients of list ORACLE-L > Subject: slowish query causing problems... > > Hello List, > > Pls help me on this problem. Our application does a > validation when it uses a certain screen, as it so happens > this screen is used very intensively. The performance is > very slow, I have isolated the main culprit. I have tried > the following. > > I have dropped all the indexes and tried recreating them > individually. Each time I have run an explain plan on the > query, the optimizer (both rule and Choose) have chosen to > do a FULL table scan on the fwepcode table. Even when using > a hint to explicitly use the index it still uses FULL. > > This is very frustrating indeed. > > SELECT DISTINCT (1) > FROM fwepcode1 > WHERE (wotype = 'TST' AND func = 'C0NEPRF' AND EXP > = '22222') > OR 'INPRG' NOT IN (SELECT VALUE > FROM valuelist > WHERE listname = 'STATUS' > AND MAXVALUE = 'AAAAA') > > Is the reason that the optimizer does not use any of the > indexes because of the SELECT DISTINCT (1)? > I have tried adjusting this query slightly to remove this > and it still insists on doing a full table scan. > Funny enough the sub query on valuelist table does use a > index. > The table contains 8920 rows. The cost according to the > explain plan is 703 and bytes 9834. > > The system is a Oracle 817 on Win2k. > > Pls advise, any options or help will be appreciated. > Many Thanks > Denham Eva > Oracle DBA > "UNIX is basically a simple operating system, but you have > to be a genius to understand the simplicity." > Dennis Ritchie. > > ------------------------------------------------------------ > DISCLAIMER > > This message is for the named person's use only. It may > contain confidential, proprietary or legally privileged > information. No confidentiality or privilege is waived or > lost by any mistransmission. If you receive this message in > error, please immediately delete it and all copies of it > from your system, destroy any hard copies of it and notify > the sender. You must not, directly or indirectly, use, > disclose, distribute, print, or copy any part of this > message if you are not the intended recipient. TFMC, its > holding company, and any of its subsidiaries each reserve > the right to monitor and manage all e-mail communications > through its networks. > > Any views expressed in this message are those of the > individual sender, except where the message states otherwise > and the sender is authorized to state them to be views of > any such entity. > ------------------------------------------------------------ > > ------------------------------------------------------------ > This e-mail message has been scanned for Viruses and Content > and cleared by MailMarshal - For more information please > visit www.marshalsoftware.com > ------------------------------------------------------------ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joan Hsieh INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
