BTS & Outer Joins
Michael Hoffman found that bts_contains() queries fail with "BTS22 - bts_contains requires an index on the search column" as soon as LEFT OUTER JOINs are added. Art Kagel explained that in ANSI join syntax WHERE filters are applied after the join, against a temp table with no BTS index, and suggested moving bts_contains into the ON clause and nesting the outer joins. That worked for a single outer join, but with two chained outer joins only the old Informix syntax (OUTER(es, OUTER ad)) succeeded; nested ANSI forms gave syntax errors or the same BTS error. Michael also noted BTS indexes can't be combined with OR. No ANSI-syntax fix was found; he planned to use non-ANSI syntax, UNIONs or split queries.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi All,
First off, thanks for the assist on showing how wildcards work with BTS
indexes. Very helpful!
Now, I've run into a bug(?) with the BTS functionality. It doesn't seem to
work with queries involving Outer Joins!
This query runs fine:
select count(*)
FROM c1
inner join pndng_c2 pn on c1.ce_pndng_flg = pn.pndng_flg
where bts_contains (ce_name, "Youth*");
1132 rows returned
This query fails:
select count(*)
FROM c1
inner join pndng_c2 pn on c1.ce_pndng_flg = pn.pndng_flg
left outer join es on c1.ce_id = es.ce_id
where bts_contains (ce_name, "Youth*");
(BTS22) - bts error - bts_contains requires an index on the search column
Obviously, the Left Outer Join is causing the issue.
Any ideas???? Nothing in the documentation (that I found) says anything about
BTS indexes only applying to simple queries.
Thanks,
Michael Hoffman
Try moving the bts_contains() call into the first ON clause. WHERE clauses
are processed post join in the ANSI syntax so the query is trying to apply
the function to a temp table that doesn't have any indexes on it at all,
let alone a BTS index. ON clause filters are processed pre-join so it
should work.
Art
Art S. Kagel, Principal Consultant
ASK Database Management
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Thu, Aug 14, 2014 at 12:24 PM, MICHAEL HOFFMAN <mrh@panix.com> wrote:
> Hi All,
> First off, thanks for the assist on showing how wildcards work with BTS
> indexes. Very helpful!
>
> Now, I've run into a bug(?) with the BTS functionality. It doesn't seem to
> work with queries involving Outer Joins!
>
> This query runs fine:
> select count(*)
> FROM c1
> inner join pndng_c2 pn on c1.ce_pndng_flg = pn.pndng_flg
> where bts_contains (ce_name, "Youth*")> ;
> 1132 rows returned
>
> This query fails:
> select count(*)
> FROM c1
> inner join pndng_c2 pn on c1.ce_pndng_flg = pn.pndng_flg
> left outer join es on c1.ce_id = es.ce_id
> where bts_contains (ce_name, "Youth*")> ;
> (BTS22) - bts error - bts_contains requires an index on the search column
>
> Obviously, the Left Outer Join is causing the issue.
>
> Any ideas???? Nothing in the documentation (that I found) says anything
> about
> BTS indexes only applying to simple queries.
>
> Thanks,
> Michael Hoffman
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--089e0158c200d2d6c2050099c383
Thanks Art.
However, I should have gone with the full query, as it presents a tougher
situation: a double Outer Join, with the second join related to the first.
When the second outer is included, the query fails with the same indexing
error.
What is strange is that the explain plan lists the c1 table *first* utilizing
the BTS index. So that should be out of the way *before* the outer joins come
into play. And yet, the query kicks an error. Hmmmmm....
Here is the full query:
select count(*)
FROM c1
inner join pn on c1.ce_pndng_flg = pn.pndng_flg
and bts_contains (c1.ce_name, "Youth*")
left outer join es on c1.ce_id = es.ce_id
left outer join ad on ad.ac_cd = es.es_stat
and ad.ac_grp != 'CR'
;
Interestingly enough, the sqexplain also turns up a wrinkle in the optimizer
dealing with ANSI vs Non-Ansi queries. When the bts_contains is in the Where
clause of an ANSI query, the index is ignored -- SEQUENTIAL SCAN on the table.
However, if either the bts_contains is moved to the "IN" clause of the ANSI
query, or the query is rewritten non_ANSI, the table is accessed via INDEX
PATH using the bts index.
(Non-ANSI:
select count(*)
FROM c1
, pn
, outer es
WHERE c1.ce_pndng_flg = pn.pndng_flg
and bts_contains (c1.ce_name, "Youth*")
and c1.ce_id = es.ce_id
)
Again, in the non-ANSI or ANSI query, if the second Outer table is included,
the query plan writes out to sqexplain, but the query fails with the index
error.
Thanks,
Mike
Well well.... *this* query works, in non-ANSI format:
(the plan also shows the c1 table using the INDEX PATH for the bts index)
select c1.ce_id, pn.pndng_flg, es.es_stat, ad.ac_grp
FROM c1
, pn
, outer ( es, outer ad )
where c1.ce_pndng_flg = pn.pndng_flg
and bts_contains (c1.ce_name, "Youth*")
and c1.ce_id = es.ce_id
and ad.ac_cd = es.es_stat and ad.ac_grp != 'CR';
Try as I might, though, I cannot get this to convert to ANSI and not kick out
the Index error. :-(
These bts indexes work great, but the learning curve is fairly high!
Thanks,
Mike
Mike:
Right, ANSI-92+ queries work differently from the older ANSI/Informix
syntax. By ANSI standards WHERE clause filters, as I mentioned, are
processed post-join so the data from the joins has to be written to temp
table(s) and the WHERE filters applied to the contents of the temp table
not to the source tables. That means the the BTS index, since it is
mentioned in the WHERE clause rather than in the ON clause, cannot be used
to limit the rows considered from the c1 table and there has to be a table
scan on that table creating a rather large temp table. Now, why the BTS
index is not being used when there is a second outer table I don't know,
but you could try nesting the OUTER clauses since the ad table is joined to
the es table not to c1 or pn. Try:
select count(*)
FROM c1
inner join pn on c1.ce_pndng_flg = pn.pndng_flg
and bts_contains (c1.ce_name, "Youth*")
left outer join (es on c1.ce_id = es.ce_id
left outer join ad on ad.ac_cd = es.es_stat
and ad.ac_grp != 'CR'
)
;
or
select count(*)
FROM c1
inner join pn on
outer (es outer ad )
where
c1.ce_pndng_flg = pn.pndng_flg
and c1.ce_id = es.ce_id
and ad.ac_cd = es.es_stat
and ad.ac_grp != 'CR'
and bts_contains (c1.ce_name, "Youth*")
;
Art
Art S. Kagel, Principal Consultant
ASK Database Management
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Thu, Aug 14, 2014 at 2:15 PM, MICHAEL HOFFMAN <mrh@panix.com> wrote:
> Thanks Art.
> However, I should have gone with the full query, as it presents a tougher
> situation: a double Outer Join, with the second join related to the first.
>
> When the second outer is included, the query fails with the same indexing
> error.
>
> What is strange is that the explain plan lists the c1 table *first*
> utilizing
> the BTS index. So that should be out of the way *before* the outer joins
> come
> into play. And yet, the query kicks an error. Hmmmmm....
>
> Here is the full query:
> select count(*)
> FROM c1
> inner join pn on c1.ce_pndng_flg = pn.pndng_flg>
> and bts_contains (c1.ce_name, "Youth*")
> left outer join es on c1.ce_id = es.ce_id
> left outer join ad on ad.ac_cd = es.es_stat
>
> and ad.ac_grp != 'CR'
> ;
>
> Interestingly enough, the sqexplain also turns up a wrinkle in the
> optimizer
> dealing with ANSI vs Non-Ansi queries. When the bts_contains is in the
> Where
> clause of an ANSI query, the index is ignored -- SEQUENTIAL SCAN on the
> table.
> However, if either the bts_contains is moved to the "IN" clause of the ANSI
> query, or the query is rewritten non_ANSI, the table is accessed via INDEX
> PATH using the bts index.
> (Non-ANSI:
> select count(*)
> FROM c1
> , pn
> , outer es
> WHERE c1.ce_pndng_flg = pn.pndng_flg
> and bts_contains (c1.ce_name, "Youth*")
> and c1.ce_id = es.ce_id
> )>
> Again, in the non-ANSI or ANSI query, if the second Outer table is
> included,
> the query plan writes out to sqexplain, but the query fails with the index
> error.
>
> Thanks,
> Mike
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c34f52242eae05009cc31b
Art, your second query (the non-ANSI) matches the one I posted in my followup.
However, your ANSI query, sorry to say, doesn't work: syntax error.
select count(*)
FROM c1
inner join pn on c1.ce_pndng_flg = pn.pndng_flg
and bts_contains (c1.ce_name, "Youth*")
left outer join (es on c1.ce_id = es.ce_id
^ SYNTAX ERROR
left outer join ad on ad.ac_cd = es.es_stat
and ad.ac_grp != 'CR'
)
;
The way I fixed it was to move the "on c1.ce_id = es.ce_id" outside the final
paren:
select count(*)
FROM c1
inner join pn on c1.ce_pndng_flg = pn.pndng_flg
and bts_contains (c1.ce_name, "Youth*")
left outer join (es left outer join ad
on ad.ac_cd = es.es_stat and ad.ac_grp != 'CR')
on c1.ce_id = es.ce_id
;
However, that query gives the original error. Ugh!
Back to the drawing board!
I wish the documentation was a little clearer about how the indexes are
processed. Such as, it mentions that you cannot utilize the same index more
than once in a single query:
bts_contains(fname, 'Art') or bts_contains(fname, 'Mike')
needs to be bts_contains(fname, 'Art OR Mike')
What it fails to mention is that you also cannot utilize 2 DIFFERENT bts
indexes in the same query, using an OR (use an AND and all is peachy).
bts_contains(fname, 'Mike') OR bts_contains(lname,'Hoffman')
It returns the same Missing Index error, even though there are indexes on both
fields. (I know 11.7 allows for composite indexes. We'll get there soon :-))
I can sort of understand this now --- the optimizer will split the query into
2 parts, building temp tables of the results. Now why it fails to find a BTS
index for, presumably, the second query is another Informix mystery :-).
Well, I suppose for our purposes, we can split the queries into multiples and
UNION the results.
As to the multiple outer joins --- maybe we'll need to call a second query
based on the bts search result set. It's not pretty or efficient (argh!), but
it will get the job done.
Thanks,
Mike
I suspected but couldn't test it. I hoped nested outers were supported but
...
Art
On Aug 14, 2014 6:06 PM, "MICHAEL HOFFMAN" <mrh@panix.com> wrote:
> Art, your second query (the non-ANSI) matches the one I posted in my
> followup.
> However, your ANSI query, sorry to say, doesn't work: syntax error.
>
> select count(*)
> FROM c1
> inner join pn on c1.ce_pndng_flg = pn.pndng_flg
> and bts_contains (c1.ce_name, "Youth*")
> left outer join (es on c1.ce_id = es.ce_id>
> ^ SYNTAX ERROR
> left outer join ad on ad.ac_cd = es.es_stat
> and ad.ac_grp != 'CR'
> )
> ;
>
> The way I fixed it was to move the "on c1.ce_id = es.ce_id" outside the
> final
> paren:
>
> select count(*)
> FROM c1
> inner join pn on c1.ce_pndng_flg = pn.pndng_flg
> and bts_contains (c1.ce_name, "Youth*")
> left outer join (es left outer join ad>
> on ad.ac_cd = es.es_stat and ad.ac_grp != 'CR')
>
> on c1.ce_id = es.ce_id
> ;
>
> However, that query gives the original error. Ugh!
>
> Back to the drawing board!
>
> I wish the documentation was a little clearer about how the indexes are
> processed. Such as, it mentions that you cannot utilize the same index more
> than once in a single query:
> bts_contains(fname, 'Art') or bts_contains(fname, 'Mike')
> needs to be bts_contains(fname, 'Art OR Mike')
>
> What it fails to mention is that you also cannot utilize 2 DIFFERENT bts
> indexes in the same query, using an OR (use an AND and all is peachy).
>
> bts_contains(fname, 'Mike') OR bts_contains(lname,'Hoffman')
>
> It returns the same Missing Index error, even though there are indexes on
> both
> fields. (I know 11.7 allows for composite indexes. We'll get there soon
> :-))
>
> I can sort of understand this now --- the optimizer will split the query
> into
> 2 parts, building temp tables of the results. Now why it fails to find a
> BTS
> index for, presumably, the second query is another Informix mystery :-).
>
> Well, I suppose for our purposes, we can split the queries into multiples
> and
> UNION the results.
> As to the multiple outer joins --- maybe we'll need to call a second query
> based on the bts search result set. It's not pretty or efficient (argh!),
> but
> it will get the job done.
>
> Thanks,
> Mike
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c25eb28b13cb05009e87a8