Oracle ALTER TABLE MOVE INCLUDING ROWS not working properly

I'm trying to use ALTER TABLE MOVE...INCLUDING ROWS as a faster alternative to DELETE, but it's not working as expected when:

1. An index exists, and
2. The filtering condition results in an empty table

Here's an example:

create table t (
    c1 int not null
);

create index t_index on t(c1);

insert into t values ( 1 );
insert into t values ( 2 );

alter table t move including rows where c1 > 2 update indexes;

This should result in an empty table, yet:

select * from t;

That's b/c it's using t_index, as shown in the query plan:

----------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |         |     1 |    13 |     1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | T_INDEX |     1 |    13 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

If we force no index, we get the correct result:

select /*+no_index(t t_index)*/ *
from t;
---
(none)

If the filtering retains some rows, we don't get this:

alter table t move including rows where c1 > 1 update indexes;

select * from t;

I'm on Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production. Is this a known bug, or am I misunderstanding how Oracle is supposed to work?

Yes, this is a bug. I am able to reproduce as late as 19.18 (I don't have anything more recent). Note what happens when you have an additional column that isn't indexed:

create table t (
    c1 int not null,
    c2 int not null
);

create index t_index on t(c1);

insert into t values ( 1,1 );
insert into t values ( 2,1 );

alter table t move including rows where c1 > 2 update indexes;

select /*+ index(t) */ * from t;

Result:

ORA-08103: object no longer exists

It used the index (because I hinted it to do so, it wouldn't have otherwise) and because it needed to get a column from the table segment that wasn't in the index, it began to compute the table block address from the ROWID stored in the index, but the ROWID referred to a non-existent segment (the old pre-move table), so threw this error. This proves that in the condition that ALTER TABLE MOVE INCLUDING ROWS matches 0 rows, Oracle is bypassing the index maintenance, but 0 rows also marks indexes at usable, so you have a usable index that has old invalid ROWIDs. Definitely a bug.

Your test is showing you a different answer rather than erroring when the index is used exclusively. Because you are not referring to any column not in the index, it doesn't actually visit the table at all (and that's why you didn't have to hint it to use the index, it saw that as an advantage and chose it as the optimal path). Your query is therefore getting everything it needs from the index itself, but the data in the index is bad because it hasn't been rebuilt when it should have been. It doesn't error for you because it never tries to use the ROWID since it doesn't need to visit the table segment. But it's simply another symptom of the same problem: an un-rebuilt index pointing to a non-existent segment. Clearly update indexes isn't happening.

Please create an SR with Oracle Support on this, it's definitely a bug when the result changes based on the execution path the optimizer chooses. If you don't have an MOS account let me know and I'll do it for you. A quick bug search didn't turn up any obvious matches, so it may be something nobody has reported yet.

Update Jan 2, 2024

I reported this in an SR back in August and Oracle Development is working this as a bug, so hopefully it will be fixed in a subsequent release.