All geek questions in one place

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

I have a stored procedure that creates and works with a temporary #table

Some of the queries will be extremely optimized if this temporary one #tablehas an index created on it.

However, creating an index inside a 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" displays a table scan.

If I just run the code above the stored procedure, the messages show the following warning:

The pointer "my_idx", indicated as an optimizer hint in the FROM clause of table "# table1", does not exist. The optimizer will select a different index.

ad-hoc ( ) "go" :

create index my_idx on #table1 (f1)
go

"QUERY X" "my_idx".

. " " , ? "" , . , "" QUERY X " ", , .

P.S. , Sybase 12 (ASE 12.5.4)

UPDATE

" " Googling, . , , .

, , porc . " " 11.5.1.

+3
2

, , Sybase, 12.5.4 , , . sql , .

( ), , , , . Sybase , , . Sybase 12.5.4, .

0

Source: https://habr.com/ru/post/1796171/

More articles:


All Articles