A query similar to this was getting a full table scan. Now we know the NDR in archived, deleted and flag is 1. My initial thought was because of this we would get a full table scan. I was wrong, because we are using owner like ‘S%’ this would trigger the full table scan.
As you see this first run did a full table scan.
Now look at what happens when we change owner like ‘S%’ to owner = ‘SYSTEM’. We now get an index range scan. Much better.
1 – filter(“ARCHIVED”=0 AND “DELETED”=0 AND “FLAG”=’1′)
2 – access(“OWNER”=’SYSTEM’)