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'))

You would benefit from learning about outer joins. :rolleyes:

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;

---

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.

Quote:

---

Originally Posted by heroles

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.

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

Quote:

---

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;

---

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. :rolleyes:

http://download.oracle.com/docs/cd/B...b28318/toc.htm

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

---