PL/SQL :: Delete Data Not Required Retaining Rows

Mar 18, 2013

CREATE TABLE "TEST_JET" ("K1" NUMBER, "K2" NUMBER, "K3" NUMBER, "K4" VARCHAR2(1)) ;
REM INSERTING into TEST_JET
Insert into TEST_JET (K1,K2,K3,K4) values (1,2,3,'I');
Insert into TEST_JET (K1,K2,K3,K4) values (1,2,3,'U');
Insert into TEST_JET (K1,K2,K3,K4) values (1,2,3,'D');
Insert into TEST_JET (K1,K2,K3,K4) values (1,2,2,'U');
Insert into TEST_JET (K1,K2,K3,K4) values (1,2,2,'D');
Insert into TEST_JET (K1,K2,K3,K4) values (1,3,5,'I');
Insert into TEST_JET (K1,K2,K3,K4) values (1,6,7,'U');
Insert into TEST_JET (K1,K2,K3,K4) values (1,6,7,'D');
Insert into TEST_JET (K1,K2,K3,K4) values (1,6,7,'T');
[code]....

based on the above result set , for a particular group ,only that op will be retained which comes out in the query . say for example , we have got 1,2,3,'D' for group 1,2,3

now since we have got the D Operation from the above query , i don't need the other two rows .i.e.
(1,2,3,'I');
(1,2,3,'U');

what is the best way to delete the data we don't want retaining the rows we want ,using a single sql statement . Also , for the result set row 7,7,7,T I first need to delete the group containing T operation, and insert two new rows .i.e. 7,7,7,D and 7,7,7,I .

View 14 Replies


ADVERTISEMENT

Massive Delete (2 Joins Required) With No Partitioning

Oct 25, 2011

I'm experiencing some infinite loop for my delete. I tried many way to deal with this problem but still take too much time. I will try to be clear as possible.

I have 4 implicated table in this problem.

The deletion is done depending of the pool_id given

Table 1 contain the pool_id
Table 2 the ticket_id foreign join ticket_pool_id with the pool_id
Table 3 ticket_child_id foreign join ticket_id with the ticket_id
Table 4 ticket_grand_child_id foreign ticket_child_id join with the ticket_child_id

Concerned count for each
table 1---->1
table 2---->1 200 000
table 3---->6 300 000
table 4---->6 300 000

So in fact it`s 6.3M+6.3M+1.2M+1 row to be deleted

Here`s the constraint :
-No partintionning
-Oracle version 9
-Online all the time so no downtime neither CTAS
-We cannot use cascade constraint
-The normalization is very important

Here`s what I tried:
-Bulk delete
-Delete with statement (In and Exists clause)
-temp table for each level and 1 level join
-procedure and commit each 20k

None of those worked in a decent time frame like less then one hour. The fact that we cannot base a delete on one of the column value is not working. Is there a way I'm getting desperate now

View 3 Replies View Related

Delete Duplicate Rows?

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

SQL & PL/SQL :: How To Delete Rows From Table

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

SQL & PL/SQL :: Delete Even Rows Of A Table

Oct 25, 2006

How to delete even rows of a table or rather alternate rows of a table.

View 31 Replies View Related

SQL & PL/SQL :: How To Delete 1% Of Rows From A Table

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

PL/SQL :: Delete Rows Between Two Particular Timestamps

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

PL/SQL :: How To Display Required Data From Emp Table

Dec 26, 2012

This is my first post in this portal. I want display the details of emp table.. for that I am using this SQL statement.

select * from emp where mgr=nvl(:mgr,mgr);

when I give the input as 7698 it is displaying the corresponding records... and also when I won't give any input then it isdisplaying all the records except the mgr with null values.

1)I want to display all the records when I won't give any input including nulls
2)I want to display all the records who's mgr is null

Is there any way to incorporate to include all these in a single query..

View 9 Replies View Related

Large Rows Committing Delete

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

PL/SQL :: Delete Millions Of Rows And Fragmentation

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

SQL & PL/SQL :: Delete The Duplicate Rows In A Table Without Using ROWID?

Mar 1, 2010

I want to delete the duplicate rows in a table without using ROWID.

I have the following data.

SNO SNAME SECTION
1 RAM A
2 SHYAM B
2 SHYAM B
3 KISHOR C
3 KISHOR D
4 RAMESH E
5 RAJESH F
5 RAJESH F
The Output Should be like this.

SNO SNAME SECTION
1 RAM A
2 SHYAM B
3 KISHOR C
3 KISHOR D
4 RAMESH E
5 RAJESH F

View 8 Replies View Related

SQL & PL/SQL :: Display Data In Required Format By Select Query?

Dec 12, 2012

i like to display a Table data like the below format,

Output:

EMPNO JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
00094 122 153 145 224 245 545 114 544 444 111 555 222
00095 233 123 145 224 205 545 114 444 444 111 555 222
00096 163 123 145 224 215 545 114 551 444 111 555 222
00097 163 0 145 224 215 545 114 551 444 111 555 222
conditions:

where condition:
where year = 2007

Table Structure:

create table HR_PAYSLIP
(
EMP_NO VARCHAR2(6) not null,
YEAR NUMBER(4) not null,
MONTH NUMBER(2) not null,
BASIC_PAY NUMBER(9,2),
)

Insert Command;

INSERT INTO PAYSLIP (EMP_NO, YEAR,MONTH,BASIC_PAY)
VALUES(00046, 2007, 1, 2314);

Pls Note: The above table data i have mentioned is an example with employee numbers and the basic_pay for all months in the particular year 2007, the employee no may be more and that must be displayed only one time like above for year 2007, and if the basic salary is zero for a month then it should be displayed as zero for a particular month

So how to write a Query for that?

View 9 Replies View Related

Data Guard :: How Much Bandwidth Required For Active Setup

May 25, 2013

How much bandwidth required to setup Active data Guard? How to measure bandwidth requirement from primary site? Any ML or doc would be great.

View 1 Replies View Related

SQL & PL/SQL :: How To Delete Rows If Second Alphabet Is A Vowel In Specific Column

Jun 1, 2012

There are four columns as follows, but I need to delete those rows from the third column only where the second letter of the word appears as vowel. For example, I want to delete the rows having the words, 'Ramu' and 'Ravi' only.

A B C D
xx y Ramu xx
yu ut Ameer uui
rtt iw Ravi iwoow
fgsg isd Intel jjuiw

View 4 Replies View Related

SQL & PL/SQL :: Delete Rows Returned By Complex Join Query

Apr 5, 2013

How Can I delete the returned two rows?

1 select s.reg_no,s.course_code,
2 s.section src_sec,a.section a_sec,a.att_date,a.att_flag
3 from attendance a ,src s
4 where a.semester_code=1
5 and a.semester_year=2013
6 and s.semester_code=1
[code]....

View 6 Replies View Related

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 View Related

SQL & PL/SQL :: Delete Table1 Rows Where 3 Fields To Join With Another Table

Aug 13, 2011

I need to delete all the registers where the table 1 does join with table 2 in 3 fields... for example:

delete taba1 t1
where t1.campo1 in ( select distinct(tr.campo1)
from tabla1 tr,
tabla2 t2
where t2.error = 0
tr.campo1 = t2.campo1
and tr.campo2 = t2.campo2

[Code]...

View 4 Replies View Related

PL/SQL :: Compare Two Tables And Delete The Common Rows From Table 1

Jul 23, 2012

I want to compare two tables , and delete the common rows from the first table

Here is what i have done :

Create table test1(Test1C1 Number,
Test1C2 varchar2(50));

Create table test2(Test2C1 Number,
Test2C2 varchar2(50));

Insert into test1 values(1,'testdata1');
Insert into test1 values(2,'testdata2');
Insert into test1 values(3,'testdata3');
[code].......

it deletes all the records from Table test1. What should I modify here ? or should I write a different query ?

The desired contents in table test1 will be

2 testdata2
4 testdata4
6 testdata6
8 testdata8
10 testdata10

View 5 Replies View Related

Delete 4 Millions Of Rows From A Table Having 25 Millions Of Rows?

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

SQL & PL/SQL :: Arranging Data In Required Format Using Decode By Hardcoding All Values

Feb 26, 2012

I have a sample table where I am arranging data in required format using 'decode' by hardcoding all values, how can we do same using pivot method, can pivot be only used when we want aggregate values in oracle?Also can't we do what i did, using generic max(decode) with select query, if it were a large table (i don't know which would be pivot column here?) ?

Table is
SQL> CREATE TABLE NOWT
2 (COURSE VARCHAR2(20),MEMBER VARCHAR2(20));
[code]....

how can we do that using pivot in Oracle?

View 6 Replies View Related

SQL & PL/SQL :: Delete Last N Sets Of Rows Of A Table With Same Dates In Sort Field

Mar 15, 2013

I know how to select the last N sets of rows, using DENSE_RANK - where multiple rows have the same timestamp but I want to only select those rows which do NOT have the top 2 unique timestamps.

i.e.:

SELECT *
FROM ( SELECT DENSE_RANK() OVER (ORDER BY myTimestamp DESC) DENSE_RANK, HISTORYID, USER_ID, myTimestamp, STATUS, FROM TXN_HIST)
WHERE DENSE_RANK > 2 order by myTimestamp DESC, HISTORYID, USER_ID;

But how do I DELETE these same rows?

View 3 Replies View Related

SQL & PL/SQL :: Delete Previously Stored Data And Enter Only Current Data In A Table?

Jul 24, 2012

how to insert data in a table by deleting previous entered data and only inserting current data like:

CREATE TABLE test
(
name VARCHAR2(20),
id NUMBER
)
INSERT INTO test VALUES ('aaa',5500);

[code]....

I got two rows. now when I do insert statement I want to delete the previously stored data and only insert the current data like:

INSERT INTO test VALUES ('aaa',8);
INSERT INTO test VALUES ('aaa',9);

it must show aaa,8 and aaa,9 bt not the previous values.

NOTE: we can not do sth like: update set... where id = ... becoz the values are dynamic.

View 4 Replies View Related

SQL & PL/SQL :: Required Data For Month To Date And Year To Date?

Apr 30, 2012

I want to get data for month to date. For example, If I pass today or any day date as parameter then i should get data for that month(month of passing date) up to passing(parameter) date. As well as i have to get year to date.For example, If I pass today or any day date as parameter then i should get data for that financial year(year of passing date) up to passing(parameter) date. how to get month to date and year to date data.

View 3 Replies View Related

SQL & PL/SQL :: Query To Delete Two Weeks Old Data?

Sep 12, 2013

I want to delete the records grater than two weeks old.From Saturday to Friday we are considering as Week.

P_ID CREATED_DT
105 28/AUG/2013
106 29/AUG/2013
107 30/AUG/2013
108 31/AUG/2013
109 01/SEP/2013
110 02/SEP/2013

[code]....

From 28/AUG/2013 to 30/AUG/2013 records has to be deleted becuase thease records are two weeks old.

From 31/AUG/2013 to 06/SEP/2013 records belongs to one week and from 07/SEP/2013 to 13/SEP/2013 belongs to another week.Today is 12/SEP/2013 so from 06/SEP/2013 to 12/SEP/2013 should be existed in the table.Once date changed to 13/SEP/2013 then from 31/AUG/2013 to 06/SEP/2013 records has to be deleted.

I am posting table and insert scripts.

CREATE TABLE process_data(p_id NUMBER,CREATED_DT DATE);
INSERT INTO process_data VALUES(TO_DATE('28/AUG/2013','DD/MON/YYYY');
INSERT INTO process_data VALUES(TO_DATE('29/AUG/2013','DD/MON/YYYY');
INSERT INTO process_data VALUES(TO_DATE('30/AUG/2013','DD/MON/YYYY');
INSERT INTO process_data VALUES(TO_DATE('31/AUG/2013','DD/MON/YYYY');
INSERT INTO process_data VALUES(TO_DATE('01/SEP/2013','DD/MON/YYYY');

[code]....

View 3 Replies View Related

SQL & PL/SQL :: How To Delete Data Without Disable Constraints

Feb 14, 2013

I want to delete data from say 100 tables without disable the constraints.

Tables having foreign key.. which is giving error if I select table randomly to delete.

I want to know the query which will give me the sequence of all tables based upon dependencies, so that If i delete the data on that sequence, it wont give me child record exists error.

View 8 Replies View Related

PL/SQL :: How To Fast Delete Lot Of Data In A Table

Jan 9, 2013

I have a table with around 650,000,000 rows and we need to delete about 60,000,000 rows at the end every month and same amount of rows accumulate throughout the month. The deletion usually takes overnight to delete. We are using 10r2 in IBM AIX. The procedure we are using to delete is:

declare
ln_count number:=0;
begin
for i in (select rowid from table1 where some_id<2012090000)
loop
delete from table1

[code]...

When this procedure is started I mostly see that the session is busy in user i/o wait for db sequencial file read. Will using cursor instead will give better results.

View 13 Replies View Related

Oracle 9i - Delete Data One By One From Particular Table

Oct 28, 2008

I need to create a stored procedure in Oracle 9i which will automatically delete data one by one from a particular table and then by means of same procedure insert record one by one in same table.

View 6 Replies View Related

Write Simple Sql Which Would Delete Data From Last N Months?

Apr 20, 2009

am trying to write a simple sql which would delete data from last n months but it will keep the data for the first of each of those month from current sysdate

e.g

Jan 1 - 30 deletes 2 - 30 keeps data for 1st
Feb 1 - 28 deletes 2 - 28 keeps data for 1st
Mar
Apr Current sysdate
May

View 3 Replies View Related

Data Guard :: Delete Datafile In Primary?

May 26, 2012

I haved drop a datafile on Primary,and the archived log has transported to Standby,but the datafile is still exist in Standby.

Primary

alter tablespace tps_hxl
drop datafile '/u02/app/oracl/oradata/hxl10.dbf';

View 7 Replies View Related

Data Guard :: How To Delete Archive Log On Standby

Jun 18, 2011

We are planning to setup a data guard (Maximum performance configuration ) between two Oracle 9i databases on two different servers.

The archive logs on the primary servers are deleted via a RMAN job bases on a policy , just wondering how I should delete the archive logs that are shipped to the standby.

Is putting a cron job on the standby to delete archive logs that are say 2 days old the proper approach or is there a built in data guard option that would some how allow archive logs that are no longer needed or are two days old deleted automatically.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved