Help with a heavy SQL with 4 table scans
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Help with a heavy SQL with 4 table scans

  1. #1
    Join Date
    Aug 2009
    Posts
    4

    Help with a heavy SQL with 4 table scans

    Hi

    I actually have a simple problem, which I'm trying to explain, before posting my SQL.

    I have table A and table B. Table A contains rows from table B, but they have been through a process and have changed. I want to list out rows from both tables, typically a union.

    Here comes the problem:
    Every rows from table A should list out. From table B I only want rows where resource_id are alike in both tables, AND where voucher_no from table B isn't already listed.

    I haven't found a good way to implement this effectively so I ended up with a simple select to list all rows from table A, and an ugly resource_id IN (Select..) and voucher_no NOT IN (select...). Totally I get 4 table scans.

    The SQL is like this now, and I hope someone has a smart way to do the same thing, but more effectively.

    SELECT resource_id,
    amount,
    voucher_no,
    status,
    'A' AS fromtable
    FROM tableA
    WHERE status IN ('N','T')

    UNION

    SELECT resource_id,
    amount,
    voucher_no,
    status,
    'B' AS fromtable
    FROM tableB
    WHERE resource_id IN (SELECT resource_id
    FROM tableA
    WHERE status IN ('N','T'))
    AND status = 'N'
    AND voucher_no NOT IN (SELECT voucher_no
    FROM tableA
    WHERE status IN ('N','T'))

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,015
    You would benefit from learning about outer joins.

    Code:
    SELECT a.resource_id,  a.amount,  a.voucher_no, a.status
      FROM tableA a
      LEFT OUTER JOIN 
         ( SELECT resource_id, voucher_no 
             FROM tableB 
            WHERE status IN ('N','T')) b
         ON a.resource_id  = b.resource_id
        AND a.voucher_no  != b.voucher_no
     ORDER BY a.resource_id;
    this space intentionally left blank

  3. #3
    Join Date
    Aug 2009
    Posts
    4
    Hi Gandolf989, thanks for your effort. I don't know if you understood the problem, but the SQL you posted is far from what I need.

    In the result I should only get ONE row pr voucher_no, but yours returned 6 rows of one voucher_no.

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,015
    Quote Originally Posted by heroles View Post
    Hi Gandolf989, thanks for your effort. I don't know if you understood the problem, but the SQL you posted is far from what I need.

    In the result I should only get ONE row pr voucher_no, but yours returned 6 rows of one voucher_no.
    I think I understood the question. But not having access to the data its hard for me to tell you exactly what they query will return. If you want one row for each voucher_no, then you need to look at the ON part of the query and make sure that it is joining on enough values.
    this space intentionally left blank

  5. #5
    Join Date
    Aug 2009
    Posts
    4
    Here's table script and inserts. I've tried several queries from other people at work, and on forums, but doesn't seem to get the correct result AND less then 4 table scans

    CREATE TABLE "TABLEA"
    ( "RESOURCE_ID" VARCHAR2(25 BYTE) NOT NULL ENABLE,
    "AMOUNT" NUMBER(30,3) NOT NULL ENABLE,
    "VOUCHER_NO" NUMBER(20,0) NOT NULL ENABLE,
    "STATUS" VARCHAR2(1 BYTE) NOT NULL ENABLE
    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    TABLESPACE "SYSTEM" ;

    CREATE TABLE "TABLEB"
    ( "RESOURCE_ID" VARCHAR2(25 BYTE) NOT NULL ENABLE,
    "AMOUNT" NUMBER(30,3) NOT NULL ENABLE,
    "VOUCHER_NO" NUMBER(20,0) NOT NULL ENABLE,
    "STATUS" VARCHAR2(1 BYTE) NOT NULL ENABLE
    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    TABLESPACE "SYSTEM" ;

    INSERT INTO tablea (amount,resource_id,status,voucher_no) VALUES (6484.00000000,'400','N',30300352 );
    INSERT INTO tablea (amount,resource_id,status,voucher_no) VALUES (1621.00000000,'100','U',30300338 );
    INSERT INTO tablea (amount,resource_id,status,voucher_no) VALUES (6484.00000000,'400','U',30300352 );
    INSERT INTO tablea (amount,resource_id,status,voucher_no) VALUES (6484.00000000,'500','U',30300356 );
    INSERT INTO tablea (amount,resource_id,status,voucher_no) VALUES (28530.00000000,'300','U',30300346 );
    INSERT INTO tablea (amount,resource_id,status,voucher_no) VALUES (25936.00000000,'200','U',30300340 );
    INSERT INTO tablea (amount,resource_id,status,voucher_no) VALUES (25936.00000000,'300','U',30300350 );
    INSERT INTO tablea (amount,resource_id,status,voucher_no) VALUES (7132.00000000,'100','U',30300342 );
    INSERT INTO tablea (amount,resource_id,status,voucher_no) VALUES (6484.00000000,'500','U',30300354 );
    INSERT INTO tablea (amount,resource_id,status,voucher_no) VALUES (1621.00000000,'100','T',30300338 );
    INSERT INTO tablea (amount,resource_id,status,voucher_no) VALUES (25936.00000000,'300','T',30300350 );
    INSERT INTO tablea (amount,resource_id,status,voucher_no) VALUES (7132.00000000,'100','T',30300342 );
    INSERT INTO tablea (amount,resource_id,status,voucher_no) VALUES (6484.00000000,'500','N',30300354 );
    INSERT INTO tablea (amount,resource_id,status,voucher_no) VALUES (25936.00000000,'200','T',30300340 );
    INSERT INTO tablea (amount,resource_id,status,voucher_no) VALUES (6484.00000000,'500','N',30300356 );
    INSERT INTO tablea (amount,resource_id,status,voucher_no) VALUES (28530.00000000,'300','T',30300346 );

    INSERT INTO tableb (amount,resource_id,status,voucher_no) VALUES (0.00000000,'300','N',30300351 );
    INSERT INTO tableb (amount,resource_id,status,voucher_no) VALUES (25936.00000000,'300','N',30300350 );
    INSERT INTO tableb (amount,resource_id,status,voucher_no) VALUES (0.00000000,'100','N',30300338 );
    INSERT INTO tableb (amount,resource_id,status,voucher_no) VALUES (0.00000000,'100','N',30300339 );
    INSERT INTO tableb (amount,resource_id,status,voucher_no) VALUES (0.00000000,'100','N',30300342 );
    INSERT INTO tableb (amount,resource_id,status,voucher_no) VALUES (0.00000000,'100','N',30300343 );
    INSERT INTO tableb (amount,resource_id,status,voucher_no) VALUES (0.00000000,'200','N',30300341 );
    INSERT INTO tableb (amount,resource_id,status,voucher_no) VALUES (35662.00000000,'200','N',30300344 );
    INSERT INTO tableb (amount,resource_id,status,voucher_no) VALUES (0.00000000,'200','N',30300345 );
    INSERT INTO tableb (amount,resource_id,status,voucher_no) VALUES (0.00000000,'300','N',30300347 );
    INSERT INTO tableb (amount,resource_id,status,voucher_no) VALUES (28530.00000000,'300','N',30300346 );
    INSERT INTO tableb (amount,resource_id,status,voucher_no) VALUES (2910.00000000,'400','N',30300349 );
    INSERT INTO tableb (amount,resource_id,status,voucher_no) VALUES (28530.00000000,'400','N',30300348 );
    INSERT INTO tableb (amount,resource_id,status,voucher_no) VALUES (661.00000000,'400','N',30300353 );
    INSERT INTO tableb (amount,resource_id,status,voucher_no) VALUES (0.00000000,'500','N',30300355 );
    INSERT INTO tableb (amount,resource_id,status,voucher_no) VALUES (6484.00000000,'500','N',30300354 );
    INSERT INTO tableb (amount,resource_id,status,voucher_no) VALUES (0.00000000,'500','N',30300357 );
    INSERT INTO tableb (amount,resource_id,status,voucher_no) VALUES (6484.00000000,'500','N',30300356 );
    INSERT INTO tableb (amount,resource_id,status,voucher_no) VALUES (25936.00000000,'200','N',30300340 );
    INSERT INTO tableb (amount,resource_id,status,voucher_no) VALUES (6484.00000000,'400','N',30300352 );

    commit;

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,015
    Adding a primary key might be helpful.

    Code:
    SYSTEM@test11g > alter table tableb 
    add constraint tableb_pkey 
    primary key (RESOURCE_ID, VOUCHER_NO);
    
    Table altered.
    
    Elapsed: 00:00:00.29
    If you can't do that at least add an index
    Code:
    SYSTEM@test11g > create index tablea_resourceid on tablea (RESOURCE_ID);
    
    Index created.
    
    Elapsed: 00:00:00.02
    Then look at my updated query
    Code:
    SYSTEM@test11g > SELECT distinct a.resource_id,	a.amount,
      2  		     a.voucher_no,   a.status
      3    FROM system.tableA a
      4    LEFT OUTER JOIN
      5  	  ( SELECT resource_id, voucher_no,
      6  		   amount,	status
      7  	      FROM system.tableB
      8  	     WHERE status IN ('N','T')) b
      9  	  ON a.resource_id  = b.resource_id
     10  	 AND a.voucher_no   = b.voucher_no
     11  	 AND b.voucher_no IS NULL
     12   ORDER BY a.resource_id, voucher_no;
    
    RESOURCE_ID		      AMOUNT VOUCHER_NO S
    ------------------------- ---------- ---------- -
    100				1621   30300338 T
    100				1621   30300338 U
    100				7132   30300342 T
    100				7132   30300342 U
    200			       25936   30300340 T
    200			       25936   30300340 U
    300			       28530   30300346 T
    300			       28530   30300346 U
    300			       25936   30300350 T
    300			       25936   30300350 U
    400				6484   30300352 N
    400				6484   30300352 U
    500				6484   30300354 N
    500				6484   30300354 U
    500				6484   30300356 N
    500				6484   30300356 U
    
    16 rows selected.
    
    Elapsed: 00:00:00.00
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1803164840
    
    -----------------------------------------------------------------------------------------------
    | Id  | Operation			| Name	      | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT		|	      |    16 |  1232 |     4  (50)| 00:00:01 |
    |   1 |  SORT UNIQUE			|	      |    16 |  1232 |     3  (34)| 00:00:01 |
    |   2 |   NESTED LOOPS OUTER		|	      |    16 |  1232 |     2	(0)| 00:00:01 |
    |   3 |    TABLE ACCESS FULL		| TABLEA      |    16 |  1104 |     2	(0)| 00:00:01 |
    |   4 |    VIEW PUSHED PREDICATE	|	      |     1 |     8 | 	   |	      |
    |*  5 |     FILTER			|	      |       |       | 	   |	      |
    |*  6 |      TABLE ACCESS BY INDEX ROWID| TABLEB      |     1 |    29 |     1	(0)| 00:00:01 |
    |*  7 |       INDEX UNIQUE SCAN 	| TABLEB_PKEY |     1 |       |     0	(0)| 00:00:01 |
    -----------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       5 - filter(NULL IS NOT NULL)
       6 - filter("STATUS"='N' OR "STATUS"='T')
       7 - access("RESOURCE_ID"="A"."RESOURCE_ID" AND "VOUCHER_NO"="A"."VOUCHER_NO")
    Finally read the concepts manual.

    http://download.oracle.com/docs/cd/B...b28318/toc.htm
    this space intentionally left blank

  7. #7
    Join Date
    Aug 2009
    Posts
    4
    Still, it isn't correct. For resource_id = 100 voucher_no 30300338 should only be printet out ones. and you miss some voucher numbers

    Table A contains rows from table B, BUT the rows in table A has changed, so amount and status and a few other columns will change, so doing distinct won't work.

    The problems lies in that I want all rows from Table A, and I want rows in table B that have the same resource_id as in table B but NOT the same voucher_no. Table B will grow with time, and will contains several hounded thousand rows in a year or two. Four table scans will be to much when data grows.

    I've tried with indexes on my SQL (which still is the first out of two SQL's that are correct, but still four table scans) but it didn't help because of the complexity

    this is what you should get:

    Code:
    RESOURCE_ID               AMOUNT                 VOUCHER_NO             STATUS FROMTABLE 
    ------------------------- ---------------------- ---------------------- ------ --------- 
    100                       1621                   30300338               T      A         
    100                       0                      30300339               N      B         
    100                       7132                   30300342               T      A         
    100                       0                      30300343               N      B         
    200                       25936                  30300340               T      A         
    200                       0                      30300341               N      B         
    200                       35662                  30300344               N      B         
    200                       0                      30300345               N      B         
    300                       28530                  30300346               T      A         
    300                       0                      30300347               N      B         
    300                       25936                  30300350               T      A         
    300                       0                      30300351               N      B         
    400                       28530                  30300348               N      B         
    400                       2910                   30300349               N      B         
    400                       6484                   30300352               N      A         
    400                       661                    30300353               N      B         
    500                       6484                   30300354               N      A         
    500                       0                      30300355               N      B         
    500                       6484                   30300356               N      A         
    500                       0                      30300357               N      B
    Last edited by heroles; 08-13-2009 at 04:41 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width