| Oracle MAX [message #539823] |
Wed, 18 January 2012 12:05  |
 |
pronosen
Messages: 5
Registered: January 2012
|
Junior Member |
|
|
Hello--
I am a new user to Oracle. The challenge I am currently facing is finding the maximum value of two independent columns of a single table.
Here is the structure of the table:
[FiscalYear] [AccountingPeriod]
------------ ------------------
[2012] ------------- [2]
[2011] ------------- [12]
[2012] ------------- [1]
What I need to be able to do is to:
1) Determine the maximum value present in column FiscalYear, and then the maximum value available for this FiscalYear under the column AccountingPeriod.
Having previous MS SQL experience, I can do this fairly easy on Microsoft SQL server, but so far was not able to do this easily on Oracle database. My other observation is that using the MAX function on Oracle is very slow (even with thse fields being indexed). So, my question is: Is it possible to run this query on Oracle with only one pass through the table where th returned result will show 2012 for FiscalYear, and 2 for Accounting Period?
Thank you in advance.
[Updated on: Wed, 18 January 2012 12:07] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Oracle MAX [message #539838 is a reply to message #539823] |
Wed, 18 January 2012 13:57  |
|
pronosen
Messages: 5
Registered: January 2012
|
Junior Member |
|
|
Per previous requests, here is my MS SQL code (I don't know if it will also work on Oracle)that creates table and some random data.
DECLARE @Table TABLE (FISCAL_YEAR INT, ACCOUNTING_PERIOD INT)
INSERT @Table VALUES(2012, 2)
INSERT @Table VALUES(2011, 12)
INSERT @Table VALUES(2012, 1)
Is it possible in Oracle to return data in one single pass through the table that would return only one line with maximum value for the FISCAL_YEAR column, and maximum value present under CCOUNTING_PERIOD column that is available for the MAX(FISCAL_YEAR) present. In this example the data returned should show 2012 for FISCAL_YEAR, and 2 for ACCOUNTING_PERIOD.
Is it also possible to do this by not using the MAX() function as it runs slow on Oracle?
Thank you.
[Updated on: Wed, 18 January 2012 13:59] Report message to a moderator |
|
|
|
| Re: Oracle MAX [message #539839 is a reply to message #539838] |
Wed, 18 January 2012 14:15 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
pronosen wrote on Wed, 18 January 2012 14:57Is it also possible to do this by not using the MAX() function as it runs slow on Oracle?
What is slow? So far I do not see any SQL you ran. Do you have one? If so post it along with explain plan and timing.
SY.
|
|
|
|
| Re: Oracle MAX [message #539840 is a reply to message #539838] |
Wed, 18 January 2012 15:16 |
John Watson
Messages: 8979
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
There isn't much point in posting SQL Server code. This is what we need:create table atable(fiscal_year integer,accounting_period integer);
insert into atable values(2012,2);
insert into atable values(2011,12);
insert into atable values(2012,1);
Is this what you want -orcl> select fiscal_year,max(accounting_period) from
2 (select * from atable where fiscal_year=(select max(fiscal_year) from atable))
3 group by fiscal_year;
FISCAL_YEAR MAX(ACCOUNTING_PERIOD)
----------- ----------------------
2012 2 |
|
|
|
|
|
|
|
|
|
| Re: Oracle MAX [message #540003 is a reply to message #539850] |
Thu, 19 January 2012 12:14 |
|
pronosen
Messages: 5
Registered: January 2012
|
Junior Member |
|
|
BlackSwan & Barbara: thank you for your input.
I tried running the code you posted against the datasource:
WITH max_val
AS (SELECT fiscal_year,
accounting_period
FROM atable
ORDER BY 1 DESC,
2 DESC)
SELECT fiscal_year,
accounting_period
FROM max_val
WHERE ROWNUM = 1;
This code runs about 6 sec faster than the one I was using before (2m6s instead 2m12s):
SELECT
MAX(FISCAL_YEAR) KEEP (DENSE_RANK FIRST ORDER BY FISCAL_YEAR DESC) FISCAL_YEAR,
MAX(ACCOUNTING_PERIOD) KEEP (DENSE_RANK FIRST ORDER BY FISCAL_YEAR DESC, ACCOUNTING_PERIOD DESC) ACCOUNTING_PERIOD
FROM atable
I am starting to suspect that the ACCOUNTING_PERIOD field is not indexed, and that's why this query takes so long. Another indication of this point is that it takes 0sec to return MAX(FISCAL_YEAR) by itself, and very long to return MAX(ACCOUNTING_PERIOD) by itself.
One more question: In the code you posted, (the part "ORDER BY 1 DESC, 2 DESC"), do numbers 1 and 2 represent the column positions in the ACTUAL UNDERLYING TABLE, or column positions under the "WITH max_val" temp. table?
Once again, thank you all for your assistance.
|
|
|
|
| Re: Oracle MAX [message #540009 is a reply to message #540003] |
Thu, 19 January 2012 12:51 |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
pronosen wrote on Thu, 19 January 2012 10:14
I am starting to suspect that the ACCOUNTING_PERIOD field is not indexed, and that's why this query takes so long. Another indication of this point is that it takes 0sec to return MAX(FISCAL_YEAR) by itself, and very long to return MAX(ACCOUNTING_PERIOD) by itself.
You can select from user_indexes or all_indexes to see what indexes are on your table. You can also SET AUTOTRACE ON from SQL*Plus before running the query to see what execution plan the optimizer is using. You can also run DBMS_STATS.GATHER_TABLE_STATS(USER, 'ATABLE') to ensure that the statistics that the optimizer uses to select the execution plan are current.
pronosen wrote on Thu, 19 January 2012 10:14
One more question: In the code you posted, (the part "ORDER BY 1 DESC, 2 DESC"), do numbers 1 and 2 represent the column positions in the ACTUAL UNDERLYING TABLE, or column positions under the "WITH max_val" temp. table?
The 1 and 2 are the column positions in that sub-query, not the underlying table. In this case, that sub-query is the only sub-query in the sub-query factoring (with) clause, but it can have more than one such sub-query. It is not a temporary table.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
