Re: Fragmentation usage

Hardly surprising since it has no way of having any idea at optimisation time what the subquery is going to return. Why do you use an uncorrelated subquery instead of an inner join? Not that would encourage parallel scans either, but it's just much more efficient SQL. Pleaes don't post in HTML, it's really annoying. Andy "Vijay" <rudrappa@india.hp.com> wrote in message news:<bu0htq$am9$1@terabinaries.xmission.com>... > This is a multi-part message in MIME format. ... > Hi All, > > I have a stored procedure which uses a table which has millions of > records & hence fragmented on a particular integer column. > Now my question is, > > Create procedure test > Select * from million_rec_table > Where fragmentation_col IN (select col from lookup_table) > End procedure> > The above query doesn't use the fragmentation query & hence it uses > a ALL fragmentation scan. > > ---------------------------------------------------------------------------- > - > > Suppose if the above query is modified as below > Create procedure test > Select * from million_rec_table > Where fragmentation_col IN (20, 30) > End procedure> > This particular procedure uses a fragmentation & scans only the fragment > which has record in 20 & 30... > > PLease let me know why this happens < Voluminous HTML snipped >