Re: Query-Optimizer on HDR-Secondary
Hi folks,
I talked to my informix-supporter: Rebuilding the hdr-mirror solved the
problem.
Regards
Markus Bschorer
"Markus Bschorer" <mb@worxbox.com> schrieb im Newsbeitrag
news:4bd44874$0$6892$9b4e6d93@newsspool2.arcor-online.net...
> Hi Art,
>
> thanks a lot for your answers. I found out, that Version 10.00.FC8
> (Solaris) has the same problem.
> I'll talk to my informix-support about that beahaviour.
> In the meantime, the following simplified index helps me to solve the
> problem:
> create unique index per_idx_02 on wbx_permission (per_object_id) using> btree in datadbs;
>
> Markus
>
>
> "Art Kagel" <art.kagel@gmail.com> schrieb im Newsbeitrag
> news:mailman.152.1272179781.1071.informix-list@iiug.org...
> Gots to ask the dumb questions first! Open a tech support case. You've
> found a bug!
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> IIUG Board of Directors (art@iiug.org)
>
> See you at the 2010 IIUG Informix Conference
> April 25-28, 2010
> Overland Park (Kansas City), KS
> www.iiug.org/conf
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Advanced DataTools, 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 Sun, Apr 25, 2010 at 3:05 AM, Markus Bschorer <mb@worxbox.com> wrote:
>
> Hi Art,
>
> Yes, of course. The UPDATE STATISTICS commands were executed on the
> primary-server. Otherwise the primary-server's optimizer wouldn't choose
> an index when building the query plan. I expect, that the distributions
> are transferred to the secondary via HDR automatically.
>
> Markus
> "Art Kagel" <art.kagel@gmail.com> schrieb im Newsbeitrag
> news:mailman.151.1272176051.1071.informix-list@iiug.org...
> Are the data distributions (UPDATE STATISTICS) up-to-date and at
> sufficient detail?
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> IIUG Board of Directors (art@iiug.org)
>
> See you at the 2010 IIUG Informix Conference
> April 25-28, 2010
> Overland Park (Kansas City), KS
> www.iiug.org/conf
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Advanced DataTools, 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 Sat, Apr 24, 2010 at 3:57 PM, Markus Bschorer <mb@worxbox.com> wrote:
>
> IDS 11.5 FC4
> Linux 64 Bit
>
> Hi everybody,
>
> it seems, that the IDS-Optimizer works different on primary and
> (hdr-)secondary servers, having identical IDS-Versions.
>
> Example:
>
> create table wbx_permission
> (
> per_object_id char(20),
> per_client_id char(10),
> per_user_type integer,
> per_perm_type smallint
> ) extent size 3170928 next size 317092 lock mode row;
> revoke all on wbx_permission from "public" as "informix";>
> create index idx_per_client_id on wbx_permission (per_client_id) using> btree
> in datadbs;
> create unique index per_idx_01 on wbx_permission
> (per_object_id,per_client_id,per_user_type,per_perm_type) using btree in> datadbs;
>
> Rows: 130.210.975
>
> The SQL-statement:
> SELECT * FROM wbx_permission WHERE per_object_id = "DEU99999990000235014">
> The primary server uses the index per_idx_01. ==> Response time: 2 ms.
> The (updatable) secondary server does a sequential scan. ==> Response
> time:
> 53 sec.
>
> Does anybody has an idea, whats going wrong on the secondary?
>
>
> Thanks a lot for any answer.
>
> Markus
>
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
>
>
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>