by We treat temp tables just like real ones. Create it, load it, index it.
Then update stats including building distributions. This seems to work
the best. If you fill and then index a temp table (or real one) the
engine knows about the index, but doesn't it think the table is still empty.
(nrows in systables)?
Steve Berg
> > Aha! I just ran into this today. I created an index on a temp table
> > filled up the table, then indexed it (or maybe it was indexing and
then
> > filling, oops), and had a select something like:
> > select * from tmp_tbl
> > where rowid<>?
> > and indexed_field matches ? <-(cursor opened with something like
"123456*")
> > and indexed_field not matches "*00"
> > and some_other_field matches ?
> > which the optimizer saw fit to do a sequential scan on the table until
> > I did an 'update statistics' on the temp table, then it did use the
index.
> If you index and then fill, the statistics definitely do not get
automatically
> updated. But if you fill and then index, the statistics should be
as soon
> June
> --
> Grounded in Palo Alto, living on animal crackers