query changing explain plan during index rebuild
alter index BNI_CC_CF_DT_OD rebuild parallel 8 online nologging ;
During the index rebuild the query
SELECT * FROM cal_cur_ownr.BOOKING_NAME_ITEM WHERE BOOKING_ID = :1 AND PURGE_DATE = :2
changed from this its normal execution plan
SELECT STATEMENT
PARTITION RANGE SINGLE
TABLE ACCESS BY LOCAL INDEX ROWID BOOKING_NAME_ITEM TABLE
INDEX RANGE SCAN IX_BNI_BKG INDEX
to
SELECT STATEMENT
PARTITION RANGE SINGLE
TABLE ACCESS FULL BOOKING_NAME_ITEM TABLE
BOOKING_NAME_ITEM is over 400Gb and a fts causes predictable problems with the application grinding to a halt. To solve the problem I had to kill all the processes doing fts and flush the shared pool after which the correct execution was used. I am not certain but believe the problem arose at the point where the alter index command completed. I am running 10.2.0.3 on a Linux 2-node RAC cluster. Has anyone run into similar problems ?