Collect Statistics On Local Partitioned Index After A Split Partition failing with the ORA-01403

(Doc ID 2334968.1)

Last updated on JANUARY 03, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.

Symptoms

When calling dbms_stats.set_index_stats for a partition that has been split,
the dbms_stats package fails with the "ORA-01403: no data found" error after a few
iterations.

Changes

 Issue Reproduces in 11.2.0.3, 12.1.0.1

STEPS:

drop table IEFD cascade constraints;
create table IEFD ( FileID NUMBER, Seq INTEGER)
PARTITION BY RANGE (FileID)
(PARTITION IP_Dummy VALUES LESS THAN (49964));

CREATE INDEX IEFD_fileid_idx ON IEFD(FileId, Seq) LOCAL ;

ALTER TABLE IEFD add PARTITION IP_10_01 VALUES LESS THAN (49967) ;

ALTER TABLE IEFD split partition IP_DUMMY at (49961)
into (partition IP_DUMMY ,partition I10 ) ;

ALTER TABLE IEFD split partition IP_DUMMY at (49958)
into (partition IP_DUMMY ,partition I9 ) ;

ALTER TABLE IEFD split partition IP_DUMMY at (49954)
into (partition IP_DUMMY ,partition I8 ) ;

ALTER TABLE IEFD split partition IP_DUMMY at (49951)
into (partition IP_DUMMY,partition I7 ) ;

ALTER TABLE IEFD split partition IP_DUMMY at (49948)
into (partition IP_DUMMY ,partition I6 ) ;

ALTER TABLE IEFD split partition IP_DUMMY at (49944)
into (partition IP_DUMMY ,partition I5 ) ;

ALTER TABLE IEFD split partition IP_DUMMY at (49941)
into (partition IP_DUMMY,partition I4 ) ;

ALTER TABLE IEFD split partition IP_DUMMY at (49938)
into (partition IP_DUMMY,partition I3 ) ;

ALTER TABLE IEFD split partition IP_DUMMY at (49935)
into (partition IP_DUMMY,partition I2 ) ;

ALTER TABLE IEFD split partition IP_DUMMY at (49931)
into (partition IP_DUMMY ,partition I1 ) ;

-- alter index IEFD_FILEID_IDX rebuild partition I1;
exec dbms_stats.gather_index_stats(user, INDNAME=>'IEFD_FILEID_IDX', PARTNAME=> 'I1');
exec dbms_stats.gather_index_stats(user, INDNAME=>'IEFD_FILEID_IDX');

ACTUAL RESULTS:

SH@dw23> create table IEFD ( FileID NUMBER, Seq INTEGER)
2 PARTITION BY RANGE (FileID)
3 (PARTITION IP_Dummy VALUES LESS THAN (49964));

Table created.

SH@dw23>
SH@dw23> CREATE INDEX IEFD_fileid_idx ON IEFD(FileId, Seq) LOCAL ;

Index created.

SH@dw23>
SH@dw23> ALTER TABLE IEFD add PARTITION IP_10_01 VALUES LESS THAN (49967) ;

Table altered.

SH@dw23>
SH@dw23> ALTER TABLE IEFD split partition IP_DUMMY at (49961)
2 into (partition IP_DUMMY ,partition I10 ) ;

Table altered.

SH@dw23>
SH@dw23> ALTER TABLE IEFD split partition IP_DUMMY at (49958)
2 into (partition IP_DUMMY ,partition I9 ) ;

Table altered.

SH@dw23>
SH@dw23> ALTER TABLE IEFD split partition IP_DUMMY at (49954)
2 into (partition IP_DUMMY ,partition I8 ) ;

Table altered.

SH@dw23>
SH@dw23> ALTER TABLE IEFD split partition IP_DUMMY at (49951)
2 into (partition IP_DUMMY,partition I7 ) ;

Table altered.

SH@dw23>
SH@dw23> ALTER TABLE IEFD split partition IP_DUMMY at (49948)
2 into (partition IP_DUMMY ,partition I6 ) ;

Table altered.

SH@dw23>
SH@dw23> ALTER TABLE IEFD split partition IP_DUMMY at (49944)
2 into (partition IP_DUMMY ,partition I5 ) ;

Table altered.

SH@dw23>
SH@dw23> ALTER TABLE IEFD split partition IP_DUMMY at (49941)
2 into (partition IP_DUMMY,partition I4 ) ;

Table altered.

SH@dw23>
SH@dw23> ALTER TABLE IEFD split partition IP_DUMMY at (49938)
2 into (partition IP_DUMMY,partition I3 ) ;

Table altered.

SH@dw23>
SH@dw23> ALTER TABLE IEFD split partition IP_DUMMY at (49935)
2 into (partition IP_DUMMY,partition I2 ) ;

Table altered.

SH@dw23>
SH@dw23> ALTER TABLE IEFD split partition IP_DUMMY at (49931)
2 into (partition IP_DUMMY ,partition I1 ) ;

Table altered.

SH@dw23>
SH@dw23> -- alter index IEFD_FILEID_IDX rebuild partition I1;
SH@dw23> exec dbms_stats.gather_index_stats(user, INDNAME=>'IEFD_FILEID_IDX', PARTNAME=> 'I1');
BEGIN dbms_stats.gather_index_stats(user, INDNAME=>'IEFD_FILEID_IDX', PARTNAME=> 'I1'); END;

*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SYS.DBMS_STATS", line 18387
ORA-06512: at "SYS.DBMS_STATS", line 18410
ORA-06512: at line 1

SH@dw23> exec dbms_stats.gather_index_stats(user, INDNAME=>'IEFD_FILEID_IDX');

PL/SQL procedure successfully completed.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms