Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Regarding Index Usage

AB312062Mar 26 2013 — edited Mar 27 2013
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
This post has been answered by Peter Gjelstrup on Mar 26 2013
Jump to Answer

Comments

Peter Gjelstrup
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
Marked as Answer by AB312062 · Sep 27 2020
AB312062
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
1 - 2
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 24 2013
Added on Mar 26 2013
#index
2 comments
79 views