Solved

Oracle Union Query very slow.

Posted on 2004-10-29
1,723 Views
Last Modified: 2010-05-18
I have this oracle query which uses union to build a list from two tables. For some reason it is very slow.

Can anyone suggest some improvements [Other than changing the crappy server..:)]

there are two tables. FCLIENT and FSALE. FCLIENT has 13000+ records and FSALE has 70000+ records. I personally think Oracle should handle this pretty well, but it is very slow.

Thanks

================================================================
select C_SEQUENCE as SEQ, C_REC_NO as RECNO, C_FIRSTNAME_INTL as FIRSTNAME, C_SURNAME as LASTNAME, C_TELEPHONE as PHONE, C_DATE_INPUT as INPUTDATE, C_POSTAL_CODE as POSTAL, C_LANGUAGE as LANG, C_ORGANIZATION as ORGANIZATION, C_CITY as CITY,0 as TICKET ,'FCLIENT' as WHICHTABLE  
from FCLIENT union  
select FS_ORDER_NUM as SEQ, 0 as RECNO, FS_FNAME as FIRSTNAME, FS_LNAME as LASTNAME,  
FS_TEL as PHONE, FS_DATE_INPUT as INPUTDATE, FS_POST_CODE as POSTAL, FS_LANGUAGE as LANG,
FS_COMPANY as ORGANIZATION, FS_CITY as CITY,FS_ORDER_NUM as TICKET,'FSALE' as WHICHTABLE  
from FSALE

0
Question by:qudoos
    5 Comments
     
    LVL 3

    Accepted Solution

    by:
    First off, I would change that to a UNION ALL which will avoid the unnecessary distinct of the result in this case.
    0
     
    LVL 1

    Author Comment

    by:qudoos
    I have never seen such performance boost with the addition of just 1 word !!!

    it rocks man !
    0
     
    LVL 1

    Author Comment

    by:qudoos
    by the way what would you do secondly ?:)
    0
     
    LVL 3

    Expert Comment

    by:ToddBarry
    I was SO sure that would help I never had a fallback suggestion... <grin>
    0
     
    LVL 1

    Author Comment

    by:qudoos
    what a witty answer man ! have a great weekend
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
    This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

    934 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now