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;
