Use the Oracle documentation Oracle Database Concepts 11g Release2 or/and 12c

http://docs.oracle.com/cd/E11882_01/server.112/e40540/indexiot.htm#CNCPT721 and/or

https://docs.oracle.com/database/121/CNCPT/indexiot.htm#CNCPT721 and/or other online documentation from oracle.com to answer/discuss the following questions/problems

Indexes study questions:

In Oracle 11g, a primary index is created when/why/how________________________________________

From the textbook Secondary indexes provide a mechanism for specifying an additional key for a base relation that can be used to retrieve data more efficiently. However, there is an overhead involved.. Evaluate and explain your decisions:

Should you or not use standard B-tree indexes on keys with very low cardinality (few distinct values, e.g. gender)

Should you or not index frequently modified columns?

Table cluster - A schema object that contains data from one or more tables, all of which have one or more columns in common. In table clusters, the database stores together all the rows from all tables that share the same cluster key.(http://docs.oracle.com/cd/E25054_01/server.1111/e25789/glossary.htm#CHDJGGGF Which DML statement type would benefit the most from two tables stored together in a table cluster?

In Oracle 11g, the following DDL statement creates a composite index. Give an example of a query that will benefit from such an index.

CREATE INDEX comp_ind

ON table1(x, y, z);

The following DDL statement creates a function-based index in Oracle 11g.

CREATE INDEX emp_fname_uppercase_idx

ON employees ( UPPER(first_name) );

Give an example of a query that will benefit from the above index.

An index improves the search time (reduces # of I/O). Traditionally, the large databases are kept in the secondary storage (there are many solutions to keep at least some of the records in memory) thus resulting in lots of I/O operations. In heap-organized table without indexes, the query requires a full table scan. If the table has n = 2,000 rows, what is the average # of I/O to access one record, assuming that 10 records are stored per block. If you add a B-tree index and search on the key, what is the average # of I/O? Assuming the blocking factor is 10.

In general, what is the difference between a dense index and a sparse index?

ISO SQL Standard CREATE INDEX does not cover the specific physical aspects; therefore, each DBMS has different options depending on the physical aspects of the software.

In Oracle 11g, it is possible to create virtual columns. A virtual column value is not physically stored; it is calculated when a table is queried. For example: annual_sal as 12*salary*commission_pct can be created with an index. Give an example of a query that will benefit from this index.