Is a reverse index scan distinquished from an index scan?

[MCMartin]

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

[VolkerBarth]

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...)

[MCMartin]

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

[MarkCulp · Accepted solution]

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".

[MarkCulp · Accepted solution]

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