[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Oracle Union Query very slow.

Posted on 2004-10-29
5
Medium Priority
?
1,812 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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.
Via a live example, show how to take different types of Oracle backups using RMAN.

656 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