Re: SQL - Using SELECT FROM x physical table
Is there an index on year for the table? If so, then getting MAX(year) and filtering on year should be relatively painless. If not, you would perform two full table scans on the table.
Using a trigger means executing it every time there is an insert. That is likely more overhead in the processing of the trigger than with an additional index. 

--
Daniel Fink

OptimalDBA    http://www.optimaldba.com
Oracle Blog   http://optimaldba.blogspot.com

Lost Data?    http://www.ora600.be/



Eriovaldo Andrietta wrote:

Hi friends:

What is the best solution for the following problem:

I need to retrieve the max(year) from a column.
This table will have around 500.000 lines.
And then select all lines of this table where the date is equal to the max(year). 
For example, I have lines for year 2005, 2006, 2007, 2008, 2009.
The max year is 2009.
So I need retrieve only lines for 2009.

Solution 1:

Select table1.col1, table1.col2, table1.year
   from table table1 ,
          (SELECT MAX(year) year
                 FROM table) table2
 where table1.year = table2.year;

Solution 2:
2.1 )Create a table tablex like that
create table tablex
 (max_year number(4))
2.2) Write in the max_year the max(year) using trigger, pl/sql whatever I use for it I will have the max_year filled as 2009 in unique line and then do it: 

Select table1.col1, table1.col2, table1.year
   from table table1 ,
          tablex
  where table1.year = tablex.max_year;

Regards
Eriovaldo








--
http://www.freelists.org/webpage/oracle-l

Other related posts: