If you eventually had added a dt_tm column to table AUDIT_DATA_BLOB, then this statement would suddenly have broken.
As a basic rule, always add a table name or alias prefix to every column in every multi-table statement.
Now, as to why I asked for them - without knowing where the columns came from, how would I possibly have guessed what the statement was truly doing? My mind-reading powers seem to have been on the blink recently
Okay, so now that I have at least some clue of what the statement is doing....
It does indeed seem odd that the statement will not use an index on that column. Reasons for this might include:
- The table is rather small and a full scan will be more efficient
- There are actually few distinct values for that column in the table and every value has many records, meaning that a significant percentage of the table will be read (anything over 5%?)
- The record size for B is very short (remember that a LOB is just a pointer). Therefore, *lots* of records fit in a given block. This will make the optimizer more likely to choose a table scan for the table because it will be very efficient.