Good day, all experts.

I'm not sure in one operation till the end, I want to advice with you.
My problem is the following:

I have a huge partitioned table about 1,5 billion of rows inside. The table has the 23 partitions.
Also there is a local partitioned index on one of the column.

I want to check this index on bad leaf and recreate if it's nessessary.
If I'm not mistaken than I must recreate on parts such way:


alter index ITEM_IDX rebuild partition TEST_Q1_JAN tablespace PART1 parallel nologging;


Please, Can anybody tell me this is safe operation or not?
And How long it will execute if each part has about 22 million rows.

Best regard,Igor.

Hi Igor,

>> I want to check this index on bad leaf and recreate if it's nessessary.

What do you mean, "bad leaf"?

*******************************************************************
>> Please, Can anybody tell me this is safe operation or not?

Not safe, as it will rebuild in the same old tablespace, and it might run out of room.

Check to make sure that space is available.

That's the only downside, but the good news is that, even if it fails, it won't corrupt the old index.

I have some great notes on index rebuilding here:

http://www.dba-oracle.com/t_index_rebuilding_issues.htm

Hi,

I meant a "badness" of index that is index fragmentation which occurs when a row included in the index is deleted.

Thank you for your link and advice. I wll read the information on it.

Best regard, Igor.

How does deleting a row make an index bad?

May be a word "bad" it's not suitable for a situation concerning index, but I think a word "badness" of index it will sound better, that is index fragmentation which occurs when a row included in the index is deleted.

it always happens when there is heavy delete activity in database.

>> it always happens when there is heavy delete activity in database.

That makes sense. Oracle index nodes are "logically deleted", so as enpty space collects, index range scans and index fast full scans run far slower.

What is your cpu_count?

Hi, sorry for my later answer.

I have 4 CPU on the production server. I tried to examine many articles about explaining the meaning of the index in Oracle and as far as I understood the optimal degree of parallelism can be safely set to N-1 where N is the number of processors in my server.

That is

alter index ITEM_IDX rebuild partition TEST_Q1_JAN tablespace PART1 parallel degree 3 nologging;

but another articles write that normally Oracle calculates the optimum degree of parallelism, so it is not necessary for you to specify integer.

That is I can issue

alter index ITEM_IDX rebuild partition TEST_Q1_JAN tablespace PART1 parallel nologging;

and Oracle calculates the optimum degree of parallelism.

Please, correct me if I'm mistaken.

And I want to ask one more question. I need to do analyse index to understand should I rebuild index or not.

But database is production, so I wrote carefully your article and found that say word-for-word:

IMPORTANT: Collecting the index_stats information is very time consuming and expensive and will introduce serious locking issues on production databases. It is strongly recommended that you perform these checks during scheduled downtime, or on a representative test database."

How do you think how long this operation will take the time if I have table with 1 billion 700 million rows inside? And is it safe operation for database or not because I will do it on production database?

I just fear to corrupt the production database. if it's not difficult for you help me please.

Thanks in advance.

Best regards, Igor.

Hi Igor,

First, what is your nmotive for rebuilding the indexes? To re-claim space or to improve performance?

Have you checked the Oracle segment advisor? That recommendes indexes for rebuilding.

>> another articles write that normally Oracle calculates the optimum degree of parallelism, so it is not necessary for you to specify integer.

Yes, but I don't agree! Setting parallelism at the system level or table level can be a mistake, because the optimizer starts thinking that full-scans are cheaper than they really are:

http://www.dba-oracle.com/art_par_ecom.htm



This should work fine, so long as there are enough contiguous extents in PART1 to hold the new index.

(Note that I specified "3" for the parallel degree.)

***********************************************************

>> I need to do analyse index to understand should I rebuild index or not.

That's one way, but thereare many. many approaches. Remember, not all indexes are accesed the same. Indexes that only access with "unique" scans will never need rebuilding. You need to see if the index experiences large range scans of full scans, to know for sure if a rebuild will help performance.

>> Collecting the index_stats information is very time consuming

No, you don't need that much analysis. Oracle MetaLink note 122008.1 has the officially authorized script to detect indexes that benefit from rebuilding. This script detects indexes for rebuilding using these rules: Rebuild the index when these conditions are true:

- deleted entries represent 20% or more of the current entries.
- the index depth is more then 4 levels.

See here:

http://www.dba-oracle.com/oracle_news/2005...es_bad_ugly.htm

*************************************************************
>> And is it safe operation for database or not because I will do it on production database?

It can be made safe, IF you make a brand new tablespace to recieve the new index (PART1), and if you have enough TEMP to sort the keys. If it fails, the old index remains in-place.

Just make sure that you do it during a planned downtime, take a backup, and punch the index DDL with dbms_metadata before you start.

What is the datatype of the indexed column? I think everyone in the community agrees there are times when rebuilding an index makes sense. Some see the circumstances as being more limited in nature than others, but if it helps in the short term and you can afford the time time to do the rebuild, then why not do it?

The type of column is NUMBER(8), NOT NULL. I just think should I parallelize the table for fast analyzing of the index, because about 1,5 billion rows inside of the table.

Hi,Donald.

Thank you for your help.
Your helped me to fill up a space in my head concerning index.
I mean your helped me to give an answer to many questions in which I was assured.

select a.CONSTRAINT_TYPE,a.CONSTRAINT_NAME,b.COLUMN_NAME from dba_constraints a,
(select CONSTRAINT_NAME,COLUMN_NAME from DBA_CONS_columns where OWNER ='AWEST' and TABLE_NAME='FSC_TRANSACTION_DIM') b where a.CONSTRAINT_NAME=b.CONSTRAINT_NAME
/

CONSTRAINT_TYPE CONSTRAINT_NAME COLUMN_NAME
--------------- --------------- -----------
C SYS_C004361 TRANS_KEY
C SYS_C004362 DATE_KEY
P XPK_DIM TRANS_KEY

Type of constraint "c" for DATE_KEY column that is it means "NOT NULL", not "UNIQUE".

I want to rebuild index in order to increase performance of database,
because a big delete activity occurs every day.

But I want to clarify some details:

1) before analyzing index I increase TEMP tablespace approximately in 2 times.

2) I parallelize the table for speeding up the process of the analyzing.

ALTER TABLE <table_name> PARALLEL (DEGREE 3);

3) do analyzing of the index

ANALYZE INDEX XIE2FSC_TRANSACTION_DIM COMPUTE STATISTICS;

ANALYZE INDEX XIE2FSC_TRANSACTION_DIM VALIDATE STRUCTURE;

4) Now find the ratio of del_lf_rows and lf_rows:-

select del_lf_rows * 100 / decode(lf_rows,0,1,lf_rows) from index_stats
where name = 'index_ name';

If it is more than 20% of rows are deleted then the index should be rebuilt.

5) if index should rebuild than I increase the all tablespaces where indexes are kept by database.

6) rebuild each of partition of local partitioned index:

alter index ITEM_IDX rebuild partition <partition_name> tablespace

<tablespace_name> parallel 3 nologging;

Am I right or in some steps I'v made a mistake?

Best regards, Igor.