Reverse Key indexes

range scans are just not possible, because within the index structure physically adjacent key values are not sort-order adjacent.

The optimizer may choose an FFI or table scan, which would not be as efficient.

Not usually a problem because there is not much logical meaning in a range-scan of a synthetic key value, so an application is not likely to generate a query that would be helped by one.

I suppose that RK indexes cannot be used in an access path to provide a sort-order either.

Quote:

---

Originally posted by slimdave
range scans are just not possible, because within the index structure physically adjacent key values are not sort-order adjacent.

---

Well, to be absolutely precise, range scans on RK indexes actauly *are possible*, however only under very specific circumstances. There are two conditions to be met for index range scan to occur on RK indexes:

1. Index must be composite one (with two or more columns indexed)
2. There query must contain the equality predicate on leftmost column(s) of the index

Consider this example:

You have a reveresed key index consisting of two columns - col1 and col2. You have something like the following values stored in those indexed columns:

Code:

---

col1, col2
----  ----
  1,    1
  1,    3
  1,    4
...    ...
  17,    1
  17,    7
  17,  34
  17,  188
  18,    0
  19,    6
  21,  -1
...    ...

---

Now the optimizer will be able tu use range scan on this reversed key index for the query like the following:

Code:

---

SELECT * FROM my_table
  WHERE col1 = 17;

---

or for the ones like those:

Code:

---

SELECT * FROM my_table
  WHERE col1 = 17 AND col2 > 50;
SELECT * FROM my_table
  WHERE col1 = 17 AND col2 BETWEEN 50 AND 60;
SELECT * FROM my_table
  WHERE col1 = 17 AND col2 != 12;

---

That is because of the fact that despide the values for COL2 are reversed before they are stored in the RK index, they will still be stored addjacent to each other for the same COL1 value. So all the index entries for the same COL1 will be stored in the same (or at least adjacent ) leaf block(s) and thus range scan will be possible if you are looking for only one COL1 value.

"range scans are just not possible, because within the index structure physically adjacent key values are not sort-order adjacent."

did I say they were, i did correct myself?



"Not usually a problem because there is not much logical meaning in a range-scan of a synthetic key value, so an application is not likely to generate a query that would be helped by one. "

What do you mean? I would have thought that a range-scan of a synthetic key value was most logical or are we still talking in relation to RKIs here?



I suppose that RK indexes cannot be used in an access path to provide a sort-order either.

Not sure what you mean by this.


Gus

Quote:

---

"range scans are just not possible, because within the index structure physically adjacent key values are not sort-order adjacent."

did I say they were, i did correct myself?

---

no, you just asked whether they were not possible or just inefficient. That was an explanation of why they weren't possible. (OK, for non-composite indexes)

Quote:

---

"Not usually a problem because there is not much logical meaning in a range-scan of a synthetic key value, so an application is not likely to generate a query that would be helped by one. "

What do you mean? I would have thought that a range-scan of a synthetic key value was most logical or are we still talking in relation to RKIs here?

---

If you have a synthetic, numeric PK there is generally not a lot of call for query predicates like ...

MY_PK_COLUMN between 1000 and 1024

The range is not generally meaningful in terms of business logic.

Quote:

---

I suppose that RK indexes cannot be used in an access path to provide a sort-order either.

Not sure what you mean by this.

---

An index can be used as a means of satisfying an ORDER BY clause (with some restrictions).

So if you have an index of EMPLOYEE_START_DATE and you execute ...

SELECT * FROM EMPS
ORDER BY EMPLOYEE_START_DATE

... the optimizer might choose to scan the index in ascending order to retrieve the table rows, rather than FTS then sort. A RK index would not provide the sort order, so couldn't be used for this purpose.

Quote:

---

Originally posted by jmodic
you have chalanged me directly, so I will throw just one small comment....

---

Jurij:
Hey i didnt challange u........well i thought u have better knowledge in oracle than any 1, so i said it wud be intersting to c u comment u this thread...and u did..

Quote:

---

Originally posted by jmodic

No, the index serach in this case will actually be a bit slower compared to normal btree index (the difference will be minimal though). The actual index tree traversing is identical in both cases, but there is a tiny overhead of CPU time with RK indexes as Oracle has first to reverse the order of bytes of the searched values to make it the same as it is stored in the index.

---

it infact wud be as i said. PS below demo...(the table defination for both tables being same and data as well....analyzed on same day and so)

Code:

---

US18>select count(*) from TEMP_REPORTING_PRODUCT_Hier ;

  COUNT(*)
----------
    421047

Elapsed: 00:00:00.50


US18>select count(*) from TEMP1_REPORTING_PRODUCT_Hier ;

  COUNT(*)
----------
    421047

Elapsed: 00:00:00.34


US18>select index_type from dba_indexes where index_name='PK_TEMP_REPORTING_PRODUCT_HIER';

INDEX_TYPE
---------------------------
NORMAL


US18>select index_type from dba_indexes where index_name='PK_TEMP1_REPORTING_PROD_HIER';

INDEX_TYPE
---------------------------
NORMAL/REV



US18>select * from TEMP_REPORTING_PRODUCT_Hier where pin=512320;

      PIN PARENT_PIN        FORDATE  PH_ITEM_NAME      PH_ITEM_DESC                       
---------- ------------------ --------- ------------------ ------------------------------------
    512320 M5IJJ              28-MAR-01 QT-MU7A9-I9        DEC C V/A Install                   

Elapsed: 00:00:00.63


US18>select * from TEMP1_REPORTING_PRODUCT_Hier where pin=512320;

      PIN PARENT_PIN        FORDATE  PH_ITEM_NAME      PH_ITEM_DESC                       
---------- ------------------ --------- ------------------ ------------------------------------
    512320 M5IJJ              28-MAR-01 QT-MU7A9-I9        DEC C V/A Install                   

Elapsed: 00:00:00.47


US18>select * from TEMP_REPORTING_PRODUCT_Hier where pin=232478;

      PIN PARENT_PIN        FORDATE  PH_ITEM_NAME      PH_ITEM_DESC                       
---------- ------------------ --------- ------------------ -------------------------------
    232478 M3369              28-MAR-01 247318-375        SuperHeroes NT CKit A/P             

Elapsed: 00:00:00.62


US18>select * from TEMP1_REPORTING_PRODUCT_Hier where pin=232478;

      PIN PARENT_PIN        FORDATE  PH_ITEM_NAME      PH_ITEM_DESC                       
---------- ------------------ --------- ------------------ -------------------------------
    232478 M3369              28-MAR-01 247318-375        SuperHeroes NT CKit A/P             

Elapsed: 00:00:00.47

---

Abhay.