top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Oracle: When to create indexes?

+1 vote
300 views
Oracle: When to create indexes?
posted Jan 9, 2015 by Archana

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

2 Answers

+1 vote
 
Best answer

An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.

There will also be an index automatically created when you create materialized view since Oracle needs to be able to quickly identify the rows when doing a fast refresh. For rowid based materialized views, it uses I_SNAP$_tablename. For primary key materialized views, it uses the original PK name, modified as necessary to make it unique.

create materialized view testmv 
refresh force with rowid
as select * from dual;

select index_name from user_indexes where table_name = 'TESTMV';

Index Name
--------------
I_SNAP$_TESTMV
answer Jan 9, 2015 by Amit Kumar Pandey
0 votes

To be created when table is queried for less than 2% or 4% to 25% of the table rows.

answer Jan 12, 2015 by Arun Gowda
...