| schedule job for multi threading [message #668169] |
Sat, 10 February 2018 16:12  |
simplesanju
Messages: 36
Registered: July 2008
|
Member |
|
|
Hi ALL
I have requirement like
select COL1,Col2 from A
minus
Select COL1,COL2 from B;
both tables have 50+ millions record so I want to automate a job which will execute in loop(not in one go ) and store output in record type.
Thanks in advance
Sanjana
|
|
|
|
|
|
| Re: schedule job for multi threading [message #668175 is a reply to message #668169] |
Sun, 11 February 2018 08:23  |
Solomon Yakobson
Messages: 3244
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Are tables relatively stable and there is limited number of changes between your checks? If so, use fast refresh materialized view. This way it will be much faster since MV fast refresh will apply just the delta. I'll assume COL1 is string and COL2 is number:
CREATE MATERIALIZED VIEW LOG ON TBL_A WITH ROWID, SEQUENCE(COL1,COL2) INCLUDING NEW VALUES
/
CREATE MATERIALIZED VIEW LOG ON TBL_B WITH ROWID, SEQUENCE(COL1,COL2) INCLUDING NEW VALUES
/
CREATE MATERIALIZED VIEW TBL_A_MINUS_TBL_B_MV
REFRESH FAST
ON DEMAND
WITH ROWID
AS
SELECT TBL_A.ROWID RID_A,
TBL_A.COL1,
TBL_A.COL2,
TBL_B.ROWID RID_B,
TBL_B.COL1 COL1_B,
TBL_B.COL2 COL2_B
FROM TBL_A,
TBL_B
WHERE NVL(TBL_A.COL1,CHR(0)) = NVL(TBL_B.COL1(+),CHR(0))
AND NVL(TBL_A.COL2,TO_BINARY_FLOAT('INF')) = NVL(TBL_B.COL2(+),TO_BINARY_FLOAT('INF'))
AND TBL_B.ROWID IS NULL
/
For example:
SQL> SELECT ROWNUM,
2 COL1,
3 COL2
4 FROM TBL_A
5 /
ROWNUM COL1 COL2
---------- ---------- ----------
1 SMITH 800
2 ALLEN 1600
3 WARD 1250
4 JONES 2975
5 MARTIN 1250
6 BLAKE 2850
7 CLARK 2450
8 SCOTT 3000
9 KING 5000
10 TURNER 1500
11 ADAMS 1100
12 JAMES 950
13 FORD 3000
14 MILLER 1300
15
15 rows selected.
SQL> SELECT ROWNUM,
2 COL1,
3 COL2
4 FROM TBL_B
5 /
ROWNUM COL1 COL2
---------- ---------- ----------
1 SMITH 800
2 ALLEN 1600
3 WARD 1250
4 JONES 2975
5 MARTIN 1250
6 BLAKE 2850
7 SCOTT 3000
8 TURNER 1500
9 ADAMS 1100
10 JAMES 950
11 FORD 3000
12
12 rows selected.
SQL> SELECT ROWNUM,
2 COL1,
3 COL2
4 FROM TBL_A_MINUS_TBL_B_MV
5 /
ROWNUM COL1 COL2
---------- ---------- ----------
1 KING 5000
2 MILLER 1300
3 CLARK 2450
SQL> DELETE TBL_B
2 WHERE COL1 IN (
3 'WARD',
4 'TURNER',
5 'ADAMS'
6 )
7 OR COL1 IS NULL
8 /
4 rows deleted.
SQL> EXEC DBMS_MVIEW.REFRESH('TBL_A_MINUS_TBL_B_MV','F')
PL/SQL procedure successfully completed.
SQL> SELECT ROWNUM,
2 COL1,
3 COL2
4 FROM TBL_A_MINUS_TBL_B_MV
5 /
ROWNUM COL1 COL2
---------- ---------- ----------
1 KING 5000
2 MILLER 1300
3 CLARK 2450
4 WARD 1250
5 ADAMS 1100
6
7 TURNER 1500
7 rows selected.
SQL>
Code assumes COL1 can't have value CHR(0). You would have to come up with a date that can't be used as column value (e.g. DATE '1-1-1' or date '9999-12-31') if COL1/COL2 is DATE/TIMESTAMP and is NULLable. And you can get rid of that NVL along with that CHR(0)/TO_BINARY_FLOAT('INF')/DATE if COL1 or/and COL2 are NOT NULL.
SY.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
