19 March, 2010

ALTER INDEX indexname REBUILD.

What does an ALTER INDEX indexname REBUILD do ? When would it be used ?


See this test case :

These are my two tables and their indexes :

QL> select table_name, blocks, num_rows,sample_size from user_tables
2 where table_name like 'STORES_LIST%';

TABLE_NAME BLOCKS NUM_ROWS SAMPLE_SIZE
------------------------------ ---------- ---------- -----------
STORES_LIST 4930 405884 405884
STORES_LIST_2 4930 405884 405884

SQL> select table_name, index_name, num_rows, leaf_blocks from user_indexes
2 where table_name like 'STORES_LIST%';

TABLE_NAME INDEX_NAME NUM_ROWS LEAF_BLOCKS
------------------------------ ------------------------------ ---------- -----------
STORES_LIST STORES_LIST_CNTRY_STT_NDX 405884 1709
STORES_LIST_2 STORES_LIST_2_CNTRY_STT_NDX 405884 1709

SQL>


STORES_LIST is a list of Stores. STORES_LIST_2 is a replica of the table.
The Index that is present is on COUNTRY+STATE.

You can see that both the tables are of exactly the same size.
The Indexes on the two tables are also of the same size.


What happens when I REBUILD the two indexes ?

SQL> alter session set sql_trace=TRUE;

Session altered.

SQL> alter index stores_list_cntry_stt_ndx rebuild;

Index altered.

SQL> alter index stores_list_2_cntry_stt_ndx rebuild;

Index altered.

SQL>


A tkprof on the trace file shows :


alter index stores_list_cntry_stt_ndx rebuild


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.05 0 10 0 0
Execute 1 2.42 2.72 1 1789 2818 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 2.44 2.77 1 1799 2818 0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 64



alter index stores_list_2_cntry_stt_ndx rebuild


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.03 0 1 0 0
Execute 1 2.22 2.63 1 4924 2817 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 2.26 2.67 1 4925 2817 0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 64

Why did the rebuild of stores_list_2_cntry_stt_ndx have to read so many more blocks (4,924 versus 1,789 in "consistent get" mode)?





..........

The answer is in the fact that there is a difference between the two REBUILDs.





..........


What I hadn't presented was this done just before the REBUILD :

SQL> alter index stores_list_2_cntry_stt_ndx unusable;

Index altered.

SQL> select table_name,index_name, status from user_indexes
where table_name like 'STORES_LIST%';

TABLE_NAME INDEX_NAME STATUS
------------------------------ ------------------------------ --------
STORES_LIST STORES_LIST_CNTRY_STT_NDX VALID
STORES_LIST_2 STORES_LIST_2_CNTRY_STT_NDX UNUSABLE

SQL>


Index STORES_LIST_2_CNTRY_STT_NDX had actually been marked UNUSABLE. A REBUILD of this index was executed by reading the *table* in it's entirety.
On the other hand, a REBUILD of STORES_LIST_CNTRY_STT_NDX was executed by re-reading only the Index. It was still valid and usable.

Thus, I have presented a case where an ALTER INDEX indexname REBUILD may either only read the index (STORES_LIST_CNTRY_STT_NDX) which can be very fast or reads the whole table again (STORES_LIST_2_CNTRL_STT_NDX).

The difference is in the fact that the latter case was working with an Index that had been marked UNUSABLE. An UNUSABLE index is not updated when DML occurs against the table. Therefore, Oracle cannot and does not guarantee that the Index has all the table rows captured with their corresponding Key Values. A Rebuild of such an Index can only be executed by re-reading the Table in it's entirety.
On the other hand a REBUILD of a VALID Index (STORES_LIST_CNTRY_STT_NDX, the index on the first table) can read the index alone (locking the table to prevent rows being changed while the index is being read, unless the "ONLINE" keyword is added to the ALTER INDEX ... command). It is assured that the Index, being VALID, is consistent with the table.


When and why would an Index (or a Partition of a Partitioned Index) be marked UNUSABLE ?
1. If it is LOCAL (Partitioned) Index on a Partitioned Table and DDL (e.g an ALTER TABLE ... PARTITON ... command) is executed against the Table.

2. If it is a GLOBAL (non-Partitioned) Index on a Partitioned Table and DDL (e.g. an ALTER TABLE ... PARTITION ... command) is executed against the Table.

3. If it (whether an Index or an Index Partition) is explicitly set to UNUSABLE -- which may be done a in DataWarehouse prior to loading a large batch of rows, with the proviso the the Index / Index Partition is rebuilt manually or by a script at the end of the data load.

4. If a "DIRECT=true" (i.e. direct path load) SQLLoader sesssion with the option "SKIP_INDEX_MAINTENANCE=true" is executed to insert rows into the table. This may be done when loading a very large batch of rows -- like the previous method, something that may be used in aDataWarehouse.

In all of such cases, the Index or Index Partition is rebuilt with the "ALTER INDEX indexname PARTITION [partitionname] rebuild" which sets the Index / Index Partition status back from UNUSABLE to VALID.
.
.
.

3 comments:

Unknown said...

Very good analysis in terms of blocks read with respect to the indexes.Good post.

Vijay said...

Hi,
I have a table in my application that has the following structure




Create table EVENT (project_id number, DISC_CMPLNT_TX clob, MAINT_ACT_TX CLOB,LGBK_CMT_TX VARCHAR2(1000 CHAR))
PARTITION BY RANGE (project_id)
(PARTITION pmax VALUES LESS THAN (MAXVALUE));

I have created the below preference on the three columns of the table.

begin
ctx_ddl.create_preference ('skipJoinLexer', 'BASIC_LEXER');
ctx_ddl.set_attribute ('skipJoinLexer', 'skipjoins', ';:.,()<>*"''#-');
ctx_ddl.create_preference ('logbookTextDS', 'MULTI_COLUMN_DATASTORE');
ctx_ddl.set_attribute ('logbookTextDS', 'COLUMNS', 'DISC_CMPLNT_TX, MAINT_ACT_TX,LGBK_CMT_TX');

end;

I have created a local partitioned index with the parameter NOPOPULATE
create index lb_text_idx on ac_maint (DISC_CMPLNT_TX) indextype is ctxsys.context local parameters ('lexer skipJoinLexer datastore logbookTextDS NOPOPULATE');


When an new value of project_id is inserted into table1 , I create a partition using the following command where prjId is the new value of the project_id
ALTER TABLE EVENT split PARTITION pmax AT ('||prjId||') INTO ( PARTITION p_' || prjId || ', PARTITION pmax)

Now I run a huge data load into the EVENT table for a project Id say 1 and insert millions of records.

I want to rebuild the lb_text_idx for this partition alone so that data is available for searching by the application.

I did a rebuild using the command " alter index lb_text_idx rebuild partition p_1".

However when I run the below sql, I get a count of 0. I know that the text MAINT is definitely present in the table.

SELECT COUNT(1) FROM EVENT WHERE PROJECT_ID = 1 and contains (DISC_CMPLNT_TX, 'MAINT' ) > 0

When I run the below sql
select parameters,status from user_ind_partitions where partition_name = 'P_1' , I get parameters as null and status as usable.

Am I getting 0 records because the parameters are set to null?

How do I rebuild with the same parameters for the specific partition alone.

Any pointers would be very helpful


Thanks,
Vijay

Hemant K Chitale said...

Vijay,

I am sorry, I don't do context indexes.

Note that these are actually "tables" and are structured differently from regular B-Tree indexes.

Hemant