The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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.6k114149237
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
22.3k9129262
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: 728 times

last updated: 23 May '11, 10:54