Skip to main content

10 Million records manipulation in Oracle DB

Say you are given a task to manipulate a set of 10 million records from a couple of tables.
What would you do? Sit and think???!!!!

See if the following helps you..

Replicate the tables data into temporary tables
Create indexes on the columns you want to manipulate the data
Write your query..

Dont ask me what language you are talking!!!!
Look at the examples

Select count(1) from hugeTable1;--7999900 records
Select count(1) from hugeTable2; --9893990

Create table tmp1
as
Select *
from hugeTable1
where 1=2;

insert /*+append*/ into tmp1
select * from hugeTable1;

Create table tmp2
as
Select *
from hugeTable2
Where 1=2;

insert /*+append*/ into tmp2
select * from hugeTable2;

create index indTmp1 on tmp1(column1,column2);
create index indTmp2 on tmp2(column1,column2);

Select *
from tmp1,tmp2
where tmp1.column1=tmp2.column1 and
          tmp1.column2=tmp2.column2;
   

Should you face any issues, please write to me ( susilaarya@gmail.com )

Comments

Post a Comment

Popular posts from this blog

Editor's Column - Traditional DBMS

Editor's column There were days when Rooms-sized processors used for processing data. IT boomed up at the start of 21st Century and so many innumerable inventions made Computer world and few among them are still up in the markets for thier better implemented programming Alogrithms which couples with computer hardware and software ( OS ). Textpads or notepads were the repository of data and programmers had to be Experts to do data manipulation. In other words, Programmers had to use thier own logic to do insert , delete, update in data source. For instance, Consider the following: file1.txt: 1 I am line number 1 2 I am line number 2 3 I am line number 3 4 I am line number 4 5 I am line number 5 6 I am line number 6 7 I am line number 7 8 I am line number 8 9 I am line number 9 10 I am line number 10 To programmatically delete 8 th record , developer had to use the following logic with OLD file handling methods in 3rd generation language. 1. Open file1.txt, an...
Post a Comment
Read more

PLSQL programming ( Learn by examples - Part 3 )

/** In this section , lets learn the following. How to use a for loop to process multiple records... **/ set serveroutput on size 100000 begin --Select objects of table type that are valid under the schema Susil for var in (Select * from all_objects where object_name not like '%$%' and object_name in ('EMPLOYEE','EMP') and status='VALID' and owner='SUSIL') loop if var.object_type='TABLE' then /* Dbms_metadata package provides us a function get_ddl to get the data definition on object. For your understanding , i have just chosen two tables in this example If you remove Object_name condition in the above SQL , it will print DDL definitions for all tables under the specified schema **/ dbms_output.put_line('====================================================================='); dbms_output.put_line('Table name =>'||var.objec...
Post a Comment
Read more

Identify current and prev details of a customer

  create table customer_rating( id char, name char, irrating char, jcrrating char, procDate date, flag char ); insert into customer_rating values('A','D','X','Y','28-Jul-21','M'); insert into customer_rating values('A','D','M','L','27-Jul-21','M'); Select cr.id, cr.name, cr_prev.irrating pcob_rating, cr.irrating cob_rating, cr_prev.jcrrating pcob_jcr_rating, cr.jcrrating cob_jcr_rating From Customer_Rating cr join Customer_Rating cr_Prev  ON cr.procdate = '28-Jul-21' AND cr_prev.id = cr.id AND cr_prev.procdate = (  Select max(procdate) from Customer_Rating  Where id=cr.id and procdate < cr.procdate ) ;
Post a Comment
Read more