Home » SQL & PL/SQL » SQL & PL/SQL » Functional based index
Functional based index [message #664022] Mon, 03 July 2017 09:56 Go to next message
Bala_Y
Messages: 7
Registered: July 2017
Junior Member
Hi Team,

I have a question , my requirement is to update the column with few million records in the table for which the column is in where condition.

The column has , only below distinct values

field name: back_flag

Y - 1.6 million
N - 2000
Null - 2 million


The table is a fact table, which has index on other columns also.


I created functional based index as below, which improved performance around 50% ( 18min before, 9.5 min after creating index)
but the same is not working in Prod, even though we collected stats everyday.

create index BACK_FLAG_bmx on ORDER_FACT(BACK_FLAG,-1) ;


Update ORDER_FACT
set BACK_FLAG='N'
Process_date=sysdate
where BACK_FLAG='Y'
and report in ('ABC','XYZ');

Note: report column also indexed already.
Re: Functional based index [message #664023 is a reply to message #664022] Mon, 03 July 2017 10:36 Go to previous messageGo to next message
John Watson
Messages: 7620
Registered: January 2010
Location: Global Village
Senior Member
First, I would have expected that index to slow it down, not speed it up.
Second, to update 2000 rows in such a small table should take only a few seconds. Perhaps there is something else going on. Row lock contention, for example?
Third, that horribly formatted execution plan shows a query that is nothing like the one you posted. And it isn't using that index.
Fourth, I wish you would not say "records" when you mean "rows".

[Updated on: Mon, 03 July 2017 10:37]

Report message to a moderator

Re: Functional based index [message #664039 is a reply to message #664022] Tue, 04 July 2017 01:13 Go to previous messageGo to next message
bbob
Messages: 21
Registered: July 2017
Junior Member
To force Oracle use the index you should include the function call into where section, smt like:
...
where ORDER_FACT(BACK_FLAG,-1) = 'Y'
...

(I dont't know what result the function returns)
Re: Functional based index [message #664042 is a reply to message #664039] Tue, 04 July 2017 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

"ORDER_FACT(BACK_FLAG,-1)" is not a function call in CREATE INDEX, it is table ORDER_FACT columns (BACK_FLAG,-1).
The title is misleading.

Re: Functional based index [message #664047 is a reply to message #664042] Tue, 04 July 2017 03:37 Go to previous messageGo to next message
cookiemonster
Messages: 13291
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you were specifying back_flag = 'N' then using an index on back_flag might make sense, but back_flag = 'Y' gives you almost half the table and oracle won't use an index in that case. If the actual number of rows updated is relatively small then an index on all three columns in the where clause would probably help.
Also, as John suggests, there's almost certainly something else going on. My dev DB can update 1.5 million rows out of a 3 million row table in 3.5 mins and you're probably updating a lot less than that. Have you got any triggers on the table?
Re: Functional based index [message #664048 is a reply to message #664047] Tue, 04 July 2017 04:05 Go to previous messageGo to next message
bbob
Messages: 21
Registered: July 2017
Junior Member
try to specify hint:

update /*+ index(ORDER_FACT BACK_FLAG_bmx)*/
...
Re: Functional based index [message #664049 is a reply to message #664048] Tue, 04 July 2017 04:17 Go to previous messageGo to next message
John Watson
Messages: 7620
Registered: January 2010
Location: Global Village
Senior Member
Probably no point in trying to help. The OP, Bala_Y, would appear to be one of those Write Once Reply Never (WORN) devices.
Re: Functional based index [message #664051 is a reply to message #664048] Tue, 04 July 2017 04:19 Go to previous messageGo to next message
cookiemonster
Messages: 13291
Registered: September 2008
Location: Rainy Manchester
Senior Member
I really wouldn't try the hint, as we've already pointed out there's no upside to using that index.
Re: Functional based index [message #664113 is a reply to message #664051] Tue, 04 July 2017 12:52 Go to previous messageGo to next message
Bala_Y
Messages: 7
Registered: July 2017
Junior Member
Thank you so much all for your comments.

It is helping me to understand valid information about Indexes.

What do you suggest to improve the performance of the mentioned update statement

As I am in production support project ,occupied with quarter end validations, there is a delay in response.
Sorry for that.

Re: Functional based index [message #664114 is a reply to message #664113] Tue, 04 July 2017 13:02 Go to previous messageGo to next message
John Watson
Messages: 7620
Registered: January 2010
Location: Global Village
Senior Member
You need to provide the SQL that you are actually running, and the execution plan with statistics. Like this:
update /*+ gather_plan_statistic */ order_fact set .... ;
select * from table(dbms_xplan.display(format=>'allstats last'));
Do it in SQL*Plus, ad copy/paste what happens. Be sure to enclose it in [code] tags.
Re: Functional based index [message #664120 is a reply to message #664114] Tue, 04 July 2017 15:14 Go to previous messageGo to next message
Bala_Y
Messages: 7
Registered: July 2017
Junior Member
Plan hash value: 2938954394
 
-------------------------------------------------------------
| Id  | Operation            | Name                | E-Rows |
-------------------------------------------------------------
|   0 | UPDATE STATEMENT     |                     |   1941 |
|   1 |  UPDATE              | ORDER_MGMT_FACT |        |
|   2 |   PARTITION RANGE ALL|                     |   1941 |
|*  3 |    TABLE ACCESS FULL | ORDER_MGMT_FACT |   1941 |
-------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter("BACKLOG_FLAG"='Y' AND 
              ("RECORD_CREATED_BY"='m_ORDER_MGMT_FACT_BR' OR 
              "RECORD_CREATED_BY"='m_ORDER_MGMT_FACT'))
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Re: Functional based index [message #664121 is a reply to message #664120] Tue, 04 July 2017 15:16 Go to previous messageGo to next message
Bala_Y
Messages: 7
Registered: July 2017
Junior Member
Hi John, Please find the above attached plan, I dropped index which was created on backlog_flag, as it is not giving any performance improvement.

This plan is taken after dropping the mentioned index BACK_FLAG_bmx.

You are suggestions are welcome

Thank you
Re: Functional based index [message #664122 is a reply to message #664121] Tue, 04 July 2017 15:23 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
since you are update more than 75% of the rows, a FTS is better choice.
At some point in time, the SQL is as efficient as it can be.
Re: Functional based index [message #664125 is a reply to message #664122] Tue, 04 July 2017 15:53 Go to previous messageGo to next message
Bala_Y
Messages: 7
Registered: July 2017
Junior Member
Thanks for the suggestion, it means, no need to do any changes for the existing code, as I already dropped the new index which I created.
Re: Functional based index [message #664133 is a reply to message #664120] Wed, 05 July 2017 00:14 Go to previous messageGo to next message
John Watson
Messages: 7620
Registered: January 2010
Location: Global Village
Senior Member
For heavens sake, man, READ. Your plan does not have the execution statistics. Look at the note:
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
Do it again, and do it right.

At least one can see that the predicate is not what you said it was. Please show the statement you are running and the structure of the table. Also:
select count(*) from order_mgmt_fact where "RECORD_CREATED_BY"='m_ORDER_MGMT_FACT';
select count(*) from order_mgmt_fact where "RECORD_CREATED_BY"='m_ORDER_MGMT_FACT_BR';
Re: Functional based index [message #664204 is a reply to message #664133] Thu, 06 July 2017 15:47 Go to previous messageGo to next message
Bala_Y
Messages: 7
Registered: July 2017
Junior Member
Hi John,

I am a beginner in understanding explain plan, I have no idea why basic plan statistics not available.
Please let me know how to get it.

If I want to post any confidential information, how should I do, please suggest.

Thank you

Re: Functional based index [message #664205 is a reply to message #664204] Thu, 06 July 2017 16:01 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
http://lmgtfy.com/?q=oracle+basic+plan+statistics
Re: Functional based index [message #664207 is a reply to message #664205] Thu, 06 July 2017 16:58 Go to previous message
Bala_Y
Messages: 7
Registered: July 2017
Junior Member
Smile Thank you, will go through the link
Previous Topic: Report hangs (possibly query related).. is this possible cause?
Next Topic: Regular Expressions
Goto Forum:
  


Current Time: Sun Oct 21 23:31:02 CDT 2018