My Oracle Support Banner

Partition Statistics on IOT are Lost During Export/Import (Doc ID 831821.1)

Last updated on APRIL 03, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.1.0.7 [Release 9.2 to 11.1]
Information in this document applies to any platform.
This problem can occur on any platform.

Symptoms

You created a index organized table (IOT) partitioned by hash (or range). Then the statistics on table were gathered and the table was transferred to another schema using original export/import (exp/imp). The partition statistics are lost as the next test demonstrates:

NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the
Oracle sample schema(s), Public Documentation delivered with an Oracle database product or other training material.
Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.
 
connect / as sysdba

drop user test cascade;
drop user test1 cascade;

create user test identified by test default tablespace users temporary tablespace temp;
create user test1 identified by test1 default tablespace users temporary tablespace temp;
grant connect, resource to test, test1;

connect test/test

create table iot_part
(
   col001 number,
   col002 varchar2(10),
   primary key (col001)
)
organization index
partition by hash (col001)
(
   partition p001,
   partition p002
);

-- populate the table
begin
  for i in 1..20 loop
    insert into iot_part values (i, lpad (to_char (i), 10, '0'));
  end loop;
  commit;
end;
/

-- gather table statistics
exec dbms_stats.gather_table_stats (ownname => 'TEST', tabname => 'IOT_PART', granularity => 'ALL');

select * from user_part_col_statistics order by table_name, partition_name, column_name;

TABLE_NAME           PARTITION_NAME       COLUMN_NAME          NUM_DISTINCT
-------------------- -------------------- -------------------- ------------
LOW_VALUE
----------------------------------------------------------------
HIGH_VALUE                                                          DENSITY
---------------------------------------------------------------- ----------
 NUM_NULLS NUM_BUCKETS SAMPLE_SIZE LAST_ANALYZED       GLO USE AVG_COL_LEN
---------- ----------- ----------- ------------------- --- --- -----------
HISTOGRAM
---------------
IOT_PART             P001                 COL001                          8
C103
C115                                                                   .125
         0           1           8 26.05.2009 16:47:15 YES NO            3
NONE

IOT_PART             P001                 COL002                          8
30303030303030303032
30303030303030303230                                                   .125
         0           1           8 26.05.2009 16:47:15 YES NO           11
NONE

IOT_PART             P002                 COL001                         12
C102
C114                                                             .083333333
         0           1          12 26.05.2009 16:47:15 YES NO            3
NONE

IOT_PART             P002                 COL002                         12
30303030303030303031
30303030303030303139                                             .083333333
         0           1          12 26.05.2009 16:47:15 YES NO           11
NONE

4 rows selected.

-- export the partitioned IOT
host exp test/test file=testiot.dmp tables=iot_part

Export: Release 11.1.0.7.0 - Production on Tue May 26 16:47:16 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                       IOT_PART
. . exporting partition                           P001          8 rows exported
. . exporting partition                           P002         12 rows exported
Export terminated successfully without warnings.

connect test1/test1

-- import partitioned IOT into new schema
host imp test1/test1 file=testiot.dmp tables=iot_part

Import: Release 11.1.0.7.0 - Production on Tue May 26 16:47:16 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.01.00 via conventional path

Warning: the objects were exported by TEST, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing TEST's objects into TEST1
. importing TEST's objects into TEST1
. . importing partition              "IOT_PART":"P001"          8 rows imported
. . importing partition              "IOT_PART":"P002"         12 rows imported
Import terminated successfully without warnings.

select * from user_part_col_statistics order by table_name, partition_name, column_name;

TABLE_NAME           PARTITION_NAME       COLUMN_NAME          NUM_DISTINCT
-------------------- -------------------- -------------------- ------------
LOW_VALUE
----------------------------------------------------------------
HIGH_VALUE                                                          DENSITY
---------------------------------------------------------------- ----------
 NUM_NULLS NUM_BUCKETS SAMPLE_SIZE LAST_ANALYZED       GLO USE AVG_COL_LEN
---------- ----------- ----------- ------------------- --- --- -----------
HISTOGRAM
---------------
IOT_PART             P001                 COL001


                                                       NO  NO
NONE

IOT_PART             P001                 COL002


                                                       NO  NO
NONE

IOT_PART             P002                 COL001


                                                       NO  NO
NONE

IOT_PART             P002                 COL002


                                                       NO  NO
NONE

4 rows selected.


=> Partition statistics are lost

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.