I am struggling to resolve a performance issue with an Oracle query that is opting to perform a full table scan of a table with several million records. The table is partitioned by month of a timestamp column and contains records that resemble work items. Multiple concurrent processes run a query against it like this:
select t.col1, t.col2, t.some_nullable_column, t.item_number from mytable t where t.rowid in ( select e.rowid from mytable e decode(t.some_nullable_column,null,0,1) = 0 order by (decode(t.some_nullable_column,null,0,1)), e.item_number fetch next 1000 rows only ) for update of t.some_nullable_column skip locked
You can glean the details of the partitioned table. The LOCAL function based index is as follows:
create index idx_mahindex on mytable (decode(some_nullable_column, null, 0, 1), item_number) LOCAL;
I am trying to get the oldest 1000 records that have a NULL value in SOME_NULLABLE_COLUMN so that the application process can lock each row and update them safely.
If I run an explain plan on the inner view query it seems to prefer an INDEX FAST FULL SCAN, and then a SORT operation. I know that I only want the oldest 1000 records so my hope was that by specifying only columns on the index, and ordering by those columns on the index in the same order, that the analyzer would be smart enough to just start reading left most leaves of the index until I find 1000 records that satisfy my filter criteria (since the index is ordered by this function, then by item_number column). Instead it is scanning the whole index wastefully.
I COULD live with that, but it gets obscenely worse when I run this inner query within the SELECT … FOR UPDATE SKIP LOCKED query. The explain plan here shows the preferred plan to be the inner view doing a FULL TABLE SCAN and rejecting the index outright. If I hint the query to force the index then it will not do the FAST FULL SCAN and SORT, it will not do INDEX RANGE SCAN, and instead will scan the entire index for rowids, then do a batched lookup for the indexed columns, from the table. The performance here is even worse still.
Is there something I am missing here that is important? If the index is ordered, it should be easy to range scan the index for the columns in the index and get a list of rowids in sorted order. Does it have to do something with the fact that this is a partitioned table? Is there an aspect of SELECT … FOR UPDATE SKIP LOCKED that makes the CBO think that a full table scan or full index scan is necessary?