Hi All,



The following query runs in 7 hours:

insert into lab_result(

sample_id, test_id, result, when_analysed, status, participant_id,
visit_id

)

select sample_id, test_id, result, when_analysed, status,
participant_id, visit_id

from session.temp_lab_result

where char(sample_id) +'-' +char(test_id) not in(select char(sample_id)
+'-' +char(test_id)from lab_result)



And this runs in 7 seconds...



insert into lab_result(

sample_id, test_id, result, when_analysed, status,
participant_id, visit_id

)

select t.sample_id, t.test_id, t.result, t.when_analysed, t.status,
t.participant_id, t.visit_id

from session.temp_lab_result t left outer join lab_result l on
t.sample_id=l.sample_id and t.test_id=l.test_id

where l.sample_id is null and l.test_id is null



I'm sure the resulting rows are the same or have I done something
massively bone headed?



Martin Bowes

On Nov 5, 2009, at 9:16 AM, Martin Bowes wrote:

Are any of these columns nullable? Sounds like you're getting an SE-
join,
and the most likely explanation is a nullable column gumming up the
works.

I don't know if the char+char expression would inhibit the notin to
outer-join
transform, but at least I would expect the old style quel-like
flattening
with ANY. It can't even do that if the inner is nullable, though.

Karl

Hi Karl,

Bingo on nulls and SE join!

The session temporary was created with nullable columns, the main table
uses non-nullable columns.

Once I corrected the session temp table to use non nullable columns the
original query with char() and not in was morpehed into right join/Hash
join and completed in much the same time as the specific ouer join case.

Marty

-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Karl
Schendel
Sent: 05 November 2009 14:28
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] char() + char() AND NOT IN vs outer join


On Nov 5, 2009, at 9:16 AM, Martin Bowes wrote:

Are any of these columns nullable? Sounds like you're getting an SE-
join,
and the most likely explanation is a nullable column gumming up the
works.

I don't know if the char+char expression would inhibit the notin to
outer-join
transform, but at least I would expect the old style quel-like
flattening
with ANY. It can't even do that if the inner is nullable, though.

Karl


_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres