| Better query for max(date) per account in big table [message #669094] |
Tue, 03 April 2018 04:02  |
 |
nciteamo
Messages: 27
Registered: October 2014
|
Junior Member |
|
|
Hai Guys
is there any better way for this query
select account_number, product_code, max(holding_date) holding_date
from rg_portfolio a
where 1=1
and holding_date <= to_date ('19-jun-2017','DD/MM/YYYY')
group by account_number, product_code
the data without aggregation is 26 millions
the query takes really long time
index is in holding_date
PLAN_TABLE_OUTPUT
SQL_ID axmm5prw982j7, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS*/a.account_number, a.product_code,
max(a.holding_date) holding_date
from rg_portfolio a where 1=1
and holding_date <= to_date ('19-jun-2017','DD/MM/YYYY') group by
a.account_number, a.product_code
Plan hash value: 4291493512
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 0 |
| 1 | HASH GROUP BY | | 1 | 107K| 0 |00:00:00.01 | 0 |
|* 2 | INDEX FAST FULL SCAN| IDX_RG_PORTFOLIO_DATE_FAHRUL | 1 | 26M| 4155K|00:00:05.98 | 25816 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("HOLDING_DATE"<=TO_DATE('19-jun-2017','DD/MM/YYYY'))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- 1 Sql Plan Directive used for this statement
--moderator edit: added [code] tags, please do so yourself in future
[Updated on: Tue, 03 April 2018 07:45] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Better query for max(date) per account in big table [message #669101 is a reply to message #669100] |
Tue, 03 April 2018 06:45  |
|
nciteamo
Messages: 27
Registered: October 2014
|
Junior Member |
|
|
msol25 wrote on Tue, 03 April 2018 18:17hi,
In your query, you have not used correct date format.It should be:
select account_number, product_code, max(holding_date) holding_date
from rg_portfolio a
where 1=1
and holding_date <= to_date ('19-jun-2017','DD-Mon-YYYY')
group by account_number, product_code
hi thanks for your response, but the time is still the same, i think its caused by the data is too big and now i am thinking to clean unused data from that table
[Updated on: Tue, 03 April 2018 06:46] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Better query for max(date) per account in big table [message #669105 is a reply to message #669094] |
Tue, 03 April 2018 07:32 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Quote:index is in holding_date The index that is being used is a composite index on account_number, product_code, and holding_date. You would need an index with holding_date as the leading column if you want an indexed range scan access path.
You have not gathered statistics for the table. You should certainly do that, with histograms, or the optimizer has no chance of getting it right.
|
|
|
|
| Re: Better query for max(date) per account in big table [message #669106 is a reply to message #669096] |
Tue, 03 April 2018 07:34 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
nciteamo wrote on Tue, 03 April 2018 10:16i am so sorry sir, i didnt say thanks in my previous questions, your replies was really valuable for me, in case of inconsistent wait event question, i took your advice to set parameter that always do direct path (and after having a discussion with my team of course).
You should update the topic (and your other topics) with what you did and what the result was. That way other people can learn. This forum is meant to spread knowledge, not just get you out of trouble.
|
|
|
|
| Re: Better query for max(date) per account in big table [message #669107 is a reply to message #669105] |
Tue, 03 April 2018 07:42 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
John Watson wrote on Tue, 03 April 2018 13:32Quote:index is in holding_date The index that is being used is a composite index on account_number, product_code, and holding_date. You would need an index with holding_date as the leading column if you want an indexed range scan access path.
You have not gathered statistics for the table. You should certainly do that, with histograms, or the optimizer has no chance of getting it right.
Barring pretty odd data skew, it is still picking the right path for me.
Index fast full is about as quick as one is liable to get on this type of query. Maybe reordering the index columns could cut that to a range scan over the index only but I'm not convinced it would be worth it when you boil single block reads down against multiblock.
An index FFS over 26 million rows should be fast enough (exceptions would be massive columns, extremely subpar hardware, huge composite index of which you need parts) except if the query is part of OLTP operations and at that point you have a fundamental design issue, not a performance one.
So to my mind, the answer remains>hardware.
[Updated on: Tue, 03 April 2018 07:43] Report message to a moderator |
|
|
|
| Re: Better query for max(date) per account in big table [message #669109 is a reply to message #669104] |
Tue, 03 April 2018 07:45 |
 |
msol25
Messages: 396
Registered: June 2011
|
Senior Member |
|
|
Hi,
Please can you try once below query:
select account_number, product_code,holding_date
from rg_portfolio a
where holding_date =
( select /*+ index_desc(b idx_holding_dt) */
from rg_portfolio b
where a.account_number = b.account_number
and a.product_code = b.product_code
and rownum = 1
);
Note: Replace idx_holding_dt index with your actual index name.
[Updated on: Tue, 03 April 2018 07:46] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
