Is a reverse index scan distinquished from an index scan in a the plan of the optimizer.

asked 23 May '11, 02:49

Martin's gravatar image

Martin
8.6k119152237
accept rate: 14%

You mean when you have an index on colA ASC and the query would sort by colA DESC?

(No, I don't know the answer...)

(23 May '11, 04:00) Volker Barth
Replies hidden

yes, I know the "index scan" strategy in the plans of the optimizer, but will a "reverse index scan" be explicitly named?

(23 May '11, 08:04) Martin

Yes, if the optimizer chooses a reverse index plan then it will distinguish this in the plan.

Example:

create table foo( i int primary key);
insert into foo( i ) select row_num from sa_rowgenerator( 1, 999999 );
commit;
select explanation( 'select i from foo where i between 333333 and 333444 order by i asc' );
--> result is foo<foo(IO)>
select explanation( 'select i from foo where i between 333333 and 333444 order by i desc' );
--> result is foo<foo(R,IO)>

Note that the 'R' in the second plan means "reverse".

permanent link

answered 23 May '11, 10:50

Mark%20Culp's gravatar image

Mark Culp
23.4k9132275
accept rate: 40%

1

FWIW: I see that 'R' is missing from the [list of plan abbreviations][1] in the docs - I will ask to have it added.

[1] http://dcx.sybase.com/index.html#1201/en/dbusage/abbreviations-plan-queryopt.html

(23 May '11, 10:54) Mark Culp
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×27

question asked: 23 May '11, 02:49

question was seen: 836 times

last updated: 23 May '11, 10:54