Forum Stats

  • 3,741,515 Users
  • 2,248,442 Discussions
  • 7,861,843 Comments

Discussions

Regarding Index Usage

AB312062
AB312062 Member Posts: 25
edited Mar 27, 2013 7:54AM in SQL & PL/SQL
Hi

I have some doubts regarding Indexes:

1. What is the difference between the below 2 index usage scenarios:
/*+ index(a index_name) parallel(a,4)*/
/*+ parallel_index(a,index_name,4)*/

Are both these same?

2. What is full index scan and fast full index scan?

3. I have a table created in database and it has a few indexes on some columns. How can I check whether the index is Bitmap or B-Tree ?


Thanks
AB
Tagged:

Best Answer

  • Peter Gjelstrup
    Peter Gjelstrup Member Posts: 4,128 Gold Trophy
    edited Mar 26, 2013 4:48PM Accepted Answer
    1. What is the difference between the below 2 index usage scenarios:
    /*+ index(a index_name) parallel(a,4)*/
    /*+ parallel_index(a,index_name,4)*/
    Are both these same?
    No,
    First are two hints, one to use index and one to instruct the optimizer to use 4 concurrent servers for a parallel operation

    The second is just one hint. it tells optimizer to use 4 concurrent servers on partitioned indexes.
    2. What is full index scan and fast full index scan?
    Full index scan do single block reads.
    Fast full scan do multi block reads.

    3. I have a table created in database and it has a few indexes on some columns. How can I check whether the index is Bitmap or B-Tree ?
    In user_|all_dba_indexes. Index_type will be BITMAP or NORMAL

    Regards
    Peter


    P.S: Speaking of hints. Bookmark this, can answer most of such questions much better than I can:
    http://www.oracle.com/pls/db112/homepage

    Edited by: Peter on Mar 26, 2013 1:47 PM

Answers

  • Peter Gjelstrup
    Peter Gjelstrup Member Posts: 4,128 Gold Trophy
    edited Mar 26, 2013 4:48PM Accepted Answer
    1. What is the difference between the below 2 index usage scenarios:
    /*+ index(a index_name) parallel(a,4)*/
    /*+ parallel_index(a,index_name,4)*/
    Are both these same?
    No,
    First are two hints, one to use index and one to instruct the optimizer to use 4 concurrent servers for a parallel operation

    The second is just one hint. it tells optimizer to use 4 concurrent servers on partitioned indexes.
    2. What is full index scan and fast full index scan?
    Full index scan do single block reads.
    Fast full scan do multi block reads.

    3. I have a table created in database and it has a few indexes on some columns. How can I check whether the index is Bitmap or B-Tree ?
    In user_|all_dba_indexes. Index_type will be BITMAP or NORMAL

    Regards
    Peter


    P.S: Speaking of hints. Bookmark this, can answer most of such questions much better than I can:
    http://www.oracle.com/pls/db112/homepage

    Edited by: Peter on Mar 26, 2013 1:47 PM
  • AB312062
    AB312062 Member Posts: 25
    Thanks Peter !

    Can you tell more about the difference in using index and parallel_index. I din't fully understand. If you could explain with some example or provide some link where I can find more information, that would be helpful.

    Regards
    AB
This discussion has been closed.