Typical-index-maintenance-tasks-9i10g

--1 Move index from one tablespace to another
alter index &OWNER.&INDEX_NAME rebuild tablespace &NEW_TS_NAME
;

--2 Moving index partition from one tablespace to another
alter index &OWNER.&INDEX_NAME
rebuild partition &IND_PART_NAME tablespace &NEW_TS_NAME
;

--3 Moving all index subpartitions from one tablespace to anotheralter index &OWNER.&INDEX_NAME
rebuild subpartition &IND_SUBPART_NAME tablespace &NEW_TS_NAME
;

--4 Unusable indexesselect 'alter index '||owner||'.'||index_name||' rebuild;'
from dba_indexes where status='UNUSABLE';

--5 Unusable index partitionsselect 'alter index '||index_owner||'.'||index_name||
' rebuild partition '||partition_name||';' sql
from dba_ind_partitions where status='UNUSABLE';

--6 Unusable index subpartitionsselect 'alter index '||index_owner||'.'||index_name||
' rebuild subpartition '||subpartition_name||';' sql
from dba_ind_subpartitions where status='UNUSABLE';

--7 All things togetherselect 'alter index '||owner||'.'||index_name||' rebuild;' sql
from dba_indexes where status='UNUSABLE'
union all
select 'alter index '||index_owner||'.'||index_name||
' rebuild partition '||partition_name||';'
from dba_ind_partitions where status='UNUSABLE'
union all
select 'alter index '||index_owner||'.'||index_name||
' rebuild subpartition '||subpartition_name||';'
from dba_ind_subpartitions where status='UNUSABLE';

moving-tablespartitions-9i-onwards

-1 Move table from one tablespace to another-- (check for unusable indexes after that).
alter table $OWNER.$TABLE_NAME move tablespace $NEW_TS_NAME
;

--2 Move table partition from one tablespace to another
-- (check for unusable indexes and partitoned indexes after that).
alter table $OWNER.$TABLE_NAME
move partition $TAB_PART_NAME tablespace $NEW_TS_NAME
;

--3 Move table subpartition from one tablespace to another
-- (check for unusable indexes, partitioned indexes, and subpartitioned indexes).
alter table $OWNER.$TABLE_NAME
move subpartition $TAB_SUBPART_NAME tablespace $NEW_TS_NAME
;

moving-schema-tablesindexes

-1 Move group of TABLE segments (check for unusable indexes after that)
select 'alter table '||owner||'.'||segment_name||' move '||
decode(segment_type,
'TABLE PARTITION','partition '||partition_name,
'TABLE SUBPARTITION','subpartition '||partition_name,null)||' tablespace &NEW_TS_NAME;' sql
from dba_segments
where segment_type like 'TABLE%'
and tablespace_name='&TS_NAME'
and owner='&OWNER'
and segment_name='&SEG_NAME'
;

--2 Move group of INDEX segments
select 'alter index '||owner||'.'||segment_name||' rebuild '||
decode(segment_type,
'INDEX PARTITION','partition '||partition_name,
'INDEX SUBPARTITION','subpartition '||partition_name,null)||' tablespace &NEW_TS_NAME;' sql
from dba_segments
where segment_type like 'INDEX%'
and tablespace_name='&TS_NAME'
and owner='&OWNER'
and segment_name='&SEG_NAME'
;

--3 List segments that will fail to expand
select /*+ all_rows */ segs.*
from
dba_segments segs,
sys.seg& s,
(select ts#,max(length) m from sys.fet& group by ts#) f
where s.ts#=f.ts# and extsize>m
and segs.header_file=s.file# and segs.header_block=s.block#
;

--4 List of fragmented segments
select segs.*
from
dba_segments segs,
(select file#, segblock# from sys.uet&
group by file#, segblock#
having count(*) > 1024
) f
where segs.header_file=f.file# and segs.header_block=f.segblock#
;

Deciding Index Type..?

guy 1: a table has 10,00000 db blocks
guy 1: it has 9999999999 rows
guy 2: vempires r on the floor
guy 1: on column productid there are 23 distinct values
guy 1: what index would u prefer
guy 2: b+tree
guy 1: nope
guy 1: this column has very low cardinality
guy 2: k
guy 2: sir
guy 1: this column has very low cardinality
guy 1: so very low selectivity . if it was highly selctive only then b+tree
guy 2: yup
guy 1: column seems good candidate for for bmp index
guy 2: im recalling
guy 1: but if heavy dml then even bmp index is also avoided
guy 2: its good .commender
guy 1: because it locks segments (actallyu exents) of objects
guy 2: k
guy 1: so no index if heavy dml on above
guy 2: k

Script: To Monitor the Usage of Indexes

Abstract
This script will monitor the usage of indexes on the database.

Instructions
Execution Environment:
SQL, SQL*Plus

Access Privileges:
Requires DBA access privileges to be executed.

Usage:
sqlplus sys/

Instructions:
Copy the script to a file and execute it from SQL*Plus.


PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text
editors, e-mail packages, and operating systems handle text formatting (spaces,
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected.


Description
You have an environment that is heavily indexed, and you want to monitor the
usage of the indexes. For example, at the end of the week before the batch
loads you would like to check which indexes are being used in queries
throughout the week.

You can find the index usage from the explain plan. If you explain all the
queries within V$SQLAREA, you can see which indexes are being used.

The following is a sample of the type of script you can write to get these
results. This script is only a sample, and works under certain assumptions.

Miscellaneous requirements and info:
- The user running the script should have all the privileges to explain
everything in v$sqlarea not loaded by SYS.
- plan_table.remarks can be used to determine privilege related errors.
- The parameter OPTIMIZER_GOAL is constant for all SQL in shared pool
ignores v$sqlarea.optimizer_mode.
- The statistics have not been regenerated between snapshots.
- No statements have been truncated.
- All objects are local.
- All referenced tables/views are either owned by the user running the
script or fully qualified names/synonyms were used in the SQL.
- No "popular" statements have aged out of (and for that matter, been
reloaded into) the shared pool since the last snapshot.
- Instance is either bounced or has the shared pool completely flushed
after each snapshot to reset the executions and other statistics to zero.
- For all statements, v$sqlarea.version_count = 1 (children).
- Review Bug:2282891 and Bug:2953935 that may affect performance of this script

NOTE: With 9i, you can use the V$SQL_PLAN instead.


Script
set echo off
Rem Drop and recreate PLAN_TABLE for EXPLAIN PLAN
drop table plan_table;
Rem Drop and recreate SQLTEMP for taking a snapshot of the SQLAREA
drop table sqltemp;
create table sqltemp
(ADDR VARCHAR2 (16),
SQL_TEXT VARCHAR2 (2000),
DISK_READS NUMBER,
EXECUTIONS NUMBER,
PARSE_CALLS NUMBER);

set echo on

Rem Create procedure to populate the plan_table by executing
Rem explain plan...for 'sqltext' dynamically
create or replace procedure do_explain
(addr IN varchar2, sqltext IN varchar2) as
dummy varchar2 (1100);
mycursor integer;
ret integer;
my_sqlerrm varchar2 (85);
begin
dummy:='EXPLAIN PLAN SET STATEMENT_ID=' ;
dummy:=dummy||''''||addr||''''||' FOR '||sqltext;
mycursor := dbms_sql.open_cursor;
dbms_sql.parse(mycursor,dummy,dbms_sql.v7);
ret := dbms_sql.execute(mycursor);
dbms_sql.close_cursor(mycursor);
commit;
exception -- Insert errors into PLAN_TABLE...
when others then
my_sqlerrm := substr(sqlerrm,1,80);
insert into plan_table(statement_id,remarks)
values (addr,my_sqlerrm);
-- close cursor if exception raised on EXPLAIN PLAN
dbms_sql.close_cursor(mycursor);
end;
/

Rem Start EXPLAINing all S/I/U/D statements in the shared pool
declare
-- exclude statements with v$sqlarea.parsing_schema_id = 0 (SYS)
cursor c1 is select address, sql_text, DISK_READS, EXECUTIONS,
PARSE_CALLS
from v$sqlarea where command_type in (2,3,6,7)
and parsing_schema_id != 0;
cursor c2 is select addr, sql_text from sqltemp;
addr2 varchar(16);
sqltext v$sqlarea.sql_text%type;
dreads v$sqlarea.disk_reads%type;
execs v$sqlarea.executions%type;
pcalls v$sqlarea.parse_calls%type;
begin
open c1;
fetch c1 into addr2,sqltext,dreads,execs,pcalls;
while (c1%found) loop
insert into sqltemp values(addr2,sqltext,dreads,execs,pcalls);
commit;
fetch c1 into addr2,sqltext,dreads,execs,pcalls;
end loop;
close c1;
open c2;
fetch c2 into addr2, sqltext;
while (c2%found) loop
do_explain(addr2,sqltext);
fetch c2 into addr2, sqltext;
end loop;
close c2;
end;
/

Rem Generate a report of index usage based on the number of times
Rem a SQL statement using that index was executed
select p.owner, p.name, sum(s.executions) totexec
from sqltemp s,
(select distinct statement_id stid, object_owner owner, object_name name
from plan_table
where operation = 'INDEX') p
where s.addr = p.stid
group by p.owner, p.name
order by 2 desc;

Rem Perform cleanup on exit (optional)
delete
from plan_table
where statement_id in(
select addr
from sqltemp
);
drop table sqltemp;



==============
Sample Output:
==============

SQL> @check_indexes

Table dropped.


Table created.


Table dropped.


Table created.

SQL> Rem Create procedure to populate the plan_table by executing
SQL> Rem explain plan...for 'sqltext' dynamically
SQL> create or replace procedure do_explain
2 (addr IN varchar2, sqltext IN varchar2)
3 as
4 dummy varchar2 (1100);
5 mycursor integer;
6 ret integer;
7 my_sqlerrm varchar2 (85);
8 begin
9 dummy:='EXPLAIN PLAN SET STATEMENT_ID=' ;
10 dummy:=dummy||''''||addr||''''||' FOR '||sqltext;
11 mycursor := dbms_sql.open_cursor;
12 dbms_sql.parse(mycursor,dummy,dbms_sql.v7);
13 ret := dbms_sql.execute(mycursor);
14 dbms_sql.close_cursor(mycursor);
15 commit;
16 exception -- Insert errors into PLAN_TABLE...
17 when others then
18 my_sqlerrm := substr(sqlerrm,1,80);
19 insert into plan_table(statement_id,remarks)
20 values (addr,my_sqlerrm);
21 -- close cursor if exception raised on EXPLAIN PLAN
22 dbms_sql.close_cursor(mycursor);
23 end;
24 /

Procedure created.

SQL> Rem Start EXPLAINing all S/I/U/D statements in the shared pool
SQL> declare
2 -- exclude statements with v$sqlarea.parsing_schema_id = 0 (SYS)
3 cursor c1 is select address, sql_text, DISK_READS, EXECUTIONS,
4 PARSE_CALLS
5 from v$sqlarea where command_type in (2,3,6,7)
6 and parsing_schema_id != 0;
7 cursor c2 is select addr, sql_text from sqltemp;
8 addr2 varchar(16);
9 sqltext v$sqlarea.sql_text%type;
10 dreads v$sqlarea.disk_reads%type;
11 execs v$sqlarea.executions%type;
12 pcalls v$sqlarea.parse_calls%type;
13 begin
14 open c1;
15 fetch c1 into addr2,sqltext,dreads,execs,pcalls;
16 while (c1%found) loop
17 insert into sqltemp values(addr2,sqltext,dreads,execs,pcalls);
18 commit;
19 fetch c1 into addr2,sqltext,dreads,execs,pcalls;
20 end loop;
21 close c1;
22 open c2;
23 fetch c2 into addr2, sqltext;
24 while (c2%found) loop
25 do_explain(addr2,sqltext);
26 fetch c2 into addr2, sqltext;
27 end loop;
28 close c2;
29 end;
30 /

PL/SQL procedure successfully completed.

SQL> Rem Generate a report of index usage based on the number of times
SQL> Rem a SQL statement using that index was executed
SQL> select p.owner, p.name, sum(s.executions) totexec
2 from sqltemp s,
3 (select distinct statement_id stid, object_owner owner, object_name name
4 from plan_table
5 where operation = 'INDEX') p
6 where s.addr = p.stid
7 group by p.owner, p.name
8 order by 2 desc;

OWNER NAME TOTEXEC
------------------------------ ------------------------------ ----------
TEST JUNK_C1 1

Viewing All Indexes Being Monitored Under Another User's Schema

PURPOSE
To show how to view all indexes being monitored.


SCOPE & APPLICATION
Instructional.


Viewing All Indexes Being Monitored Under Another User's Schema:
================================================================

V$OBJECT_USAGE does not display rows for all indexes in the database whose
usage is being monitored.

'ALTER INDEX MONITORING USAGE' places an entry in V$OBJECT_USAGE for
that particular index to help determine if the index is being used or not. The
V$OBJECT_USAGE view uses the username logged into database when the 'ALTER
INDEX MONITORING USAGE' is issued. This will not enable any user other
than the user who issued the 'ALTER INDEX MONITORING USAGE' to view if
index is being monitored or not.

The view structure may be changed slightly (see below) in order to expand its
scope system-wide (see below) so that you may see all indexes being monitored.

For example:

Showing User Scott monitoring his Index on EMP table:

SQL> connect scott/tiger
SQL> set LONG 30000

SQL> select text from dba_views where view_name ='V$OBJECT_USAGE';

TEXT
select io.name, t.name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
ou.start_monitoring,
ou.end_monitoring
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
where io.owner# = userenv('SCHEMAID')
and i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#


SQL> select index_name, table_name, uniqueness, status from user_indexes
where table_name = 'EMP';

INDEX_NAME TABLE_NAME UNIQUENES STATUS
PK_EMP EMP UNIQUE VALID

SQL> alter index PK_EMP monitoring usage;

Index altered.

SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
PK_EMP EMP YES NO 10/12/2001 06:42:35


Then connect as another user to view indexes being monitored:

SQL> connect / as sysdba;

Connected.

SQL> select * from v$object_usage;

no rows selected


To be able to view them do the following:

SQL> create or replace view V$ALL_OBJECT_USAGE
(OWNER,
INDEX_NAME,
TABLE_NAME,
MONITORING,
USED,
START_MONITORING,
END_MONITORING)
as
select u.name, io.name, t.name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
ou.start_monitoring,
ou.end_monitoring
from sys.user$ u, sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
where i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#
and u.user# = io.owner#

View created.


SQL> select * from v$all_object_usage;

OWNER INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
SCOTT PK_EMP EMP YES NO 10/12/2001 06:42:35



Related Documents:
==================

Oracle9i Database Administrator's Guide Volume 1 (Managing Indexes: Monitoring
Index Usage)

Note 144070.1 Identifying unused indexes in Oracle9i

Identifying unused indexes in Oracle9i

PURPOSE
-------

The purpose of this document is to explain how to find unused indexes
using the new feature in Oracle9: "Identifying Unused Indexes" via
ALTER INDEX MONITORING USAGE, as mentioned in Oracle9i Database
Administrator's Guide, Chapter 11.

The clause MONITORING / NOMONITORING USAGE is useful in determining
whether an index is being used.


SCOPE & APPLICATION
-------------------

This article is intended for database Administrators who want to
identify unused indexes in their database.


IDENTIFYING UNUSED INDEXES
--------------------------

You can find indexes that are not being used by using the ALTER INDEX
MONITORING USAGE functionality over a period of time that is
representative of your workload.

PART 1 will demonstrate the new feature using a simple example.

PART 2 will give a detailed instruction how to identify all unused
indexes in the database.


PART 1 - Monitoring usage of indexes - a simple example
---------------------------------------------------------

To demonstrate the new feature, you can use the following example:
(a) Create and populate a small test table
(b) Create Primary Key index on that table
(c) Query v$object_usage: the monitoring has not started yet
(d) Start monitoring of the index usage
(e) Query v$object_usage to see the monitoring in progress
(f) Issue the SELECT statement which uses the index
(g) Query v$object_usage again to see that the index has been used
(h) Stop monitoring of the index usage
(i) Query v$object_usage to see that the monitoring stopped


Detailed steps:

(a) Create and populate a small test table

create table products
(prod_id number(3),
prod_name_code varchar2(5));

insert into products values(1,'aaaaa');
insert into products values(2,'bbbbb');
insert into products values(3,'ccccc');
insert into products values(4,'ddddd');
commit;


(b) Create Primary Key index on that table

alter table products
add (constraint products_pk primary key (prod_id));


(c) Query v$object_usage: the monitoring has not started yet

column index_name format a12
column monitoring format a10
column used format a4
column start_monitoring format a19
column end_monitoring format a19
select index_name,monitoring,used,start_monitoring,end_monitoring
from v$object_usage;

no rows selected


(d) Start monitoring of the index usage

alter index products_pk monitoring usage;

Index altered.


(e) Query v$object_usage to see the monitoring in progress

select index_name,monitoring,used,start_monitoring,end_monitoring
from v$object_usage;

INDEX_NAME MONITORING USED START_MONITORING END_MONITORING
------------ ---------- ---- ------------------- -------------------
PRODUCTS_PK YES NO 04/25/2001 15:43:13

Note: Column MONITORING='YES', START_MONITORING gives the timestamp.


(f) Issue the SELECT statement which uses the index

First, make sure that index will be used for this statement.
Create plan_table in your schema, as required by Oracle Autotrace
utility:

@$ORACLE_HOME/rdbms/admin/utlxplan

Table created.

Use Oracle Autotrace utility to obtain the execution plan:

set autotrace on explain
select * from products where prod_id = 2;
.
.
Execution Plan
------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCTS'
2 1 INDEX (UNIQUE SCAN) OF 'PRODUCTS_PK' (UNIQUE)

set autotrace off

Now, since you know the index will be used for this query, issue the
actual SELECT statement:

select * from products where prod_id = 2;

PROD_ID PROD_
---------- -----
2 bbbbb


(g) Query v$object_usage again to see that the index has been used

select index_name,monitoring,used,start_monitoring,end_monitoring
from v$object_usage;

INDEX_NAME MONITORING USED START_MONITORING END_MONITORING
------------ ---------- ---- ------------------- -------------------
PRODUCTS_PK YES YES 04/25/2001 15:43:13

Note: Column USED='YES'.


(h) Stop monitoring of the index usage

alter index products_pk nomonitoring usage;

Index altered.


(i) Query v$object_usage to see that the monitoring stopped

select index_name,monitoring,used,start_monitoring,end_monitoring
from v$object_usage;

INDEX_NAME MONITORING USED START_MONITORING END_MONITORING
------------ ---------- ---- ------------------- -------------------
PRODUCTS_PK NO YES 04/25/2001 15:43:13 04/25/2001 15:48:44

Note: Column MONITORING='NO', END_MONITORING gives the timestamp.



PART 2 - How to identify all unused indexes in the database
-------------------------------------------------------------

To identify all unused indexes in the database, you can do the
following:
(a) Create a SQL script to start monitoring all indexes except those
owned by users SYS and SYSTEM
(b) Create another script to stop monitoring all indexes except those
owned by users SYS and SYSTEM
(c) Connect as a user with ALTER ANY INDEX system privilege and run
the start monitoring script
(d) Perform normal activities in your database
(e) After a period of time that is representative of your workload,
run the stop monitoring script
(f) Query v$object_usage to see what indexes have not been used


Detailed steps:

(a) Create a SQL script to start monitoring all indexes except those
owned by users SYS and SYSTEM

set heading off
set echo off
set feedback off
set pages 10000
spool startmonitor.sql
select 'alter index '||owner||'.'||index_name||' monitoring usage;'
from dba_indexes
where owner not in ('SYS','SYSTEM');
spool off


(b) Create another script to stop monitoring all indexes except those
owned by users SYS and SYSTEM

set heading off
set echo off
set feedback off
set pages 10000
spool stopmonitor.sql
select 'alter index '||owner||'.'||index_name||' nomonitoring usage;'
from dba_indexes
where owner not in ('SYS','SYSTEM');
spool off


(c) Connect as a user with ALTER ANY INDEX system privilege and run
the newly created script to start monitoring.

@startmonitor

(d) Perform normal activities in your database


(e) After a period of time that is representative of your workload,
connect as a user with ALTER ANY INDEX system privilege and run
the script to stop monitoring.

@stopmonitor


(f) Query v$object_usage in join with dba_indexes, to see what indexes
have not been used

select d.owner, v.index_name
from dba_indexes d, v$object_usage v
where v.used='NO' and d.index_name=v.index_name;



RELATED DOCUMENTS
-----------------

Note 1033478.6 Script Monitoring the Usage of Indexes(prior Oracle9i)
Note 1015945.102 How to Monitor Most Recently Accessed Indexessing script

Oracle9i Database Administrator's Guide

Identifying Unused Indexes with the ALTER INDEX MONITORING USAGE Command

Checked for relevance on 03-July-2007

PURPOSE
This document explains how to identify unused indexes in order to remove them.


SCOPE & APPLICATION
DBAs must be aware that unused indexes:
* consume storage space
* degrade performance by unnecessary overheads during DML operations

REFERENCES

Note 144070.1 Identifying unused indexes in Oracle9i


How to Identify Unused Indexes in Order to Remove Them:
=======================================================
The examples below work if the user logged is the owner of the index.

1. Set the index under MONITORING:

SQL> alter index I_EMP monitoring usage;
Index altered.

SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MON USED START_MONITORING END_MONITORING
--------------- --------------- --- ---- ------------------- -------------------
I_EMP EMP YES NO 03/14/2001 09:17:19


2. Check if the monitored indexes are used or not through the USED column in
V$OBJECT_USAGE view:

SQL> select sal from emp where ename='SMITH';

SAL
----------
800


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (RANGE SCAN) OF 'I_EMP' (NON-UNIQUE)


The explain plan indicates the index is used.


SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MON USED START_MONITORING END_MONITORING
--------------- --------------- --- ---- ------------------- -------------------
I_EMP EMP YES YES 03/14/2001 09:17:19


If the index was not used, the DBA could drop this unused index.


3. To stop monitoring an index, use the following SQL statement:

SQL> alter index i_emp nomonitoring usage;
Index altered.

SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MON USED START_MONITORING END_MONITORING
--------------- --------------- --- ---- ------------------- -------------------
I_EMP EMP NO YES 03/14/2001 09:17:19 03/14/2001 09:55:24


As soon as you turn monitoring on again for the index, both columns
MONITORING and USED are reset.


SQL> alter index i_emp monitoring usage;
Index altered.

SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MON USED START_MONITORING END_MONITORING
--------------- --------------- --- ---- ------------------- -------------------
I_EMP EMP YES NO 03/14/2001 09:57:27

Why isn't my index getting used?---Case Study

Why isn't my index getting used?

There are many possible causes of this. In this section, we'll take a look at some of the most common.
Case 1

We're using a B*Tree index, and our predicate does not use the leading edge of an index. In this case, we might have a table T with an index on T(x,y). We query SELECT * FROM T WHERE Y = 5. The optimizer will tend not to use the index since our predicate did not involve the column X -- it might have to inspect each and every index entry in this case (we'll discuss an index skip scan shortly where this is not true). It will typically opt for a full table scan of T instead. That does not preclude the index from being used. If the query was SELECT X,Y FROM T WHERE Y = 5, the optimizer would notice that it did not have to go to the table to get either X or Y (they are in the index) and may very well opt for a fast full scan of the index itself, as the index is typically much smaller than the underlying table. Note also that this access path is only available with the CBO.

There is another case whereby the index on T(x,y) could be used with the CBO is during an index skip scan. The skip scan works well if and only if the leading edge of the index (X in the previous example) has very few distinct values and the optimizer understands that. For example, consider an index on (GENDER, EMPNO) where GENDER has the values M and F, and EMPNO is unique. A query such as

select * from t where empno = 5;

might consider using that index on T to satisfy the query in a skip scan method, meaning the query will be processed conceptually like this:

select * from t where GENDER='M' and empno = 5
UNION ALL
select * from t where GENDER='F' and empno = 5;

It will skip throughout the index, pretending it is two indexes: one for Ms and one for Fs. We can see this in a query plan easily. We'll set up a table with a bivalued column and index it:

ops$tkyte@ORA10GR1> create table t
2 as
3 select decode(mod(rownum,2), 0, 'M', 'F' ) gender, all_objects.*
4 from all_objects
5 /
Table created.

ops$tkyte@ORA10GR1> create index t_idx on t(gender,object_id)
2 /
Index created.

ops$tkyte@ORA10GR1> begin
2 dbms_stats.gather_table_stats
3 ( user, 'T', cascade=>true );
4 end;
5 /
PL/SQL procedure successfully completed.

Now, when we query this, we should see the following:

ops$tkyte@ORA10GR1> set autotrace traceonly explain
ops$tkyte@ORA10GR1> select * from t t1 where object_id = 42;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=1 Bytes=95)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=4 Card=1 Bytes=95)
2 1 INDEX (SKIP SCAN) OF 'T_IDX' (INDEX) (Cost=3 Card=1)

The INDEX SKIP SCAN step tells us that Oracle is going to skip throughout the index, looking for points where GENDER changes values and read down the tree from there, looking for OBJECT_ID=42 in each virtual index being considered. If we increase the number of distinct values for GENDER measurably, as follows:

ops$tkyte@ORA10GR1> update t
2 set gender = chr(mod(rownum,256));
48215 rows updated.

ops$tkyte@ORA10GR1> begin
2 dbms_stats.gather_table_stats
3 ( user, 'T', cascade=>true );
4 end;
5 /
PL/SQL procedure successfully completed.

we'll see that Oracle stops seeing the skip scan as being a sensible plan. It would have 256 mini indexes to inspect, and it opts for a full table scan to find our row:

ops$tkyte@ORA10GR1> set autotrace traceonly explain
ops$tkyte@ORA10GR1> select * from t t1 where object_id = 42;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=158 Card=1 Bytes=95)
1 0 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=158 Card=1 Bytes=95)

Case 2

We're using a SELECT COUNT(*) FROM T query (or something similar) and we have a B*Tree index on table T. However, the optimizer is full scanning the table, rather than counting the (much smaller) index entries. In this case, the index is probably on a set of columns that can contain nulls. Since a totally null index entry would never be made, the count of rows in the index will not be the count of rows in the table. Here the optimizer is doing the right thing -- it would get the wrong answer if it used the index to count rows.
Case 3

For an indexed column, we query using the following:

select * from t where f(indexed_column) = value

and find that the index on INDEX_COLUMN is not used. This is due to the use of the function on the column. We indexed the values of INDEX_COLUMN, not the value of F(INDEXED_COLUMN). The ability to use the index is curtailed here. We can index the function if we choose to do it.

Case 4

We have indexed a character column. This column contains only numeric data. We query using the following syntax:

select * from t where indexed_column = 5

Note that the number 5 in the query is the constant number 5 (not a character string). The index on INDEXED_COLUMN is not used. This is because the preceding query is the same as the following:

select * from t where to_number(indexed_column) = 5

We have implicitly applied a function to the column and, as noted in case 3, this will preclude the use of the index. This is very easy to see with a small example. In this example, we're going to use the built-in package DBMS_XPLAN. This package is available only with Oracle9i Release 2 and above (in Oracle9i Release 1, we will use AUTOTRACE instead to see the plan easily, but we will not see the predicate information—that is only available in Oracle9i Release 2 and above):

ops$tkyte@ORA10GR1> create table t ( x char(1) constraint t_pk primary key,
2 y date );
Table created.

ops$tkyte@ORA10GR1> insert into t values ( '5', sysdate );
1 row created.

ops$tkyte@ORA10GR1> delete from plan_table;
3 rows deleted.

ops$tkyte@ORA10GR1> explain plan for select * from t where x = 5;
Explained.

ops$tkyte@ORA10GR1> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------
Plan hash value: 749696591
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 12 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("X")=5)

As you can see, it full scanned the table, and even if we were to hint the query

ops$tkyte@ORA10GR1> explain plan for select /*+ INDEX(t t_pk) */ * from t
2 where x = 5;
Explained.

ops$tkyte@ORA10GR1> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------
Plan hash value: 3473040572
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 34 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 12 | 34 (0)| 00:00:01 |
|* 2 | INDEX FULL SCAN | T_PK | 1 | | 26 (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_NUMBER("X")=5)

it uses the index, but not for a UNIQUE SCAN as we might expect -- it is FULL SCANNING this index. The reason lies in the last line of output there: filter(TO_NUMBER("X")=5). There is an implicit function being applied to the database column. The character string stored in X must be converted to a number prior to comparing to the value 5. We cannot convert 5 to a string, since our NLS settings control what 5 might look like in a string (it is not deterministic), so we convert the string into a number, and that precludes the use of the index to rapidly find this row. If we simply compare strings to strings

ops$tkyte@ORA10GR1> delete from plan_table;
2 rows deleted.

ops$tkyte@ORA10GR1> explain plan for select * from t where x = '5';
Explained.

ops$tkyte@ORA10GR1> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------
Plan hash value: 1301177541
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 12 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | T_PK | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X"='5')

we get the expected INDEX UNIQUE SCAN, and we can see the function is not being applied. You should always avoid implicit conversions anyway. Always compare apples to apples and oranges to oranges. Another case where this comes up frequently is with dates. We try to query:

-- find all records for today
select * from t where trunc(date_col) = trunc(sysdate);

and discover that the index on DATE_COL will not be used. We can either index the TRUNC(DATE_COL) or, perhaps more easily, query using range comparison operators. The following demonstrates the use of greater than and less than on a date. Once we realize that the condition

TRUNC(DATE_COL) = TRUNC(SYSDATE)

is the same as the condition

select *
from t
where date_col >= trunc(sysdate)
and date_col < trunc(sysdate+1)

this moves all of the functions to the right-hand side of the equation, allowing us to use the index on DATE_COL (and it has the same exact effect as WHERE TRUNC(DATE_COL) = ➥ TRUNC(SYSDATE)).

If possible, you should always remove the functions from database columns when they are in the predicate. Not only will doing so allow for more indexes to be considered for use, but also it will reduce the amount of processing the database needs to do. In the preceding case, when we used

where date_col >= trunc(sysdate)
and date_col < trunc(sysdate+1)

the TRUNC values are computed once for the query, and then an index could be used to find just the qualifying values. When we used TRUNC(DATE_COL) = TRUNC(SYSDATE), the TRUNC(DATE_COL) had to be evaluated once per row for every row in the entire table (no indexes).

Case 5

The index, if used, would actually be slower. I see this a lot -- people assume that, of course, an index will always make a query go faster. So, they set up a small table, analyze it, and find that the optimizer doesn't use the index. The optimizer is doing exactly the right thing in this case. Oracle (under the CBO) will use an index only when it makes sense to do so. Consider this example:

ops$tkyte@ORA10GR1> create table t
2 ( x, y , primary key (x) )
3 as
4 select rownum x, object_name
5 from all_objects
6 /
Table created.

ops$tkyte@ORA10GR1> begin
2 dbms_stats.gather_table_stats
3 ( user, 'T', cascade=>true );
4 end;
5 /
PL/SQL procedure successfully completed.

If we run a query that needs a relatively small percentage of the table, as follows:

ops$tkyte@ORA10GR1> set autotrace on explain
ops$tkyte@ORA10GR1> select count(y) from t where x < 50;

COUNT(Y)
----------
49

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=28)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=3 Card=41 Bytes=1148)
3 2 INDEX (RANGE SCAN) OF 'SYS_C009167' (INDEX (UNIQUE)) (Cost=2 Card=41)

it will happily use the index; however, we'll find that when the estimated number of rows to be retrieved via the index crosses a threshold (which varies depending on various optimizer settings, physical statistics, and so on), we'll start to observe a full table scan:

ops$tkyte@ORA10GR1> select count(y) from t where x < 15000;

COUNT(Y)
----------
14999

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=57 Card=1 Bytes=28)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=57 Card=14994 Bytes=419832)

This example shows the optimizer won't always use an index and, in fact, it makes the right choice in skipping indexes. While tuning your queries, if you discover that an index isn't used when you think it "ought to be," don't just force it to be used -- test and prove first that the index is indeed faster (via elapsed and I/O counts) before overruling the CBO. Reason it out.
Case 6

We haven't analyzed our tables in a while. They used to be small, but now when we look at them, they have grown quite large. An index will now make sense, whereas it didn't originally. If we analyze the table, it will use the index.

Without correct statistics, the CBO cannot make the correct decisions.
Index Case Summary

In my experience, these six cases are the main reasons I find that indexes are not being used. It usually boils down to a case of "They cannot be used -- using them would return incorrect results" or "They should not be used -- if they were used, performance would be terrible."

Why isn't my index getting used?-----1

by Jonathan Lewis

The question in the title of this piece is probably the single most frequently occurring question that appears in the Metalink forums and Usenet newsgroups. This article uses a test case that you can rebuild on your own systems to demonstrate the most fundamental issues with how cost-based optimisation works. And at the end of the article, you should be much better equipped to give an answer the next time you hear that dreaded question.

Because of the wide variety of options that are available when installing Oracle, it isn't usually safe to predict exactly what will happen when someone runs a script that you have dictated to them. But I'm going to risk it, in the hope that your database is a fairly vanilla installation, with the default values for the mostly commonly tweaked parameters. The example has been built and tested on an 8.1.7 database with the db_block_size set to the commonly used value of 8K and the db_file_multiblock_read_count set to the equally commonly used value 8. The results may be a little different under Oracle 9.2

Run the script from Figure 1, which creates a couple of tables, then indexes and analyses them.

create table t1 as
select
trunc((rownum-1)/15) n1,
trunc((rownum-1)/15) n2,
rpad('x', 215) v1
from all_objects<
where rownum <= 3000;

create table t2 as
select
mod(rownum,200) n1,
mod(rownum,200) n2,
rpad('x',215) v1
from all_objects
where rownum <= 3000;

create index t1_i1 on t1(N1);
create index t2_i1 on t2(n1);

analyze table t1 compute
statistics;
analyze table t2 compute
statistics;

Figure 1: The test data sets.

Once you have got this data in place, you might want to convince yourself that the two sets of data are identical — in particular, that the N1 columns in both data sets have values ranging from 0 to 199, with 15 occurrences of each value. You might try the following check:

select n1, count(*)
from t1
group by n1;

and the matching query against T2 to prove the point.

If you then execute the queries:

select * from t1 where n1 = 45;
select * from t2 where n1 = 45;

You will find that each query returns 15 rows. However if you

set autotrace traceonly explain

you will discover that the two queries have different execution paths.

The query against table T1 uses the index, but the query against table T2 does a full tablescan.

So you have two sets of identical data, with dramatically different access paths for the same query.
What Happened to the Index?

Note: if you've ever come across any of those "magic number" guidelines regarding the use of indexes, e.g., "Oracle will use an index for less than 23 percent, 10 percent, 2 percent (pick number at random) of the data," then you may at this stage begin to doubt their validity. In this example, Oracle has used a tablescan for 15 rows out of 3,000, i.e., for just one half of one percent of the data!

To investigate problems like this, there is one very simple ploy that I always try as the first step: Put in some hints to make Oracle do what I think it ought to be doing, and see if that gives me any clues.

In this case, a simple hint:

/*+ index(t2, t2_i1) */

is sufficient to switch Oracle from the full tablescan to the indexed access path. The three paths with costs (abbreviated to C=nnn) are shown in Figure 2:

select * from t1 where n1 = 45;

EXECUTION PLAN
--------------
TABLE ACCESS BY INDEX ROWID OF T1 (C=2)
INDEX(RANGE SCAN) OF T1_I1 (C=1)


select * from t2 where n1 = 45;

EXECUTION PLAN
--------------
TABLE ACCESS FULL OF T2 (C=15)


select /*+ index(t2 t2_i1) */
*
from t1
where n1 = 45;

EXECUTION PLAN
--------------
TABLE ACCESS BY INDEX ROWID OF T2 (C=16)
INDEX(RANGE SCAN) OF T2_I1 (C=1)

Figure 2: The different queries and their costs.

So why hasn't Oracle used the index by default in for the T2 query? Easy — as the execution plan shows, the cost of doing the tablescan is cheaper than the cost of using the index.
Why is the Tablescan Cheaper?

This, of course, is simply begging the question. Why is the cost of the tablescan cheaper than the cost of using the index?

By looking into this question, you uncover the key mechanisms (and critically erroneous assumptions) of the Cost Based Optimiser.

Let's start by examining the indexes by running the query:

select
table_name,
blevel,
avg_data_blocks_per_key,
avg_leaf_blocks_per_key,
clustering_factor
from user_indexes;

The results are given in the table below:
T1 T2
Blevel 1 1
Data block / key 1 15
Leaf block / key 1 1
Clustering factor 96 3000

Note particularly the value for "data blocks per key." This is the number of different blocks in the table that Oracle thinks it will have to visit if you execute a query that contains an equality test on a complete key value for this index.

So where do the costs for our queries come from? As far as Oracle is concerned, if we fire in the key value 45, we get the data from table T1 by hitting one index leaf block and one table block — two blocks, so a cost of two.

If we try the same with table T2, we have to hit one index leaf block and 15 table blocks — a total of 16 blocks, so a cost of 16.

Clearly, according to this viewpoint, the index on table T1 is much more desirable than the index on table T2. This leaves two questions outstanding, though:

Where does the tablescan cost come from, and why are the figures for the avg_data_blocks_per_key so different between the two tables?

The answer to the second question is simple. Look back at the definition of table T1 — it uses the trunc() function to generate the N1 values, dividing the "rownum - 1 "by 15 and truncating.

Trunc(675/15) = 45
Trunc(676/15) = 45
…
Trunc(689/15) = 45

All the rows with the value 45 do actually appear one after the other in a tight little clump (probably all fitting one data block) in the table.

Table T2 uses the mod() function to generate the N1 values, using modulus 200 on the rownum:

mod(45,200) = 45
mod(245,200) = 45
…
mod(2845,200) = 45

The rows with the value 45 appear every two hundredth position in the table (probably resulting in no more than one row in every relevant block).

By doing the analyze, Oracle was able to get a perfect description of the data scatter in our table. So the optimiser was able to work out exactly how many blocks Oracle would have to visit to answer our query — and, in simple cases, the number of block visits is the cost of the query.
But Why the Tablescan?

So we see that an indexed access into T2 is more expensive than the same path into T1, but why has Oracle switched to the tablescan?

This brings us to the two simple-minded, and rather inappropriate, assumptions that Oracle makes.

The first is that every block acquisition equates to a physical disk read, and the second is that a multiblock read is just as quick as a single block read.

So what impact do these assumptions have on our experiment?

If you query the user_tables view with the following SQL:

select
table_name,
blocks
from user_tables;

you will find that our two tables each cover 96 blocks.

At the start of the article, I pointed out that the test case was running a version 8 system with the value 8 for the db_file_multiblock_read_count.

Roughly speaking, Oracle has decided that it can read the entire 96 block table in 96/8 = 12 disk read requests.

Since it takes 16 block (= disk read) requests to access the table by index, it is clearer quicker (from Oracle's sadly deluded perspective) to scan the table — after all 12 is less than 16.

Voila! If the data you are targetting is suitably scattered across the table, you get tablescans even for a very small percentage of the data — a problem that can be exaggerated in the case of very big blocks and very small rows.
Correction

In fact, you will have noticed that my calculated number of scan reads was 12, whilst the cost reported in the execution plan was 15. It is a slight simplfication to say that the cost of a tablescan (or an index fast full scan for that matter) is

'number of blocks' /
db_file_multiblock_read_count.

Oracle uses an "adjusted" multi-block read value for the calculation (although it then tries to use the actual requested size when the scan starts to run).

For reference, the following table compares a few of the actual and adjusted values:
Actual Adjusted
4 4.175
8 6.589
16 10.398
32 16.409
64 25.895
128 40.865

As you can see, Oracle makes some attempt to protect you from the error of supplying an unfeasibly large value for this parameter.

There is a minor change in version 9, by the way, where the tablescan cost is further adjusted by adding one to result of the division — which means tablescans in V9 are generally just a little more expensive than in V8, so indexes are just a little more likely to be used.
Adjustments

We have seen that there are two assumptions built into the optimizer that are not very sensible.

* A single block read costs just as much as a multi-block read — (not really likely, particularly when running on file systems without direction)
* A block access will be a physical disk read — (so what is the buffer cache for?)

Since the early days of Oracle 8.1, there have been a couple of parameters that allow us to correct these assumption in a reasonably truthful way.

See Tim Gorman's article for a proper description of these parameters, but briefly:

Optimizer_index_cost_adj takes a value between 1 and 10000 with a default of 100. Effectively, this parameter describes how cheap a single block read is compared to a multiblock read. For example the value 30 (which is often a suitable first guess for an OLTP system) would tell Oracle that a single block read costs 30% of a multiblock read. Oracle would therefore incline towards using indexed access paths for low values of this parameter.

Optimizer_index_caching takes a value between 0 and 100 with a default of 0. This tells Oracle to assume that that percentage of index blocks will be found in the buffer cache. In this case, setting values close to 100 encourages the use of indexes over tablescans.

The really nice thing about both these parameters is that they can be set to "truthful" values.

Set the optimizer_index_caching to something in the region of the "buffer cache hit ratio." (You have to make your own choice about whether this should be the figure derived from the default pool, keep pool, or both).

The optimizer_index_cost_adj is a little more complicated. Check the typical wait times in v$system_event for the events "db file scattered read" (multi block reads) and "db file sequential reads" (single block reads). Divide the latter by the former and multiply by one hundred.
Improvements

Don't forget that the two parameters may need to be adjusted at different times of the day and week to reflect the end-user workload. You can't just derive one pair of figures, and use them for ever.

Happily, in Oracle 9, things have improved. You can now collect system statistics, which are originally included just the four:

+ Average single block read time
+ Average multi block read time
+ Average actual multiblock read
+ Notional usable CPU speed.

Suffice it to say that this feature is worth an article in its own right — but do note that the first three allow Oracle to discover the truth about the cost of multi block reads. And in fact, the CPU speed allows Oracle to work out the CPU cost of unsuitable access mechanisms like reading every single row in a block to find a specific data value and behave accordingly.

When you migrate to version 9, one of the first things you should investigate is the correct use of system statistics. This one feature alone may reduce the amount of time you spend trying to "tune" awkward SQL.

In passing, despite the wonderful effect of system statistics both of the optimizer adjusting parameters still apply — although the exact formula for their use seems to have changed between version 8 and version 9.
Variations on a Theme

Of course, I have picked one very special case — equality on a single column non-unique index, where thare are no nulls in the table — and treated it very simply. (I haven't even mentioned the relevance of the index blevel and clustering_factor yet.) There are numerous different strategies that Oracle uses to work out more general cases.

Consider some of the cases I have conveniently overlooked:

+ Multi-column indexes
+ Part-used multi-column indexes
+ Range scans
+ Unique indexes
+ Non-unique indexes representing unique constraints
+ Index skip scans
+ Index only queries
+ Bitmap indexes
+ Effects of nulls

The list goes on and on. There is no one simple formula that tells you how Oracle works out a cost — there is only a general guideline that gives you the flavour of the approach and a list of different formulae that apply in different cases.

However, the purpose of this article was to make you aware of the general approach and the two assumptions built into the optimiser's strategy. And I hope that this may be enough to take you a long way down the path of understanding the (apparently) strange things that the optimiser has been known to do.

Script: To Monitor the Usage of Indexes

Subject: Script: To Monitor the Usage of Indexes
Doc ID: Note:1033478.6 Type: SCRIPT
Last Revision Date: 30-MAY-2007 Status: PUBLISHED

Abstract
This script will monitor the usage of indexes on the database.


Product Name, Product Version
Oracle Server, 7.3 to 10.0
Platform Platform Independent
Date Created 09-Jul-1997

Instructions
Execution Environment:
SQL, SQL*Plus

Access Privileges:
Requires DBA access privileges to be executed.

Usage:
sqlplus sys/

Instructions:
Copy the script to a file and execute it from SQL*Plus.


PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text
editors, e-mail packages, and operating systems handle text formatting (spaces,
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected.



Description
You have an environment that is heavily indexed, and you want to monitor the
usage of the indexes. For example, at the end of the week before the batch
loads you would like to check which indexes are being used in queries
throughout the week.

You can find the index usage from the explain plan. If you explain all the
queries within V$SQLAREA, you can see which indexes are being used.

The following is a sample of the type of script you can write to get these
results. This script is only a sample, and works under certain assumptions.

Miscellaneous requirements and info:
- The user running the script should have all the privileges to explain
everything in v$sqlarea not loaded by SYS.
- plan_table.remarks can be used to determine privilege related errors.
- The parameter OPTIMIZER_GOAL is constant for all SQL in shared pool
ignores v$sqlarea.optimizer_mode.
- The statistics have not been regenerated between snapshots.
- No statements have been truncated.
- All objects are local.
- All referenced tables/views are either owned by the user running the
script or fully qualified names/synonyms were used in the SQL.
- No "popular" statements have aged out of (and for that matter, been
reloaded into) the shared pool since the last snapshot.
- Instance is either bounced or has the shared pool completely flushed
after each snapshot to reset the executions and other statistics to zero.
- For all statements, v$sqlarea.version_count = 1 (children).
- Review Bug:2282891 and Bug:2953935 that may affect performance of this script

NOTE: With 9i, you can use the V$SQL_PLAN instead.




References




Script
set echo off
Rem Drop and recreate PLAN_TABLE for EXPLAIN PLAN
drop table plan_table;
Rem Drop and recreate SQLTEMP for taking a snapshot of the SQLAREA
drop table sqltemp;
create table sqltemp
(ADDR VARCHAR2 (16),
SQL_TEXT VARCHAR2 (2000),
DISK_READS NUMBER,
EXECUTIONS NUMBER,
PARSE_CALLS NUMBER);

set echo on

Rem Create procedure to populate the plan_table by executing
Rem explain plan...for 'sqltext' dynamically
create or replace procedure do_explain
(addr IN varchar2, sqltext IN varchar2) as
dummy varchar2 (1100);
mycursor integer;
ret integer;
my_sqlerrm varchar2 (85);
begin
dummy:='EXPLAIN PLAN SET STATEMENT_ID=' ;
dummy:=dummy||''''||addr||''''||' FOR '||sqltext;
mycursor := dbms_sql.open_cursor;
dbms_sql.parse(mycursor,dummy,dbms_sql.v7);
ret := dbms_sql.execute(mycursor);
dbms_sql.close_cursor(mycursor);
commit;
exception -- Insert errors into PLAN_TABLE...
when others then
my_sqlerrm := substr(sqlerrm,1,80);
insert into plan_table(statement_id,remarks)
values (addr,my_sqlerrm);
-- close cursor if exception raised on EXPLAIN PLAN
dbms_sql.close_cursor(mycursor);
end;
/

Rem Start EXPLAINing all S/I/U/D statements in the shared pool
declare
-- exclude statements with v$sqlarea.parsing_schema_id = 0 (SYS)
cursor c1 is select address, sql_text, DISK_READS, EXECUTIONS,
PARSE_CALLS
from v$sqlarea where command_type in (2,3,6,7)
and parsing_schema_id != 0;
cursor c2 is select addr, sql_text from sqltemp;
addr2 varchar(16);
sqltext v$sqlarea.sql_text%type;
dreads v$sqlarea.disk_reads%type;
execs v$sqlarea.executions%type;
pcalls v$sqlarea.parse_calls%type;
begin
open c1;
fetch c1 into addr2,sqltext,dreads,execs,pcalls;
while (c1%found) loop
insert into sqltemp values(addr2,sqltext,dreads,execs,pcalls);
commit;
fetch c1 into addr2,sqltext,dreads,execs,pcalls;
end loop;
close c1;
open c2;
fetch c2 into addr2, sqltext;
while (c2%found) loop
do_explain(addr2,sqltext);
fetch c2 into addr2, sqltext;
end loop;
close c2;
end;
/

Rem Generate a report of index usage based on the number of times
Rem a SQL statement using that index was executed
select p.owner, p.name, sum(s.executions) totexec
from sqltemp s,
(select distinct statement_id stid, object_owner owner, object_name name
from plan_table
where operation = 'INDEX') p
where s.addr = p.stid
group by p.owner, p.name
order by 2 desc;

Rem Perform cleanup on exit (optional)
delete
from plan_table
where statement_id in(
select addr
from sqltemp
);
drop table sqltemp;



==============
Sample Output:
==============

SQL> @check_indexes

Table dropped.


Table created.


Table dropped.


Table created.

SQL> Rem Create procedure to populate the plan_table by executing
SQL> Rem explain plan...for 'sqltext' dynamically
SQL> create or replace procedure do_explain
2 (addr IN varchar2, sqltext IN varchar2)
3 as
4 dummy varchar2 (1100);
5 mycursor integer;
6 ret integer;
7 my_sqlerrm varchar2 (85);
8 begin
9 dummy:='EXPLAIN PLAN SET STATEMENT_ID=' ;
10 dummy:=dummy||''''||addr||''''||' FOR '||sqltext;
11 mycursor := dbms_sql.open_cursor;
12 dbms_sql.parse(mycursor,dummy,dbms_sql.v7);
13 ret := dbms_sql.execute(mycursor);
14 dbms_sql.close_cursor(mycursor);
15 commit;
16 exception -- Insert errors into PLAN_TABLE...
17 when others then
18 my_sqlerrm := substr(sqlerrm,1,80);
19 insert into plan_table(statement_id,remarks)
20 values (addr,my_sqlerrm);
21 -- close cursor if exception raised on EXPLAIN PLAN
22 dbms_sql.close_cursor(mycursor);
23 end;
24 /

Procedure created.

SQL> Rem Start EXPLAINing all S/I/U/D statements in the shared pool
SQL> declare
2 -- exclude statements with v$sqlarea.parsing_schema_id = 0 (SYS)
3 cursor c1 is select address, sql_text, DISK_READS, EXECUTIONS,
4 PARSE_CALLS
5 from v$sqlarea where command_type in (2,3,6,7)
6 and parsing_schema_id != 0;
7 cursor c2 is select addr, sql_text from sqltemp;
8 addr2 varchar(16);
9 sqltext v$sqlarea.sql_text%type;
10 dreads v$sqlarea.disk_reads%type;
11 execs v$sqlarea.executions%type;
12 pcalls v$sqlarea.parse_calls%type;
13 begin
14 open c1;
15 fetch c1 into addr2,sqltext,dreads,execs,pcalls;
16 while (c1%found) loop
17 insert into sqltemp values(addr2,sqltext,dreads,execs,pcalls);
18 commit;
19 fetch c1 into addr2,sqltext,dreads,execs,pcalls;
20 end loop;
21 close c1;
22 open c2;
23 fetch c2 into addr2, sqltext;
24 while (c2%found) loop
25 do_explain(addr2,sqltext);
26 fetch c2 into addr2, sqltext;
27 end loop;
28 close c2;
29 end;
30 /

PL/SQL procedure successfully completed.

SQL> Rem Generate a report of index usage based on the number of times
SQL> Rem a SQL statement using that index was executed
SQL> select p.owner, p.name, sum(s.executions) totexec
2 from sqltemp s,
3 (select distinct statement_id stid, object_owner owner, object_name name
4 from plan_table
5 where operation = 'INDEX') p
6 where s.addr = p.stid
7 group by p.owner, p.name
8 order by 2 desc;

OWNER NAME TOTEXEC
------------------------------ ------------------------------ ----------
TEST JUNK_C1 1




Disclaimer
EXCEPT WHERE EXPRESSLY PROVIDED OTHERWISE, THE INFORMATION, SOFTWARE,
PROVIDED ON AN "AS IS" AND "AS AVAILABLE" BASIS. ORACLE EXPRESSLY DISCLAIMS
ALL WARRANTIES OF ANY KIND, WHETHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
PURPOSE AND NON-INFRINGEMENT. ORACLE MAKES NO WARRANTY THAT: (A) THE RESULTS
THAT MAY BE OBTAINED FROM THE USE OF THE SOFTWARE WILL BE ACCURATE OR
RELIABLE; OR (B) THE INFORMATION, OR OTHER MATERIAL OBTAINED WILL MEET YOUR
EXPECTATIONS. ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE DOWNLOADED OR
OTHERWISE OBTAINED IS DONE AT YOUR OWN DISCRETION AND RISK. ORACLE SHALL HAVE
NO RESPONSIBILITY FOR ANY DAMAGE TO YOUR COMPUTER SYSTEM OR LOSS OF DATA THAT
RESULTS FROM THE DOWNLOAD OF ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE.

ORACLE RESERVES THE RIGHT TO MAKE CHANGES OR UPDATES TO THE SOFTWARE AT ANY
TIME WITHOUT NOTICE.



Limitation of Liability
IN NO EVENT SHALL ORACLE BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
SPECIAL OR CONSEQUENTIAL DAMAGES, OR DAMAGES FOR LOSS OF PROFITS, REVENUE,
DATA OR USE, INCURRED BY YOU OR ANY THIRD PARTY, WHETHER IN AN ACTION IN
CONTRACT OR TORT, ARISING FROM YOUR ACCESS TO, OR USE OF, THE SOFTWARE.

SOME JURISDICTIONS DO NOT ALLOW THE LIMITATION OR EXCLUSION OF LIABILITY.
ACCORDINGLY, SOME OF THE ABOVE LIMITATIONS MAY NOT APPLY TO YOU

Viewing All Indexes Being Monitored Under Another User's Schema

Subject: Viewing All Indexes Being Monitored Under Another User's Schema
Doc ID: Note:160712.1 Type: BULLETIN
Last Revision Date: 11-JUL-2007 Status: PUBLISHED


PURPOSE
To show how to view all indexes being monitored.


SCOPE & APPLICATION
Instructional.


Viewing All Indexes Being Monitored Under Another User's Schema:
================================================================

V$OBJECT_USAGE does not display rows for all indexes in the database whose
usage is being monitored.

'ALTER INDEX MONITORING USAGE' places an entry in V$OBJECT_USAGE for
that particular index to help determine if the index is being used or not. The
V$OBJECT_USAGE view uses the username logged into database when the 'ALTER
INDEX MONITORING USAGE' is issued. This will not enable any user other
than the user who issued the 'ALTER INDEX MONITORING USAGE' to view if
index is being monitored or not.

The view structure may be changed slightly (see below) in order to expand its
scope system-wide (see below) so that you may see all indexes being monitored.

For example:

Showing User Scott monitoring his Index on EMP table:

SQL> connect scott/tiger
SQL> set LONG 30000

SQL> select text from dba_views where view_name ='V$OBJECT_USAGE';

TEXT
select io.name, t.name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
ou.start_monitoring,
ou.end_monitoring
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
where io.owner# = userenv('SCHEMAID')
and i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#


SQL> select index_name, table_name, uniqueness, status from user_indexes
where table_name = 'EMP';

INDEX_NAME TABLE_NAME UNIQUENES STATUS
PK_EMP EMP UNIQUE VALID

SQL> alter index PK_EMP monitoring usage;

Index altered.

SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
PK_EMP EMP YES NO 10/12/2001 06:42:35


Then connect as another user to view indexes being monitored:

SQL> connect / as sysdba;

Connected.

SQL> select * from v$object_usage;

no rows selected


To be able to view them do the following:

SQL> create or replace view V$ALL_OBJECT_USAGE
(OWNER,
INDEX_NAME,
TABLE_NAME,
MONITORING,
USED,
START_MONITORING,
END_MONITORING)
as
select u.name, io.name, t.name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
ou.start_monitoring,
ou.end_monitoring
from sys.user$ u, sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
where i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#
and u.user# = io.owner#

View created.


SQL> select * from v$all_object_usage;

OWNER INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
SCOTT PK_EMP EMP YES NO 10/12/2001 06:42:35



Related Documents:
==================

Oracle9i Database Administrator's Guide Volume 1 (Managing Indexes: Monitoring
Index Usage)

Note 144070.1 Identifying unused indexes in Oracle9i

Viewing All Indexes Being Monitored Under Another User's Schema

Subject: Viewing All Indexes Being Monitored Under Another User's Schema
Doc ID: Note:160712.1 Type: BULLETIN
Last Revision Date: 11-JUL-2007 Status: PUBLISHED


PURPOSE
To show how to view all indexes being monitored.


SCOPE & APPLICATION
Instructional.


Viewing All Indexes Being Monitored Under Another User's Schema:
================================================================

V$OBJECT_USAGE does not display rows for all indexes in the database whose
usage is being monitored.

'ALTER INDEX MONITORING USAGE' places an entry in V$OBJECT_USAGE for
that particular index to help determine if the index is being used or not. The
V$OBJECT_USAGE view uses the username logged into database when the 'ALTER
INDEX MONITORING USAGE' is issued. This will not enable any user other
than the user who issued the 'ALTER INDEX MONITORING USAGE' to view if
index is being monitored or not.

The view structure may be changed slightly (see below) in order to expand its
scope system-wide (see below) so that you may see all indexes being monitored.

For example:

Showing User Scott monitoring his Index on EMP table:

SQL> connect scott/tiger
SQL> set LONG 30000

SQL> select text from dba_views where view_name ='V$OBJECT_USAGE';

TEXT
select io.name, t.name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
ou.start_monitoring,
ou.end_monitoring
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
where io.owner# = userenv('SCHEMAID')
and i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#


SQL> select index_name, table_name, uniqueness, status from user_indexes
where table_name = 'EMP';

INDEX_NAME TABLE_NAME UNIQUENES STATUS
PK_EMP EMP UNIQUE VALID

SQL> alter index PK_EMP monitoring usage;

Index altered.

SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
PK_EMP EMP YES NO 10/12/2001 06:42:35


Then connect as another user to view indexes being monitored:

SQL> connect / as sysdba;

Connected.

SQL> select * from v$object_usage;

no rows selected


To be able to view them do the following:

SQL> create or replace view V$ALL_OBJECT_USAGE
(OWNER,
INDEX_NAME,
TABLE_NAME,
MONITORING,
USED,
START_MONITORING,
END_MONITORING)
as
select u.name, io.name, t.name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
ou.start_monitoring,
ou.end_monitoring
from sys.user$ u, sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
where i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#
and u.user# = io.owner#

View created.


SQL> select * from v$all_object_usage;

OWNER INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
SCOTT PK_EMP EMP YES NO 10/12/2001 06:42:35



Related Documents:
==================

Oracle9i Database Administrator's Guide Volume 1 (Managing Indexes: Monitoring
Index Usage)

Note 144070.1 Identifying unused indexes in Oracle9i

Identifying unused indexes in Oracle9i

Subject: Identifying unused indexes in Oracle9i
Doc ID: Note:144070.1 Type: BULLETIN
Last Revision Date: 22-APR-2007 Status: PUBLISHED


PURPOSE
-------

The purpose of this document is to explain how to find unused indexes
using the new feature in Oracle9: "Identifying Unused Indexes" via
ALTER INDEX MONITORING USAGE, as mentioned in Oracle9i Database
Administrator's Guide, Chapter 11.

The clause MONITORING / NOMONITORING USAGE is useful in determining
whether an index is being used.


SCOPE & APPLICATION
-------------------

This article is intended for database Administrators who want to
identify unused indexes in their database.


IDENTIFYING UNUSED INDEXES
--------------------------

You can find indexes that are not being used by using the ALTER INDEX
MONITORING USAGE functionality over a period of time that is
representative of your workload.

PART 1 will demonstrate the new feature using a simple example.

PART 2 will give a detailed instruction how to identify all unused
indexes in the database.


PART 1 - Monitoring usage of indexes - a simple example
---------------------------------------------------------

To demonstrate the new feature, you can use the following example:
(a) Create and populate a small test table
(b) Create Primary Key index on that table
(c) Query v$object_usage: the monitoring has not started yet
(d) Start monitoring of the index usage
(e) Query v$object_usage to see the monitoring in progress
(f) Issue the SELECT statement which uses the index
(g) Query v$object_usage again to see that the index has been used
(h) Stop monitoring of the index usage
(i) Query v$object_usage to see that the monitoring stopped


Detailed steps:

(a) Create and populate a small test table

create table products
(prod_id number(3),
prod_name_code varchar2(5));

insert into products values(1,'aaaaa');
insert into products values(2,'bbbbb');
insert into products values(3,'ccccc');
insert into products values(4,'ddddd');
commit;


(b) Create Primary Key index on that table

alter table products
add (constraint products_pk primary key (prod_id));


(c) Query v$object_usage: the monitoring has not started yet

column index_name format a12
column monitoring format a10
column used format a4
column start_monitoring format a19
column end_monitoring format a19
select index_name,monitoring,used,start_monitoring,end_monitoring
from v$object_usage;

no rows selected


(d) Start monitoring of the index usage

alter index products_pk monitoring usage;

Index altered.


(e) Query v$object_usage to see the monitoring in progress

select index_name,monitoring,used,start_monitoring,end_monitoring
from v$object_usage;

INDEX_NAME MONITORING USED START_MONITORING END_MONITORING
------------ ---------- ---- ------------------- -------------------
PRODUCTS_PK YES NO 04/25/2001 15:43:13

Note: Column MONITORING='YES', START_MONITORING gives the timestamp.


(f) Issue the SELECT statement which uses the index

First, make sure that index will be used for this statement.
Create plan_table in your schema, as required by Oracle Autotrace
utility:

@$ORACLE_HOME/rdbms/admin/utlxplan

Table created.

Use Oracle Autotrace utility to obtain the execution plan:

set autotrace on explain
select * from products where prod_id = 2;
.
.
Execution Plan
------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCTS'
2 1 INDEX (UNIQUE SCAN) OF 'PRODUCTS_PK' (UNIQUE)

set autotrace off

Now, since you know the index will be used for this query, issue the
actual SELECT statement:

select * from products where prod_id = 2;

PROD_ID PROD_
---------- -----
2 bbbbb


(g) Query v$object_usage again to see that the index has been used

select index_name,monitoring,used,start_monitoring,end_monitoring
from v$object_usage;

INDEX_NAME MONITORING USED START_MONITORING END_MONITORING
------------ ---------- ---- ------------------- -------------------
PRODUCTS_PK YES YES 04/25/2001 15:43:13

Note: Column USED='YES'.


(h) Stop monitoring of the index usage

alter index products_pk nomonitoring usage;

Index altered.


(i) Query v$object_usage to see that the monitoring stopped

select index_name,monitoring,used,start_monitoring,end_monitoring
from v$object_usage;

INDEX_NAME MONITORING USED START_MONITORING END_MONITORING
------------ ---------- ---- ------------------- -------------------
PRODUCTS_PK NO YES 04/25/2001 15:43:13 04/25/2001 15:48:44

Note: Column MONITORING='NO', END_MONITORING gives the timestamp.



PART 2 - How to identify all unused indexes in the database
-------------------------------------------------------------

To identify all unused indexes in the database, you can do the
following:
(a) Create a SQL script to start monitoring all indexes except those
owned by users SYS and SYSTEM
(b) Create another script to stop monitoring all indexes except those
owned by users SYS and SYSTEM
(c) Connect as a user with ALTER ANY INDEX system privilege and run
the start monitoring script
(d) Perform normal activities in your database
(e) After a period of time that is representative of your workload,
run the stop monitoring script
(f) Query v$object_usage to see what indexes have not been used


Detailed steps:

(a) Create a SQL script to start monitoring all indexes except those
owned by users SYS and SYSTEM

set heading off
set echo off
set feedback off
set pages 10000
spool startmonitor.sql
select 'alter index '||owner||'.'||index_name||' monitoring usage;'
from dba_indexes
where owner not in ('SYS','SYSTEM');
spool off


(b) Create another script to stop monitoring all indexes except those
owned by users SYS and SYSTEM

set heading off
set echo off
set feedback off
set pages 10000
spool stopmonitor.sql
select 'alter index '||owner||'.'||index_name||' nomonitoring usage;'
from dba_indexes
where owner not in ('SYS','SYSTEM');
spool off


(c) Connect as a user with ALTER ANY INDEX system privilege and run
the newly created script to start monitoring.

@startmonitor

(d) Perform normal activities in your database


(e) After a period of time that is representative of your workload,
connect as a user with ALTER ANY INDEX system privilege and run
the script to stop monitoring.

@stopmonitor


(f) Query v$object_usage in join with dba_indexes, to see what indexes
have not been used

select d.owner, v.index_name
from dba_indexes d, v$object_usage v
where v.used='NO' and d.index_name=v.index_name;



RELATED DOCUMENTS
-----------------

Note 1033478.6 Script Monitoring the Usage of Indexes(prior Oracle9i)
Note 1015945.102 How to Monitor Most Recently Accessed Indexessing script

Oracle9i Database Administrator's Guide

Identifying Unused Indexes with the ALTER INDEX MONITORING USAGE Command

Subject: Identifying Unused Indexes with the ALTER INDEX MONITORING USAGE Command
Doc ID: Note:136642.1 Type: BULLETIN
Last Revision Date: 02-JUL-2007 Status: PUBLISHED


Checked for relevance on 03-July-2007

PURPOSE
This document explains how to identify unused indexes in order to remove them.


SCOPE & APPLICATION
DBAs must be aware that unused indexes:
* consume storage space
* degrade performance by unnecessary overheads during DML operations

REFERENCES

Note 144070.1 Identifying unused indexes in Oracle9i


How to Identify Unused Indexes in Order to Remove Them:
=======================================================
The examples below work if the user logged is the owner of the index.

1. Set the index under MONITORING:

SQL> alter index I_EMP monitoring usage;
Index altered.

SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MON USED START_MONITORING END_MONITORING
--------------- --------------- --- ---- ------------------- -------------------
I_EMP EMP YES NO 03/14/2001 09:17:19


2. Check if the monitored indexes are used or not through the USED column in
V$OBJECT_USAGE view:

SQL> select sal from emp where ename='SMITH';

SAL
----------
800


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (RANGE SCAN) OF 'I_EMP' (NON-UNIQUE)


The explain plan indicates the index is used.


SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MON USED START_MONITORING END_MONITORING
--------------- --------------- --- ---- ------------------- -------------------
I_EMP EMP YES YES 03/14/2001 09:17:19


If the index was not used, the DBA could drop this unused index.


3. To stop monitoring an index, use the following SQL statement:

SQL> alter index i_emp nomonitoring usage;
Index altered.

SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MON USED START_MONITORING END_MONITORING
--------------- --------------- --- ---- ------------------- -------------------
I_EMP EMP NO YES 03/14/2001 09:17:19 03/14/2001 09:55:24


As soon as you turn monitoring on again for the index, both columns
MONITORING and USED are reset.


SQL> alter index i_emp monitoring usage;
Index altered.

SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MON USED START_MONITORING END_MONITORING
--------------- --------------- --- ---- ------------------- -------------------
I_EMP EMP YES NO 03/14/2001 09:57:27

Types Of Partitioned Indexes

Indexes, like tables, may be partitioned. There are two possible methods to partition indexes. You may either:



Equi-partition the index with the table - Also known as a local index. For every table partition, there will be an index partition that indexes just that table partition. All of the entries in a given index partition point to a single table partition and all of the rows in a single table partition are represented in a single index partition.



Partition the index by range - Also known as a global index. Here the index is partitioned by range, and a single index partition may point to any (and all) table partitions.



In the locally partitioned index, the index entries in a given partition, point into exactly one table partition. The globally partitioned index diagram however, shows that the index entries in a global index may point into any or all of the table partitions. Also, note that the number of index partitions may in fact be different than the number of table partitions.



Since global indexes may be partitioned by range only, you must use local indexes if you wish to have a hash or composite partitioned index. The local index will be partitioned using the same scheme as the underlying table.

How Can I Identify Which Index represents Which Primary Or Unique Key constraint?

The connection between constraints and the indexes which are used to check these constraints for the current user can be described by this query:

select --+ rule o.owner index_owner, o.object_name index_name, n.name constraint_name from sys.cdef$ c, dba_objects o, sys.con$ n where c.enabled = o.object_id and c.con# = n.con# and n.owner# = uid;If you leave away the condition and n.owner# = uid you get all the constraints. You may further limit this query to your constraint name by adding the condition and n.name = 'your_constraint_name'.

Why can indexes and constraints be so different? In particular, you may use for example an index on columns (c, a, b) to enable a unique constraint on columns (a, b, c). Remember a constraint is a logical structure whereas an index is a physical one. So a unique or a primary constraint just describe the uniqueness. If (c, a, b) is unique then all other permutations are unique as well.

Further, these indexes may also be non-unique. You need this if you have a deferred constraint that is checked only at commit time. If you would insist on a unique index the attempt to insert duplicate values would fail before the commit although another command may have undone the duplicate entry.

This original query was contributed to comp.databases.oracle.server by Thomas Kyte

Index Orgainized Table-Introduction

Index Organized Tables or IOT where introduced in Oracle with the arrival of Oracle 8. These tables where introduced primarily for Internet applications that involve data access based on single column primary keys.

Storage organization has been always a key factor in faster access of data. With normal tables there are no indexes created at first. First you have to create a table, then create indexes for faster performance in data access.

Indexes have some drawbacks such as it stores data in two places, one in table and in index. If a query is issued (it is assumed that it uses the index), it checks the index and retrieves the address of data in table. Then the data is fetched from the tables to produce the query output.

But in case of an Index Organized Table, data is stored in the index itself with the rows placed in a key-sequenced order, thus eliminating the necessity of duplicate data being stored in index and table.

An Index Organized Table is created using the keyword ORGANIZATION INDEX at the end of the CREATE TABLE script.

Refer the following example for creation of a IOT:
CREATE TABLE temp1 (
slno NUMBER (3) NOT NULL,
CONSTRAINT PK_temp1
PRIMARY KEY ( slno))
ORGANIZATION INDEX;

Now we will check in what order the data is actually stored by inserting some rows into the table temp1.

insert into temp1 values(6);

insert into temp1 values(1);

insert into temp1 values(5);

insert into temp1 values(4);

insert into temp1 values(3);

insert into temp1 values(2);

insert into temp1 values(9);

insert into temp1 values(7);

insert into temp1 values(8);

insert into temp1 values(10);

Now after inserting these rows by issuing a select statement without any order clause will retrieve the rows in stored order. Let's check this by issuing the following statement:

select * from temp1;

The output is:
SLNO
1
2
3
4
5
6
7
8
9
10

Now from this exercise we are clear as to how Oracle stores the data in an Index Organized Table for a normal table the rows would have been selected in the inserted order.

What is IOT?
So what is an IOT? An IOT has entirely different logic of storage and indexing. In normal tables as soon as we create a row it is associated with a ROWID. This ROWID is permenant as long as the data is there. When an index is created, it stores the column data as well as the ROWID of the table data as it provides its physical location.

IOTs do not consider ROWID. This is because the data is actually stored in a B-Tree index that sorts the data with the leaves in the order of the primary key's data. As and when INSERTs or UPDATEs are fired against this IOT, the rows are re-arranged to store the data in sorted order of the primary key.

The access to the data is fast because as soon as the values are found in the B-Tree index, Oracle is ready to pump the output directly as it is not tied up with ROWIDs. Hence there are two benefits of using IOT:
1. Lookup to table from index is eliminated
2. Storage requirements are reduced as data is stored only in one place.

Courtesy: Oracle9i Index-Organized Tables Technical Whitepaper - Oracle Corporation

ORA -2429:Cannot Drop Index used for enforcement of Unique/primary key

To simulate the error do the following:

create table temp_index as select object_name from user_objects where object_type='INDEX';

create table testing(testid number primary key, testchar varchar2(200));

Find out the index created for the primary key by the following statement:

select object_name from user_objects where object_type='INDEX'
minus
select object_name from temp_index;

Drop the index by issuing the following statement:
drop index SYS_C00249876;

You will get the error ORA-02429: Cannot drop index used for enforcement of unique/primary key

In Index organized tables this problem will be more. You will not be able to drop either the primary key or index.

Consider the example:
CREATE TABLE temp1 (
testid NUMBER (3) NOT NULL,
CONSTRAINT pk_temp1
PRIMARY KEY ( testid))
ORGANIZATION INDEX ;

alter table temp1 drop constraint pk_temp1;

You will get the following error:
ORA-25188: Cannot drop/disable/defer the primary key constraint for index-organized tables or sorted hash cluster

drop index pk_temp1;
You will get the following error:
ORA-02429: Cannot drop index used for enforcement of unique/primary key

How to rename a Index?

The normal way of renaming an index is by the following two commands:

drop index id1;

create index id2 on table_name(column_name, column_name1);

But starting from Oracle 8i, you will be able to rename an index by issuing the following command:

alter index id1 rename to id2;