Re: Matches not Work in 7.30
We had the same problem and I even opened a case up with Informix (and yes, we did update
statistics). In our case, and probably yours as well, the matches does work, it just takes
forever to come back with any rows. It turns out that it was related to the new sub-query
flattening feature of 7.30. Once we turned it off using the NO_SUBQF environment variable (not
documented - set to a non-zero value in shell before you bring the engine up), our problem went
away.
Another interesting note, the combination of OPT_GOAL = first rows (can't remember the number
that equates to) and sub-query flattening turns out to be DEADLY to some queries. If turning
sub-query flattening off doesn't seem to make a difference, make sure you also have OPT_GOAL set
to all rows (again whatever the numerical value that is)!
--- On Fri, 27 Nov 1998 14:02:06 +0200 Billy Wheeler <billy@west.co.za> wrote:
On 27 Nov 98, at 12:06, Petr Pomykacek wrote:
Did you run update statistics?
> Hi,
>
> I have IDS 7.30.UC2 on SCO Open server 3.2.5.0.2 and 7.30.UC3 with
> Solaris 2.6
> After switch to 7.30 from 5.08 discontinued work MATCHES.
> Follow example witt sqlexplain output.
>
> explain:
>
> QUERY:
> ------
> select * from ziv_sbj where obcvel matches "INFORMIX SOFTWARE S.R.O.">
> Estimated Cost: 5
> Estimated # of Rows Returned: 1
>
> 1) zu6.ziv_sbj: INDEX PATH
>
> (1) Index Keys: obcvel
> Lower Index Filter: zu6.ziv_sbj.obcvel = 'INFORMIX SOFTWARE
> S.R.O.'
>
>
> QUERY:
> ------
> select * from ziv_sbj where obcvel matches "INFORMIX*">
> Estimated Cost: 59202
> Estimated # of Rows Returned: 76387
>
> 1) zu6.ziv_sbj: SEQUENTIAL SCAN !!!!!!!!!
>
> Filters: zu6.ziv_sbj.obcvel MATCHES 'INFORMIX*'
>
> Table has 500 000 rows
>
> create table "zu6".ziv_sbj
> (
> zivsbj integer not null ,
> cissbj date,
> prafor smallint not null ,
> rocpod char(11),
> jmepod char(15),
> pripod char(20),
> obcjme char(64),
> obcvel char(64),
> icopod char(9),
> sbjsta char(16),
> _stpins datetime year to minute not null ,
> _usrins char(8) not null ,
> _stpupd datetime year to minute,
> _usrupd char(8),
> _numver integer not null ,
> _nxtrec integer,
> primary key (zivsbj)
> );
> revoke all on "zu6".ziv_sbj from "public";>
> create index "zu6".i_ziv_sbj_1 on "zu6".ziv_sbj (rocpod,icopod);
>
> create index "zu6".i_ziv_sbj_2 on "zu6".ziv_sbj (icopod,rocpod);
>
> create index "zu6".i_ziv_sbj_3 on "zu6".ziv_sbj (obcjme);
> create index "zu6".i_ziv_sbj_4 on "zu6".ziv_sbj (obcvel);
> create index "zu6".i_ziv_sbj_6 on "zu6".ziv_sbj (pripod,jmepod);
>
> thanks Petr
>
>
>
>
>
--
Ciao,
Billy
None of the ideas expressed above are actually mine. They are told to me by
Luthor and Ferdinand, the five inch tall space aliens who live under my desk.
In return for these ideas, I have given them permission to eat any dust
bunnies they may find there. Your eyes are weary from staring at the CRT.
You feel sleepy. Notice how restful it is to watch the cursor blink.
Close your eyes. The opinions stated above are yours. You cannot imagine
why you ever felt otherwise. The opinions expressed in this letter do, in
fact, represent the opinions of West, its employees, directors and customers,
as well as the entire IT industry of South Africa, no, the world, because,
let's face it, I have that kind of power at my disposal.
---------------End of Original Message-----------------
--------------------------------------------------------
Name: Bill Weaver
E-mail: Bill Weaver <billw@fscorp.com>
Date: 11/27/98
Time: 10:53:48
This message was sent by Z-Mail Pro - from NetManage
NetManage - delivers Standards Based IntraNet Solutions
--------------------------------------------------------