Which statements are correct regarding indexes? (Choose all that apply.)
A.
A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index.
B.
Indexes should be created on columns that are frequently referenced as part of any expression.
C.
When a table is dropped, the corresponding indexes are automatically dropped.
D.
For each DML operation performed, the corresponding indexes are automatically updated.
Explanation:
http://viralpatel.net/blogs/understanding-primary-keypk-constraint-in-oracle/
From my understanding, if a column is frequently used as a condition then it’s better to have it indexed.
In B is “..any expression”
For me it’s mean:
-… WHERE (COLUMN1* 3) = 10
-… WHERE UPPER(COLUMN1) = ‘SOMENAME’
-… WHERE COLUMN1 || COLUMN2 = ‘SOMENAME’
In that case You have to have “function index” exactly the same as this expression.
Please atention in this phase: “referenced as part of any expression.” For used a index in this case, you should create a expression, not in a field.
Not necessarily. Imagine that you have a table with 10 columns on it, and there’s a column which you use with frequency, should you create an index for it? No, it wouldn’t make any considerable difference. Although if a table has a lot of data on it and uses a column frequently, then the creation of an index probably would help.