[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Oracle Union Query very slow.

Posted on 2004-10-29
5
Medium Priority
?
1,829 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
Comment
Question by:qudoos
  • 3
  • 2
5 Comments
 
LVL 3

Accepted Solution

by:
ToddBarry earned 400 total points
ID: 12447083
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
ID: 12447376
I have never seen such performance boost with the addition of just 1 word !!!

it rocks man !
0
 
LVL 1

Author Comment

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

Expert Comment

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

Author Comment

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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Suggested Courses

591 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