cancel
Showing results for 
Search instead for 
Did you mean: 

Help Required in Sql

Chitta
Frequent Advisor

Help Required in Sql

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.

14 REPLIES
Yogeeraj_1
Honored Contributor

Re: Help Required in Sql

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: Help Required in Sql

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Chitta
Frequent Advisor

Re: Help Required in Sql

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
Honored Contributor

Re: Help Required in Sql

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Chitta
Frequent Advisor

Re: Help Required in Sql

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
Honored Contributor

Re: Help Required in Sql

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Chitta
Frequent Advisor

Re: Help Required in Sql

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
Honored Contributor

Re: Help Required in Sql

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: Help Required in Sql

attached a more readable version of the output.
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Chitta
Frequent Advisor

Re: Help Required in Sql

Hi,
Now am getting confused,
both queries are giving diffrent results,
i dont know which one is correct.

please confirm,

Regs,
Chitta
TwoProc
Honored Contributor

Re: Help Required in Sql

this might run faster without indexes - give it a try ...

select doc_num,doc_type from
(( select doc_num,doc_type from tableA)
intersect
(select doc_num,doc_type from tableD))
minus
(select doc_num,doc_type from tableB)
minus
(select doc_num,doc_type from tableC)
;

Depending on what you want to do with some records in B, but not in C, or in C but not in B - you may have to play with parenthesis and grouping for the "minus" section. The above removes records from the result that are in either of B or C. Your question was not clear on that aspect, however if all of B and C were the same - then you'd just use one of the tables, you wouldn't need both. Therefore, I wrote the above with removing from the answer set anything in either B or C.
We are the people our parents warned us about --Jimmy Buffett
Chitta
Frequent Advisor

Re: Help Required in Sql

Hi,
Thanks for update,
my concern is not on performance related,

my requirement is, i want to select some data form table A and the condition is those data should not be in tbale B anc C, and should be in D.

like select a.no from a where a.no<>b.no AND a.no<>c.no AND a.no=d.no

it should be 'AND',

So i had couple of queries having same condition, but giving diffrent result(i.e, varies in total no.of rows)

since the volume of data is very hing (approx'ly, 1,000,000 on each table am b=not able to run the query handy.


please advise,
Regs,
Chitta.




Chitta
Frequent Advisor

Re: Help Required in Sql

Hi All,
thanks for support,
i had completed the task.

Regs,
Chitta
Yogeeraj_1
Honored Contributor

Re: Help Required in Sql

hi Chitta,

Glad to hear this from you.

Can you please let us know which solution you finally adopted?

also read:
http://forums11.itrc.hp.com/service/forums/helptips.do?#34

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)