Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: slowish query causing problems...

Re: slowish query causing problems...

From: Joan Hsieh <joan.hsieh_at_tufts.edu>
Date: Tue, 14 Jan 2003 08:58:58 -0800
Message-ID: <F001.0052F46E.20030114085858@fatcity.com>


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:EvaD_at_TFMC.co.za]
> 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: joan.hsieh_at_tufts.edu

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: ListGuru_at_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).
Received on Tue Jan 14 2003 - 10:58:58 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US