In DB2 8.1.2 for Windows, the CREATE INDEX command has an option,
"ALLOW REVERSE SCANS."

Why would one ever not want to allow reverse scans?

Stan

Apparently there's an increased chance of deadlock if you allow a scan
in both directions.

Stanley Sinclair wrote:

> In DB2 8.1.2 for Windows, the CREATE INDEX command has an option,
> "ALLOW REVERSE SCANS."
>
> Why would one ever not want to allow reverse scans?
>
> Stan

Blair,

The above implies that the index scan goes simultanously in both ASC
and DESC -- ie, that there are two scans simultaneously. Certainly
sound more efficient for a large table. Does the Optimizer put its
$0.02 in?

Stan

No, the scan for one query won't go in both directions at the same time
- the extra chance for deadlocks could arise from two queries sorting on
the same column in opposite directions.

Stanley Sinclair wrote:

> Blair,
>
> The above implies that the index scan goes simultanously in both ASC
> and DESC -- ie, that there are two scans simultaneously. Certainly
> sound more efficient for a large table. Does the Optimizer put its
> $0.02 in?
>
> Stan

"Blair Adamache" <badamache@2muchspam.yahoo.com> wrote in message
news:bmaggg$n6$1@hanover.torolab.ibm.com...
> No, the scan for one query won't go in both directions at the same time
> - the extra chance for deadlocks could arise from two queries sorting on
> the same column in opposite directions.
>
That's nasty. Sounds like a train wreck.

> deadlocks could arise from two queries sorting on
> the same column in opposite directions

How can sorting create any deadlock ever?

Regards Rolf

Rolf,

Instead of "sorting on," read "searching the index on." Right, Blair?

You are reading "sorting" as in the SQL command "SORT BY."

Stan

Here's how the desginer who built it described it (Type 2 indexes were
added in v8):

"There is a small additional chance of a deadlock between 2 scanners.
This was particularly the case before type 2 indexes, and much less so
now. Before type 2 indexes, you could get deadlock doing a forward scan
and reverse scan on the same range, with at least one X locking."

Stanley Sinclair wrote:

> Rolf,
>
> Instead of "sorting on," read "searching the index on." Right, Blair?
>
> You are reading "sorting" as in the SQL command "SORT BY."
>
> Stan