question

[sams]

hi,
i need to understand the following lines:
1) reverse key indexes r useful only for queries that contain equality predicates.
what r equality predicates?
2) 'coz lexically adjacent keys r not stored next to each other in a reverse key index,range searches can't be performed using such an index.
what does the above line stresses on?

[Mr.Hanky]

Hey sams; My translation on this topic is as follows, I'm not sure if it is correct.

Let's say you have an index that goes like this
0001
0002
0003
0004
0005

Well, if you use this index to look for 0999 you will have to search through 999 rows, correct?

Now if you reverse it you will have
1000
2000
3000
4000
5000

This would make searching much faster, see what I mean?
As I said I'm not sure if this is exactly correct but it is my translation of reverse key index.

MH

[pando]

it means

select * from emp
where empno = XXXX

see equality is the equal sign, the problem with reverse keys is that they are not sequential as normal b*tree index keys
if we have empno

1233
1234
1235
1236
1237
1238
1239 (next to each other)

we have 10 sequential keys when stored in normal b*tree index, in other words if we wanna search for 1236 (or from 1236 to 1239, range) it's pretty simple

if we have reverse keys we would have

9321
8321
7321
6321
5321
4321
3321 (NOT next to each other)

to find 1236 is simple as above as well, however if we wanna do range scan from 1236 to 1239 it gets more difficult, we have to search more blocks since they are not sequential
in other words not lexically adjacent keys (not next to each other)

[julian]

This would make searching much faster, see what I mean?

I don't know if the search will be faster but with reversing the index whose entries are being generated from a sequence at least the chance that consequent entries are in the same Oracle block is smaller. Example: if two instances of the same DB try to insert rows in a SALES table whose primary key is say generated from a sequence. Then the application shows that latest sales. What would be the problem: if the application is not partitioned and both instances select from the SALES tables, the new entries being in same blocks will fcrce Oracle to ping the blocks (well in 9i they should go through the interconnect). This will slow the application. But if the index is reverse, you might have totally eliminated the problem.