Re: Some 4GL and SQL problems
Neil Smith wrote:
>
> Thanks again for a clear, and concise answer.
>
> I have changed my code to use sqlerrd[2], and I have also chosen to
> hardcode the common cases of my select against possible null values and
> have dynamic generation of the not so common cases as you suggested.
>
> However, I have some queries regarding your statement below:
>
> In article <376807C7.F2CFA83A@bloomberg.net>,
> kagel@bloomberg.net wrote:
> > Truth is the query you want to perform SHOULD be a join, the
> > sub-query is a correlated sub-query and ALL CORRELATED
> > SUB-QUERIES CAN BE REWRITTEN AS SIMPLE JOINS! They run faster,
> > if the indexes are present anyway.
>
> The query in question was:
>
> >> select id from table1
> >> where (a, b, c) in (
> >> select a, b, c from table2
> >> );>
> How is this correlated? There is no reference to the tables of the
No it is not correlated, I must have put something in my coffee that
day.
> main query within the subquery. Is a correlated subquery something
> different in the Informix world to the Oracle world? As an example of
Now, now let's not get vulgar and start tossing the 'O' word around.
> what I would consider to be a correlated subquery, the statement could
> be rewritten as:
>
> select id from table1
> where exists (
> select 9 from table2
> where table2.a = table1.a
> and table2.b = table1.b
> and table3.c = table1.c
> );
Truth.
> Also, I am dubious about your claim that all correlated subqueries can
> be rewritten as simple queries. Is there a proof of this somewhere?
Actually yes there is, though damned if I remember where I saw the
proof. I just know that I have NEVER seen one, without aggregates,
that I could NOT rewrite as a join. Correlated subqueries with
aggregates you have to perform the sub-query to a temp table then join
to the temp table. Remember, the object here is to eliminate the
overhead of performing the sub-query for every row of the outer partial
result set so the temp table solution applies.
> The case that springs straight to mind as requiring a correlated
> subquery is that of deleting duplicates from a table. For instance,
> consider table X with columns a, b, and c. The statememnt would be
>
> delete from X where rowid in (
> select rowid from X x_1
> where exists (
> select 9
> from X x_2
> where x_2.a = x_1.a
> and x_2.b = x_1.b
> and x_2.c = x_1.c
> group by x_2.a, x_2.b, x_2.c
> having count(*) > 1
> ) and rowid != (
> select max(rowid)
> from X x_3
> where x_3.a = x_1.a
> and x_3.b = x_1.b
> and x_3.c = x_1.c
> )
> );
> Now, rewrite that without a subquery.
Aw, now I'm gonna look weasely when I say that that is not strictly a
query and it DOES have an aggregate. OK, OK:
select a, b, c, max(rowid) keeper
from X
group by a, b, c
having count(*) > 1
into temp fred;
delete from X where rowid in (
select X.rowid
from X, fred
where fred.a = X.a
and fred.b = X.b
and fred.c = X.c
and X.rowid != fred.keeper
);
drop table fred;
It does eliminate the correlated sub-query and eliminate the possibly
thousands of executions of the sub-query and it WILL run much faster,
except on IDS 7.31 which MAY do the same thing to your version anyway
though I have eliminated the second correlated sub-query altogether and
I'm not certain that even 7.31 can do that automatically.
> Also, this brings to mind another usefull feature of which Informix is
> deficient. Why can you not correlate the table of the delete clause?
> In Oracle, the statement above could be:
Same query isn't it? Because strictly the values returned by the
sub-query will be unstable because the outer statement is deleting rows
from the very table it is querying.
Art S. Kagel