Sybase stored procedure - how do I create an index on a #table?
- by DVK
I have a stored procedure which creates and works with a temporary #table
Some of the queries would be tremendously optimized if that temporary #table would have an index created on it.
However, creating an index within the stored procedure fails:
create procedure test1 as
SELECT f1, f2, f3
INTO #table1
FROM main_table
WHERE 1 = 2
-- insert rows into #table1
create index my_idx on #table1 (f1)
SELECT f1, f2, f3 FROM #table1 (index my_idx) WHERE f1 = 11 -- "QUERY X"
When I call the above, the query plan for "QUERY X" shows a table scan.
If I simply run the code above outside the stored procedure, the messages show the following warning:
Index 'my_idx' specified as optimizer hint in the FROM clause of table '#table1' does not exist. Optimizer will choose another index instead.
This can be resolved when running ad-hoc (outside the stored procedure) by splitting the code above in two batches by addding "go" after index creation:
create index my_idx on #table1 (f1)
go
Now, "QUERY X" query plan shows the use of index "my_idx".
QUESTION: How do I mimique running the "create index" in a separate batch when it's inside the stored procedure? I can't insert a "go" there like I do with the ad-hoc copy above.
P.S. If it matters, this is on Sybase 12.
