Search code examples
sqloracle

Strange full table scan statements in Oracle

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.

Solution

  • 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.