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

SQL to return records in m,n sequence

I want my SQL to return only records from 101 to 200. See below options which would do the required.. Select * from (Select rownum rownumber,employee.* from employee) Where rownumber between 101 and 200; -- Using rownum label in oracle Select * from (Select employee.*, row_number() over (order by emp_id desc) Rownumber from employee) where Rownumber between 101 and 200; --Using the analytical function row_number Select * from (Select employee.*, row_number() over (order by emp_id desc) Rownumber from employee) where Rownumber between :Fromnumber and :tonumber; -- lets user decide the recordset chunk... Hope this is helpful!!!!!!
Post a Comment
Read more

PLSQL programming - Part 1

Pre-requisite : Basic SQL Knowledge in Oracle and programming knowledge in any language Browse my blog to read the overview of PLSQL if you need it. Anonymous block ............. ........... This is where you declare Variables that you are going to use it in PLSQL block ............ Data types are chosen based on requirement. For example, if you want to process strings you go for data types of characters and for numeric operations Int, float , double or number. Begins ..... ......... ......... .......... This is where you implement programming logic and get the desired outcome. .......... .......... Ends..... We call it anonymous block because we don't name it or reference it. (Unlike Package, Stored procedure,& Other objects) Learn from examples Example 1: declare var1_num number; --Declaring number type variable var2_str varchar2; begin var1_num := 10; --Assigning 'Number' Value to the variable var2_str := 'I am a string value...
1 comment
Read more

What happens when you create tables with the existing SYNONYM names

See below snippets: Select * from dba_synonyms where synonym_name='DUAL'; OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK PUBLIC DUAL SYS DUAL (NULL) We know that DUAL table is accessible by users. Lets create a table named DUAL under our schema (USER). CREATE TABLE dual AS SELECT * FROM dual; Select * from dual; insert into dual values('Y'); commit; Select * from dual; DUMMY ---------- X Y The Synonym is not referred unless the Synonym name is not used by any object under the USER/SCHEMA... So , When creating tables in your schema, take a look at SYNONYMs declared as PUBLIC and try not to use them.
1 comment
Read more