Data is not getting inserted into proper partition

Question

I have a table with proper partition.

Code snippet:

CREATE TABLE RSA_DBA.RSA_ADHOC_ACCT
(   
    RSA_ACCT_DTL_GID NUMBER(12,0) NOT NULL, 
    EFF_DT DATE NOT NULL, 
    RSA_CMPSN_REC_GID NUMBER(12,0), 
    ACCT_ID NUMBER(12,0), 
    ACCT_CMPSN_AMT NUMBER(16,3), 
    INSRT_USER VARCHAR2(30 BYTE) DEFAULT USER NOT NULL, 
    INSRT_TS DATE DEFAULT SYSDATE NOT NULL, 
    UPDT_USER VARCHAR2(30 BYTE) DEFAULT USER NOT NULL, 
    UPDT_TS DATE DEFAULT SYSDATE NOT NULL, 
    PRCS_RUN_ID NUMBER(12,0), 
    SRC_TS DATE, 
    VLD_IND CHAR(1 BYTE), 

    CONSTRAINT PK_RSA_ADHOC_ACCT_DTL_AI 
        PRIMARY KEY (RSA_ACCT_DTL_GID, EFF_DT)
    PARTITION BY RANGE (EFF_DT) 
    PARTITION PTN_M20221201  VALUES LESS THAN (TO_DATE(' 2023-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    PARTITION PTN_M20230101  VALUES LESS THAN (TO_DATE(' 2023-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    PARTITION PTN_M20230201  VALUES LESS THAN (TO_DATE(' 2023-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
     :
     :
     :
     PARTITION PTN_M20241001  VALUES LESS THAN (TO_DATE(' 2024-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
     PARTITION PTN_M20241101  VALUES LESS THAN (TO_DATE(' 2024-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
)

When I am trying to insert data, few data got inserted properly in respective partition while others didn't.

Sharing screenshot of data available data in table:

SQL:

select trunc(EFF_DT), count(ACCT_CMPSN_AMT) 
from RSA_DBA.RSA_ADHOC_ACCT
group by  trunc(EFF_DT)
order by 1;

Now we we look into table "dba_tab_partitions" to check if data are properly inserted I can see only 2 partition has proper data, highlighted.

SQL:

SELECT table_name, partition_name, num_rows
FROM dba_tab_partitions
WHERE table_name  like 'RSA_ADHOC_ACCT' and NUM_ROWS > 0
ORDER BY table_name, partition_name;

Please note: here I can see for other partition data is very huge. Can someone guide me - where can I fix this issue? It's a PROD issue.

Have you queried the actual partitions ? Use the "partition" clause in the select statement to limit the resultset to only records that are stored in a particular partition ? What you are showing just shows the amount of data in a partition, it doesn't show that data is in the wrong partition. — Koen Lostrie
– Koen Lostrie, Commented Aug 26, 2024 at 10:11

Pooja · Accepted Answer · 2024-08-26 10:36:02Z

1

When checked issue found in "Gathering statistics". Since the data volume was high and hence oracle didn't performed proper partition maintenance. when checked object_id of the inserted data with respect to eff_dt it was matching with the partion object_id value(obtained using dba_objects)

SQL:

select distinct substr(dbms_rowid.rowid_object(rowid),1,15) object_id , eff_dt
from RSA_ADHOC_ACCT;

Object_id obtained:

Thus just by executing below SQL, I was able to get these data reflected corresponding to partition.

EXEC dbms_stats.gather_table_stats(ownname => 'RSA_DBA', tabname => RSA_ADHOC_ACCT, estimate_percent => 100);

answered Aug 26, 2024 at 10:36

Pooja

3331 gold badge7 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Paul W Over a year ago

Correct, you cannot rely on statistics as being accurate unless you gather with compute statistics immediately before. Stats are intended for the optimizer to get a good-enough idea of cardinality for its decision-making tree. It should only be used by Oracle internally for this purpose.

Collectives™ on Stack Overflow

Data is not getting inserted into proper partition

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related