Re: Does NOT automatically cause a full table scan? 9.2.0.5

From: Ben <balvey_at_comcast.net>
Date: 3 Nov 2006 10:37:08 -0800
Message-ID: <1162579028.073303.143930@h48g2000cwc.googlegroups.com>

mfullerton_at_gmail.com wrote:
> Can you post an explain plan for this? you may find that oracle rewrote
> your query. It could have also ran your query based on an index of t.b
> and taken the result set and filtered out the x,y,z.
>
> Also, histograms will only be used if you created them. I have no idea
> if you did since you didn't post anything about that.
> Ben wrote:
> > 9.2.0.5 Ent Ed AIX5L
> >
> > Does NOT automatically cause a full table scan or is that dependant
> > upon histograms and the skew of the data? For example a statement like
> >
> > select t.a, t.b, t.c
> > from t
> > where t.b = 'abc'
> > and not t.a = 'xyz';

This was just kind of a theoretical question but here is the explain plan that brought up the comment.

SELECT IBLITM, IBMCU
FROM PRODDTA.F4102
WHERE IBMCU = ' 45';

---

| Id  | Operation                   |  Name       | Rows  | Bytes |

Cost |

---

| 0 | SELECT STATEMENT | | 1072 | 41808 | 233 |
| 1 | TABLE ACCESS BY INDEX ROWID| F4102 | 1072 | 41808 | 233 |
| 2 | INDEX RANGE SCAN | F4102_10 | 1072 | | 11 |

---

SELECT IBLITM, IBMCU
FROM PRODDTA.F4102
WHERE NOT IBMCU = ' 45';

---

| Id | Operation | Name | Rows | Bytes | Cost |

---

|   0 | SELECT STATEMENT     |             |   301K|    11M|  3564 |
|   1 |  TABLE ACCESS FULL   | F4102       |   301K|    11M|  3564 |
--------------------------------------------------------------------

I didn't create histograms, that was just a general question. If there was a histogram that said 94% of the values in ibmcu = 45, then would it use that same index to get the values that <> 45? Received on Fri Nov 03 2006 - 12:37:08 CST