Oracle in World: Indexes

Showing posts with label Indexes. Show all posts

Thursday, September 25, 2008

ORA-01450: maximum key length (3215) exceeded

Error Description
Whenever I try to rebuild index online then it fails with message ORA-00604: error occurred at recursive SQL level 1 along with ORA-01450: maximum key length (3215) exceeded. Below is the scenario.
SQL> create table tab1(a varchar2(3000),b varchar2(2000));
Table created.

SQL> create index tab1_I on tab1(a,b);
Index created.

SQL> alter index tab1_I rebuild online;
alter index tab1_I rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded

Let's now create one table with column length 3000+199=3199 bytes and see what happens.
SQL> create table tab3(a varchar2(3000),b varchar2(199));
Table created.

SQL> create index tab3_I on tab3(a,b);
Index created.

Try to rebuild it online and it works.
SQL> alter index tab3_I rebuild online;
Index altered.

Now just add extra 1 bytes on column b. And whenever we try to rebuild it online it will fail.

SQL> alter table tab3 modify b varchar2(200);
Table altered.

SQL> alter index tab3_I rebuild online;
alter index tab3_I rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded

Cause of the Problem
When creating a index the total length of the index cannot exceed a certain value. Primarily this value depends on DB_BLOCK_SIZE.
If 2K block size then maximum index key length=758
If 4K block size then maximum index key length=1578
If 8K block size then maximum index key length=3218
If 16K block size then maximum index key length=6498
How the maximum index key length is measured by?
Maximum index key length=Total index length (Sum of width of all indexed column+the number of indexed columns)+Length of the key(2 bytes)+ROWID(6 bytes)+the length of the rowid(1 byte)

The index key size is limited by the value of db_block_size, because a key value may not span multiple blocks. So, based on the size of the block size of index depends. In fact, it is required that any index block must contain at least TWO index entries per block.

So we can say that the maximum key length for an index will be less than half of
the DB_BLOCK_SIZE. But we know that in a block there also needed space for PCTFREE, INITRANS and space for block overhead(Block Header,ROW Directory, Table Directory, etc). After considering these bytes the actual space that can be used for the Index key is actually just over 1/3 of the DB_BLOCK_SIZE.

Solution of the Problem
The causes already indicates what might be the solutions. Solution may be,
1)Increase your database block size. Create a tablespace with bigger block size and create index on that tablespace.

2)If you have index on multiple columns then you can split index to single or 2 columns so that size does not extended over it can handle.

3)Rebuild the index without online clause. That is
ALTER INDEX index_name REBUILD;
Because The online rebuild of the index creates a journal table and index. This internal journal IOT table contains more columns in its index. This is a feature of online rebuild. This time the error arises because that current value of the initialization parameter db_block_size is not large enough to create internal journal IOT.

Monday, June 23, 2008

Examples of Usage of Composite Index

If I create an index on column (a,b,c) then
a, ab, abc combination of columns in where clause will use the index.
bc, b, and c combinations of columns in where clause will not use the index.

The example is given below.

SQL> create table comp_tab(a number, b varchar2(10), c varchar2(10));
Table created.

SQL> begin
for i in 1 .. 10000
loop
insert into comp_tab values(i,'pc-'||round(dbms_random.value(1,20000),0),'pc-'||round(dbms_random.value(1,20000),0));
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> create index comp_tab_I on comp_tab(a,b,c);
Index created.

SQL> analyze table comp_tab estimate statistics;
Table analyzed.

SQL> set autot trace

a in where cluase will use the index as a is leading portion of index.
SQL> select a,c,b from comp_tab where a=565;
Execution Plan
----------------------------------------------------------
Plan hash value: 753992557
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| COMP_TAB_I | 1 | 19 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------

ac combination will use the index.
SQL> select a,c,b from comp_tab where a=565 and c='pc-3722';
Execution Plan
----------------------------------------------------------
Plan hash value: 753992557
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| COMP_TAB_I | 1 | 19 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------

bc combination is used in where clause so will not use index.
SQL> select b,c,a from comp_tab where b='pc-5895' and c='pc-2893';
Execution Plan
----------------------------------------------------------
Plan hash value: 3767570742
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 11 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| COMP_TAB | 1 | 19 | 11 (0)| 00:00:01 |
------------------------------------------------------------------------------

Only b combination is used so will not use the index.
SQL> select a,c,b from comp_tab where b='pc-6735';
Execution Plan
----------------------------------------------------------
Plan hash value: 3767570742
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 11 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| COMP_TAB | 1 | 19 | 11 (0)| 00:00:01 |
------------------------------------------------------------------------------

abc combination is used in where clause and so will use the index.
SQL> select b,c,a from comp_tab where b='pc-5895' and c='pc-2893' and a=564;
Execution Plan
----------------------------------------------------------
Plan hash value: 753992557
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| COMP_TAB_I | 1 | 19 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------

ab combination in where clause will use the index.
SQL> select c from comp_tab where b='pc-5895' and a=564;
Execution Plan
----------------------------------------------------------
Plan hash value: 753992557
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| COMP_TAB_I | 1 | 19 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Related Documents
http://arjudba.blogspot.com/2009/12/oracle-object-type-exercises-varray.html
http://arjudba.blogspot.com/2009/12/practice-oracle-joins-examples.html
http://arjudba.blogspot.com/2009/12/oracle-security-practices.html
http://arjudba.blogspot.com/2009/12/exercises-with-oracle-create-table-add.html
http://arjudba.blogspot.com/2009/12/oracle-database-creation-exercises.html
http://arjudba.blogspot.com/2009/12/basic-oracle-sql-exercise.html
http://arjudba.blogspot.com/2009/08/format-model-modifiers-fx-and-fm.html
http://arjudba.blogspot.com/2009/08/number-format-models-in-oracle.html
http://arjudba.blogspot.com/2009/08/format-models-in-oracle.html
http://arjudba.blogspot.com/2009/07/sql-decode.html
http://arjudba.blogspot.com/2009/07/how-to-know-row-of-table-belong-to.html
http://arjudba.blogspot.com/2009/06/how-to-know-which-objects-are-being.html
http://arjudba.blogspot.com/2009/06/ddl-with-wait-option-in-11g.html
http://arjudba.blogspot.com/2009/06/ora-00939-too-many-arguments-when-case.html
http://arjudba.blogspot.com/2009/03/oracle-datatype-internal-code.html
http://arjudba.blogspot.com/2009/03/how-to-know-list-of-constraints-and.html
http://arjudba.blogspot.com/2009/02/how-to-know-dependent-objectswhich.html
http://arjudba.blogspot.com/2009/02/how-to-search-stringkey-value-from.html
http://arjudba.blogspot.com/2009/02/how-to-know-when-tableobjects-ddlcode.html
http://arjudba.blogspot.com/2009/02/ora-00920-invalid-relational-operator.html
http://arjudba.blogspot.com/2009/01/adding-default-value-to-column-on-table.html
http://arjudba.blogspot.com/2009/01/ora-12838-cannot-readmodify-object.html
http://arjudba.blogspot.com/2009/01/ora-01779-cannot-modify-column-which.html
http://arjudba.blogspot.com/2009/01/updating-table-based-on-another-table.html
http://arjudba.blogspot.com/2009/01/ora-00054-resource-busy-and-acquire.html
http://arjudba.blogspot.com/2008/12/troubleshoot-ora-02292-ora-02449-and.html
http://arjudba.blogspot.com/2008/06/ora-00903-oracle-database-reserved.html
http://arjudba.blogspot.com/2008/06/hints-in-oracle.html
http://arjudba.blogspot.com/2008/06/examples-of-usage-of-composite-index.html
http://arjudba.blogspot.com/2008/06/find-indexes-and-assigned-columns-for.html
http://arjudba.blogspot.com/2008/06/reasons-for-using-alter-table-statement.html
http://arjudba.blogspot.com/2008/06/alter-table-rename-table-add-column.html
http://arjudba.blogspot.com/2008/06/ora-01830-date-format-picture-ends.html
http://arjudba.blogspot.com/2008/06/default-date-timestamp-and-timestamp.html
http://arjudba.blogspot.com/2008/06/create-temporary-table-in-oracle.html
http://arjudba.blogspot.com/2008/06/example-of-antijoin-semijoin-curtesian.html
http://arjudba.blogspot.com/2008/12/ora-02297-cannot-disable-constraint.html
http://arjudba.blogspot.com/2008/10/convert-decimal-to-hexadecimal-on.html
http://arjudba.blogspot.com/2008/10/how-to-generate-fibonacci-series-in.html
http://arjudba.blogspot.com/2008/10/same-sounded-words-in-oracle.html
http://arjudba.blogspot.com/2008/09/type-of-constraint-in-oracle.html
http://arjudba.blogspot.com/2008/09/how-to-move-lob-data-to-another.html
http://arjudba.blogspot.com/2008/08/subqueries-in-oracle-with-example.html
http://arjudba.blogspot.com/2008/08/how-to-monitor-alert-log-file-in-oracle.html
http://arjudba.blogspot.com/2008/08/solution-of-ora-01873-leading-precision.html
http://arjudba.blogspot.com/2008/07/literals-and-literal-types-in-oracle.html
http://arjudba.blogspot.com/2008/07/ora-01722-invalid-number.html
http://arjudba.blogspot.com/2008/07/ora-00936-missing-expression.html
http://arjudba.blogspot.com/2008/07/ora-01756-quoted-string-not-properly.html
http://arjudba.blogspot.com/2008/07/pls-00428-into-clause-is-expected-in.html
http://arjudba.blogspot.com/2008/07/schema-object-naming-rules.html
http://arjudba.blogspot.com/2008/06/datetime-and-interval-datatypes.html
http://arjudba.blogspot.com/2008/06/large-object-lob-datatypes-with-example.html
http://arjudba.blogspot.com/2008/06/history-of-sql.html
http://arjudba.blogspot.com/2008/06/what-is-sql.html

Choice to create composite index

Whenever you create index on multiple columns in a table then that index is called composite index.

Advantage of Using Composite Index:
----------------------------------------------------------------
Improved selectivity: As we all know index is created for the column that has higher selectivity means there are a few duplicate column values of the indexing columns. Sometimes two or more columns each with poor selectivity, can be combined to form a composite index with higher selectivity.

Reduced I/O: If composite index is created with all the columns selected by the query, then oracle can return the values from the index itself instead of accessing values from the table using ROWID scan unlike single key index.

To know more about ROWID scan and explain plan search within my blog.

Composite Key Usage in a Query
----------------------------------------------------------
It is important to maintain order of the index while creating composite key index. For example create index on column(x,y) is not same as of create index on column(y,x). So while creating composite index take special care of the order of the columns inside index. More specifically, a SQL statement can use an access path involving a composite index if the statement contains constructs that use a leading portion of the index. Order of columns inside index creation make the leading constructs.

An example will make you clear.
If I create index as

CREATE INDEX comp_indx ON tabl(a, b, c);
Then,
a, ab, and abc combinations of columns in where clause will use the index.
bc, b, and c combinations of columns in where clause will not use the composite index.

Th ordering of keys of composite index is given below.

Ordering Keys for Composite Indexes
-----------------------------------------------------------
•Create the composite index so the keys used in WHERE clauses make up a leading portion.

•If some keys are used in WHERE clauses more frequently, then while creating composite index make sure that the more frequently selected keys make up a leading portion to allow the statements that use only these keys to use the index.

•If all keys are used in the WHERE clauses equally often but the data is physically ordered on one of the keys, then place that key first in the composite index.

•The example is given in Examples of Usage of Composite Index

Choosing Keys for Composite Indexes
----------------------------------------------------------------------
•Consider to create of composite index on columns that is used frequently in WHERE clause condition combined with AND operation.

•If several queries select a combination of columns based on one or more columns then consider to create composite index based on these columns.

Related Documents:
---------------------------------------------
Examples of Usage of Composite Index

Sunday, June 22, 2008

Index Creation and Maintenance Issue in Oracle

•Drop the index that is not used by the application. Though as you may think that query optimizer select the best execution plan and thus avoid non selective index while execute query, so if I keep index and if I don't drop it what is the problem. The problem is before generating execution plan the SQL engine must continue to maintain all indexes defined against a table, regardless of whether they are used or not.

•One way to know whether application use index against table is issuing ALTER INDEX ... monitoring usage and then query from V$OBJECT_USAGE and look at USED column. The following section is an example to see whether index table_16K_I is used or not.

SQL> alter index table_16K_I monitoring usage;
Index altered.

SQL> select START_MONITORING,USED,MONITORING, INDEX_NAME from V$OBJECT_USAGE where table_name='TABLE_16K';
START_MONITORING USE MON INDEX_NAME
------------------- --- --- ------------------------------
06/23/2008 01:22:28 NO YES TABLE_16K_I

•If you create one index for a table then it may affect the execution plan of another sql statement. So after you create one index run SQL trace facility and reexamine application performance.

•You can use SQL Access advisor which automatically suggests you whether to create index on which column of the table based on the application current workload, or hypothetical workload or a user defined set of SQL statements, or a SQL Tuning set. You can easily run SQLAccess advisor from enterprise manager. To run it
Go to Enterprise Manager Home Page> Related Links> Advisor Central> SQL Access Advisor and then proceed as you wish.

Monday, June 16, 2008

What and when oracle uses Rowid Scans in execution plans

What is Rowid Scans?
-------------------------------------------
•The rowid of a row specifies the datafile and data block containing the row and the location of the row in that block. Locating a row by specifying its rowid is the fastest way to retrieve a single row, because the exact location of the row in the database is specified.

•To access a table by rowid, Oracle first obtains the rowids of the selected rows, either from the statement's WHERE clause or through an index scan of one or more of the table's indexes. Oracle then locates each selected row in the table based on its rowid.

When the Optimizer Uses Rowids?
------------------------------------------------------------
Suppose I made an index on table test_tab column col1. The table has two columns col1 and col2. Now if in my query I use to select both columns like I use SELECT * FROM TEST_TAB WHERE COL1=1; then optimizer at first step retrieve the rowid from index on column col1 and then in the second step optimizer look for row using that rowid.

Access by rowid does not need to follow every index scan. If the index contains all the columns needed for the statement, then table access by rowid might not occur.

An example will make you clear.
SQL> create table test_tab as select level col1, level col2 from dual connect by level<=1000;
Table created.

SQL> set autot trace
SQL> create index test_tab_I on test_tab(col1);
Index created.

Here, I select col1 , the only column by which I created index test_tab_I. So, to select only col1 no need to rowid scan.

SQL> select col1 from test_tab where col1=99;

Execution Plan
----------------------------------------------------------
Plan hash value: 933728095

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| TEST_TAB_I | 1 | 13 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("COL1"=99)

Note
-----
- dynamic sampling used for this statement

Here I select both col1 and col2. col2 is not inside test_tab_I index. So it used index rowid scan to find the col2 field.
SQL> select * from test_tab where col1=99;

Execution Plan
----------------------------------------------------------
Plan hash value: 3125486718

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 1 | 26 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_TAB_I | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("COL1"=99)

Note
-----
- dynamic sampling used for this statement

Note that in both cases dynamic sampling are used. Because I have not gather statistics. If I generate statistics then note will disappear.

SQL> analyze table test_tab estimate statistics;
Table analyzed.

SQL> select * from test_tab where col1=99;

Execution Plan
----------------------------------------------------------
Plan hash value: 3125486718

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 1 | 6 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_TAB_I | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("COL1"=99)

Tuesday, June 10, 2008

Find indexes and assigned columns for a table

Sunday, June 8, 2008

Use of Hint to use Index.

1.Create Two Tables.
-----------------------
create table table_a(n number ,k varchar2(15));
create table table_b(k varchar2(15), b number);
/

2.Create Index.
------------------------
create index table_a_I_K on table_a(k);
create index table_b_I_K on table_b(K);
/

3.Insert Random values in the Table.
--------------------------------------------
In table_a at column n I inserted 1 to 10000 values and in column k I inserted strings contains 'pc-' + random integer between 1 to 20000.

begin
for i in 1 .. 10000
loop
insert into table_a values(i,'pc-'||round(dbms_random.value(1,20000),0));
end loop;
end;
/

In table_b I inserted 100000 rows. In column b I inserted 1 to 100000 values and in column k I inserted strings contained 'pc-' + random integer between 1 to 40000.

begin
for i in 1 .. 100000
loop
insert into table_b(b,k) values(i,'pc-'||round(dbms_random.value(1,40000),0));
end loop;
end;
/

4.Enable Tracing to see whether index used or not.

SQL>SET AUTOT TRACE

5. See explain plan.

SQL>select a.n, b.b from table_a a , table_b b where a.k=b.k;
24981 rows selected.

Execution Plan
----------------------------------------------------------

Plan hash value: 1725158669

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 29970 | 1287K| 79 (3)| 00:00:01 |
|* 1 | HASH JOIN | | 29970 | 1287K| 79 (3)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TABLE_A | 10000 | 214K| 8 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TABLE_B | 84053 | 1805K| 70 (2)| 00:00:01 |

Why my query did not used indexes? Because query optimizer got a explain plan in which full table scan takes less time than index scan.
However we can force to use index. Then we have to use Index hint. Like, Index(table_name) entry within /* and */.

SQL> set timi on
SQL> select /*+INDEX (b)*/ a.n, b.b from table_a a , table_b b where a.k=b.k;

24981 rows selected.

Elapsed: 00:00:00.55

Execution Plan
----------------------------------------------------------
Plan hash value: 1071190786

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 29970 | 1287K| 1181 (1)| 00:00:15 |
|* 1 | HASH JOIN | | 29970 | 1287K| 1181 (1)| 00:00:15 |
| 2 | TABLE ACCESS FULL | TABLE_A | 10000 | 214K| 8 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| TABLE_B | 84053 | 1805K| 1172 (1)| 00:00:15 |
| 4 | INDEX FULL SCAN | TABLE_B_I_K | 84053 | | 371 (0)| 00:00:05 |
--------------------------------------------------------------------------------------------

Related Documentation:
--------------------------
When you will make index and when not

Saturday, May 31, 2008

How does one prevent Oracle from using an Index?

Autotrace Option:
------------------------
SET AUTOTRACE OFF: No AUTOTRACE report is generated. This is the default.
SET AUTOTRACE ON EXPLAIN: The AUTOTRACE report shows only the optimizer execution path.
SET AUTOTRACE ON STATISTICS: The AUTOTRACE report shows only the SQL statement execution statistics.
SET AUTOTRACE ON: The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.
SET AUTOTRACE TRACEONLY: Like SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any. If STATISTICS is enabled, query data is still fetched, but not printed.

Now look explain plan of a query which will use index.
SQL> SELECT COUNT(*) FROM TEST WHERE A=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2811351645

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX UNIQUE SCAN| SYS_C005994 | 1 | 3 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------

Now we can prevent oracle from using index in several methods.

Method 1:Adding an expression to the indexed column:
------------------------------------------------------------------------
SQL> SELECT COUNT(*) FROM TEST WHERE A+0=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 160014765

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX FULL SCAN| SYS_C005994 | 1 | 3 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------

Method 2:Specifying the FULL hint to force full table scan:
--------------------------------------------------------------------------------------
SQL> select /*+ FULL(TEST)*/ * from TEST WHERE A=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 8 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

Method 3:Specifying NO_INDEX hint
------------------------------------------------------------------
SQL> select /*+ NO_INDEX(TEST) */ count(*) from test where A=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| TEST | 1 | 3 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

Method 4:Using a function over the indexed column
-------------------------------------------------------------------
SQL> select count(*) from test where to_number(A)=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 160014765

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX FULL SCAN| SYS_C005994 | 1 | 3 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------

Saturday, April 19, 2008

Troubleshoot unusable Index in Oracle

If a procedural object, such as a stored PL/SQL function or a view, becomes invalid, the DBA does not necessarily have to do anything: the first time it is accessed, Oracle will attempt to recompile it, and this may well succeed.

But if an index becomes unusable for any reason, it must always be repaired explicitly before it can be used.

This is because an index consists of the index key values, sorted into order, each with the relevant
rowid. The rowid is the physical pointer to the location of the row to which the index key refers.

If the rowids of the table are changed, then the index will be marked as unusable. This could occur for a number of reasons. Perhaps the most common is that the table has been moved, with the ALTER TABLE...MOVE command. This will change the physical placement of all the rows, and therefore the index entries will be pointing to the wrong place. Oracle will be aware of this and will therefore not permit use of the index.

Identifying Unusable Indexes
---------------------------------
In earlier releases of the Oracle database, when executing SQL statements, if the session attempted to use an unusable index it would immediately return an error, and the statement would fail. Release 10g of the database changes this behavior. If a statement attempts to use an unusable index, the statement will revert to an execution plan that does not require the index.

Thus, statements will always succeed—but perhaps at the cost of greatly reduced performance. This behavior is controlled by the instance parameter SKIP_UNUSABLE_INDEXES, which defaults to TRUE. The exception to this is if the index is necessary to enforce a constraint: if the index on a primary key column becomes unusable, the table will be locked for DML.

To detect indexes which have become unusable, query the DBA_INDEXES view:
SQL> select owner, index_name from dba_indexes where status='UNUSABLE';

Repairing Unusable Indexes
----------------------------------------

To repair the index, it must be re-created with the ALTER INDEX...REBUILD command.

This will make a pass through the table, generating a new index with correct rowid pointers
for each index key. When the new index is completed, the original unusable index is dropped.

The syntax of the REBUILD command has several options. The more important ones are TABLESPACE, ONLINE, and NOLOGGING. By default, the index will be rebuilt within its current tablespace, but by specifying a table space with the TABLESPACE keyword, it can be moved to a different one.

Also by default, during the course of the rebuild the table will be locked for DML. This can be avoided by using the ONLINE keyword.

1)Create Table and insert row in it:
----------------------------------------
SQL> create table test ( a number primary key);
Table created.

SQL> insert into test values(1);
1 row created.

SQL> commit;
Commit complete.

2)Check the Index Status
--------------------------
SQL> select index_name, status from user_indexes;
INDEX_NAME STATUS
------------------------------ --------
SYS_C0044513 VALID

3)Move the Table and Check Status:
------------------------------------
SQL> alter table test move;
Table altered.

SQL> select index_name, status from user_indexes;
INDEX_NAME STATUS
------------------------------ --------
SYS_C0044513 UNUSABLE

4)Rebuild The Index:
-----------------------
SQL> alter index SYS_C0044514 rebuild online;
Index altered.

SQL> select index_name, status from user_indexes;
INDEX_NAME STATUS
------------------------------ --------
SYS_C0044514 VALID