SEO

I know what defining an index to ALLOW REVERSE SCANS does.

My question is, under what circumstances will the optimizer find a
reverse scan useful? In all cases that I can think of, the optimizer
should be able to traverse the index in the normal way.

On Dec 6, 2:54 am, deangc <dean.cochr...@gmail.com> wrote:
> I know what defining an index to ALLOW REVERSE SCANS does.
>
> My question is, under what circumstances will the optimizer find a
> reverse scan useful? In all cases that I can think of, the optimizer
> should be able to traverse the index in the normal way.

One situation is that sometimes to issue "SELECT MAX(col) ... " and
other times to issue "SELECT MIN(col) ... " for that column.

deangc wrote:
> I know what defining an index to ALLOW REVERSE SCANS does.
>
> My question is, under what circumstances will the optimizer find a
> reverse scan useful? In all cases that I can think of, the optimizer
> should be able to traverse the index in the normal way.
>
ORDER BY in reverse order of the index.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

When you have an index with multiple columns does it make much sense
to use ALLOW REVERSE SCANS? For instance:

COL1, COL2 DESC, COL3, COL4 DESC

?

Thanks,
Chris

ChrisC wrote:
> When you have an index with multiple columns does it make much sense
> to use ALLOW REVERSE SCANS? For instance:
Let's turn this around: In later versions of DB2 for LUW INDICEs are by
default created with that option. There is no extra cost (anymore).
No matter what your index is, who says you never want to see the "last
rows"?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

On 6 Dez., 03:24, Serge Rielau <srie...@ca.ibm.com> wrote:
> Let's turn this around: In later versions of DB2 for LUW INDICEs are by
> default created with that option. There is no extra cost (anymore).

".. later version .." ?? V8 ? v9 ?

were currently v8 FP14, do we suffer (from extra costs) if we use this
option ? how heavy are the costs ? (storage ? extra CPU cycles, ??)

joerg

On Dec 5, 6:24 pm, Serge Rielau <srie...@ca.ibm.com> wrote:
> ChrisC wrote:
> > When you have an index with multiple columns does it make much sense
> > to use ALLOW REVERSE SCANS? For instance:
>
> Let's turn this around: In later versions of DB2 for LUW INDICEs are by
> default created with that option. There is no extra cost (anymore).
> No matter what your index is, who says you never want to see the "last
> rows"?

There must be some additional cost. It would be slight, I would guess.
Because you are storing some extra data in the index leaf pages (a
pointer to the previous page in addition to one to the next), you'd
have some increase in index depth. I agree it wouldn't be much.

I did try this, creating indexes on DB2 LUW 8, FP 10 (I think it
is...) with and without ALLOW REVERSE SCANS, and it did indeed show an
increased cost for one of MIN/MAX of the indexed column. I don't
understand why that would be the case. Surely, to find the MAX of an
indexed column requires you to read, at most, (index page depth)
pages. I don't see why making the leaf level a double linked list
would improve this. Index page depth is always going to be orders of
magnitude smaller than the number of leaf pages.

Now, ORDER BY DESC I can understand: it makes sense that ALLOW REVERSE
SCANS would eliminate the need for a sort. But MIN/MAX I don't
understand.

jammann.ch@gmail.com wrote:
> On 6 Dez., 03:24, Serge Rielau <srie...@ca.ibm.com> wrote:
>> Let's turn this around: In later versions of DB2 for LUW INDICEs are by
>> default created with that option. There is no extra cost (anymore).
>
> ".. later version .." ?? V8 ? v9 ?
>
> were currently v8 FP14, do we suffer (from extra costs) if we use this
> option ? how heavy are the costs ? (storage ? extra CPU cycles, ??)
deangc pretty much summed it up. this is about a reverse pointer. Even
on a 4K page this is very low overhead, which is why it's now default.
I'm sure this changed no later than DB2 9. May have been 8.2... but
that's eons ago here in development ;-)
You should be able to run a simple test by creating a dummy table and a
dummy index. Just check SYSCAT.INDEXES:
db2 => create table x(c1 int);
DB20000I The SQL command completed successfully.
db2 => create index i on x(c1);
DB20000I The SQL command completed successfully.
db2 => select reverse_scans from syscat.indexes where indname = 'I';

REVERSE_SCANS
-------------
Y

1 record(s) selected.
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab