CODEWITHSUNDEEP
sqloracle-databasesql-execution-plan
Fabio
Fabio

Reputation: 13

Oracle SQL - Why this query has full table scan?

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.

Upvotes: 0

Views: 2476

Answers (1)

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36977

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.

Upvotes: 1

Related Questions