RE: SQL - Using SELECT FROM x physical table
Date: Tue, 16 Feb 2010 15:48:43 +0100
Message-ID: <CF9A39CD0F65EA49ADF70FCBF9BC2FF7A1B70B_at_SW-GNETCW-MBX02.tcads.thomascook.com>
In that case, using the rank() analytical function could get rid of one of the table scans :
Select col1, col2, ... from (
Select col1, col2, ... , rank() over (order by year desc) r
From table
)
Where r = 1
mvg,
Jo
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Daniel Fink
Sent: dinsdag 16 februari 2010 15:26
To: ecandrietta_at_gmail.com
Cc: ORACLE-L
Subject: 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:Received on Tue Feb 16 2010 - 08:48:43 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 -- http://www.freelists.org/webpage/oracle-l