43 Indexes and Synonyms

• ·         Confirms the existence of indexes from the USER_INDEXES data dictionary view-Confirming Index

e.g. SELECT * FROM user_indexes  where table_name = 'EMPLOYEES';

SELECT ucm.index_name, ucm.column_name, ucm.column_position, uix.uniqueness

FROM user_indexes uix INNER JOIN user_ind_columns ucm ON uix.index_name = ucm.index_name

WHERE ucm.table_name = 'EMPLOYEES';

• ·         Schema object that speeds up retrieval of rows-INDEX

Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns). Non-unique indexes do not impose this restriction on the column values

• ·         To refer to a table by another name to simplify access- CREATE PUBLIC SYNONYM

Public: creates synonym accessible to all users

• ·         An index that you create on multiple columns in a table-Composite Index
• ·         The Oracle Server automatically creates this index when you define a column in a table to have a PRIMARY KEY or a UNIQUE KEY constraint- Unique index
• ·         Stores the indexed values and uses the index based on a SELECT statement to retrieve the data- function-based index

A function-based index stores indexed values and uses the index based on SELECT statement to retrieve the data. It is based on expressions. The index expression is built from table columns, constants, SQL functions and user-defined functions.

• ·         Removes an index-DROP INDEX
• ·         Gives alternative names to objects- Synonym

1.  What is an index and what is it used for?

Definition: These are schema objects which make retrieval of rows from table faster.

  - They are meant to be efficient way to find data in database. I may like to drop an index if, queries in application are not using some index or say it is not speeding up the queries or may be table is very small. An index provides direct and fast access to row in table. 

·         I should create an index if the table is large and most queries are expected to retrieve less than 2 to 4 percent of the rows.

·         I should create an index if one or more columns are frequently used together in a join condition.

Purpose: An index provides direct and fast access to row in table. They provide indexed path to locate data quickly, so hereby reduce necessity of heavy disk input/output operations.

Track usage of index:

Look into what indexes employees table has:

SELECT ucm.index_name, ucm.column_name, ucm.column_position, uix.uniqueness

FROM user_indexes uix INNER JOIN user_ind_columns ucm ON uix.index_name = ucm.index_name

WHERE ucm.table_name = 'EMPLOYEES';

Start monitoring an index:

ALTER INDEX emp_id_pk MONITORING USAGE;

Note down column values in V$OBJECT_USAGE:

SELECT * FROM v$object_usage WHERE index_name = 'EMP_ID_PK';

Run a statement which may be using the index:

SELECT * from employees where employee_id = 100;

Note down column values in V$OBJECT_USAGE:

SELECT * FROM v$object_usage WHERE index_name = 'EMP_ID_PK';

Stop monitoring an index:

ALTER INDEX emp_id_pk NOMONITORING USAGE;

2.  What is a ROWID, and how is it used?

Indexes use ROWID's (base 64 string representation of the row address containing block identifier, row location in the block and the database file identifier) which is the fastest way to access any particular row.

3.  When will an index be created automatically?

For primary/unique keys: Although unique index can   be created manually, but preferred should be by using unique/primary constraint in the table. So, it   means that primary key/unique key use already existing unique index but if index is not present already, it is created while applying unique/primary key constraint.

Oracle also creates index automatically for LOB storage, xmltype and materialized view.

4.  Create a nonunique index (foreign key) for the DJs on Demand column (cd_number) in the D_TRACK_LISTINGS table. Use the Oracle Application Developer SQL Workshop Data Browser to confirm that the index was created.

Creating index (non-unique):

CREATE INDEX d_tlg_cd_number_fk_i

 on d_track_listings (cd_number);

Verify by SQL statement:

SELECT ucm.index_name, ucm.column_name, ucm.column_position, uix.uniqueness

FROM user_indexes uix INNER JOIN user_ind_columns ucm ON uix.index_name = ucm.index_name

WHERE ucm.table_name = 'D_TRACK_LISTINGS' AND column_name = 'CD_NUMBER';

Verify by object browser:

Or search in object browser:

5.  Use the join statement to display the indexes and uniqueness that exist in the data dictionary for the DJs on Demand D_SONGS table.

SELECT ucm.index_name, ucm.column_name, ucm.column_position, uix.uniqueness

FROM user_indexes uix INNER JOIN user_ind_columns ucm ON uix.index_name = ucm.index_name

WHERE ucm.table_name = 'D_SONGS';

6.  Use a SELECT statement to display the index_name, table_name, and uniqueness from the data dictionary USER_INDEXES for the DJs on Demand D_EVENTS table.

SELECT index_name, table_name,uniqueness  FROM user_indexes  where table_name = 'D_EVENTS';

7.  Write a query to create a synonym called dj_tracks for the DJs on Demand d_track_listings table.

CREATE PUBLIC SYNONYM dj_tracks FOR d_track_listings;

ORA-01031: insufficient privileges

Means I don't have CREATE PUBLIC SYNONYM privilege

CREATE SYNONYM dj_tracks FOR d_track_listings;

8.  Create a function-based index for the last_name column in DJs on Demand D_PARTNERS table that makes it possible not to have to capitalize the table name for searches. Write a SELECT statement that would use this index.

Read this as last_name

What All indexes this table has right now:

SELECT ucm.index_name, ucm.column_name, ucm.column_position, uix.uniqueness

FROM user_indexes uix INNER JOIN user_ind_columns ucm ON uix.index_name = ucm.index_name

WHERE ucm.table_name = 'D_PARTNERS';

Create index:

CREATE INDEX d_ptr_last_name_idx

ON d_partners(LOWER(last_name));

Start monitoring an index:

ALTER INDEX d_ptr_last_name_idx MONITORING USAGE;

Note down column values in V$OBJECT_USAGE:

SELECT * FROM v$object_usage WHERE index_name = 'D_PTR_LAST_NAME_IDX';

Run a statement which may be using the index:

SELECT *

FROM d_partners

WHERE LOWER(last_name) = 'something';

Note down column values in V$OBJECT_USAGE:

SELECT * FROM v$object_usage WHERE index_name = 'D_PTR_LAST_NAME_IDX';

Stop monitoring an index:

ALTER INDEX d_ptr_last_name_idx NOMONITORING USAGE;

If I want case insensitive search rather than upper or lower confusion:

CREATE INDEX d_ptr_last_name2_idx

ON d_partners(NLSSORT (last_name, 'NLS_SORT=BINARY_CI'));

ALTER INDEX d_ptr_last_name2_idx MONITORING USAGE;

SELECT * FROM v$object_usage WHERE index_name = 'D_PTR_LAST_NAME2_IDX';

Just making sure, the previous index is not used in execution plan:

DROP INDEX d_ptr_last_name_idx;

SELECT *

FROM d_partners

WHERE NLSSORT (last_name, 'NLS_SORT=BINARY_CI') = NLSSORT ('pLuMb', 'NLS_SORT=BINARY_CI');

SELECT * FROM v$object_usage WHERE index_name = 'D_PTR_LAST_NAME2_IDX';

ALTER INDEX d_ptr_last_name2_idx NOMONITORING USAGE;

9.  Create a synonym for the D_TRACK_LISTINGS table. Confirm that it has been created by querying the data dictionary.

See problem 7 above for creation part too.

CREATE SYNONYM dj_tracks FOR d_track_listings;

ORA-00955: name is already used by an existing object

CREATE SYNONYM dj_tracks2 FOR d_track_listings;

Synonym created.

SELECT * FROM user_synonyms WHERE table_NAME = UPPER('d_track_listings');

10. Drop the synonym that you created in question 9.

DROP SYNONYM dj_tracks2;

Synonym dropped.

SELECT * FROM user_synonyms WHERE table_NAME = UPPER('d_track_listings');