Strange full table scan statements in Oracle

Question

I executed the following three statements in a single instance of Oracle 19c:

CREATE TABLE t AS SELECT \* FROM dba_objects;

CREATE INDEX ix_t_name ON t(object_id,  object_name, owner);

SELECT object_id, object_name, owner FROM t;

I want to know why the last query did not go through the index?????

when I added a primary key constraint on the object_id, SELECT object_id, object_name, owner FROM t; This statement will be indexed, and I would like to know why. Thank you for your answer.

Answer 1

The columns are nullable. Oracle Database doesn't index wholly null rows.

Thus it can't use an index scan, because there's a chance some rows will be missing from the index.

To overcome this, make at least one of these columns mandatory. This guarantees every row will be in the index.

create table t as select * from dba_objects;

create index ix_t_name on t(object_id,  object_name, owner);

select column_name, nullable from user_tab_cols
where  table_name = 'T'  
and    column_name in ( 'OBJECT_ID', 'OBJECT_NAME', 'OWNER' );

COLUMN_NAME          N
-------------------- -
OWNER                Y
OBJECT_NAME          Y
OBJECT_ID            Y

set serveroutput off
set feed only
select object_id, object_name, owner from t;
set feed on

select * from dbms_xplan.display_cursor( format => 'BASIC LAST');

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| T    |
----------------------------------

alter table t 
  modify object_name not null;

set feed only
select object_id, object_name, owner from t;
set feed on

select * from dbms_xplan.display_cursor( format => 'BASIC LAST');

------------------------------------------
| Id  | Operation            | Name      |
------------------------------------------
|   0 | SELECT STATEMENT     |           |
|   1 |  INDEX FAST FULL SCAN| IX_T_NAME |
------------------------------------------