Rebuild All Indexes using 1 SQL | Single query

Below is query to rebuild all indexes in Oracle in one  go. It will generate the dynamic queries as output which can be copy and execute in same SQL window and all indexes will be rebuild automatically.

select
distinct  'alter index "'|| o.owner || '"."' || o.object_name || '" rebuild '
|| decode(i.partition_name, NULL, '', ' PARTITION "'|| i.partition_name ||'"' )
||' online nologging;' as rebuild_list
from
(
select
owner, object_name, subobject_name
from dba_objects
where
(( object_type = 'INDEX PARTITION' and  subobject_name is not null)
OR
(object_type ='INDEX' and  subobject_name is null))
and
created > to_date('1900-01-01-00:00:00','YYYY-MM-DD-HH24:MI:SS')
) o,
(  select id.owner, id.index_name, ip.partition_name
  from
    dba_indexes  id
  where
      'NO' in (ip.logging, id.logging)
    and id.owner = ip.index_owner (+)
    and id.index_name  = ip.index_name (+)) i
where
  o.owner          = i.owner and
  o.object_name    = i.index_name and
  o.subobject_name = i.partition_name;

why query is get faster after create a index

Index is method, applied on table to access the data faster

If we do not create index on table, and we execute the query and then oracle engine do the full table scan to return the required data.

figure explanation

Branch Blocks and Leaf Blocks

A B-tree index has two types of blocks: branch blocks for searching and leaf blocks that store values. The upper-level branch blocks of a B-tree index contain index data that points to lower-level index blocks. In Figure , the root branch block has an entry 0-40, which points to the leftmost block in the next branch level. This branch block contains entries such as 0-10 and 11-19. Each of these entries points to a leaf block that contains key values that fall in the range.

Branch blocks store the minimum key prefix needed to make a branching decision between two keys. This technique enables the database to fit as much data as possible on each branch block. The branch blocks contain a pointer to the child block containing the key. The number of keys and pointers is limited by the block size.
The leaf blocks contain every indexed data value and a corresponding rowid used to locate the actual row. Each entry is sorted by (key, rowid). Within a leaf block, a key and rowid is linked to its left and right sibling entries. The leaf blocks themselves are also doubly linked. In Figure  the leftmost leaf block (0-10) is linked to the second leaf block (11-19).
 --------------

-> After create a index, Database retrieve the rows by traverse the index , instead of traverse the table. It help to to save the data input output reads and disk loads.

If sql statement only access indexed columns in select clause, then Database will load the index and start the index range scan.( it fetch the data after index scan).

example
select ename from emp where ename = 'smith';
It is assumed that the index is created on emp (ename) , and query contain only ename column in select clause of query, then database will do index scan and return desired result.

If sql statement have the others column instead of indexed columns in select clause of sql statement

then database retrive the other data with help of rowid (as u can see the figure , leaf block store rowid with each indexed item , which point the table rowid).
example
select ename,empno, mgr, sal, job from emp where ename = 'smith';
It is assumed that the index is created on emp (ename) , and query contain other columns in select clause of query, then database will do index scan and use rowid (which point to table data) and return the desired result.

alter table move command

"alter table move" command


This command is generally used to move the segments of table from one tablespace to other tablespace, so
 how to use this command:

ALTER TABLE TABLE_NAME MOVE TABLESPCE tablespace_name;

after this command indexes are become invalid, in that way we need to rebuild the indexes and at same we can change the segment of indexes
how to:

(MOVE INDEX TO TABLESPACE (NOT DOMAIN INDEX / FULL TEXT INDEXES,.,,, IOT- TOP  AND LOB ARE THESE ARE THE CUISINES OF DOMAIN INDEX))

ALTER INDEX INDEX_NAME REBUID TABLESPACE TRY;

then need to MOVE LOB SEGMENT TO NEW TABLESPACE
the table_name , and column_name information can get from the user_lobs data dictionary table:

alter table table_name move lob(column_name) store as segment_name_unique ( tablespace try);

Domain index lob segment can moved by rebuild with replace parameters 

ALTER INDEX DOMAIN_INDEX_NAME REBUILD PARAMETERS('REPLACE LEXER HYPHEN_LEXER STORAGE MYSTORE');

you may also like 

for last command follow this post::

Full Text Index / Domain Index ( create datastore, assign tablespace for storage)

Space Tuning / Reclaim space from tablespace / Freeup unused space

First of all identified most wasted space tables after this we will move the table into another tablespace. For this create a new tablespace or we can move table in a preexisting tablespace with the help of  "alter table move" command.

alter table table_name move tablespace tablespace_name;

after executing this command now we need to rebuild index (with or without tablespace clause)

alter index index_name rebuild tablespace tablespace_name;

*note table should not contain Full text index or domain index
If it contains then the scenerio is :

1. Collect create index script only for domain indexes 
2. Save it at some safe place
3. Drop domain index
4. Move table with "alter table move" command as above.
5. Rebuild indexes as above
6. And Rebuild domain indexes

If you forgot to drop domain index , No problem u might got some error as below:
ORA-02327: cannot create index on expression with datatype LOB
ORA-30967: operation directly on the Path Table is disallowed
 

SQL> alter table DR$BLB_01$I move tablespace try;
alter table DR$BLB_01$I move tablespace try
            *
ERROR at line 1:
ORA-30967: operation directly on the Path Table is disallowed


SQL> alter table DR$BLB_01$K move tablespace try;

Table altered.

SQL> alter table DR$BLB_01$N move tablespace try;

Table altered.

SQL> alter table DR$BLB_01$R move tablespace try;
alter table DR$BLB_01$R move tablespace try
            *
ERROR at line 1:
ORA-30967: operation directly on the Path Table is disallowed


SQL> alter table EMP move tablespace try;

Table altered.

and indexes
SQL> alter index SYS_IL0000074265C00002$$ rebuild tablespace try;
alter index SYS_IL0000074265C00002$$ rebuild tablespace try
*
ERROR at line 1:
ORA-02327: cannot create index on expression with datatype LOB

solution to this

alter table table_name move lob(column_name_having_blob_clob_datatype) store as a tablespace tablespace_name;


Read also::
short and brief description on "alter table move"

Finding USED UNUSED INDEX IN ORACLE

Finding USED UNUSED INDEX IN ORACLE

In Oracle by default index tracking / monitoring is off. In order to find unused used index we need to set "INDEX MONITORING" on.

command:
alter index index_name monitoring usage;

data will save in table "v$object_usage"

select * from v$object_usage;

This data dictionary table is independently store the schema data. To access this data dictionary table a schema/user does not need any extra grant or privileges.

Full Text Index / Domain Index ( create datastore, assign tablespace for storage)

Full Text Index / Domain Index ( create datastore, assign tablespace for storage)

Grant required role/Previliges to user:
GRANT EXECUTE ON CTXSYS.CTX_DDL TO myuser;

Create storage preferences and print joins (lexer - basic_lexer)

begin
    ctx_ddl.create_preference('mystore', 'BASIC_STORAGE');
    ctx_ddl.set_attribute('mystore', 'I_TABLE_CLAUSE','tablespace LOB_INDEX storage (initial 32k)');
    ctx_ddl.set_attribute('mystore', 'K_TABLE_CLAUSE','tablespace LOB_INDEX storage (initial 32k)');
    ctx_ddl.set_attribute('mystore', 'R_TABLE_CLAUSE','tablespace LOB_INDEX storage (initial 32k) lob (data) store as (disable storage in row cache)');
    ctx_ddl.set_attribute('mystore', 'N_TABLE_CLAUSE','tablespace LOB_INDEX storage (initial 32k)');
    ctx_ddl.set_attribute('mystore', 'I_INDEX_CLAUSE','tablespace LOB_INDEX storage (initial 32k) compress 2');
    ctx_ddl.set_attribute('mystore', 'P_TABLE_CLAUSE','tablespace LOB_INDEX storage (initial 32k)');
    ctx_ddl.set_attribute('mystore', 'S_TABLE_CLAUSE','tablespace LOB_INDEX storage (initial 32k)');
    ctx_ddl.create_preference('mylex', 'BASIC_LEXER');
    ctx_ddl.set_attribute('mylex', 'printjoins', '_-');
    ctx_ddl.set_attribute ( 'mylex', 'index_themes', 'NO');
    ctx_ddl.set_attribute ( 'mylex', 'index_text', 'YES'); 
end;
/
CREATE TABLE my_docs (
    id    NUMBER(10)     NOT NULL,
    name  VARCHAR2(200)  NOT NULL,
    doc   BLOB           NOT NULL
);

create index domain_idx on my_docs(doc) indextype is ctxsys.context parameters( 'lexer mylex storage mystore');
Now index data will store in tablespace "LOB_INDEX"


YOU may also like :-
basic-lexer printjoins domain indexes

Lexer -> Basic Lexer -> Printjoins in DOMAIN index

lexer  Web definitions

Fancy term for a tokener..

Lexer Types

Use the lexer preference to specify the language of the text to be indexed. To create a lexer preference, you must use one of the following lexer types:

Type     Description
BASIC_LEXER                     Lexer for extracting tokens from text in languages, such as English and most western European languages that use white space delimited words.
MULTI_LEXER                    Lexer for indexing tables containing documents of different languages
CHINESE_VGRAM_LEXER     Lexer for extracting tokens from Chinese text.
CHINESE_LEXER                 Lexer for extracting tokens from Chinese text.
JAPANESE_VGRAM_LEXER   Lexer for extracting tokens from Japanese text.
JAPANESE_LEXER               Lexer for extracting tokens from Japanese text.
KOREAN_MORPH_LEXER     Lexer for extracting tokens from Korean text.
USER_LEXER                      Lexer you create to index a particular language.
WORLD_LEXER                   Lexer for indexing tables containing documents of different languages; autodetects languages in a document


BASIC_LEXER

Use the BASIC_LEXER type to identify tokens for creating Text indexes for English and all other
supported whitespace-delimited languages.

The BASIC_LEXER also enables base-letter conversion, composite word indexing, case-sensitive indexing
 and alternate spelling for whitespace-delimited languages that have extended character sets.

In English and French, you can use the BASIC_LEXER to enable theme indexing.

Note:
Any processing the lexer does to tokens before indexing (for example, removal of characters,
 and base-letter conversion) are also performed on query terms at query time. This ensures
 that the query terms match the form of the tokens in the Text index.

BASIC_LEXER supports any database character set.

BASIC_LEXER attribute printjoins:
    Specify the non alphanumeric characters that, when they appear anywhere in a word (beginning, middle, or end), are processed as alphanumeric and included with the token in the Text index. This includes printjoins that occur consecutively.

    For example, if the hyphen '-' and underscore '_' characters are defined as printjoins, terms such as pseudo-intellectual and _file_ are stored in the Text index as pseudo-intellectual and _file_.
    
BASIC_LEXER ExampleThe following example sets printjoin characters and disables theme indexing with the BASIC_LEXER:

begin
ctx_ddl.create_preference('mylex', 'BASIC_LEXER');
ctx_ddl.set_attribute('mylex', 'printjoins', '_-');
ctx_ddl.set_attribute ( 'mylex', 'index_themes', 'NO');
ctx_ddl.set_attribute ( 'mylex', 'index_text', 'YES');
end;

To create the index with no theme indexing and with printjoins characters set as described, issue the following statement:

create index myindex on mytable ( docs ) indextype is ctxsys.context parameters ( 'LEXER mylex' );

domain index is marked LOADING/FAILED/UNUSABLE

domain index is marked LOADING/FAILED/UNUSABLE

Replicate the error
CREATE TABLE my_docs (
   id    NUMBER(10)     NOT NULL,
   name  VARCHAR2(200)  NOT NULL,
   doc   BLOB           NOT NULL
 );


 ALTER TABLE my_docs ADD (
   CONSTRAINT my_docs_pk PRIMARY KEY (id)
 );


 CREATE SEQUENCE my_docs_seq;


 CREATE OR REPLACE DIRECTORY documents AS 'C:\work';


 CREATE OR REPLACE PROCEDURE load_file_to_my_docs (p_file_name  IN  my_docs.name%TYPE) AS
   v_bfile      BFILE;
   v_blob       BLOB;
 BEGIN
   INSERT INTO my_docs (id, name, doc)
   VALUES (my_docs_seq.NEXTVAL, p_file_name, empty_blob())
   RETURN doc INTO v_blob;

   v_bfile := BFILENAME('DOCUMENTS', p_file_name);
   Dbms_Lob.Fileopen(v_bfile, Dbms_Lob.File_Readonly);
   Dbms_Lob.Loadfromfile(v_blob, v_bfile, Dbms_Lob.Getlength(v_bfile));
   Dbms_Lob.Fileclose(v_bfile);

   COMMIT;
 END;
 /



create index idx_domain on my_docs(doc) indextype is ctxsys.context parameters ('sync (on commit)');

Now Rebuild the index "idx_domain" to replicate the error , on same time i execute below command and it throws error:

SQL>  EXEC load_file_to_my_docs('try.pdf');
BEGIN load_file_to_my_docs('try.pdf'); END;

*
ERROR at line 1:
ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE
ORA-06512: at "G.LOAD_FILE_TO_MY_DOCS", line 5
ORA-06512: at line 1

Solution::
Rebuilds Domain index/ full text index as below:::
 
ALTER INDEX your_index REBUILD ONLINE PARAMETERS ('REPLACE LEXER your_lexer');

Duplicate Index / Redundant Index Oracle

what is duplicate index ?
This is when table has multiple indexes defined on the same columns. The indexes may have with different names.
for example :
first index is created on columns : index1(a,b,c)
second index is created on columns : index2(a,b)
so as above "a" and "b" columns are mutual of each other and place in same order, therefore second index is duplicate of first:
note: index3(a,b) and index4(b,a), both have different definition.

How to identify duplicate index (oracle):
source::::
http://www.dba-oracle.com/t_detecting_duplicate_indexes.htm
select /*+ rule */
   a.table_owner,
   a.table_name,
   a.index_owner,
   a.index_name,
   column_name_list,
   column_name_list_dup,
   dup duplicate_indexes,
   i.uniqueness,
   i.partitioned,
   i.leaf_blocks,
   i.distinct_keys,
   i.num_rows,
   i.clustering_factor
from
  (
   select
      table_owner,
      table_name,
      index_owner,
      index_name,
      column_name_list_dup,
      dup,
      max(dup) OVER
       (partition by table_owner, table_name, index_name) dup_mx
   from
      (
       select
          table_owner,
          table_name,
          index_owner,
          index_name,
          substr(SYS_CONNECT_BY_PATH(column_name, ','),2) 
          column_name_list_dup,
          dup
       from
          (
          select
            index_owner,
            index_name,
            table_owner,
            table_name,
            column_name,
            count(1) OVER
             (partition by
                 index_owner,
                 index_name) cnt,
             ROW_NUMBER () OVER
               (partition by
                  index_owner,
                  index_name
                order by column_position) as seq,
             count(1) OVER
               (partition by
                  table_owner,
                  table_name,
                  column_name,
                  column_position) as dup
   from
      sys.dba_ind_columns
   where
      index_owner not in ('SYS', 'SYSTEM'))
where
   dup!=1
start with seq=1
connect by prior seq+1=seq
and prior index_owner=index_owner
and prior index_name=index_name
)) a,
(
select
   table_owner,
   table_name,
   index_owner,
   index_name,
   substr(SYS_CONNECT_BY_PATH(column_name, ','),2) column_name_list
from
(
select index_owner, index_name, table_owner, table_name, column_name,
count(1) OVER ( partition by index_owner, index_name) cnt,
ROW_NUMBER () OVER ( partition by index_owner, index_name order by column_position) as seq
from sys.dba_ind_columns
where index_owner not in ('SYS', 'SYSTEM'))
where seq=cnt
start with seq=1
connect by prior seq+1=seq
and prior index_owner=index_owner
and prior index_name=index_name
) b, dba_indexes i
where
    a.dup=a.dup_mx
and a.index_owner=b.index_owner
and a.index_name=b.index_name
and a.index_owner=i.owner
and a.index_name=i.index_name
order by
   a.table_owner, a.table_name, column_name_list_dup;
  
  
  
  
  
For a Particular Schema:
  
  
select /*+ rule */
 a.table_name, a.index_name, column_name_list, column_name_list_dup, dup duplicate_indexes,
 i.uniqueness, i.partitioned, i.leaf_blocks, i.distinct_keys, i.num_rows, i.clustering_factor
from
  (select
  table_name, index_name,
  column_name_list_dup, dup,
  max(dup) OVER (partition by table_name, index_name) dup_mx
   from
      (select
   table_name, index_name,
   substr(SYS_CONNECT_BY_PATH(column_name, ','),2)  column_name_list_dup, dup
       from
   (select
    index_name, table_name, column_name,
    count(1) OVER (partition by index_name) cnt,
    ROW_NUMBER () OVER (partition by index_name order by column_position) as seq,
    count(1) OVER (partition by table_name, column_name, column_position) as dup
   from
    user_ind_columns
   )
  where
   dup!=1
   start with seq=1
   connect by prior seq+1=seq
   and prior index_name=index_name
  )) a,
(
select
   table_name, index_name,
   substr(SYS_CONNECT_BY_PATH(column_name, ','),2) column_name_list
from
 ( select index_name, table_name, column_name,
  count(1) OVER ( partition by index_name) cnt,
  ROW_NUMBER () OVER ( partition by index_name order by column_position) as seq
  from user_ind_columns
 )
where seq=cnt
start with seq=1
connect by prior seq+1=seq
and prior index_name=index_name
) b, user_indexes i
where
    a.dup=a.dup_mx
and a.index_name=b.index_name
and a.index_name=i.index_name
order by
   a.table_name, column_name_list_dup;  

Finding UNUSED indexes in oracle

 First we need to start monitoring for indexes:

SQL> ALTER INDEX index_name MONITORING USAGE;
 Index altered.
 
SQL> ALTER INDEX index_name1 MONITORING USAGE;
 Index altered.

Execute the below query:

SQL> SELECT v.index_name, v.table_name,
 v.monitoring, v.used,
 start_monitoring, end_monitoring
 FROM v$object_usage v, user_indexes u
 WHERE v.index_name = u.index_name;


INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
ADD_ID_PK                      ADDRESS                        YES NO  01/26/2013 17:06:15
 

Full Text Index error

SQL> create table t ( a nvarchar2(2000));

Table created.

SQL>
SQL>
SQL> create index idx on t(a) indextype is ctxsys.context;
create index idx on t(a) indextype is ctxsys.context
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10509: invalid text column: A
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364


solution
Change datatype of column to 'blob' or 'clob'
then try again