question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: question

  1. #1
    Join Date
    Jan 2002
    Posts
    44

    Angry

    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?
    thanks a lot

  2. #2
    Join Date
    Jan 2001
    Posts
    3,131
    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
    I remember when this place was cool.

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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)

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width