I have to update 20 and 60 million records of a table. The update statement are
1> 20 million recs
update mycustomer set update_time=add_months(sysdate,240) where seq_num = 1;
commit;
2> 60 million recs
update mycustomer set update_time=sysdate-seq_num where seq_num <> 1;
commit;
Q1> Is there any way to improve performance
Q2> Will parallel dml improve performance
Q2> Would a pl/sql cursor make any difference in speed.
create table test1
( ID NUMBER(11)
,MEMBER_NMBR NUMBER(10)
,CODE_NMBR NUMBER(7)
,ROW_EFCTV_DT DATE
,ROW_TRMNTN_DT DATE
[code]....
insert into test1 values (11007,7462,32,'30/sep/2012','31/dec/9999',3,'25/sep/1998','AUTUMN',1,0,344);
insert into test1 values (11007,7462,32,'30/oct/2012','31/dec/9999',3,'25/sep/1998','AUTUMN',1,0,344);
IDMEMBER_NMBRCODE_NMBRROW_EFCTV_DTROW_TRMNTN_DTFLAG_NMBRBRTH_DTNAMECLAIM_CDAMT1AMT2
1100774623209/30/2012 00:0012/31/9999 00:00309/25/1998 00:00AUTUMN10344
1100774623210/30/2012 00:0012/31/9999 00:00309/25/1998 00:00AUTUMN10344
I have to update the row_trmntn_dt of first row to row_efctv_dt of 2nd row which is 30th Oct 2012 - 1 day i.e. 29th Oct 2012
This is my query:
UPDATE t_tt_hours a
SET a.sak_request = (
SELECT b.sak_request
FROM t_requests b, co c
[Code]...
The problem I am having is that it is updating all rows even when it is pulling back a null value for b.sak_request. I've tried adding b.sak_request is not null to the select statement like this:
UPDATE t_tt_hours a
SET a.sak_request = (
SELECT b.sak_request
FROM t_requests b, co c
WHERE b.nam_eds_tracking_id = c.id_dir_track_eds
[Code]...
but it doesn't seem to make a difference. The reason I need to do this is that the difference between where it matches with a valid (non-null) value is 396 rows vs. 12,484 rows which is too time consuming to run on my page.
i am reading the columns value from different table but i want to update it with single update statement. such as how to update multiple columns (50 columns) of table with single update statement .. is there any sql statement available i know it how to do with pl/sql.
View 5 Replies View RelatedI have one doubt about update command in sql. How to update the multiple rows with different values using update statment.
Eg:-
SQL> set linesize 500;
SQL> set pagesize 500;
SQL> select * from emp;
SQL> select empno,ename,sal from emp;
SQL> select empno,ename,sal from emp;
EMPNO ENAME SAL
---------- ---------- ----------
7839 KING 5000
7698 BLAKE 2850
7782 CLARK 2450
7566 JONES 2975
7654 MARTIN 1250
[Code]....
The above table contains 14 records. Now i would like to update the salary column with different values like
EMPNO SAL
===========
7839 18000
7698 20000
7782 5000
...
...
...
7934 25000
How to update above values with single update query.
I have come across a code where i need to know exact logic for the use of FOR UPDATE and COMMIT used in a cursor.The code is like :
Declare
Cursor c1 is select * from emp FOR UPDATE;
Z c1%rowtype;
Begin
Open C1;
Fetch c1 into Z;
dbms_output.put_line(z.ename);
Commit;
Fetch c1 into Z;
dbms_output.put_line(z.ename);
end;
When i run above code, i get error as below, but first value from table emp i.e. emp.ename is displayed :
Error at line 1
ORA-01002: fetch out of sequence
ORA-06512: at line 9
But when i run by removing FOR UPDATE, i get first 2 values displayed without any error message:
Declare
Cursor c1 is select * from emp;
Z c1%rowtype;
Begin
Open C1;
Fetch c1 into Z;
dbms_output.put_line(z.ename);
Commit;
Fetch c1 into Z;
dbms_output.put_line(z.ename);
end;
Actually I need update statement some thing like below scenario...
I explained my problem cleary in attached PDF since i cannot format text well here..
I need to write a script which copies 4 col data from one table to another table. there are three tables
cwat_curr_mst and cwat_assigned_customer and cwat_assignment_mst.
Cwat curr mst has PK curr_id and cwat_assigned_customer has PK assignment_id.
Also cwat_assigned_customer has customer_id.
In cwat_assignment_mst has Curr_id and Assignment_ID.
cwat_curr_mst and cwat_assigned_customer tables has 4 cols in common
they are
ASRT_SNM_NO, SNM_NO, FLORIDA_NO, CBRN_NO.
So from curr_mst all these 4 cols data needs to come/copy into cwat_assigned_customer.
I have made changes in my form
1st step:-
for Inserting records created below cursor, records are inserted and I can see on my screen (form) also.
DECLARE
CURSOR InvestIS
select * from Tempinvest;
BEGIN
OPEN Invest;
GO_BLOCK('ReturnReport');
last_record;
[code]......
2nd step:-
Updating records
I wan to update some columns in this table so I created a 2nd cursor to update records but this cursor is not working accordingly. my requirement is: before Update first of all Find inv_co_code & inv_fnd_code when find then update column Redunits
2nd Cursor
DECLARE
ddate date; refdate date; co_code number; co_name varchar2(50); fnd_code number; fnd_name varchar2(50); units number; amount number; stat varchar2(1);
CURSOR bnr IS
select Ddate,refdate,co_code,co_name,fnd_code,fnd_name,units,amount,stat from (
select inv_date DDate,Vdate RefDate,inv_co_code Co_code,inv_co_name Co_name,inv_fnd_code Fnd_code,inv_fnd_name Fnd_name,
inv_nofu Units,inv_amount Amount,Status Stat from (
------------- Bonus -------------------
select inv_date,vdate,inv_co_code,inv_co_name,inv_fnd_code,inv_fnd_name,inv_nofu,Inv_amount,inv_uprice,'B' Status from investment
where code is null and inv_date <= :dd1
---------------- Redemption --------------
Union All
select red_date,null,red_co_code,red_co_name,red_fnd_code,red_fnd_name,red_nofu,red_amount,red_uprice,'R' Status from redemption
where red_date <= :dd1
))
where co_code = 13 and fnd_code = 1
order by co_code,fnd_code,ddate ;
------------------------------------------
vddateddate%type;
vrefdate refdate%type;
vco_codeco_code%type;
vco_nameco_name%type;
[code].......
I'm currently facing a non-critical situacion while trying to update a table. Here's the PL/SQL Code. It's a pretty straight-forward script, It just update one single column of the table. The only problem is that I have to update over 15 milions records. When I use the for update cursor, I put inside the loop a counter, when it reaches 1000 records, then commit, else keep counting.
The block throws the exception ORA-01002: fetch out of sequence.
I've tried to do some reserch on Google, buy it only says it is an out of secuence fetch caused by a commit inside a for update cursor.
My question is. Is there any risk by putting the commit under the end loop, I mean by doing this I'd be updating over 15.000.000 records at the same time. Will I have any issue with the rollback segment ?
Here's the code as along with the Create Table and Insert statements.
CREATE TABLE TEST_1
(
TEST_ID NUMBER PRIMARY KEY,
IS_LOCKED NUMBER NOT NULL
);
INSERT INTO TEST_1
(TEST_ID, IS_LOCKED)
VALUES
(1, 0);
[Code]....
I would like to update the particular employee name by using the cursor ..For that I would like to show the steps which I did . as in the below screen it updated John but it removed the other ename ...I like to update John only and remaining ename should be as it ..
The code which I have written on KEY-COMMIT is below.
CREATE TABLE "SCOTT"."SEATALLOTMENT"
("YEAR" NUMBER(4,0),
"COLLEGECODE" CHAR(4 BYTE),
"COURSECODE" CHAR(3 BYTE),
[Code].....
Now i want to UPDATE reducing the AVAILABLE column by 1 in COURSESEATS table based on common columns collegecode,coursecode for a ROW inserted into SEATALLOTMENT table ,i am confused to what approach i have to follow whether its a procedure or a trigger
CASE:
Here in this case as i insert a row with krcl,cse as college code and course code respectively into seatallotment table the available column in courseseat table for the respective row with mentioned common column must become 59 from 60
I have a table which has columns like First_Name,Last_Name and Display_Name. Now the every entry in the table have first name and last name populated .
I would like to populate the display name based on the format Display Name = First Name+ " " + Last Name.
For eg. If the first name John and last name is Doe , then the display name should be John Doe.write such a queries which dynamically picks up all the rows and populate the display name.
I have the following table and data , So i want to update this table(word column) with words that has same soundex with one word of them, the word must be that has the long length if the two words has same length So, update by any of them.
drop table t1;
create table t1 (id number(10) ,word varchar2(100));
insert into t1 values(1,'Londn');
insert into t1 values(2,'Egypt');
insert into t1 values(3,'London');
insert into t1 values(4,'Gorgey');
insert into t1 values(5,'Michal');
insert into t1 values(6,'Michel');
insert into t1 values(7,'London');
insert into t1 values(8,'Egpt');
insert into t1 values(9,'Londan');
insert into t1 values(10,'Gorgy');
insert into t1 values(11,'Gorge');the results must be as following
id word
1 London
2 Egypt
3 London
4 Gorey
5 Michal
6 Michal
7 London
8 Egypt
9 London
10 Gorey
11 GoreyNote: this is the results of soundex
SQL> select soundex(word) from t1;
SOUN
----
L535
E213
L535
G620
M240
M240
L535
E213
L535
G620
G620
11 rows selected.
I am trying to update a column based on another column in the same table (student table) and a column from another table (school table)
Code is:
update student_table
set student_code =
(select l.student_code
from school_table l, student_table n
where l.school = n.schoolname)
I get the following error ORA - 01427 Single-row subquery returns more than one row.
I have a table zTest with several products:
create table zTEST
( PRODUCT number,
ID number,
Flag number,
FLAG_L1 number );
[Code]...
The field FLAG_L1 is the field FLAG with a Lag 1 (order by ID, partition by PRODUCT). I want to write the field FLAG_L1 in an update statement, how can I do this?
I have two tables. By joining these two tables, I need to update a field in table1.
UPDATE table1
SET table1.FLAG = 'Fixed'
where table2.lastname = table1.lastname
and table2.status in ('fulltime','parttime')
I keep getting error 'table1.lastname' is invalid identifier.
I can't understand the error message. I made sure that the fields exist.
We had two tables.
Table 1: matusetrans
ITEMNUM Location Quantity transdate
AM1324 AM1 2 12-4-12
AM1324 AM1 2 15-5-12
AM1324 AM1 3 10-6-12
AM1324 AM1 4 5-1-13
[Code]....
Table 2: Inventory
ITEMNUM STORELOC lastyear currentyear
AM1324 AM1 need sum(quantity) here need sum(quantity)
AM1324 AM2 need sum(quantity) here need sum(quantity)
We have to update the last year and current year columns with sum of quantities for each item from matusetrans table based on date at different location in Inventory table.
we had nearly 13,000 records(itemnum's with different location) in inventory table in DB we have to update entire records.
How to write an sql queries to update lastyear and currentyear columns with sum of quantities based on itemnum and location in Inventory table
Edit/Delete Message
I have a below UPDATE STATEMENT and it is taking more than 1hr to complete. tune this to complete as quick as possible.
UPDATE A_TABLE A
SET FLG= 1
WHERE EXISTS (SELECT ID FROM B_TABLE B where A.ID=B.ID);
Count of the table A_TABLE --- 16,331,441
Count of the table B_TABLE --- 1,271,903
We have index on B.ID and A.ID
PROCEDURE split_name (
full_name IN VARCHAR2,
name1 OUT VARCHAR2,
name2 OUT VARCHAR2,
name3 OUT VARCHAR2,
name4 OUT VARCHAR2,
name5 OUT VARCHAR2
)
the above is the procedure declaration. i want to use this function in updating the records in a table. this procedure is working fine in forms and it is splitting the names perfectly and i dont know how to use this in update statement as it is not returning anything like functions do.
While updating a table, is it possible to get the old value of a column. I need to get it inside a PLSQL block and not in a trigger.for example :-
DECLARE
l_amountNUMBER(5) := 0;
BEGIN
UPDATECustTransactions
SETamount = 150
WHEREcust_id = 5412
RETURNING amount INTO l_amount;
DBMS_OUTPUT.PUT_LINE ( 'l_amount= ' || l_amount );
END;
By using the RETURNING clause i am getting the new updated value. I need the old value for some processing. Do i have to explicitly query it before the update stmt? Pls reply.
I am having trouble figuring out why an update statment is ignoring the primary kiy index when performing an update through the application. The index IS used when the update is run from sql*plus or other sql tools.
The statement is very simple:
update ITEM_MASTER set COST = :1 where SMARTPART_NUM = :2;
ITEM_MASTER has unique, primary key index on SMARTPART_NUM
When I use OEM and other tools, I can see the index is not used in the exlain plan, and the query has a high CPU cost due to the full table scan. The table is analyzed, lately using 100%. Table rowcount is 229768
SELECT column_name, num_distinct, num_buckets, histogram, trunc(last_analyzed) ANALYZED FROM USER_TAB_COL_STATISTICS
WHERE table_name = 'ITEM_MASTER' AND column_name = 'SMARTPART_NUM';
COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM ANALYZED
------------------------------ ------------ ----------- --------------- ---------
SMARTPART_NUM 198417 254 HEIGHT BALANCED 25-NOV-12
The database is Oracle Database 10g Release 10.2.0.4.0 - 64bit Production (on Windows, standard edition)
Here are parameters related to optimizer:
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.4
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
We've tried rebuilding the indexes then re-analyzing, dropping stats and locking at zero, using various sample sizes; none have any impact.
We wondered if the bind variables are causing this, though in testing outside the application, we tried pl/sql and sql batches to mimic the passing of values into the binds, and all still used the index.
Other than putting a hint into SQL in the application (we'd have to ask for a customization), how can I "fix" the database to use the index? In other words, how can I make the database see that the cost of a FTS is much higher than using the index, whenever it sees these updates (either from sql or the application)? This is the simplest example, but we have 4-5 SQL updates on this and other tables that are ignoring the indexes and using full table scans, so we'd like to fix it for all.
Using Oracle 11gr2. If I call the following update statement from session A, session A will lock the row until a commit/rollback statement is issued from session A. If session B calls the same update statement and same row, session B will have to wait until the lock is released in session A. However, the application developers are speaking in terms of threads. Could it be possible that the update statement is called within the same session by multiple requests? If yes, could the case statement be evaluated without the row being locked which could lead to false results? Trying to keep this post brief. tableA has columnA (primary key, number) and columnB (number)
{update tableAset columnB = case when columnB = 3 then 4 when columnB = 4 then 5 else columnB endwhere columnA = 6;}
Could 2 requests (almost at the exact same time) in the same session evaluate columnB as 3. The desired result would be the first request sets the column to 4 and the second request sets the column to 5.
i have two tables test1 and test2. i want to update the column(DEPT_DSCR) of both the tables TEST1 and TEST2 using select for update and current of...using cursor.
I have a code written as follows :
DECLARE
v_mydept1 TEST1.DEPT_CD%TYPE;
v_mydept2 TEST2.DEPT_CD%TYPE;
CURSOR C1 IS SELECT TEST1.DEPT_CD,TEST2.DEPT_CD FROM TEST1,TEST2 WHERE TEST1.DEPT_CD = TEST2.DEPT_CD AND TEST1.DEPT_CD = 'AA' FOR UPDATE OF TEST1.DEPT_DSCR,TEST2.DEPT_DSCR;
[code].......
The above code when run says that it runs successfully. But it does not updates the desired columns[DEPT_DSCR].
It only works when we want to update single or multiple columns of same table...i.e. by providing these columns after "FOR UPDATE OF"
I am not sure what is the exact problem when we want to update multiple columns of different tables.
I was trying to update the AGENTS table with the data based on queries. But all the records updates with the same data. I can't understand why.
Write a PL script to populate these columns as follows:
TRAVEL_STATUS is one of three values:
GLOBETROTTER: agent has visited five or more different locations in the course of his missions
ROVER: agent has visited between one and four locations
SLOB: agent has been on no missions.
CONTACTS is the number of targets that share the agent's home location.
some of the tables and columns are
agents table: agent_id, location_id
targets table: target_id, location_id
missions_agents table: agent_id, mission_id
missions_targets: target_id, mission_id
missions table: mission_id, location_id
My code is
DECLARE
status AGENTS.TRAVEL_STATUS%TYPE;
contact AGENTS.CONTACTS%TYPE;
CURSOR loc_cur
IS
SELECT
a.AGENT_ID id,
[code].....
I have two tables lets say TAB_A and TAB_B. I altered table B to include a new column from table A I wrote a merge statement as follows to merge the data
MERGE INTO TAB_AUSING TAB_BON (TAB_A.SECURITYPERSONKEY=TAB_B.SECURITYPERSONKEY)WHEN MATCHED THEN UPDATE SET TAB_A.PPYACCOUNT=TAB_B.PPYACCOUNT;
I know INSERT is for inserting new records UPDATE to my knowledge is to modify currently existing records (loosely) MERGE is one I rarely used, until this particular scenario. The code works perfectly fine, but I was wondering how could I write an update statement? Or in this scenario should I even be using an update statement?
I have a table called test_renames that has two columns(new_name and old_name) and one row with the following values
NEW_NAME OLD_NAME
DRUG10 DRUG1
I have another table called SH0 that has 10,000 rows and the column of interest is called ITEM1. ITEM 1 contains the following distinct drug names:
DRUG1,DRUG2,DRUG3,DRUG4,DRUG5
Each DRUG has multiple rows associated with it.
I need to only update rows for DRUG1 and set the name from DRUG1 to DRUG10 for all rows where ITEM1 is DRUG1.
However I need to write this update statement by joining with the values in the test_renames table that contains the NEW_NAME and the OLD_NAME.
DRUG1 has 2,000 rows associated with it. The following SQL statement gives me 2,000 rows
(SELECT NVL(CHG.NEW_NAME, SH.ITEM1)
FROM test_renames CHG,sh0 sh
WHERE UPPER(SH.ITEM1) = UPPER(CHG.OLD_NAME)
AND UPPER(SH.ITEM1) IN (SELECT UPPER(OLD_NAME) from test_renames))
However when I run the following update statement, it appears that the SQL query updates all the 10,000 rows in the table instead of just touching the 2,000 rows for DRUG1. It does change all the rows for DRUG1 to DRUG10, but all the other ITEM1 values become null. What I need from the update statement is only chnage the rows for DRUG1 to DRUG10 and leave the other rows that I associated with DRUG2, DRUG3 etc. Here is my update statement.
UPDATE SH0 SH
SET ITEM1 = (SELECT NVL(CHG.NEW_NAME, SH.ITEM1)
FROM test_renames CHG
WHERE UPPER(SH.ITEM1) = UPPER(CHG.OLD_NAME)
AND UPPER(SH.ITEM1) IN (SELECT UPPER(OLD_NAME) from test_renames))
This update statement touches all 10,000 rows. I am looking to update only the relevant 2,000 rows for DRUG1.
I try to apply update statement on the button for the specific records, the command does executes if in the end i write execute_query it always asks for do u wanna save the records, yes or no. I want to remove that message. Also if i write commit it does remove that message but everytime i press the button it updates the old record fine but also adds one more entry for the same record.
View 5 Replies View RelatedI have a table TEMP_TEST_1 to be loaded with multiple rows which contains the available items for a store with their sequence numbers. I also have another table TEMP_INV_1 which holds a column for inventory count of the items, and this column is to be updated after loading the first table TEMP_TEST_1.
The table TEMP_TEST_1 is updated through all possible channels in OLTP. So while updating the inventory, which is the best way. My update should either add the total number of inserts per item into the TEMP_TEST_1 table's INVENTORY_CNT column i.e. existing value + count of new rows OR a full update by taking the complete row count (Grouped by item id) and update the INVENTORY_CNT column
--------------------------------------------------------------------------------
-- Initial setup SQL's, TEMP_TEST_1 is updates on OLTP through multiple channels
--------------------------------------------------------------------------------
CREATE TABLE TEMP_TEST_1 ( ITEMID NUMBER ( 4 ),
INVENTORY_CNT NUMBER ( 2 ) );
CREATE TABLE TEMP_INV_1 ( ITEMID NUMBER ( 4 ),
ITEMSEQ NUMBER ( 5 ) );
INSERT INTO
[code].......
--------------------------------------------------------------------------------
-- Inserts for new items in the store
--------------------------------------------------------------------------------
INSERT INTO
TEMP_INV_1
VALUES
( 1,
11 );
INSERT INTO
TEMP_INV_1
[code].......
I am issuing an update statement in which I am using multiple tables it is giving me an error " set keyword missing"
update E_CONT_DETAIL_NUMB_VALUE ecdnv, y_obj_category yoc, t_contact tc
set ecdnv.ContTPRecCount = 1000
where tc.default_category_id = (select primary_key from y_ojb_category where tree_position = 'CONT')
and ecdnv.detail_field_id=tc.default_category_id;
update E_CONT_DETAIL_NUMB_VALUE ecdnv, y_obj_category yoc, t_contact tc
*
ERROR at line 1:
ORA-00971: missing SET keyword