topic Re: Help Required in Sql in Operating System - HP-UX

Help Required in Sql

Chitta — Fri, 02 May 2008 07:25:49 GMT

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.

Re: Help Required in Sql

Yogeeraj_1 — Fri, 02 May 2008 07:49:29 GMT

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

Re: Help Required in Sql

Yogeeraj_1 — Fri, 02 May 2008 07:59:16 GMT

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

Re: Help Required in Sql

Chitta — Fri, 02 May 2008 08:56:55 GMT

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

Re: Help Required in Sql

Yogeeraj_1 — Fri, 02 May 2008 09:35:51 GMT

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

Re: Help Required in Sql

Chitta — Fri, 02 May 2008 12:57:39 GMT

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

Re: Help Required in Sql

Yogeeraj_1 — Sat, 03 May 2008 05:53:45 GMT

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

Re: Help Required in Sql

Chitta — Tue, 06 May 2008 05:15:34 GMT

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

Re: Help Required in Sql

Yogeeraj_1 — Tue, 06 May 2008 05:51:46 GMT

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

Re: Help Required in Sql

Yogeeraj_1 — Tue, 06 May 2008 05:53:43 GMT

attached a more readable version of the output.

Re: Help Required in Sql

Chitta — Tue, 06 May 2008 15:28:45 GMT

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

please confirm,

Regs,
Chitta

Re: Help Required in Sql

TwoProc — Tue, 06 May 2008 16:09:46 GMT

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.

Re: Help Required in Sql

Chitta — Wed, 07 May 2008 03:38:25 GMT

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.

Re: Help Required in Sql

Chitta — Fri, 09 May 2008 03:08:02 GMT

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

Regs,
Chitta

Re: Help Required in Sql

Yogeeraj_1 — Fri, 09 May 2008 03:14:57 GMT

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