Re: SQL - Using SELECT FROM x physical table
- From: Igor Neyman <igor.neyman@xxxxxxxxx>
- To: norman.dunbar@xxxxxxxxxxxxxxxxxxxxxxxxx
- Date: Tue, 16 Feb 2010 09:18:35 -0500
Norman,
I don't think, original poster specified that the type of the column is
"DATE" and not just some "INT".
Igor Neyman
On Tue, Feb 16, 2010 at 9:13 AM, Dunbar, Norman <
norman.dunbar@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
> >> 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).
>
> How about an index on the YYYY part of the date?
>
> CREATE INDEX index_name ON table_name(to_char(date_column, 'yyyy'));
>
> On a test table with 500,000 rows, the plan for:
>
> SELECT count(*)
> FROM my_table
> WHERE to_char(my_date, 'yyyy') = (
> SELECT to_char(max(my_date), 'yyyy')
> FROM norman
> );
>
> gives me a full scan on the max() part but an index range scan for the
> select part using my function based index.
>
>
> Another alternative would be to partition on the year and that should
> reduce the amount of data being read as only one partition would need to
> be read.
>
> Cheers,
> Norm.
>
>
> Information in this message may be confidential and may be legally
> privileged. If you have received this message by mistake, please notify the
> sender immediately, delete it and do not copy it to anyone else. We have
> checked this email and its attachments for viruses. But you should still
> check any attachment before opening it. We may have to make this message and
> any reply to it public if asked to under the Freedom of Information Act,
> Data Protection Act or for litigation. Email messages and attachments sent
> to or from any Environment Agency address may also be accessed by someone
> other than the sender or recipient, for business purposes. If we have sent
> you information and you wish to use it please read our terms and conditions
> which you can get by calling us on 08708 506 506. Find out more about the
> Environment Agency at www.environment-agency.gov.uk
>
> Information in this message may be confidential and may be legally
> privileged. If you have received this message by mistake, please notify the
> sender immediately, delete it and do not copy it to anyone else.
>
> We have checked this email and its attachments for viruses. But you should
> still check any attachment before opening it.
> We may have to make this message and any reply to it public if asked to
> under the Freedom of Information Act, Data Protection Act or for litigation.
> Email messages and attachments sent to or from any Environment Agency
> address may also be accessed by someone other than the sender or recipient,
> for business purposes.
>
> If we have sent you information and you wish to use it please read our
> terms and conditions which you can get by calling us on 08708 506 506. Find
> out more about the Environment Agency at www.environment-agency.gov.uk
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
