Sybase stored procedure - how do I create an index on a #table?

Question

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. Please note that I'm aware of the solution of "split up the 'QUERY X' into a separate stored procedure" and am looking for a solution that will avoid that.

P.S. If it matters, this is on Sybase 12 (ASE 12.5.4)

---

UPDATE:

I have been seeing several references to "schema bumping" during my Googling before posing the question. But that doesn't seem to happen in my case.

You can create a table, populate it, create an index on it and select values from it in the same porc and have the optimizer fully cost it based on accurate information. This is called 'schema bumping' and has been in place since 11.5.1.

Answer 1

The Sybase documentation says that you create and use a temporary index in the same stored procedure:

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc20023_1251/html/optimizer/X26029.htm

I think to get around this you will need to split your stored procedure into at least two parts, one to create and populate the table then build the index, and then a second one to run the select query.

Answer 2

I am not sure how you are getting this problem, might be in older version of Sybase, however with version 12.5.4 I tried executing the same thing as suggested by you but in my case the optimizer correctly suggested the use of index created in the stored procedure. Usually in a stored procedure we do not need to break sql into batches because else we would have been required to have a seperate batch for create table command as well.

In case we try to create index within a same batch (not in a stored procedure) we will do get the same error as specified by you above because we are trying to create an index on a table and then trying to use it within the same batch. Usually the Sybase server will compile the whole batch in one go and hence the problem. But as far as stored procedure is concerned in Sybase 12.5.4 there will be no problem.