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.
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 |
------------------------------------------
Your query:
SELECT object_id, object_name, owner FROM t;
does not have any WHERE, HAVING, or other filter clauses in it. Indices are useful when starting off with a large number of records and then discarding ones which are not part of the result set. In the case of your query, you are simply asking for the entire table, so Oracle is choosing to satisfy that request using a regular full table scan.
On the other hand, if your query were:
SELECT object_id, object_name, owner FROM t WHERE object_id = 1234;
in this case the index might be usable for a more rapid lookup of the record whose object_id is 1234.
3