OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » max date

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

max date [message #228354]

Mon, 02 April 2007 12:16

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.

Report message to a moderator

Re: max date [message #228356 is a reply to message #228354]

Mon, 02 April 2007 12:30

Barbara Boehmer
Messages: 9100
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
	);

Report message to a moderator

Re: max date [message #228360 is a reply to message #228354]

Mon, 02 April 2007 12:47

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: max date [message #228362 is a reply to message #228354]

Mon, 02 April 2007 12:51

svercha
Messages: 2
Registered: April 2007

Junior Member

thank you!

Report message to a moderator

Re: max date [message #228397 is a reply to message #228362]

Mon, 02 April 2007 22:38

rleishman
Messages: 3728
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

Report message to a moderator

Previous Topic:	Intra-Partitioning Query
Next Topic:	Run SQL files using SQL-PLUS

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 12 07:26:17 CST 2024