query not giving desired result

Database

query not giving desired result

Hi All,
When i am trying to run below query for doing B-A(finding out records in B which are not in A)

SELECT

COLUMNS
FROM DATABASE.B SRC LEFT OUTER JOIN DATABASE.A TRG
ON
SRC.a= TRG.a
AND SRC.b=TRG.b
AND SRC.c = TRG.c
WHERE TRG.EFF_END_DTE = DATE '3499-12-31
AND trg.a is null
AND trg.b is null
AND trg.c is NULL;

I am not getting even a single record with explain plan showing that it searches for rows with values ( trg.a,trg.b.trg.c) null in table A and redistributing those....
Ideal scenario should have been that
AND trg.a is null
AND trg.b is null
AND trg.c is NULL;
should have been applied after the join to get desired results.Same type of queries are used elsewhere also in other tables..giving normal resultset.

Table demographics are below:
NOTE:
B has PI(a,b,c)
A has PI(a,b,c,EFF_STA_DTE)

CREATE SET TABLE TABLE B ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
c VARCHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
a VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
b VARCHAR(765) CHARACTER SET LATIN NOT CASESPECIFIC,
ff DECIMAL(5,0) COMPRESS 0. ,
EFF_STA_DTE DATE FORMAT 'YYYY-MM-DD' NOT NULL,
EFF_END_DTE DATE FORMAT 'YYYY-MM-DD' NOT NULL COMPRESS (DATE '3499-12-31'),
DW_LOD_TMP TIMESTAMP(6) FORMAT 'YYYYMMDDBHH:MI:SS.S(6)' NOT NULL,
DW_UPD_LOD_TMP TIMESTAMP(6) FORMAT 'YYYYMMDDBHH:MI:SS.S(6)' NOT NULL)
PRIMARY INDEX ( c ,a ,b );

2)TABLE A

CREATE SET TABLE TABLE A ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
c VARCHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
a VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
b VARCHAR(765) CHARACTER SET LATIN NOT CASESPECIFIC,
ff DECIMAL(5,0) COMPRESS 0. ,
EFF_STA_DTE DATE FORMAT 'YYYY-MM-DD' NOT NULL,
EFF_END_DTE DATE FORMAT 'YYYY-MM-DD' NOT NULL COMPRESS (DATE '3499-12-31'),
DW_LOD_TMP TIMESTAMP(6) FORMAT 'YYYYMMDDBHH:MI:SS.S(6)' NOT NULL,
DW_UPD_LOD_TMP TIMESTAMP(6) FORMAT 'YYYYMMDDBHH:MI:SS.S(6)' NOT NULL)
PRIMARY INDEX ( c ,a ,b ,EFF_STA_DTE );

Stats are collected on all desired columns & indexes.

Please advise what is wrong in above query & how to get desired result .

Also please provide critical points to consider while formulating such type of queries.

Regards,
Prakhar
3 REPLIES
Enthusiast

Re: query not giving desired result

Select *
From B
Minus
Select * From A
;

Depending on volumes, it may take a while because the different PI's mean it will have to rehash one of the tables.
Make sure you have stats (up to date!) on both primary indices.

Re: query not giving desired result

Also, using a where clause in left or right outer joins makes it an inner join...always keep your conditions in on clause
Junior Supporter

Re: query not giving desired result

"Also, using a where clause in left or right outer joins makes it an inner join...always keep your conditions in on clause"

Could you please elaborate?

Cheers.

Carlos.