Re: SQL - Using SELECT FROM x physical table
From: Daniel Fink <daniel.fink_at_optimaldba.com>
Date: Tue, 16 Feb 2010 07:26:01 -0700
Message-ID: <4B7AAAF9.3080601_at_optimaldba.com>
Date: Tue, 16 Feb 2010 07:26:01 -0700
Message-ID: <4B7AAAF9.3080601_at_optimaldba.com>
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:Received on Tue Feb 16 2010 - 08:26:01 CST
> 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