Stored Procedure: Sequential/Index Search
Santram asked how to force a select in a stored procedure to use an index instead of a sequential scan, despite SET OPTIMIZATION. Leo Cardoso said a sequential scan is best on a small table and advised update statistics on a big one. Bashar suggested optimizer directives on IDS 7.3 or higher. Art S. Kagel asked for the real goal and suggested ORDER BY and current statistics. No outcome was reported.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
In stored procedure, I have a select stmt which retrieves data from a single table. Also, there is an index on all columns which I am going after in where clause. When execute, data is read sequentially instead of using index. I have tried with SET OPTIMIZATION LOW/HIGH/MEDIUM. But still fetching sequentially. Is there anyway I can force this select stmt to retrieve rows using Index and not sequentially? Please reply your suggestions at rajd@ialtd.com
Hi... >When execute, data is read sequentially instead of using index. I have tried >with SET OPTIMIZATION LOW/HIGH/MEDIUM. But still fetching sequentially. >Is there anyway I can force this select stmt to retrieve rows using Index >and not sequentially? If your table is small, the answer is no. The index is one more layer and the sequential scan is best. If your table is big, try update statistics and look at the query plan again! :) []s from Brazil LEO Cardoso ---
If you are IDS 7.3 or higher, you can control the access plan with optimizer directives. Check the syntax of the Select statement. -- Bashar Chalabi CTL, London santram <santram@ix.netcom.com> wrote in message news:7qons4$t98@dfw-ixnews4.ix.netcom.com... > In stored procedure, I have a select stmt which retrieves data from a single > table. Also, there is an index on all columns which I am going after in > where clause. > > When execute, data is read sequentially instead of using index. I have tried > with SET OPTIMIZATION LOW/HIGH/MEDIUM. But still fetching sequentially. > > Is there anyway I can force this select stmt to retrieve rows using Index > and not sequentially? > > Please reply your suggestions at rajd@ialtd.com > > > >
Bashar wrote: > > If you are IDS 7.3 or higher, you can control the access plan with optimizer > directives. Check the syntax of the Select statement. > > -- > Bashar Chalabi > CTL, London > > santram <santram@ix.netcom.com> wrote in message > news:7qons4$t98@dfw-ixnews4.ix.netcom.com... > > In stored procedure, I have a select stmt which retrieves data from a > single > > table. Also, there is an index on all columns which I am going after in > > where clause. > > > > When execute, data is read sequentially instead of using index. I have > tried > > with SET OPTIMIZATION LOW/HIGH/MEDIUM. But still fetching sequentially. > > > > Is there anyway I can force this select stmt to retrieve rows using Index > > and not sequentially? What is your goal here? What is the original problem with the SP that you are trying to solve? Do you want the rows in the order of that index? Just add an ORDER BY clause? That is what they are in the language for! Want that index used for some filtering that it is not being used for? Your statistics must be up-to-date for the optimizer to have a chance of selecting the correct index. Have you updated statistics according to recommendations? Get my dostats.ec utility, or any of the SQL, sh, or Perl scripts that purport to do this for you easily. Otherwise please, tell us your problem, not your proposed solution. Art S. Kagel