Oracle: When to create indexes?

Answer 1

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 2

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