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
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 RelatedThe 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
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)
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.
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].....
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.
How to delete even rows of a table or rather alternate rows of a table.
View 31 Replies View RelatedI 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 RelatedHave 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 RelatedI 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 .
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
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 .
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
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]....
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]...
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
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?
I am trying to delete the one month data from the table , which contains the end customer sales data.The total data count in the table is 30 crores. And the data of one month is nearly 10 Crores.I am using oracle 10g , The date field to be used in the condition of delete is indexed.
best way to delete such huge data count.
I have table of 60 gb(indexes 60gb ) and it is subject to fragmentation around 10gb .
I m going to remove fragmentation . As i know i have three options
1.expdp/impdp .
2.CTAS (create table ........as.......) with parallel option .
3.Moving table into another tablespace with parallel option .
I have 4 physical and 4 logical cores(total 8 cores) on server .
find out whether the rowmovement option in a partitioned table will cause fragmentation or not?
View 3 Replies View RelatedA query to find the table fragmentation.
View 1 Replies View RelatedI got the error ORA-01653: unable to extend table <OWNER.TABLE_NAME> by <BYTE> in tablespace <TABLESPACE> in my production database. But I could see 4GB of free space available in the tablespace. But this was resolved after increasing the Tablespace size by 1 more GB. So I wanted to know how I can I reclaim the 4GB space ?
While searching in internet I got few tips like there will be a fragmentation in the tablespace, the free space available in the tablespace may not be a continuous block. To avoid this we need to reorganize the tables using ALTER TABLE <TABLE_NAME> MOVE <TABLESPACE>; command.
But in my tablespace there are huge number of tables exists I cannot do reorganization of all the tables. So I need to know how to identify particularly what are the tables has more fragmentation? so that I can go for reorganizing those tables only.
My Database version in 9.2.0.7
Tablespaces are Locally Managed
To remove fragmentation which is the best method.
First one :
-----------------
1)Created a backup table from the Fragmented table (This table is a partitioned one).
2)Analyzed this table.
3)DROP the Fragmented table
4)Inserted the backuped up data from backup table to the Re-created table.
5)Analyze this table.
Second method
-------------------
1) Create a backup table newly, with PCTFREE =0
2) Inserted the data from Fragmented table ( This is a partitioned table) to backup table
3) Analyzed this table.
4) Truncate Fragmented table
4) Did Exchange partition of Fragmented table with Backup table.
The second method is not found to be removing the Fragmentation. Before the fragmentation was 28% after Second method the fragmentation is still the same. While the first method the fragmentation reduced to 16%.
Query used to find Fragmentation.
select table_name,
round((blocks*8),2) "table size kb",
round((num_rows*avg_row_len/1024),2) "actual data in table kb",
round((blocks*8),2)- round((num_rows*avg_row_len/1024),2) "wasted space kb",
[code]...
I am having a requirement like below,
Scene 1:
If duplicate records found for SSN,BWE with SAME DATE_CREATED than take the record with HIGHEST DATE_MODIFIED.
Table Structure:
SSN BWE DATE_CREATE DATE_MODIFIED
123 01-JAN-2008 02-JAN-2009 03-JAN-2014
123 01-JAN-2008 02-JAN-2009 03-JAN-2013
Output needed:
SSN BWE DATE_CREATE DATE_MODIFIED
123 01-JAN-2008 02-JAN-2009 03-JAN-2014
Scene 2:
If duplicate records found for SSN,BWE with different DATE_CREATED than take the record with HIGHEST DATE_CREATED.
Table Structure:
SSN BWE DATE_CREATE DATE_MODIFIED
123 01-JAN-2008 02-JAN-2009 03-JAN-2014
123 01-JAN-2008 04-JAN-2009 03-JAN-2013
Output needed:
SSN BWE DATE_CREATE DATE_MODIFIED
123 01-JAN-2008 04-JAN-2009 03-JAN-2013
How to achieve this requirement. My source data has 25 million of records like this.
i have a problem to view tens of millions of data in oracle 11g i have table Book_Issue that have tens of millions of data but when i execute query to see all the data select * from Book_Issue
it only view 5.000 data
what query should i execute to view million data in oracle 11g
Does "Update on a Partitioned Column" cause fragmentation ?
See example below :
Suppose I have a table as below that has approx 3 million rows and growing :
1) Table ABC :
file_id number
status_flag varchar2(1)
file_Content clob
date_created date
2) This table is list partitioned on Column "status_flag".
Column "status_flag" can take 3 values "A" or "B" or "C"
3) A file_id can transition from 1 "Status_flag" to another. [ A--> B , C--> B, B-->C, A --> C, etc ]
This means its possible to UPDATE the "status_flag " - the partitioned column.
Question :
-------------
1) Would the movement of rows from 1 partition to another cause fragmentation ?
2) Or Instead, should this be achieved by maintaining 3 tables - 1 each for every "status_Flag" as Table_A, Table_B, Table_C
This would mean that, if file_id 1, changes "status_flag" from 'A' to 'B' :
-- The corresponding row from Table_A will be created in Table_B
--- The same will be deleted from Table_A
This would still cause fragmentation ... with the overhead of Inserting a CLOB column from 1 table to another.
3) How to determine how much percent of the table is fragmented ?
Oracle DB version : 10.2.0.5.0
OS version HP-UX B.11.31 U ia64
DB size : 2TB
We have the above configuration for one of our oracle database. One of our DBA acts like he is only the person on this earth who is managing the process of fragmentation removal from the tablespaces in order to improve the performance and wastage of the space. He performs that task at weekends and takes one-off day extra. I am not sure how the fragmentation removal improves the performance and deallocates the space.
Is it compulsory to perform the rebuild process weekly in order to remove fragmented space from tablespaces? Do we have any other method to automatically re-organize objects occupying waste space?
Why the reuild of indexes using separate tablespace improves performance? is there any specifi reason for it?
There is a table in Database with millions of records and a query --- Select rowid, ANI, DNIS, message from tbl_sms_talkies where rownum<=:"SYS_B_0" ---- using the high CPU and also this query having high number of executions.
View 10 Replies View Relatedwhich is the fast way of inserting 60 millions of records from a view to a table.
method 1:
create table t_temp_table as select * from v_dump_data;
method 2:
through bulk collect
---Bulk_Collect With FORALL----------
DECLARE
TYPE srvc_tab IS TABLE OF t_temp_table%ROWTYPE;
l_srvc_tab srvc_tab := srvc_tab();
l_start_time NUMBER;
l_end_time NUMBER;
l_error_count NUMBER;
[code].....