| Please help for Improving the Explain plan [message #645529] |
Tue, 08 December 2015 03:00  |
 |
msol25
Messages: 396
Registered: June 2011
|
Senior Member |
|
|
Dear,
Please can you help me for below mentioned query for improving explain plan.
Having function based index on trunc(actual_bill_dtm).
select /*+ index( b BILLSUMMARY_KPI_OPT_IX) */ *
from dm_irb_gsm.billsummary b
where trunc(actual_bill_dtm) > DATE '2015-12-08'
Explain Plan:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 114K| 26M| 18691 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| BILLSUMMARY | 114K| 26M| 18691 (1)| 00:00:01 |
|* 2 | INDEX FULL SCAN | BILLSUMMARY_KPI_OPT_IX | 114K| | 17853 (1)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(TRUNC(INTERNAL_FUNCTION("ACTUAL_BILL_DTM"))>TO_DATE(' 2015-12-08 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
filter(TRUNC(INTERNAL_FUNCTION("ACTUAL_BILL_DTM"))>TO_DATE(' 2015-12-08 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
|
|
|
|
| Re: Please help for Improving the Explain plan [message #645530 is a reply to message #645529] |
Tue, 08 December 2015 03:06  |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
1. You might get a better execution if you remove the hint and let the CBO decide whether to use your index.
2. Drop the FBI, create a normal index, and change your predicate to actual_bill_dtm > to_date('2015-12-08','yyyy-mm-dd')
How many rows in the table? How many rows does the query return?
|
|
|
|
| Re: Please help for Improving the Explain plan [message #645534 is a reply to message #645529] |
Tue, 08 December 2015 03:45 |
 |
FDAVIDOV
Messages: 20
Registered: December 2014
|
Junior Member |
|
|
My first question is: why would you need to express the WHERE condition as "trunc(actual_bill_dtm) > DATE '2015-12-08'"?
If I understand correctly, you have a timestamp that includes data AND time, right? If so, you can just calculate the date AND TIME as of which you want to get data (in your example it would be '2015-12-09', which is equivalent to '2015-12-09 00:00') and change the condition to:
actual_bill_dtm >= DATE '2015-12-09'
Just a thought.
By the way, John's suggestion is missing the fact that you want to extract records belonging to the NEXT DAY and on (i.e. an entry registered on '2015-12-08 01:00:00' should NOT be included).
[Updated on: Tue, 08 December 2015 03:48] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Please help for Improving the Explain plan [message #647604 is a reply to message #645626] |
Wed, 03 February 2016 21:08 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
** I apologize. After re-reading this post I see the John has already discussed this issue. **
Assuming your stats are up-to-date and the numbers you have posted are accurate, then your query should be doing an INDEX RANGE SCAN, not and INDEX FULL SCAN.
The reason you are not getting the right index usage is because of the trunc function applied to the column in the query (assuming that actual_bill_dtm is a DATE column). I do not understand what trunc is intended to do since
select /*+ index( b BILLSUMMARY_KPI_OPT_IX) */ *
from dm_irb_gsm.billsummary b
where trunc(actual_bill_dtm) > DATE '2015-12-08'
and
select /*+ index( b BILLSUMMARY_KPI_OPT_IX) */ *
from dm_irb_gsm.billsummary b
where actual_bill_dtm > DATE '2015-12-08'
will provide the same answer will they not?
1. make sure stats are up-to-date
2. check to see if there is skew on your date column actual_bill_dtm
3. use this query and let Oracle do the driving
select *
from dm_irb_gsm.billsummary b
where actual_bill_dtm > DATE '2015-12-08'
You should get a query plan that uses the index to only fetch the 114K rows out of the 48 million you want.
? is there anything else you have not told that maybe we should know ?
If you want to learn the skill of SQL Tuning, you can get my book from Amazon. Here are some free book related items you can review before you purchase the book.
Provided below are:
1. the first chapter of the book. Reading this will help you decide if the book is something you are interested in before you spend money on it.
2. the scripts from the book. You can use these in your tuning work regardless of it you purchase the book or not later. These are free.
3. an organizational document that will help you record the significant events of a SQL Tuning session so you can explain to someone else later how you solved a problem.
4. a brief description of the kind of info you will want to provide to ORAFaq, when you are looking for detailed tuning help.
These artifacts are free, you do not need to buy the book to use them, and you can give them to others freely as well. Good luck. Kevin
Kevin
[Updated on: Wed, 03 February 2016 21:10] Report message to a moderator |
|
|
|
| Re: Please help for Improving the Explain plan [message #647617 is a reply to message #647604] |
Thu, 04 February 2016 03:35 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Kevin Meade wrote on Thu, 04 February 2016 03:08I do not understand what trunc is intended to do since
select /*+ index( b BILLSUMMARY_KPI_OPT_IX) */ *
from dm_irb_gsm.billsummary b
where trunc(actual_bill_dtm) > DATE '2015-12-08'
and
select /*+ index( b BILLSUMMARY_KPI_OPT_IX) */ *
from dm_irb_gsm.billsummary b
where actual_bill_dtm > DATE '2015-12-08'
will provide the same answer will they not?
They won't provide the same answer. See FDAVIDOV's post above.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
