Re: Updatable column - index column consideration

From: Jonathan Lewis <>
Date: Fri, 10 Feb 2012 22:34:09 -0000
Message-ID: <97573D824F7A4062BEE1D4395213CB49_at_Primary>

Look at function-based indexes. One based on value P, one on value C. Exclude the H rows since it's a waste to index them.

Change the code to use the function-based indexes, and make sure that you have designed indexes to support the "most recent job id" subquery properly.


Jonathan Lewis
Oracle Core (Apress 2011)

  • Original Message ----- From: "Antony Raj" <> To: <> Sent: Friday, February 10, 2012 9:09 PM Subject: Updatable column - index column consideration


I have a huge table say 50 million rows.It has a status_flag column and it's values are 'H' and 'C'.
Everyday,a job inserts ~85000 rows into this table with a status_flag='P' and at the end of proceesing the batch job,the status_flag is gettin updated from 'P' to 'C' and the previous day's rows(identified by jobid) are updated from 'C' to 'H'.

Since this column status_flag is getting updated and volatile in nature,is it recommended not to index this column??

There are few other SELECT statements using status_flag as one their predicates.


-- Received on Fri Feb 10 2012 - 16:34:09 CST

Original text of this message