| Functional based index [message #664022] |
Mon, 03 July 2017 09:56  |
 |
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  |
John Watson
Messages: 8929
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 #664047 is a reply to message #664042] |
Tue, 04 July 2017 03:37 |
cookiemonster
Messages: 13920
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 #664114 is a reply to message #664113] |
Tue, 04 July 2017 13:02 |
John Watson
Messages: 8929
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')); |
|
|
|
|
|
|
|
|
|
|
|
| Re: Functional based index [message #664133 is a reply to message #664120] |
Wed, 05 July 2017 00:14 |
John Watson
Messages: 8929
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
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'; |
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
Thank you, will go through the link