Indexing a Temporary Table...

Indexing a Temporary Table...

Post by Stephen Ber » Sat, 23 Jan 1999 04:00:00



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

accurate
as soon

- Show quoted text -

> as the index is built.

> June
> --

> Grounded in Palo Alto, living on animal crackers

 
 
 

1. Using Index on Temporary Table

Preecha ,

I dont know where the rest of this thread is but I am guessing that your
temp tables either dont exist or are empty when your stored procedure is
created.  If this is so then the reason why the indexes are not used is
because the execution plan is worked out when the procedure is created and
so no indexes or data exists.  If you are creating the index inside of the
procedure aswell SQL is just not clever enough to work out that it can use
that one UNLESS  you place the select statement inside of an exec("")
statement.  This will cause it to be evaluated at actual run-time meaning
you loose some of the performance from the procedure BUT it will see the
indexes then.

I hope this was of use to you.

Steve Robinson

2. Unexpected language change - ENglish to German

3. index on temporary table

4. Database Server Disk Usage

5. Can't reference index on temporary table

6. DTS - Where is information Stored?

7. Index for Temporary table in a Stored Procedure

8. Removing of old backupfiles failed

9. Revise question on Creating an Index on temporary table

10. Problem creating Index for Temporary Table

11. Creating Index in Temporary table

12. Index on temporary tables

13. help Creating Index on Temporary Tables