Re: Updatable column - index column consideration
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 10 Feb 2012 22:34:09 -0000
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.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
----- Original Message -----
From: "Antony Raj" <ca_raj@xxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, February 10, 2012 9:09 PM
Subject: Updatable column - index column consideration
Hi,
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.
Thanks
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
