JL Computer Consultancy

Practical Oracle 8i - Building Efficient Databases

Chapter 12: Introducing Partitioning.


Skip_unusable_indexes pt II (21stMay 2001)

Skip_unusable_indexes (2nd May 2001)

Splitting Partitions (corrected 3rd Mar 2001)

Rebuilding indexes online




Back to Book Index


Skip_unusable_indexes pt II:

Following on from my May 2nd note on how the behaviour of the skip_unusable_indexes parameter has changed, I did a few more experiments and came across an interesting little anomaly, which could be considered a good thing, but might be considered a bad thing depending on your environment.

In principle, it is possible to alter the parameter skip_unusable_indexes at the session level, however, when you do so there are some side effects that make it appear to have been set at the system level. Consider the sequence of actions:

session 1 - 
        alter index partitioned_idx modify partition p2 unusable;
        alter session set skip_unusable_indexes = true;
        select * from partitioned_table 
        where indexed_col = {some literal value in p2};

Because of the new behaviour of the parameter, the query does a tablescan of the partition, rather than taking an indexed access path - which is a good thing, presumably. On the other hand, if you repeat the query in another session which has NOT changed the default parameter value, the query STILL takes the tablescan path, when it should take the indexed path and fail.

Of course, this is a bit of a special, and possibly artificial, case but I have seen similar circumstances where it is more desirable for a query to fail with an expected error than for it to run very slowly. Be warned, therefore, that when using partitioned tables there is another case where skip_unusable_indexes has a side-effect that may take you by surprise.

Top of page



For completeness, I have mentioned this parameter in the addenda to Chapter 7 (Enhanced Indexing) and Chapter 13 (Using Partitioning).The reason I raise it specifically for this chapter is my comment (p.262/3) that there is no option to bypass the problem of a query failing with Oracle error

        ORA-01502: index XXX.YYY or partition of such index is in unusable state.

Even when the session-level parameter skip_unusable_indexes is set to true, Oracle 8.1.5 will fail a query that hits the unusable index partition.

However, in Oracle 8.1.6+ the effect of this parameter has changed. If it is set to true, then Oracle will in general not even consider using an index with an unusable partition. This, of course, has some side-effects: on one hand, queries no long fail because an index partition has become unusable (a good thing), on the other hand, a query that ought to run very quickly against partitions 10 to 14 (say) now runs very slowly because index partition 3 (say) has become unusable (a bad thing).

I did say that in general Oracle will not consider using the index if any one partition is unusable - there are special cases, of course. One of them is when Oracle can determine at parse time that it will definitely not hit the unusable partition. This leads to the following sort of surprise:

        select count(*) from partitioned_table where partitioning_column = 33;
        select count(*) from partitioned_table where partitioning_column = :b1;

If one partition of your highly desirable index on partitioning_column is unusable (and the parameter is set to true) then the second query will do a full table scan of whichever partition it needs to access. The first query will use the index (provided the literal value supplied does not identify the partition with the unusable index).

Basically, if your execution plan for your query does not have a partition start/stop with literal values, then Oracle will refuse to consider the index as an option for accessing the data. In many cases I suspect that this will be sufficient to ensure that the parameter stays false at all times.

Top of page


Splitting Partitions:

My thanks to Cory Brooks (hbrooks1@nc.rr.com) for pointing out a couple of typos - one very serious - in the following notes. The errors appeared in the last line of create table temp_pt2; the important point is that you pick a 'mid-point' value then create one table of values strictly less than that mid-point, and the other table as values greater than or equal to that mid-point.

There is some discussion (p.245 and ff.) about splittling partitions, with some reference to the need to rebuild and relocate index partitions, and the possible denial of service to end-users. Richard Sutherland of PACE (rvsutherland@yahoo.com) has pointed out that it can sometimes be useful to take advantage of the ubiquitous exchange partition feature to reduce this potential stress point.

The variations on a theme are numerous, but the basic idea is simple - make sure that the partition you split is an empty one. One example of the approach might be:

        create table temp_pt1 as
        select * from partitioned_table partition (pNNN)
        where partition_column < {your midpoint value};
        -- create indexes on temp_pt1;
        create table temp_pt2 as
        select * from partitioned_table partition (pNNN)
        where partition_column >= {your midpoint value};
        -- create indexes on temp_pt2;
        create table temp as 
        select * from partitioned_table
        where rownum < 1;
        alter table partitioned_table 
               exchange partition pNNN with temp;
        alter table partitioned_table 
               split partition pNNN at {your midpoint value}
               into ( partition pNNN_1 , partition pNNN_2);
        -- analyze the two new partitions here to avoid losing table stats
        alter table partitioned_table
               exchange partition pNNN_1 with temp_pt1
               including indexes without validation;
        alter table partitioned_table
               exchange partition pNNN_2 with temp_pt2
               including indexes without validation;

Although this procedure requires you to ensure that the users are not going to change the data in the target partition, and does leave a hole in the data for a brief period, it does mean that you can prepare the split, putting all the different bits of data and index in the correct tablespaces, and then effect the switch in a few moments without any risk of errors or failures.

Top of page


Rebuilding indexes online:

There is a mention of rebuilding indexes online (p.246) in this chapter. There is a fairly serious bug with this feature, described in the addenda to chapter 7, that is not fixed until version

Top of page



Default tablespaces: There is a passing reference to changing the default tablespace (p 256) for new partitions of a partitioned indexes that is syntactically incorrect. This should read:

        alter index partitioned_index
        modify default attributes
        tablespace default_ts;

Typo: p 239 Column article_topic in the table definition should be article_title. (corrected in 2nd edition).

Top of page


Back to Book Index