Unable To Query Data From PARTITION_MIGRATE Even Though Data Exists (Doc ID 2239324.1)

Last updated on MARCH 30, 2023

Applies to:

Goal

In system all classes like /event,/item,/bill etc were not partitioned at the time of installation.

As mentioned in below document, converted non-partitioned class to partitioned class after installation.
https://docs.oracle.com/cd/E16754_01/doc.75/e16719/adm_nonpart_to_part.htm#BRMSA963

Whenever, querying data with partition key(poid_id0/obj_id0) unable to get output as poid_id0/obj_id0 value which is in partition_migrate doesn't matches with partition boundaries. Since it doesn't match query don't even considering that partition.

Below are the queries which are not working even though data exists in the partition.

select * from event_t where poid_id0 = 279399098758907656;
select * from event_t partition (partition_migrate) where poid_id0 = 279399098758907656;

Even read obj also not working.But 279399098758907656 exists in database.

Whenever converting non partitioned classes to partitioned classes ootb script partitioning.pl (which internal executes event_partition.sql to enable partition) is exchanging partition without validation.

So, Whenever querying that data with partition key in where clause partition pruning concept comes into picture and it is checking in latest partition only .

Query 1:select * from event_t where poid_id0 = 279399098758907656(not working);--> Query optimizer is looking into 3rd partition i.e current active partition.
Query 2:select * from event_t where poid_id0 like 279399098758907656(this is working); --> Query optimizer is looking into all partitions as we have given like .

Code Snippet of event_partition.sql:
v_xcnge_partition_cmd := 'ALTER TABLE ' || v_partitioned_temp_table ||' EXCHANGE PARTITION partition_migrate WITH TABLE ' ||v_table_name || ' INCLUDING INDEXES WITHOUT VALIDATION';

Example :
Table Name Partiition Name High Value Position
EVENT_T PARTITION_MIGRATE 17592186044416 1
EVENT_T PARTITION_HISTORIC 35184372088832 2
EVENT_T P_R_01092017 302163387498889216 3
EVENT_T P_R_02092017 302708745266266112 4
EVENT_T P_R_03092017 303201326475509760 5
EVENT_T P_R_04092017 303746684242886656 6
EVENT_T P_R_05092017 304274449824219136 7
EVENT_T P_R_06092017 304819807591596032 8
EVENT_T P_R_07092017 305347573172928512 9
EVENT_T PARTITION_LAST_PIN 1152921504606846976 10
EVENT_T P_D_01182017 1455243221780135936 11
EVENT_T P_D_02182017 1455788579547512832 12
EVENT_T P_D_03182017 1456281160756756480 13
EVENT_T P_D_04182017 1456826518524133376 14
EVENT_T P_D_05182017 1457354284105465856 15
EVENT_T P_D_06182017 1457899641872842752 16
EVENT_T P_D_07182017 1458427407454175232 17
EVENT_T PARTITION_LAST MAXVALUE 18

Here max value of PARTITION_MIGRATE is 17592186044416 which is less than the poids existed in that partition so query optimizer is not even considering this partition.

Even the minimum poid which is in event_t is more than max_value of partition migrate.

min=17592187519101, max=1455700618689976505

max_value of partition_migrate is 17592186044416

Que. 1. What is the reason behind this issue ?

Que. 2. Why below query is not working even though data exists?

select * from event_t where poid_id0 = 279399098758907656;

Solution

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