All Forums Analytics
sk73 52 posts Joined 07/09
19 Jul 2011
need SQL help (The Output does not contain correct number of rows)

Hi,

I need your help on this issue.

I have a query between two tables A & B.

I get >100 rows in the result set when I write a query as follows:-

SELECT
A.id, B.id
FROM
table_a A,
table_b B
where
A.id=B.id;

But, no rows are returned when I write a query as follows:-

SELECT
A.id,B.id, A.F_KEY
FROM
table_a A,
table_b B
where
A.id=B.id;

Is there some thing I am missing when I write this query?

Please, help.

Thanks,
Sree

mnylin 118 posts Joined 12/09
19 Jul 2011

what do the explain plans show between the two queries?

sk73 52 posts Joined 07/09
19 Jul 2011

The explain in the second case does the join whereas in the first case, there is no join and the output is a straight SELECT from table b.

The id field in table A and the id field in table B is same and it is "CUSTOMER_ID".

From tweaking more, I am into a feeling that the optimizer is getting into a thinking that the optimizer is not doing the join when (A.id, B.id) is the result set. But, when I use (A.id, B.id, A.), the join takes place and the result set is 0.

My new question is:- Why is the optimizer not doing the join operation?

Please share your thoughts.

Thanks,
Sree

dnoeth 4628 posts Joined 11/04
19 Jul 2011

Hi Sree,
check the table definition if there's a FOREIGN KEY WITH NO CHECK OPTION REFERENCES tableA(id)

The optimizer might do a join elimination if tableA.id is defined as NOT NULL (i.e. always the same as tableB.id), but when you select another column from tableA it has to do the join. TableA seems to be empty.

When you use soft/dummy RI you must check FKs manually within your load steps to avoiud such wrong answers.

Dieter

Dieter

sk73 52 posts Joined 07/09
22 Jul 2011

Great answer Sir. that is exactly what happened. We have excellent practices here. Our ETL is getting better day-by-day.

Another item gets into the checklist now.

Sree

You must sign in to leave a comment.