PL/SQL :: How To Improve DELETE Statement That Remove Millions Of Rows
Jun 18, 2012
The following query take lot of time when exectued, even after I drop the indexes, is there a better way to write the following query?
DELETE from pwr_part
where ft_src_ref_id in (select ft_src_ref_id
from pwr_purge_ft);
--Table:pwr_part
--UIP10371 foreign key (FT_SRC_REF_ID, FT_DTL_SEQ)
[Code]...
Explain Plan:
Description Object owner Object name Cost Cardinality Bytes
SELECT STATEMENT, GOAL = ALL_ROWS 224993 5492829 395483688
HASH JOIN RIGHT SEMI 224993 5492829 395483688
INDEX FAST FULL SCAN PWR_OWNER PWR_PURGE_FT_PK 43102 23803770 142822620
PARTITION HASH ALL 60942 27156200 1792309200
TABLE ACCESS FULL PWR_OWNER PWR_PART 60942 27156200 1792309200
View 6 Replies
ADVERTISEMENT
Dec 14, 2007
I joined the forum just today, i need some tips on deleting the millions of rows from a huge table having 25 millions of rows.
View 4 Replies
View Related
Mar 18, 2013
i have deleted 20 lak rows from a table which has 30 lak rows,
and i wanted to release the fragmented space, what is the procedure other than exp/imp or alter table move
and also the recommended way to do this prod env... (coalesce /alter move etc.. )
db version is 11.2
View 7 Replies
View Related
Aug 6, 2011
INSERT /*+ APPEND */
INTO sales_fact
SELECT *
FROM customer_sales_fact_staging
ORDER BY time_wh_id;
COMMIT;
[URL]
Suppose in the above case
SELECT *
FROM customer_sales_fact_staging
ORDER BY time_wh_id;
returns a huge number say more than 10 million or 50 million
will the commit work(one single transaction)
View 1 Replies
View Related
Nov 9, 2011
I have a table which has plenty of rows. In production, I would estimate it to be from 30 millions to 300 millions. I need to update on column (flag) in all the rows (created before certain date).Now saying just:
UPDATE MyTable SET flag = 3 WHERE created < to_date('2010-10-08 23:59:59', 'YY-MM-DD HH24:MI:SS');
COMMIT;
Does not seem like a good idea - the commit-buffer would become too big.I will write a PL/SQL script for this. The question is, whether I should:
a) Update each row separately, and commit after every 10000 rows. ( WHERE RowId = [rowId] )
b) Update 10000 rows with set of dates ( WHERE rowId > [some_row_id] AND RowId < [some_row_id_2]
In the latter example the some_row_ids would naturally be fetched. The rowIds come from sequence. So which one would be more effective?I am not too familiar with PL/SQL or Oracle for that matter.
View 2 Replies
View Related
Jan 11, 2012
I am using JDBC to run a few queries from my Java program (multi-threaded one).I am facing an issue where a select statement is blocking a delete statement. From the java code point of view, there are 2 different threads accessing the same tables (whith different DB connection objects).
When the block occurs (which i was able to find out from the java thread dump that there is a lock on oracle), the below is the output:
SQL> SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')
2 || ' User '||s1.username || '@' || s1.machine
3 || ' ( SID= ' || s1.sid || ' ) with the statement: ' || sqlt2.sql_text
||' is blocking the SQL statement on '|| s2.username || '@'
4 5 || s2.machine || ' ( SID=' || s2.sid || ' ) blocked SQL -> '
6 ||sqlt1.sql_text AS blocking_status FROM v$lock l1, v$session s1, v$lock l2 ,
7 v$session s2,v$sql sqlt1, v$sql sqlt2
8 WHERE s1.sid =l1.sid
9 AND s2.sid =l2.sid AND sqlt1.sql_id= s2.sql_id
AND sqlt2.sql_id= s1.prev_sql_id AND l1.BLOCK =1
10 AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2;
[code]...
From the above it can be seen that a select statement is blocking a delete. Unless the select is select for Update, it should not block other statements is not it ?
View 10 Replies
View Related
Oct 15, 2008
I have a file name field in my database that stores each file name with the extension .TXT and almost each file name is different.I would like to remove this extension from all of the file names without using the different file name each time I update. Is there any SQL statement that will allow me to do this? I am using Oracle.
View 1 Replies
View Related
Apr 8, 2010
I have a sql like
select TRIM(column_name)
from user_tab_columns
where table_name = 'MTL_SYSTEM_ITEMS_VL' AND COLUMN_NAME IN ('DESCRIPTION ');
[Code]....
Like this i have many rows. What clause other than trim i should apply to no cosider the spaces?
View 27 Replies
View Related
Sep 9, 2013
I have a simple column in a table which store a description. This can include line breaks so when you select from it, it looks similar to below
'Part number: X
Batch: Y
Size: Z'
I would like to be able to create a view (so not having any impact on the original data) to return it as below
'Part number: X Batch: Y Size: Z'
View 3 Replies
View Related
May 8, 2013
how to remove duplicate rows from table?
View 6 Replies
View Related
Nov 16, 2011
I have a table table1 with 2 crore records.
select * from table1;
id code updateddatetime
------------------------------------
1 10001 2011-10-21 15:31:21.390
2 10001 2011-10-21 15:31:22.390
3 10001 2011-10-21 15:31:21.390
4 10001 2011-10-21 15:31:22.390
5 10002 2011-10-21 15:31:22.390
I want to delete records like id 2 which has odd updated time which is more than id 3 updated time.
is there any alternatives without using cursors as it taking so much time to process.
View 12 Replies
View Related
Jun 14, 2011
I have a view and in that view i need to remove duplicate rows from output. For that i need to run select query in where clause of view if select query return true then we need to execute second condition.
my requirement in view like
And..........
And ((select count(*) from table A where conditions)=1 )then name is null
AND
in that code first we need to check first select query condition then we need to apply name is null condition. but i tried to run it but select query not run properly. because tables is used in View.
View 4 Replies
View Related
May 31, 2011
I have two tables one source table and one destination . Column names and data types of both table are same.
source table (source)
ID NAME
1 aa
2 bb
3 cc
. ...
. ..
. ..
destination Table(dest)
ID NAME
1 aa
2 bb
I need destination table like
destination table
ID NAME
1 aa
2 bb
3 cc
. .....
. ....
I want to remove repeated rows
View 2 Replies
View Related
Jun 3, 2011
I am running the following delete query and it has been running for over 2hrs:
delete from dw.ACCOUNT_FACT
where rowid in
(select rowid from DW.ACCOUNT_FACT
minus
select max(rowid) from DW.ACCOUNT_FACT
[Code]..
Here is the explan plain result:
explain plan for delete from dw.ACCOUNT_FACT
where rowid in
(select rowid from DW.ACCOUNT_FACT
minus
select max(rowid) from DW.ACCOUNT_FACT
group by CRTORD_FIPS_CD, LAST_PAYMENT_DT, ORDER_NUM,
[Code]....
PLAN_TABLE_OUTPUT
Plan hash value: 611392786
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 2604G| 260T| | 9018K (91)| 30:03:37 |
| 1 | DELETE | ACCOUNT_FACT | | | | | |
|* 2 | HASH JOIN | | 2604G| 260T| 369M|
[Code].....
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(ROWID="$kkqu_col_1")
I have all constraints disabled. How do I make this delete finish faster? We're trying to remove duplicates from this table using the criteria giving in the statement.
View 16 Replies
View Related
Mar 14, 2011
I am trying to run following sql query,but it is throwing following error.
SQL> delete from b$gc_count_temp a INNER JOIN COMPLEMENTS ON b$gc_count_temp.CON
NECTION_ID_TEMP=COMPLEMENTS.feature_conn_id
2 WHERE a.current_designation is null and a.current_low is null and a.current
_high is null;
delete from b$gc_count_temp a INNER JOIN COMPLEMENTS ON b$gc_count_temp.CONNECTI
ON_ID_TEMP=COMPLEMENTS.feature_conn_id
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
View 4 Replies
View Related
Sep 6, 2012
I've small doubt regarding the subqueries in delete statements. observe the below statements with their results.
SQL> alter table emp3 rename column deptno to deptid;
Table altered.
SQL> select deptid from dept;
select deptid from dept
*
ERROR at line 1:
ORA-00904: "DEPTID": invalid identifier
[code]...
but when you use same subquery in update or select stmt it throws 'invalid identifier' or similar error.Why same does not happen with delete stmt ?
View 6 Replies
View Related
Jan 23, 2009
I am getting ORA-00933 after running below mentioned delete statement;
DELETE FROM REPOSITORY.MEDIASEGMENT MS
INNER JOIN REPOSITORY.ROUTINGEVENT RE ON TRIM(MS.Segment_Key) = TRIM(RE.uuid)
INNER JOIN REPOSITORY.TEMPCONTACT TC ON TRIM(RE.Contact_Key) = TRIM(TC.vduid)
WHERE TC.CREATETIME BETWEEN (TO_DATE('04/24/2008 00:00:00','MM/DD/YYYY, HH24:MI:SS')) AND
(TO_DATE('04/30/2008 23:59:00','MM/DD/YYYY, HH24:MI:SS'))
View 2 Replies
View Related
Mar 14, 2013
I was reading about merge statement and tried some variations,
create table MERGE_TEST(
C1 number,
C2 varchar2(10 char),
c3 number);
insert into MERGE_TEST values(1, 'Name 3', 300);
insert into MERGE_TEST values(1, 'Name 2', 200);
insert into MERGE_TEST values(1, 'Name 1', 100);
commit;
[code]...
why is result different in this querys?
View 14 Replies
View Related
Feb 26, 2013
I have a session on a system here that has been stuck on a DELETE statement for a very long time and the session is pegging the CPU. Using TOAD here is the "current statement":
DELETE FROM WORKFLOW
WHERE ID = :B1
ID is the primary key of the table.Here are some relevant stats, also from TOAD's session browser:
Elapsed time = 35507986900
CPU time = 35531815481
Buffer gets = 972040769
Disk reads = 951289273
Executions = 71462
I'm not sure I understand "executions" because from the information I have from the people who initiated this, this particular delete should only be occurring 30 times... maybe that stat means something other than what I think it does.I also ran a trace for 30 seconds using:
CODESQL> begin dbms_monitor.session_trace_enable(session_id=>97, serial_num=>15, wai
ts=>true, binds=>true); end;
2
3 /
PL/SQL procedure successfully completed.
SQL> begin dbms_monitor.session_trace_disable(session_id=>97, serial_num=>15); e
nd;
2 /
PL/SQL procedure successfully completed.
I ran tkprof over the resulting trc file:
CODE[root@localhost trace]# /oradb/devmain/product/11.2.0/dbhome_1/bin/tkprof ltw35qa1_ora_19558.trc
output = delete_scattered2.txt
TKPROF: Release 11.2.0.1.0 - Development on Wed Feb 27 04:04:50 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
[root@localhost trace]#
The resulting file is attached. Now the offending query in the tkprof, based on my interpretation, is the select from the CMTE00 table, which contains 2344482 rows and no index on the workflow_id column. The relationship between CMTE00 and WORKFLOW tables are 1 to 1. There is a foreign key on CMTE00 pointing to the primary key of WORKFLOW which is what I assume initiated this query - I assume this is oracle checking the referential integrity since our code is not executing that statement. Also of interest, prior to this delete statement, the corresponding entry in CMTE00 was deleted in the same transaction. Google searching "db scattered file read" lead me to one of your (Don - if you read this) articles and appears to indicate that individual blocks are being fetched off the disk and this is what is taking up all the time.
View 14 Replies
View Related
Sep 7, 2010
I executed the following delete statement.
DELETE FROM sre_t WHERE TO_CHAR(end_dt,'yyyy')<'2000'
or TO_CHAR(start_dt)<'yyyy')<'2000';
It's executing for 15 to 20 minutes after that i got the error "session timed out"..The table is having four crore records.The delete statement is deleting 12,00000 records.
View 4 Replies
View Related
Feb 6, 2011
I having a problem with duplicate rows with a query.
What I have so far is
SELECT D.Student_Id,
E.Latest_Reg_Date,
E.Attendance_Count,
[Code].....
View 1 Replies
View Related
Oct 19, 2011
I am using the below proc to delete some records
1)select client_id,count(*) from TCLIENT_NOTIFICATION_PACK where client_id=1620560178 group by client_id order by 2 desc;
client_id count(*)
----------- ---------
16205601785128
2)select client_id, count(*) from TCLIENT_NOTIFICATION_PACK
where client_id=1620560178
group by client_id
having count(*) > 40
order by 2 desc
client_id count(*)
----------- ---------
16205601785128
3) select client_id,clnt_notification_pack_tid
-- bulk collect into v_client_id,v_notif_tid
from (select clnt_notification_pack_tid,
client_id,
clnt_notification_pack_typ_tid,
crte_dt,
[code]....
4) Iam using the below proc to delete the rows from table, except the 4 rows returned above
declare
v_clnt_notification_pack_tid TCLIENT_NOTIFICATION_PACK.CLNT_NOTIFICATION_PACK_TID%type;
tYPE t_client_id is table of TCLIENT_NOTIFICATION_PACK.client_id%type;
tYPE t_notif_tid is table of TCLIENT_NOTIFICATION_PACK.clnt_notification_pack_tid%type;
v_client_id t_client_id;
v_notif_tid t_notif_tid;
[code]....
5) After running this procedure, i shud see 5124 records, but i see zero records.
View 23 Replies
View Related
Oct 25, 2006
How to delete even rows of a table or rather alternate rows of a table.
View 31 Replies
View Related
Jun 13, 2011
I have a table, in that i have "n" no of rows from that i want to delete 1% of rows from that table
View 26 Replies
View Related
Jan 4, 2013
Have a table which has 3 columns id,name,time where time is of datatype timestamp and it stores the time when the row was inserted. Need an query which accepts 2 parameters as input Ex: Start_Time,End_Time and all the rows in between the above mentioned timestamps must be deleted.
View 8 Replies
View Related
Jan 12, 2011
If i inserted the values in table it gets inserting very few rows only.I dont know y it is?
View 15 Replies
View Related
Apr 15, 2011
Test1 table have around 385772300 rows. below delete and select statment talking lot of time.
Select stament taking more than 1 hrs.
SELECT TO_NUMBER(MAX(f.T3))
--INTO v_FISCAL_MONTH_ID
FROM Test1 f;
delete statment taking more than 2 hours
DELETE FROM TEST1 WHERE TRUNC(T10) < TRUNC(ADD_MONTHS(SYSDATE,-36));
CREATE TABLE Test1
(
[Code].....
View 4 Replies
View Related
Nov 8, 2010
I was asked in a telephone interview about committing a delete ( a few million rows)in an oracle log table which had a trillion rows in total. He said that delete took 2 days.
Then he asked me if then commit is performed(assuming a huge rollback segments are allocated) how long does it take for that commit .
View 5 Replies
View Related
Aug 28, 2007
The following script doesnt work.
DELETE
FROM PSPROJECTITEM I
WHERE I.PROJECTNAME = 'TEMP1'
and (I.OBJECTTYPE between 79 and 84
or (I.OBJECTTYPE = 58
and I.OBJECTID1 = 104
[code]...
It gives the following error:
Error at Command Line:12 Column:6
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:
View 7 Replies
View Related
Mar 9, 2010
In my code I am using delete statement which is taking too much time to execute.
Statement is as follow:
DELETE FROM TRADE_ORDER_EMP_ALLOCATION T
WHERE (ARTEMIS_SOURCE_SYSTEM_ID,NM_ARTEMIS_SOURCE_SYSTEM,CD_BOOK_KEY,ACTIVITY_DT)
IN (SELECT ARTEMIS_SOURCE_SYSTEM_ID,NM_ARTEMIS_SOURCE_SYSTEM,CD_BOOK_KEY,ACTIVITY_DT
FROM LOAD_TRADE_ORDER
WHERE IND_IS_BAD_RECORD='N');
Tables Used:
oTRADE_ORDER_EMP_ALLOCATION Row count (329525880)
oLOAD_TRADE_ORDER Row count (29281)
Every column in "IN" clause and select clause is containing index on it
Every time no of rows which to be deleted is vary (May be in hundred ,thousand or hundred thousand )so that I am Unable to use "BITMAP" index on the table "LOAD_TRADE_ORDER" column "IND_IS_BAD_RECORD" though it is containing distinct record in it.
Even table "TRADE_ORDER_EMP_ALLOCATION" is containing "RANGE" PARTITION over it on the column "ARTEMIS_SOURCE_SYSTEM_ID". With this I am enclosing table scripts with Indexes and Partitions over it.
way for fast execution in of above delete statement?
View 4 Replies
View Related
