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.
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.
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 |