Home » RDBMS Server » Performance Tuning » Better query for max(date) per account in big table (12.1.0.2.0)
Better query for max(date) per account in big table [message #669094] Tue, 03 April 2018 04:02 Go to next message
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 #669095 is a reply to message #669094] Tue, 03 April 2018 04:05 Go to previous messageGo to next message
John Watson
Messages: 8270
Registered: January 2010
Location: Global Village
Senior Member
Were the replies to your previous questions of any value? Or is replying just waste of time?
Re: Better query for max(date) per account in big table [message #669096 is a reply to message #669095] Tue, 03 April 2018 04:16 Go to previous messageGo to next message
nciteamo
Messages: 27
Registered: October 2014
Junior Member
i 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).

Re: Better query for max(date) per account in big table [message #669097 is a reply to message #669096] Tue, 03 April 2018 04:42 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Better hardware.
Re: Better query for max(date) per account in big table [message #669100 is a reply to message #669097] Tue, 03 April 2018 06:17 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
hi,

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 

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 Go to previous messageGo to next message
nciteamo
Messages: 27
Registered: October 2014
Junior Member
msol25 wrote on Tue, 03 April 2018 18:17
hi,

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 #669102 is a reply to message #669101] Tue, 03 April 2018 07:15 Go to previous messageGo to next message
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  max(holding_date)
          from   rg_portfolio b
          where  a.account_number = b.account_number
           and   a.product_code = b.product_code
     );

Re: Better query for max(date) per account in big table [message #669104 is a reply to message #669102] Tue, 03 April 2018 07:20 Go to previous messageGo to next message
nciteamo
Messages: 27
Registered: October 2014
Junior Member
msol25 wrote on Tue, 03 April 2018 19:15
Hi,

Please can you try once below query:


select  account_number, product_code,holding_date
from    rg_portfolio a
where   holding_date =
     (   select  max(holding_date)
          from   rg_portfolio b
          where  a.account_number = b.account_number
           and   a.product_code = b.product_code
     );

i have tried that one and same result
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 Go to previous messageGo to next message
John Watson
Messages: 8270
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 Go to previous messageGo to next message
John Watson
Messages: 8270
Registered: January 2010
Location: Global Village
Senior Member
nciteamo wrote on Tue, 03 April 2018 10:16
i 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 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
John Watson wrote on Tue, 03 April 2018 13:32
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.
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 Go to previous messageGo to next message
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

Re: Better query for max(date) per account in big table [message #669110 is a reply to message #669107] Tue, 03 April 2018 07:55 Go to previous messageGo to next message
John Watson
Messages: 8270
Registered: January 2010
Location: Global Village
Senior Member
I'm not sure. The estimated v. actual cardinalities are pretty odd. If that plan really is the best, then failing hardware changes some parallelism might help.
Re: Better query for max(date) per account in big table [message #669111 is a reply to message #669110] Tue, 03 April 2018 07:59 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
I'm actually most suspicious that 6 seconds is a "really long time"

Well that and the fact that this even is allowed
TO_DATE('19-jun-2017','DD/MM/YYYY')
Smile
Re: Better query for max(date) per account in big table [message #669113 is a reply to message #669105] Tue, 03 April 2018 08:08 Go to previous messageGo to next message
nciteamo
Messages: 27
Registered: October 2014
Junior Member
John Watson wrote on Tue, 03 April 2018 19:32
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.
i am sorry there was missed information
the index was already a composite index with holding_date as leading column
idx_rg_portfolio_date_fahrul(holding_date,product_code, account_number )

but after doing gather statistics for the table, its 3 times faster..

thank you very much sir

Re: Better query for max(date) per account in big table [message #669114 is a reply to message #669113] Tue, 03 April 2018 08:10 Go to previous message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Can you post the new query results? I'm curious.
Previous Topic: Fine Tune Big SQL Reporting Query
Next Topic: Is there a way to find out activities due to redo logs
Goto Forum:
  


Current Time: Mon Jun 01 18:47:03 CDT 2020