CODEWITHSUNDEEP
oracle-databaseplsqlpartitioning
user3178723
user3178723

Reputation: 11

Updating partitioned table oracle

Hi I have a partitioned table and when I am trying to update taking few selected partition in a loop with passing partition name dynamically, it s not working.

for i in 1..partition_tbl.count Loop

UPDATE cdr_data PARTITION(partition_tbl(i)) cdt
SET A='B'
WHERE
cdt.ab='c'

End Loop;

The partition_tbl object has all the partition in which I want to perform this update.

Please suggest me how to proceed here.

Thanks in advance

Upvotes: 1

Views: 16073

Answers (3)

Jakub P
Jakub P

Reputation: 542

In case of table partitioned by RANGE INTERVAL(...) on DATE column querying by that column the range has to fit in single interval, if the range overlaps on neighbouring partition the query seems to be scanning ALL partitions (20s execution instead of below 1s)

  CREATE TABLE "BP_AUDIT_LOG_PRC" 
   (    
   "AUDIT_LOG_SKID" NUMBER(19,0) NOT NULL ENABLE, 
...
    "CRTN_DATE" DATE DEFAULT ON NULL SYSDATE NOT NULL ENABLE, 
...
     CONSTRAINT "BP_AUDIT_LOG_PRC_PK" PRIMARY KEY ("AUDIT_LOG_SKID")
  USING INDEX 
   ) ...
  PARTITION BY RANGE ("CRTN_DATE") INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) 
 (PARTITION "P_INITIAL"  VALUES LESS THAN 
(TO_DATE(' 2019-01-01 00:00:00', 
         'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ) 

> select min(crtn_date),max(crtn_date) from bp_audit_log_prc PARTITION (SYS_P61497)
> 1 row retrieved starting from 1 in 699 ms (execution: 678 ms, fetching: 21 ms)
--PARTITION (SYS_P61497)
--MIN(CRTN_DATE)   MAX(CRTN_DATE)
--2024-02-01 00:01:24  2024-02-29 23:59:43

> select min(crtn_date),max(crtn_date) from bp_audit_log_prc
  where 1=1 and crtn_date between to_date('2024.02.01','YYYY.MM.DD') and to_date('2024.03.01','YYYY.MM.DD')
> 1 row retrieved starting from 1 in 20 s 686 ms (execution: 20 s 647 ms, fetching: 39 ms)

> select min(crtn_date),max(crtn_date) from bp_audit_log_prc 
  where 1=1 and crtn_date between to_date('2024.02.01','YYYY.MM.DD') and to_date('2024.03.01','YYYY.MM.DD')-1/24/60/60
> 1 row retrieved starting from 1 in 786 ms (execution: 769 ms, fetching: 17 ms)

...so querying by specific partition might be better choice for someone.

Upvotes: 0

Michal Pravda
Michal Pravda

Reputation: 829

Preferably let Oracle take care about partitions - pretend in your statement they do not exist

UPDATE cdr_data  cdt SET A='B' WHERE cdt.ab='c'

it will from the where conditions and your partitions definitions choose the right partition(s) to apply the command on.

There may be rare event when you need a partition bounded DML, but certainly it is not the example shown. In such situation you can't provide partition name dynamically, like you can't normally provide table name dynamically e.g. you can't

select * from _variable_containing_table_name

If you really insist on partition bounded command then it would be

select * from table_name partition (partition_Name)

e.g.

select * from bills partition (p201403)

To use dynamic partition name the whole statement would have to be dynamically executed via execute immediate or dbms_sql.

But once again, do not choose partition, Oracle will.

Upvotes: 3

Justin Cave
Justin Cave

Reputation: 231661

What is the problem that you are trying to solve? It doesn't make sense to run separate UPDATE statements against each partition in a loop. If you really want to update every row in the table where ab = 'c', just issue a single UPDATE statement

UPDATE cdr_data cdt
   SET a = 'B'
 WHERE ab = 'c'

potentially with a PARALLEL hint that would allow Oracle to update multiple partitions in parallel.

If you really, really want to update each partition independently, it would make much more sense to do so based on the partition keys. For example, if your table has daily partitions based on a date

FOR i IN 1 .. <<number of daily partitions>>
LOOP
  UPDATE cdr_data cdt
     SET a = 'B'
   WHERE ab = 'c'
     AND partition_key = <<minimum date>> + i;
END LOOP;

Using the partition( <<partition name>> ) syntax is an absolute last resort. If you're really determined to go down that path, you'd need to use dynamic SQL, constructing the SQL statement in the loop and using EXECUTE IMMEDIATE or dbms_sql to execute it.

Upvotes: 4

Related Questions