Home » SQL & PL/SQL » SQL & PL/SQL » schedule job for multi threading (oracle 10g)
schedule job for multi threading [message #668169] Sat, 10 February 2018 16:12 Go to next message
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 #668170 is a reply to message #668169] Sat, 10 February 2018 17:26 Go to previous messageGo to next message
BlackSwan
Messages: 26274
Registered: January 2009
Location: SoCal
Senior Member
simplesanju wrote on Sat, 10 February 2018 14:12
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.

Never do in PL/SQL that which can be done in plain SQL.

Row by row LOOP will be slower than above.

Quantify why you want to do LOOP.
Re: schedule job for multi threading [message #668175 is a reply to message #668169] Sun, 11 February 2018 08:23 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2812
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.
Re: schedule job for multi threading [message #668185 is a reply to message #668175] Mon, 12 February 2018 00:32 Go to previous message
simplesanju
Messages: 36
Registered: July 2008
Member
Thank you Solomon Yakobson
Previous Topic: difference between date
Next Topic: How to get the functions name used by a column in the whole database
Goto Forum:
  


Current Time: Wed Nov 14 11:40:37 CST 2018