Updating clause used to know vipdatingxxx ru

Since my table is too huge and update will be on many rows(some cases upto millions) I face severe bad performance.

Hence I am trying to use a WITH clause during update but I'm hitting error in it.

SQL select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID d17p32g0zdt3w, child number 0 ------------------------------------- update t set object_name = ( with t1 as ( select /* materialize */ t.rowid rid, case when count(distinct trunc(created,'MM')) over ( partition by owner ) = 1 then 'X' else 'Y' end flag from t where object_type = 'TABLE' ) select flag from t1 where rid = t.rowid ) where object_type = 'TABLE' Plan hash value: 890551451 -------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | 1 | | 0 |.76 | 8879 | | | | | 1 | UPDATE | T | 1 | | 0 |.76 | 8879 | | | | |* 2 | TABLE ACCESS FULL | T | 1 | 1630 | 2841 |.01 | 1538 | | | | | 3 | TEMP TABLE TRANSFORMATION | | 2841 | | 2841 |.73 | 1538 | | | | | 4 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D669D_9269CDB4 | 1 | | 0 |.01 | 1538 | 1024 | 1024 | | | 5 | WINDOW SORT | | 1 | 1630 | 2841 |.01 | 1538 | 232K| 232K| 206K (0)| |* 6 | TABLE ACCESS FULL | T | 1 | 1630 | 2841 |.01 | 1538 | | | | |* 7 | VIEW | | 2841 | 1630 | 2841 |.72 | 0 | | | | | 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D669D_9269CDB4 | 2841 | 1630 | 8071K|.36 | 0 | | | | -------------------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OBJECT_TYPE"='TABLE') 6 - filter("OBJECT_TYPE"='TABLE') 7 - filter("RID"=: B1) 32 rows selected.

So i tried below options but none of these seems working: Option1: update t set object_id = n_sorting_order where rowid in ( with x as ( select rowid r,dense_Rank() over(partition by object_type order by object_name,created) n_sorting_order from t where owner = 'SCOTT' ) select r , n_sorting_order from x ); / Note : Option 1 having some syntax error, not sure how to fix it.

Option2: update t x set object_id = (SELECT dense_Rank() over(partition by object_type order by object_name,created) FROM t y where owner = 'SAPMIG' and x.rowid = y.rowid); Option3: update t x set object_id = (SELECT dense_Rank() over(partition by object_type order by object_name,created) FROM t y where owner = 'SAPMIG' and x.rowid = y.rowid) where exists (SELECT 'x' FROM t y where owner = 'SAPMIG' and x.rowid = y.rowid) Hi, I understood the usage of with clause now referring this case, but here my "Set" statement is using a constant value of 100.

From last few days I came across many situations where I need to update a table based on some analytical function output.

Since, my table don't have any primary key I am having limitation on update using SQL and hence I left with the only alternatives of using PL/SQL.

Search for updating clause used to know:

updating clause used to know-10

Leave a Reply

Your email address will not be published. Required fields are marked *

One thought on “updating clause used to know”