something about the cbo was bothering me

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.

image

As you see this first run did a full table scan.

image

Now look at what happens when we change owner like ‘S%’ to owner = ‘SYSTEM’. We now get an index range scan. Much better.

image

1 – filter(“ARCHIVED”=0 AND “DELETED”=0 AND “FLAG”=’1′)

2 – access(“OWNER”=’SYSTEM’)