Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

[Info-Ingres] char() + char() AND NOT IN vs outer join

1 view
Skip to first unread message

Martin Bowes

unread,
Nov 5, 2009, 9:16:53 AM11/5/09
to Ingres and related product discussion forum

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

Karl Schendel

unread,
Nov 5, 2009, 9:27:56 AM11/5/09
to Ingres and related product discussion forum

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

> 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)

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


Martin Bowes

unread,
Nov 5, 2009, 9:54:16 AM11/5/09
to Ingres and related product discussion forum
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

Karl


_______________________________________________
Info-Ingres mailing list
Info-...@kettleriverconsulting.com
http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres


0 new messages