SQL oracle - Union query issue

Hello all

I have this query bellow that work great but i would like to know if i can make it better that that?

Is there a way to short it up so that it do the same thing?

Thanks again

  SELECT   BILL,
           CARRIER_ID,
           MILES,
           CUBE,
           BEST_DEP,
           FREIGHT_COST
    FROM   LOAD
   WHERE   BEST_DEP BETWEEN TO_DATE ('2013-01-01', 'YYYY-MM-DD')
                        AND  TO_DATE ('2013-12-31', 'YYYY-MM-DD')
           AND CARRIER_ID IN
                    ('UPS',
                     'PUROLATOR')
UNION
  SELECT   BOL,
           CARRIER_ID,
           MILES,
           CUBE,
           BEST_DEP,
           FREIGHT_COST
    FROM   FREIGHT_MOVEMENT_HIST
   WHERE   BEST_DEP BETWEEN TO_DATE ('2012-01-01', 'YYYY-MM-DD')
                        AND  TO_DATE ('2012-12-31', 'YYYY-MM-DD')
           AND CARRIER_ID IN
                    ('UPS',
                     'PUROLATOR')
ORDER BY   CARRIER_ID;

Open in new window

LVL 11
Wilder1626Asked:
Who is Participating?
 
awking00Connect With a Mentor Commented:
Just a note - If best_dep contains a time element, you will need to restructure your where clauses to accommodate any records for 12-31. For example,
where best_dep between .... and to_date('2012-12-31 23:59:59','yyyy-mm-dd hh24:mi:ss')
or
where best_dep >= to_date('2012-01-01','yyyy-mm-dd')
    and best_dep <to_date('2013-01-01','yyyy-mm-dd')
0
 
sdstuberConnect With a Mentor Commented:
if the data between the sets is distinct you should change  "UNION" to "UNION ALL"

other than that, there isn't much to either query so nothing really to change there.


you could look at indexes on the respective tables to help, what does the current explain plan look like?  - use dbms_xplan.display  -  please NO screen shots
0
 
slightwv (䄆 Netminder) Commented:
Maybe a Materialized View?
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Wilder1626Author Commented:
Hello all

I tried the union all but i have the same result.

slightwv,
i'm pretty new to SQL but what is a Materialized View?

How would i build the query?
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
In a nutshell, a materialized view is a select statement whose results are stored in physical form.  It is a good way to take large complex queries and more or less pre-execute them, get the results, and store them.  then anything needing the data can access it without executing the large query.  Just select from the view.

It will take the select statement you have and create a table of the results.  It will be updated either at a set time or as the underlying data changes.  How and when you refresh the view is up to your reauirements.

There is a ton of information out there on them if you look around.

Here's a good starting point from the online docs:
http://docs.oracle.com/cd/E11882_01/server.112/e25789/schemaob.htm#CNCPT88875
0
 
Wilder1626Author Commented:
Thanks a lot for all this information
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.