index unusable

[jpdar]

Hi guys,
We have one global index on a partitioned table (Oracle 8.1.6). Their is one partition for each days worth of data. We have a job that deletes partitions over 30 days. Does this job ' alter table xxxx drop partition xxxx' cause our global index to become unusable?

When I attempt to rebuild the index sqlplus freezes. Should I drop the index and re-create it?
Should I be using local partitioned indexes?
Thanks in advance

 

[karluk]

You're right, dropping table partitions will cause the index to be marked as unusable. Since a rebuild doesn't work, you should drop and recreate the index.

I would definitely experiment with local partitioned indexes. Certain types of queries should run faster with this type of index, and I think that maintenance will be easier. It looks as if it will be possible to rebuild local indexes instead of the drop and recreate that your global index seems to require.

 

[THoey]

jpdar,

Did you ever resolve this issue? I am looking to do the same thing and would love to "learn from your experience".

Post back and I can give you some contact information.

Thanks in advance... Terry M. Hoey

 

[HenryMonster]

This is an abstract of Oracle's documentation:

==============================
Dropping a Table Partition

You use the ALTER TABLE...DROP PARTITION statement to drop a table partition from either a range or composite partitioned table. If you want to preserve the data in the partition, you should merge the data into an adjacent partition instead.

If there are local indexes defined for the table, this statement also drops the matching partition or subpartitions from the local index.

Any global nonpartitioned indexes on the table will be marked UNUSABLE, and all partitions of any global partitioned indexes will be marked UNUSABLE, unless the partition being dropped or its subpartitions are empty.

=============================================


At the same time I'd question whether you
really need to bother with a partitioned
table in your case. How many records get
get inserted into the table over one month
(averagely). If we are talking millions,
then your approach is probably right,
but it it's 10,000 - 20,000 a month, why not simply
delete them. You should not necessarilry delete
the whole month at a time: delete all record,
say older than 31 days. If you smartly
set you pctfree and pctused parameters, Oracle
will reclaim the space left available by the deleted
rows without allocating too many additional extents.
A good idea would be in this case to rebuild your indexes on this table and to run analyze table if you use cost
based optimizer.

 

[THoey]

Thanks for the information.

In my case, I am talking about a table that gets approximately 600K records loaded each day. I am going to keep the data for one month. Most access to the data will be reports/queries that just look at yesterdays data, but we do want to have it available for several weeks back. It was suggested that a partitioned table might be a good solution.

I have created a table partitioned on the days of the month (day 01, 02, ..., 31) and am looking into being able to pull reports based on that day number. I have created a LOCAL index based on the DAYOFMONTH field. Also, when that day number comes around the following month, I will need to purge that old data before I load it. I was planning on using the TRUNCATE TABLE ... PARTITION ... command. I don't have a problem recreating the index, as this takes less time than to DELETE ... WHERE ... command does.

Any other suggestions would be GREATLY appreciated.

BTW, jpdar, sorry for the emails you are going to receive due to responses here. I was in the process of creating a new thread when Henry replied.

Terry M. Hoey

 

[jpdar]

THOEY,
We have implemented local partitioned indexes on our partitioned table with good results. We have 4 million records per day.
Now we are looking at IOT's.
Thanks,
Jon