Oracle SQL - Why this query has full table scan?

Question

I've got a problem with this query:

SELECT *
  FROM customer_table PARTITION (p25062014)
 WHERE (   customer_table.username NOT IN ('user1','user2','user3')
        OR customer_table.username IS NULL
       )
   AND (customer_table.ip NOT IN ('ip1','ip2','ip3') OR customer_table.ip IS NULL
       )
   AND (   customer_table."ACCOUNT DEVICE ID" NOT IN ('deviceId1','deviceId2','deviceId3')
        OR customer_table."ACCOUNT DEVICE ID" IS NULL
       )

Even if I've created the indexes on this table for these fields, I've got a "Table Access Full" on the table:

CREATE INDEX customer_table_USERNAME ON customer_table
(USERNAME)

CREATE INDEX customer_table_DEVICE_ID ON customer_table
("ACCOUNT DEVICE ID")

CREATE INDEX customer_table_IP ON customer_table
(IP)

Plan
SELECT STATEMENT  ALL_ROWSCost: 2  Bytes: 965  Cardinality: 1       
    2 PARTITION RANGE SINGLE  Cost: 2  Bytes: 965  Cardinality: 1  Partition #: 1  Partitions accessed #21  
        1 TABLE ACCESS FULL TABLE customer_table Cost: 2  Bytes: 965  Cardinality: 1  Partition #: 2  Partitions accessed #21

How I can I fix it? Thanks.

If your table has only one row ("Cardinality = 1"), then a full table scan is the most efficient method for accessing the data for almost any query I can think of. — Gordon Linoff
Hi Gordon, this table is almost empty for now, but soon it will contains about 2 millions of record for every partition. — Fabio
Does it need to be fixed? What actual issue are you facing? Null values aren't indexed and not in usually can't use an index efficiently, so a full table (or partition) scan may appropriate - even if you do have more than one row. Are your statistics up to date? Oracle will choose a plan based on the statistics and the state it thinks the table is in when the query is parsed; not on your projected growth, which it knows nothing about. — Alex Poole
@Fabio . . . When you add more data to the table -- and be sure the statistics are updated -- then Oracle will choose a better execution plan. — Gordon Linoff
And how I can be sure that Oracle updates the statistics? I also can change the query in order to have better performance, but I can't imagine a different query — Fabio

Erich Kitzmueller Erich Kitzmueller · Accepted Answer · 2014-07-01T11:03:36

NOT IN where clauses usually do not use indexes, since there is not much to gain from the index. Read the details here. Though that blog explains why indexes are ignored for the "not eqals" comparision, the same is valid also for NOT IN.

Oracle SQL - Why this query has full table scan?

1 Answers