Help Required in Sql

[Chitta]

Hi All,
i need help to build a query in sql and am using oracle 9i database.

my requirement is
I want to select some records from table A and those records should not be in the table B and C and should be in the Table D.
There is a common column for all the table were doc_num and doc_type to compare data.

please Immediate reply would be greatly appricated

regs,
Chitta.

[Yogeeraj_1]

Hi chitta,

it should be as easy as this:

select *
from tableA
where not exists (select 'x' from tableB where tableA.doc_num=tableB.doc_num and tableA.doc_type=tableB.doc_type)
and not exists (select 'x' from tableC where tableA.doc_num=tableC.doc_num and tableA.doc_type=tableC.doc_type)
and exists (select 'x' from tableD where tableA.doc_num=tableD.doc_num and tableA.doc_type=tableD.doc_type);


hope this helps!
kind regards
yogeeraj

[Yogeeraj_1]

hi again,

a simple example to demonstrate:

Scripts:
==========
create table tableA (doc_num number(1), doc_type varchar2(1));
create table tableB (doc_num number(1), doc_type varchar2(1));
create table tableC (doc_num number(1), doc_type varchar2(1));
create table tableD (doc_num number(1), doc_type varchar2(1));

insert into tableA values(1,'A');
insert into tableA values(2,'B');
insert into tableA values(3,'C');
insert into tableA values(4,'D');
insert into tableA values(5,'E');

insert into tableB values(1,'A');
insert into tableB values(2,'B');

insert into tableC values(3,'C');

insert into tableD values(4,'D');


Execution:
==========
yogi@mydb.mu> select *
2 from tableA
3 where not exists (select 'x' from tableB where tableA.doc_num=tableB.doc_nu
m and tableA.doc_type=tableB.doc_type)
4 and not exists (select 'x' from tableC where tableA.doc_num=tableC.doc_nu
m and tableA.doc_type=tableC.doc_type)
5* and exists (select 'x' from tableD where tableA.doc_num=tableD.doc_nu
m and tableA.doc_type=tableD.doc_type)

DOC_NUM D
__________ _
4 D

Elapsed: 00:00:00.26
yogi@mydb.mu>

hope this helps too!
kind regards
yogeeraj

[Chitta]

Thank you very much yoogeraj,

But the table have 992345 records,
and other tables are having 20 to 30 lacks records
So whenever i issue the command, it get hangs for long time.
can we make any workarround to make this query fast.

please suggest.
regs,
chitta

[Yogeeraj_1]

hi,

to help me better understand what is happening, can you please post the output of the following commands: (run using SQLPLUS)

(example)
yogi@mydb.mu>set autotrace traceonly
yogi@mydb.mu>select *
2 from tableA
3 where not exists (select 'x' from tableB where tableA.doc_num=tableB.doc_num and tableA.doc_type=tableB.doc_type)
4 and not exists (select 'x' from tableC where tableA.doc_num=tableC.doc_num and tableA.doc_type=tableC.doc_type)
5 and exists (select 'x' from tableD where tableA.doc_num=tableD.doc_num and tableA.doc_type=tableD.doc_type);

Elapsed: 00:00:00.15

Execution Plan
__________________________________________________________
Plan hash value: 175544403

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 60 | 14 (15)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 1 | 60 | 14 (15)| 00:00:01 |
|* 2 | HASH JOIN ANTI | | 1 | 45 | 10 (10)| 00:00:01 |
|* 3 | HASH JOIN SEMI | | 1 | 30 | 7 (15)| 00:00:01 |
| 4 | TABLE ACCESS FULL| TABLEA | 5 | 75 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| TABLED | 1 | 15 | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | TABLEC | 1 | 15 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | TABLEB | 2 | 30 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("TABLEA"."DOC_NUM"="TABLEB"."DOC_NUM" AND
"TABLEA"."DOC_TYPE"="TABLEB"."DOC_TYPE")
2 - access("TABLEA"."DOC_NUM"="TABLEC"."DOC_NUM" AND
"TABLEA"."DOC_TYPE"="TABLEC"."DOC_TYPE")
3 - access("TABLEA"."DOC_NUM"="TABLED"."DOC_NUM" AND
"TABLEA"."DOC_TYPE"="TABLED"."DOC_TYPE")

Note
-----
- dynamic sampling used for this statement


Statistics
__________________________________________________________
351 recursive calls
0 db block gets
926 consistent gets
24 physical reads
332 redo size
390 bytes sent via SQL*Net to client
429 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
8 sorts (memory)
0 sorts (disk)
1 rows processed

yogi@mydb.mu>



revert!
kind regards
yogeeraj

[Chitta]

Hi yogeeraj,
please find the explain plan fro the query

91369 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 FILTER
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'AP_INV_HDR'
3 2 INDEX (RANGE SCAN) OF 'AP_INV_HDR_I1' (UNIQUE)
4 1 TABLE ACCESS (FULL) OF 'AP_PAY_LINE'
5 1 TABLE ACCESS (FULL) OF 'AP_DBCR_ALLOC_HDR'
6 1 INDEX (RANGE SCAN) OF 'AP_INV_LINE_I1' (UNIQUE)




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
399412589 consistent gets
379116023 physical reads
0 redo size
10763754 bytes sent via SQL*Net to client
42915 bytes received via SQL*Net from client
6093 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
91369 rows processed

[Yogeeraj_1]

Hi Chitta,

We still dont have enough information on your tables, indexes, etc.

Based on lines:
4 1 TABLE ACCESS (FULL) OF 'AP_PAY_LINE'
5 1 TABLE ACCESS (FULL) OF 'AP_DBCR_ALLOC_HDR'


I would recommend that you create indexes on the columns that you are using to join these tables.

e.g.
create index ind_AP_PAY_LINE_2 on AP_PAY_LINE() tablespace TBS_IMEDIUM01 pctfree 5;


After you have create the indexes, please re-run the query that i posted earlier and post the output.


revert!
kind regards
yogeeraj

[Chitta]

Thanks a lot for reply,

I made request to create a index on those tables,
and for the query, i got another one from the portal i just want to know Whether it is right or not,

here ar ethe qoery,
select * from A
where (columns) not in (select from b union all select from c)
and (columns) in (select from d)

which one i should follow,

early reply would be more helpful to us,

Regs,
Chitta

[Yogeeraj_1]

hi Chitta,

You will have to run the query and analyse the performance as per the autotrace output.

I do not have enough data here to get meaningful results.

Below the output of the second query (gives a better performance)

yogi@mydb.mu>set autotrace traceonly
yogi@mydb.mu> select *
2 from tableA
3 where (tableA.doc_num,tableA.doc_type) not in (select tableB.doc_num,tableB.doc_type from tableB union all select tableC.doc_num,tableC.doc_type from tableC )
4* and (tableA.doc_num,tableA.doc_type) in (select tableD.doc_num,tableD.doc_type from tableD)

Elapsed: 00:00:00.14

Execution Plan
__________________________________________________________
Plan hash value: 236380764

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 13 (8)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN SEMI | | 1 | 10 | 7 (15)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TABLEA | 5 | 25 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| TABLED | 1 | 5 | 3 (0)| 00:00:01 |
| 5 | UNION-ALL | | | | | |
|* 6 | TABLE ACCESS FULL| TABLEB | 1 | 5 | 3 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL| TABLEC | 1 | 5 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter( NOT EXISTS ( (SELECT /*+ */
"TABLEB"."DOC_NUM","TABLEB"."DOC_TYPE" FROM "TABLEB" "TABLEB" WHERE
LNNVL("TABLEB"."DOC_NUM"<>:B1) AND LNNVL("TABLEB"."DOC_TYPE"<>:B2))
UNION ALL (SELECT /*+ */ "TABLEC"."DOC_NUM","TABLEC"."DOC_TYPE" FROM
"TABLEC" "TABLEC" WHERE LNNVL("TABLEC"."DOC_NUM"<>:B3) AND
LNNVL("TABLEC"."DOC_TYPE"<>:B4))))
2 - access("TABLEA"."DOC_NUM"="TABLED"."DOC_NUM" AND
"TABLEA"."DOC_TYPE"="TABLED"."DOC_TYPE")
6 - filter(LNNVL("TABLEB"."DOC_NUM"<>:B1) AND
LNNVL("TABLEB"."DOC_TYPE"<>:B2))
7 - filter(LNNVL("TABLEC"."DOC_NUM"<>:B1) AND
LNNVL("TABLEC"."DOC_TYPE"<>:B2))


Statistics
__________________________________________________________
8 recursive calls
0 db block gets
62 consistent gets
0 physical reads
0 redo size
390 bytes sent via SQL*Net to client
429 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

yogi@mydb.mu>


hope this helps!

kind regards
yogeeraj

[Yogeeraj_1]

attached a more readable version of the output.