Select all indexes by status
Check indexes by status by two queries from metadata of Oracle. Depending on your needs and rights you can use them:
- all_indexes - this will group and count all indexes by status
- user_objects - this will show you all indexes and their information. You can filter by status.
- dba_indexes - third query is preparing script for dropping all indexes for a particular owner
--select all indexes grouped by status
SELECT
status,
COUNT(*)
FROM all_indexes
GROUP BY status
--view all invalid indexes
select *
from user_objects
where object_type = 'INDEX'
and status ='INVALID'--and status ='VALID'
--prepared script for dropping all indexes for MYOWNER
SELECT 'drop index MYOWNER.' || index_name || ';'
FROM dba_indexes
WHERE table_owner = 'MYOWNER';
Index rebuild
In oracle indexes rebuilding is fairly an easy operation. It depends on the type of the index:
- normal index rebuild
alter index index_name rebuild;
- normal index rebuild has this syntax:
alter index <index_name> rebuild partition <partition_name>;
Information about the partition can be taken from the table DDL using tools like SQL Developer.
alter index index_name rebuild partition partition1;
alter index index_name rebuild partition partition2;
alter index index_name rebuild partition partition3;
...
alter index index_name rebuild partition partition64;
Script collecting all needed subpartitions
SELECT 'ALTER INDEX '||index_owner||'.'|| INDEX_NAME ||' rebuild SUBPARTITION ' || SUBPARTITION_NAME ||' ONLINE PARALLEL;'
FROM dba_ind_subpartitions
WHERE INDEX_NAME = 'INDEX_NAME';
Script collecting all needed partitions
SELECT 'ALTER INDEX '||'MYOWNER'||'.'|| 'INDEX_NAME' ||' rebuild PARTITION ' || PARTITION_NAME ||' ONLINE PARALLEL;'
FROM all_ind_partitions
WHERE INDEX_NAME = 'INDEX_NAME'
and INDEX_OWNER = 'MYOWNER';
SELECT 'ALTER INDEX '||'MYOWNER'||'.'|| 'INDEX_NAME' ||' rebuild PARTITION ' || PARTITION_NAME ||' ONLINE PARALLEL;'
FROM dba_ind_partitions
WHERE INDEX_NAME = 'INDEX_NAME'
Alter Index Offline
In oracle you may need to make the index unusable or to drop it prior to rebuild it:
- make index unusable
alter index index_name unusable;
- drop index
drop index index_name
Create index
After dropping an index or if you need a new one you can create it by:
-- create unique index
CREATE UNIQUE INDEX INDEX_NAME ON "TABLE" ("COLUMN") ONLINE;
-- create online index
CREATE INDEX "INDEX_NAME" ON "TABLE" ("COLUMN", "COLUMN2") ONLINE;
--create index parallel
create index "MYOWNER"."MYTABLE$IND" on "MYOWNER"."MYTABLE" (column1,column2,column3) tablespace ts_indexes local parallel 12 online;
--create partioned global
create index "MYOWNER"."MYTABLE$IND" on "MYOWNER"."MYTABLE" (column_date) global;
--create partioned local
create index "MYOWNER"."MYTABLE$IND" on "MYOWNER"."MYTABLE" (column_date) local;
--create partion index by hash
CREATE INDEX INDEX_NAME ON "MYOWNER"."MYTABLE" (column1,column2,column3) GLOBAL
PARTITION BY HASH (column1,column2)
(PARTITION p1 TABLESPACE tbs_1,
PARTITION p2 TABLESPACE tbs_2,
PARTITION p3 TABLESPACE tbs_3,
PARTITION p4 TABLESPACE tbs_4);
--create index by range
CREATE INDEX "MYOWNER"."MYTABLE$IND" ON "MYOWNER"."MYTABLE" ("COLUMN1","COLUMN1")
GLOBAL PARTITION by range (COLUMN1)
(
PARTITION r1 VALUES LESS THAN (TO_DATE('1-JAN-1999','DD-MON-YYYY')),
PARTITION r2 VALUES LESS THAN (TO_DATE('1-JAN-2000','DD-MON-YYYY')),
PARTITION r3 VALUES LESS THAN (TO_DATE('1-JAN-2001','DD-MON-YYYY')),
...
);
Check index usage
Explain plan
If you wonder whether the index is used and what is the performance you can check execute plan for a given query by :
explain plan for select * from mytable;
select * from table(dbms_xplan.display);
- Run first query and replace select * from mytable with your query
- wait for explained to be shown
- run select * from table(dbms_xplan.display); in order to get the result
- result would be something like:
-------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 111| 444| 5 | | 1 | TABLE ACCESS FULL | MYTABLE | 111| 444| 5 |
SQL Tunning Advisor
You can use also SQL Tunning Advisor which is available after version 3.0 of SQL Developer(it requires also Oracle Database 11g Enterprise Edition with access to the Tuning and Diagnostic management packs and with the sample schema installed). It can be executed by F10 while you selected your query or by right click on it and select SQL Tunning Advisor.
It will give you information for the several execution plans, recommendations for new indexes or query enhancements:
- findings on SQL profiles
- gathering statistics
- useful indexes
- rewrites query
The output would be:
GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name : 11111 Tuning Task Owner : MyOwner Workload Type : Single SQL Statement ------------------------------------------------------------------------------- ------------------------------------------------------------------------------- FINDINGS SECTION (2 findings) ------------------------------------------------------------------------------- 1- SQL Profile Finding (see explain plans section below) -------------------------------------------------------- 2- Index Finding (see explain plans section below) -------------------------------------------------- ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- 2- Original With Adjusted Cost ------------------------------ 3- Using New Indices -------------------- 4- Using SQL Profile --------------------
Errors related to indexes
For some queries on Oracle tables with select SQL statement, Oracle can return the following error:
ORA-01502: index ‘string.string’ or partition of such index is in unusable state
The error indicates an attempt has been made to access an index or index partition that has been marked unusable by a direct load or by a DDL operation.
The solution would be:
1.Drop the specified index and/or recreate the index
2.Rebuild the specified index
3.Rebuild the unusable index partition
Generally, the following SQL manipulation language will be enough to rebuild the unusable index:
- index rebuild
ALTER INDEX index_name REBUILD