Indexing a Temporary Table...

Does anyone have any insight to indexing a explicit temporary table created with a SELECT ... INTO TEMP ... clause. I create one with no errors, but the EXPLAIN plan tells me that the optimizer is doing a SEQUENTIAL SCAN instead of using the INDEX PATH. Thanks! Bryan Hughes Healtheon Corp.

Bryan Hughes wrote: > Does anyone have any insight to indexing a explicit temporary table created > with a SELECT ... INTO TEMP ... clause. > > I create one with no errors, but the EXPLAIN plan tells me that the > optimizer is doing a SEQUENTIAL SCAN instead of using the INDEX PATH. You mean you created the index with a CREATE INDEX statement, but it is not being used? It works for me. What version of what product are you using? Did you try UPDATE STATISTICS? (I didn't have to, but it couldn't hurt.) June -- june_t@hotmail.com Grounded in Palo Alto, living on M&M's (plain)

On Thu, 7 Jan 1999 17:10:31 -0800, "Bryan Hughes" <bryan@healtheon.com> wrote: >Does anyone have any insight to indexing a explicit temporary table created >with a SELECT ... INTO TEMP ... clause. > >I create one with no errors, but the EXPLAIN plan tells me that the >optimizer is doing a SEQUENTIAL SCAN instead of using the INDEX PATH. Do you mean that the select...into temp statement uses a sequential scan, or subsequent selects on the temp table use a sequential scan? How many rows in the temp table? what sort of selects? Example? Cheers, Douglas Wilson

On Fri, 08 Jan 1999 04:17:29 GMT, dgwilson@gte.net (Douglas Wilson) wrote: >On Thu, 7 Jan 1999 17:10:31 -0800, "Bryan Hughes" <bryan@healtheon.com> wrote: > >>Does anyone have any insight to indexing a explicit temporary table created >>with a SELECT ... INTO TEMP ... clause. >How many rows in the temp table? what sort of selects? 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. Cheers, Douglas Wilson

Douglas Wilson wrote: > 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 as the index is built. June -- june_t@hotmail.com Grounded in Palo Alto, living on animal crackers