Home » SQL & PL/SQL » SQL & PL/SQL » max date
max date [message #228354] Mon, 02 April 2007 12:16 Go to next message
svercha
Messages: 2
Registered: April 2007
Junior Member
hello, I was wondering if I can get advise on this. I have 5 tables that among other things all have an INSERT_DATE column in them. So when these tables are populated, sysdate is inserted in these columns. I have to find a max date of all these dates in these 5 tables. So I can accomplish this by doing max and then max with union. So my query looks as follows:

select max(mxDt) as lastDate
from (select max(tbl1.insert_date) as mxDt from table1
UNION
select max(tbl2.insert_date) as mxDt from table2
UNION
select max(tbl3.insert_date) as mxDt from table3
UNION
select max(tbl4.insert_date) as mxDt from table4
UNION
select max(tbl5.insert_date) as mxDt from table5
)

this works, but is kind of slow..is there a better way to accomplish this? thank you in advace.
Re: max date [message #228356 is a reply to message #228354] Mon, 02 April 2007 12:30 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
You only need one max in the outer query; You don't need them in the inner queries. UNION ALL is faster than UNION, because UNION takes extra time to eliminate duplicates and you don't need to eliminate duplicates, since you will be taking the max anyhow. You also don't need all the extra aliases.

 
select max (insert_date) as lastDate
from (select insert_date from table1
	UNION ALL
	select insert_date from table2
	UNION ALL
	select insert_date from table3
	UNION ALL
	select insert_date from table4
	UNION ALL
	select insert_date from table5
	);

Re: max date [message #228360 is a reply to message #228354] Mon, 02 April 2007 12:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Use UNION ALL instead of UNION.
Create indexes on insert_date columns.

A MAX of all "UNION ALL SELECT" will be slower than a MAX of "UNION ALL SELECT MAX" above all if there is some or all indexes on insert_date columns.

Regards
Michel
Re: max date [message #228362 is a reply to message #228354] Mon, 02 April 2007 12:51 Go to previous messageGo to next message
svercha
Messages: 2
Registered: April 2007
Junior Member
thank you!
Re: max date [message #228397 is a reply to message #228362] Mon, 02 April 2007 22:38 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Whoa! Back up!

Revert to your original query, but create an index on insert_date in each table. When you perform a MAX() on an indexed column, Oracle can pick the maximum value straight off the top of the index. You may have to gather statistics first.

If (as suggested earlier) you only have a MAX on the outer query, Oracle will have to fast-full scan all of the indexes instead, which will be much slower.

With your original query and the indexes in place, you may use UNION or UNION ALL; a unique sort of 5 rows will trouble Oracle too much.

Ross Leishman
Previous Topic: Intra-Partitioning Query
Next Topic: Run SQL files using SQL-PLUS
Goto Forum:
  


Current Time: Sat Dec 03 20:10:12 CST 2016

Total time taken to generate the page: 0.04046 seconds