SQL statement ?
A poster found an ERP query using "where kolom1 matches '*O*'" on a char(1) column and asked about the performance impact. The consensus: a leading wildcard prevents that filter from being used as a lower index filter (an anchored pattern like 'b*' can use an index), but other index keys can still be used, with the MATCHES applied as a post-filter; on a one-character, low-selectivity column the practical cost is small. Advice was to replace it with an equality test if possible and compare SET EXPLAIN plans/costs; examples of plans were posted.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi All, I found in our ERP-Application the following SQL-statement: ...... where kolom1 matches '*O*' ...... This kolom1 is a "char(1)" ! The result is okay, but what does it do with the performance? Alex
"X. Pietje" wrote: > > Hi All, > > I found in our ERP-Application the following SQL-statement: > ...... > where kolom1 matches '*O*' > ...... > > This kolom1 is a "char(1)" ! > > The result is okay, but what does it do with the performance? Practically speaking, probably not much. The optimizer will not be able to use any index starting with that column and will only use the key in other indexes containing that column up to that column but since the filter value of a one character column is rarely high those indexes that start with koloml are not likely to be used for filtering anyway. If you can change it do. You can determine the actual cost by running versions of the same query under SET EXPLAIN ON with the MATCHES clause and with an equality filter and compare the query plans and costs. Art S. Kagel
In article <390F0C44.188C7CE3@bloomberg.net>, kagel@bloomberg.net wrote: > "X. Pietje" wrote: > > > > Hi All, > > > > I found in our ERP-Application the following SQL-statement: > > ...... > > where kolom1 matches '*O*' > > ...... > > > > This kolom1 is a "char(1)" ! > > > > The result is okay, but what does it do with the performance? > > Practically speaking, probably not much. The optimizer will not be able > to use any index starting with that column and will only use the key in > other indexes containing that column up to that column but since the filter > value of a one character column is rarely high those indexes that start > with koloml are not likely to be used for filtering anyway. If you can > change it do. You can determine the actual cost by running versions of > the same query under SET EXPLAIN ON with the MATCHES clause and with an > equality filter and compare the query plans and costs. > > Art S. Kagel > Oh, great Obi-wan... Doesn't a 'matches' force a sequential scan on the table? I didn't think it would use an index at all. I could be wrong, I don't use it much. Just wondering. -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.
On Tue, 02 May 2000 18:54:57 GMT, mars1972@my-deja.com wrote: >Oh, great Obi-wan... Doesn't a 'matches' force a sequential scan on the >table? I didn't think it would use an index at all. I could be wrong, >I don't use it much. Just wondering. Obi-wan not here. If anchor it has, index it might use. With 'matches "O*"' anchor it may use. With 'matches "*O*"', anchor it does not have, so index it cannot use. HTH, Douglas Wilson (channelling the force as best he can).
mars1972@my-deja.com wrote: > > In article <390F0C44.188C7CE3@bloomberg.net>, > kagel@bloomberg.net wrote: > > "X. Pietje" wrote: > > > > > > Hi All, > > > > > > I found in our ERP-Application the following SQL-statement: > > > ...... > > > where kolom1 matches '*O*' > > > ...... > > > > > > This kolom1 is a "char(1)" ! > > > > > > The result is okay, but what does it do with the performance? > > > > Practically speaking, probably not much. The optimizer will not be > able > > to use any index starting with that column and will only use the key > in > > other indexes containing that column up to that column but since the > filter > > value of a one character column is rarely high those indexes that > start > > with koloml are not likely to be used for filtering anyway. If you > can > > change it do. You can determine the actual cost by running versions > of > > the same query under SET EXPLAIN ON with the MATCHES clause and with > an > > equality filter and compare the query plans and costs. > > > > Art S. Kagel > > > > Oh, great Obi-wan... Doesn't a 'matches' force a sequential scan on the > table? I didn't think it would use an index at all. I could be wrong, > I don't use it much. Just wondering. No Luke, seek not the force let it flow through you. A MATCHES will prevent that one filter from using an index (although if the MATCHES column is toward the end of an index key the earlier part of the key WILL be used for index filteriing and the results will be index-sequential lower filtered for the MATCHES column) but other index keys CAN be used and the results will be filtered for the MATCHES condition. Art S. Kagel
In article <390F3C8C.8843DBDF@bloomberg.net>, Art S. Kagel
<kagel@bloomberg.net> writes
>mars1972@my-deja.com wrote:
>>
>> In article <390F0C44.188C7CE3@bloomberg.net>,
>> kagel@bloomberg.net wrote:
>> > "X. Pietje" wrote:
>> > >
>> > > Hi All,
>> > >
>> > > I found in our ERP-Application the following SQL-statement:
>> > > ......
>> > > where kolom1 matches '*O*'
>> > > ......
>> > >
>> > > This kolom1 is a "char(1)" !
>> > >
>> > > The result is okay, but what does it do with the performance?
>> >
>> > Practically speaking, probably not much. The optimizer will not be
>> able
>> > to use any index starting with that column and will only use the key
>> in
>> > other indexes containing that column up to that column but since the
>> filter
>> > value of a one character column is rarely high those indexes that
>> start
>> > with koloml are not likely to be used for filtering anyway. If you
>> can
>> > change it do. You can determine the actual cost by running versions
>> of
>> > the same query under SET EXPLAIN ON with the MATCHES clause and with
>> an
>> > equality filter and compare the query plans and costs.
>> >
>> > Art S. Kagel
>> >
>>
>> Oh, great Obi-wan... Doesn't a 'matches' force a sequential scan on the
>> table? I didn't think it would use an index at all. I could be wrong,
>> I don't use it much. Just wondering.
>
>No Luke, seek not the force let it flow through you. A MATCHES will prevent
>that one filter from using an index (although if the MATCHES column is
>toward the end of an index key the earlier part of the key WILL be used for
>index filteriing and the results will be index-sequential lower filtered
>for the MATCHES column) but other index keys CAN be used and the results
>will be filtered for the MATCHES condition.
>
>Art S. Kagel
Aaah, young Jedi...study the ways of Master Kagel well. Follow then
enlightment will. Focus, open your mind..
-------------
create table a
(
b char(20))
create index a1 on a(b);
set explain on;
insert into a values ("a") <repeat x8>
insert into a values ("b") <repeat x3>
update statistics high for table a
set explain on;
select * from a where b matches "b*";
...
follow the path of the query...
QUERY:
------
select * from a where b matches "b*"
Estimated Cost: 1
Estimated # of Rows Returned: 3
1) informix.a: INDEX PATH
(1) Index Keys: b (Key-Only)
Lower Index Filter: informix.a.b MATCHES 'b*'
....
used the index is..yes?
...
QUERY:
------
select * from a where b matches "*b"
Estimated Cost: 2
Estimated # of Rows Returned: 2
1) informix.a: SEQUENTIAL SCAN
Filters: informix.a.b MATCHES '*b'
Ah, used not the index is, eh?
The star must not the leading character. Eh?
--
David Williams
Yes also try this one:
create table t45 (
one char(10),
two char(10)
);
insert into t45 values ("fred", "alan"); X20
insert into t45 values ("george","brandon"); X11
create index t45_1 on t45(one);
create index t45_2 on t45(two);
create index t45_3 on t45(one,two);
create index t45_4 on t45(two,one);
update statistics HIGH on table t45;
set explain on;
Check the query paths:
QUERY:
------
select * from t45 where two matches "a*"
Estimated Cost: 2
Estimated # of Rows Returned: 20
1) kagel.t45: INDEX PATH
(1) Index Keys: two one (Key-Only)
Lower Index Filter: kagel.t45.two MATCHES 'a*'
QUERY:
------
select * from t45 where two matches "b*"
Estimated Cost: 1
Estimated # of Rows Returned: 11
1) kagel.t45: INDEX PATH
(1) Index Keys: two one (Key-Only)
Lower Index Filter: kagel.t45.two MATCHES 'b*'
QUERY:
------
select * from t45 where two matches "*a*"
Estimated Cost: 2
Estimated # of Rows Returned: 11
1) kagel.t45: INDEX PATH
Filters: kagel.t45.two MATCHES '*a*'
(1) Index Keys: one two (Key-Only)
QUERY:
------
select * from t45 where two matches "*b*"
Estimated Cost: 2
Estimated # of Rows Returned: 11
1) kagel.t45: INDEX PATH
Filters: kagel.t45.two MATCHES '*b*'
(1) Index Keys: one two (Key-Only)
QUERY:
------
select * from t45 where one = "fred" and two matches "*l*"
Estimated Cost: 2
Estimated # of Rows Returned: 7
1) kagel.t45: INDEX PATH
Filters: kagel.t45.two MATCHES '*l*'
(1) Index Keys: one two (Key-Only)
Lower Index Filter: kagel.t45.one = 'fred'
Art S. Kagel
David Williams wrote:
>
> In article <390F3C8C.8843DBDF@bloomberg.net>, Art S. Kagel
> <kagel@bloomberg.net> writes
> >mars1972@my-deja.com wrote:
> >>
> >> In article <390F0C44.188C7CE3@bloomberg.net>,
> >> kagel@bloomberg.net wrote:
> >> > "X. Pietje" wrote:
> >> > >
> >> > > Hi All,
> >> > >
> >> > > I found in our ERP-Application the following SQL-statement:
> >> > > ......
> >> > > where kolom1 matches '*O*'
> >> > > ......
> >> > >
> >> > > This kolom1 is a "char(1)" !
> >> > >
> >> > > The result is okay, but what does it do with the performance?
> >> >
> >> > Practically speaking, probably not much. The optimizer will not be
> >> able
> >> > to use any index starting with that column and will only use the key
> >> in
> >> > other indexes containing that column up to that column but since the
> >> filter
> >> > value of a one character column is rarely high those indexes that
> >> start
> >> > with koloml are not likely to be used for filtering anyway. If you
> >> can
> >> > change it do. You can determine the actual cost by running versions
> >> of
> >> > the same query under SET EXPLAIN ON with the MATCHES clause and with
> >> an
> >> > equality filter and compare the query plans and costs.
> >> >
> >> > Art S. Kagel
> >> >
> >>
> >> Oh, great Obi-wan... Doesn't a 'matches' force a sequential scan on the
> >> table? I didn't think it would use an index at all. I could be wrong,
> >> I don't use it much. Just wondering.
> >
> >No Luke, seek not the force let it flow through you. A MATCHES will prevent
> >that one filter from using an index (although if the MATCHES column is
> >toward the end of an index key the earlier part of the key WILL be used for
> >index filteriing and the results will be index-sequential lower filtered
> >for the MATCHES column) but other index keys CAN be used and the results
> >will be filtered for the MATCHES condition.
> >
> >Art S. Kagel
>
> Aaah, young Jedi...study the ways of Master Kagel well. Follow then
> enlightment will. Focus, open your mind..
>
> -------------
> create table a
> (
> b char(20))
> create index a1 on a(b);
> set explain on;
> insert into a values ("a") <repeat x8>
> insert into a values ("b") <repeat x3>
> update statistics high for table a
> set explain on;
> select * from a where b matches "b*";>
> ...
> follow the path of the query...
>
> QUERY:
> ------
> select * from a where b matches "b*">
> Estimated Cost: 1
> Estimated # of Rows Returned: 3
>
> 1) informix.a: INDEX PATH
>
> (1) Index Keys: b (Key-Only)
> Lower Index Filter: informix.a.b MATCHES 'b*'
>
> ....
>
> used the index is..yes?
>
> ...
>
> QUERY:
> ------
>
> select * from a where b matches "*b">
> Estimated Cost: 2
> Estimated # of Rows Returned: 2
>
> 1) informix.a: SEQUENTIAL SCAN
>
> Filters: informix.a.b MATCHES '*b'
>
> Ah, used not the index is, eh?
>
> The star must not the leading character. Eh?
>
> --
> David Williams
Thanks Art, for doing all that work!
I will try this on one of our large tables.
Alex
"Art S. Kagel" wrote:
> Yes also try this one:
>
> create table t45 (
> one char(10),
> two char(10)
> );
> insert into t45 values ("fred", "alan"); X20
> insert into t45 values ("george","brandon"); X11>
> create index t45_1 on t45(one);
> create index t45_2 on t45(two);
> create index t45_3 on t45(one,two);
> create index t45_4 on t45(two,one);>
> update statistics HIGH on table t45;>
> set explain on;>
> Check the query paths:
>
> QUERY:
> ------
> select * from t45 where two matches "a*">
> Estimated Cost: 2
> Estimated # of Rows Returned: 20
>
> 1) kagel.t45: INDEX PATH
>
> (1) Index Keys: two one (Key-Only)
> Lower Index Filter: kagel.t45.two MATCHES 'a*'
>
> QUERY:
> ------
> select * from t45 where two matches "b*">
> Estimated Cost: 1
> Estimated # of Rows Returned: 11
>
> 1) kagel.t45: INDEX PATH
>
> (1) Index Keys: two one (Key-Only)
> Lower Index Filter: kagel.t45.two MATCHES 'b*'
>
> QUERY:
> ------
> select * from t45 where two matches "*a*">
> Estimated Cost: 2
> Estimated # of Rows Returned: 11
>
> 1) kagel.t45: INDEX PATH
>
> Filters: kagel.t45.two MATCHES '*a*'
>
> (1) Index Keys: one two (Key-Only)
>
> QUERY:
> ------
> select * from t45 where two matches "*b*">
> Estimated Cost: 2
> Estimated # of Rows Returned: 11
>
> 1) kagel.t45: INDEX PATH
>
> Filters: kagel.t45.two MATCHES '*b*'
>
> (1) Index Keys: one two (Key-Only)
>
> QUERY:
> ------
> select * from t45 where one = "fred" and two matches "*l*">
> Estimated Cost: 2
> Estimated # of Rows Returned: 7
>
> 1) kagel.t45: INDEX PATH
>
> Filters: kagel.t45.two MATCHES '*l*'
>
> (1) Index Keys: one two (Key-Only)
> Lower Index Filter: kagel.t45.one = 'fred'
>
> Art S. Kagel
>
> David Williams wrote:
> >
> > In article <390F3C8C.8843DBDF@bloomberg.net>, Art S. Kagel
> > <kagel@bloomberg.net> writes
> > >mars1972@my-deja.com wrote:
> > >>
> > >> In article <390F0C44.188C7CE3@bloomberg.net>,
> > >> kagel@bloomberg.net wrote:
> > >> > "X. Pietje" wrote:
> > >> > >
> > >> > > Hi All,
> > >> > >
> > >> > > I found in our ERP-Application the following SQL-statement:
> > >> > > ......
> > >> > > where kolom1 matches '*O*'
> > >> > > ......
> > >> > >
> > >> > > This kolom1 is a "char(1)" !
> > >> > >
> > >> > > The result is okay, but what does it do with the performance?
> > >> >
> > >> > Practically speaking, probably not much. The optimizer will not be
> > >> able
> > >> > to use any index starting with that column and will only use the key
> > >> in
> > >> > other indexes containing that column up to that column but since the
> > >> filter
> > >> > value of a one character column is rarely high those indexes that
> > >> start
> > >> > with koloml are not likely to be used for filtering anyway. If you
> > >> can
> > >> > change it do. You can determine the actual cost by running versions
> > >> of
> > >> > the same query under SET EXPLAIN ON with the MATCHES clause and with
> > >> an
> > >> > equality filter and compare the query plans and costs.
> > >> >
> > >> > Art S. Kagel
> > >> >
> > >>
> > >> Oh, great Obi-wan... Doesn't a 'matches' force a sequential scan on the
> > >> table? I didn't think it would use an index at all. I could be wrong,
> > >> I don't use it much. Just wondering.
> > >
> > >No Luke, seek not the force let it flow through you. A MATCHES will prevent
> > >that one filter from using an index (although if the MATCHES column is
> > >toward the end of an index key the earlier part of the key WILL be used for
> > >index filteriing and the results will be index-sequential lower filtered
> > >for the MATCHES column) but other index keys CAN be used and the results
> > >will be filtered for the MATCHES condition.
> > >
> > >Art S. Kagel
> >
> > Aaah, young Jedi...study the ways of Master Kagel well. Follow then
> > enlightment will. Focus, open your mind..
> >
> > -------------
> > create table a
> > (
> > b char(20))
> > create index a1 on a(b);
> > set explain on;
> > insert into a values ("a") <repeat x8>
> > insert into a values ("b") <repeat x3>
> > update statistics high for table a
> > set explain on;
> > select * from a where b matches "b*";> >
> > ...
> > follow the path of the query...
> >
> > QUERY:
> > ------
> > select * from a where b matches "b*"> >
> > Estimated Cost: 1
> > Estimated # of Rows Returned: 3
> >
> > 1) informix.a: INDEX PATH
> >
> > (1) Index Keys: b (Key-Only)
> > Lower Index Filter: informix.a.b MATCHES 'b*'
> >
> > ....
> >
> > used the index is..yes?
> >
> > ...
> >
> > QUERY:
> > ------
> >
> > select * from a where b matches "*b"> >
> > Estimated Cost: 2
> > Estimated # of Rows Returned: 2
> >
> > 1) informix.a: SEQUENTIAL SCAN
> >
> > Filters: informix.a.b MATCHES '*b'
> >
> > Ah, used not the index is, eh?
> >
> > The star must not the leading character. Eh?
> >
> > --
> > David Williams