Whitepaper: Dynamic generation of advice on index to be created on table columns

Problem Statement:

While executing queries on large volume table data we need to make sure that appropriate indexes are created on table columns. There are different kinds of indexes for different situations. The index strategy might be different depending on the sizes of various tables in a query, the distribution of data in each query, and the columns used in the WHERE clauses.

Index Strategy:

·         Type of index

·         Table column(s) to include

·         Whether to use a single column or a combination of columns

·         Special features such as parallelism, turning off logging, compression, invisible indexes, and so on

·         Uniqueness

·         Naming conventions

·         Tablespace placement

·         Initial sizing requirements and growth

·         Impact on performance of SELECT statements (improvement)

·         Impact on performance of INSERT, UPDATE, and DELETE statements

·         Global or local index, if the underlying table is partitioned

Defining Index:

Indexes need to be defined taking into consideration of the data in the table and giving importance to business needs.

·         Define a primary key constraint for each table that results in an index automatically being created on the columns specified in the primary key

·         Create unique key constraints on non-null column values that are required to be unique

·         Explicitly create indexes on foreign key columns.

·         Create indexes on columns used often as predicates in the WHERE clause of frequently executed SQL queries.

·         Create a separate tablespace for the indexes. This allows to more easily manage indexes separately from tables for tasks such as backup and recovery.

·         Consider creating indexes on columns used in the ORDER BY, GROUP BY, UNION, or DISTINCT clauses

·         Use invisible index to test any index performance without affecting the performance for other simultaneous users

Script:

Below is a sample script to advice on the type of index to be created depending on the data available in the table. The script can be enhanced to identify query predicates and to provide advice for creating indexes on tables. The table GL_CODE_COMBINATIONS is taken as an example in the below script and can be altered to check for any tables in the database.

 
DECLARE
tbl_name VARCHAR2 (30) := 'GL_CODE_COMBINATIONS';
sql_stmt VARCHAR2 (1000);
sql_stmt_null VARCHAR2 (1000);
null_cnt NUMBER := 0;
unq_cnt NUMBER := 0;
ln_comp_ind NUMBER := 0;
lc_comp_ind VARCHAR2 (3) := 'NO';
lc_ind VARCHAR2 (3) := 'NO';
lc_advice VARCHAR2 (1000) := 'Index Advice: ';
BEGIN
FOR cur_tbl IN (SELECT atc.column_id, atc.column_name, atc.data_type, atc.data_length,
atc.data_precision, atc.table_name, ai.index_name, ai.uniqueness,
ai.LOGGING, ai.status, ai.visibility
FROM all_tab_cols atc,
(SELECT ai.index_name, aic.column_name, ai.uniqueness, ai.LOGGING,
ai.status, ai.visibility, ai.table_name
FROM all_indexes ai, all_ind_columns aic
WHERE ai.table_name = tbl_name
AND aic.index_name = ai.index_name
AND ai.table_name = aic.table_name) ai
WHERE atc.table_name = tbl_name
AND atc.table_name = ai.table_name(+)
AND atc.column_name = ai.column_name(+)
-- AND ROWNUM < 20
ORDER BY 1)
LOOP
IF cur_tbl.index_name IS NOT NULL
THEN
lc_ind := 'YES';
SELECT MAX (column_position)
INTO ln_comp_ind
FROM all_ind_columns
WHERE index_name = cur_tbl.index_name;
IF ln_comp_ind > 1
THEN
lc_comp_ind := 'YES';
ELSE
lc_comp_ind := 'NO';
END IF;
ELSE
lc_ind := 'NO';
END IF;
DBMS_OUTPUT.put_line ('Column Name: ' || cur_tbl.column_name);
DBMS_OUTPUT.put_line ('Index Exists: ' || lc_ind);
DBMS_OUTPUT.put_line ('Index Name: ' || NVL (cur_tbl.index_name, 'NA'));
DBMS_OUTPUT.put_line ('Composite Index: ' || lc_comp_ind);
-- check unique column values
-- First check if having null values
sql_stmt_null :=
'select count(1) from ' || cur_tbl.table_name || ' where ' || cur_tbl.column_name
|| ' is null';
BEGIN
EXECUTE IMMEDIATE sql_stmt_null
INTO null_cnt;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error while checking NULL');
DBMS_OUTPUT.put_line (SQLERRM);
END;
IF null_cnt > 0
THEN
DBMS_OUTPUT.put_line ('NULL Values: Yes');
DBMS_OUTPUT.put_line ('UNIQUE: NON-UNIQUE Data Values');
lc_advice := lc_advice || 'No index needed as this column contains NULL values. ';
DBMS_OUTPUT.put_line (lc_advice);
ELSE
DBMS_OUTPUT.put_line ('NULL Values: No');
BEGIN
sql_stmt :=
'select count(1) from (
select '
|| cur_tbl.column_name
|| ' , count( '
|| cur_tbl.column_name
|| ' ) from '
|| cur_tbl.table_name
|| '
where '
|| cur_tbl.column_name
|| ' is not null
group by '
|| cur_tbl.column_name
|| '
having count( '
|| cur_tbl.column_name
|| ' ) > 1)';
-- DBMS_OUTPUT.put_line (sql_stmt);
EXECUTE IMMEDIATE sql_stmt
INTO unq_cnt;
IF unq_cnt = 0
THEN
DBMS_OUTPUT.put_line ('UNIQUE: UNIQUE Data Values');
lc_advice :=
lc_advice
|| 'UNIQUE Index can be created on this column if not already existing. ';
DBMS_OUTPUT.put_line (lc_advice);
ELSE
-- DBMS_OUTPUT.put_line (unq_cnt);
DBMS_OUTPUT.put_line ('UNIQUE: NON-UNIQUE Data Values');
IF unq_cnt <= 5
THEN
lc_advice := lc_advice || 'Bitmap Index can be created for this column. ';
DBMS_OUTPUT.put_line (lc_advice);
ELSE
lc_advice :=
lc_advice || 'NON-UNIQUE or Composite Index can be created for this column. ';
DBMS_OUTPUT.put_line (lc_advice);
END IF;
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error while checking Uniqueness');
DBMS_OUTPUT.put_line (SQLERRM);
END;
END IF;
DBMS_OUTPUT.put_line (' ');
lc_advice := 'Index Advice: ';
END LOOP;
END;