Re: Help needed.
This is a multi-part message in MIME format.
--------------EDD15CA41A555490D61DD2DB
Content-Type: text/plain; charset=EUC-KR
Content-Transfer-Encoding: 7bit
As far as I know, you cannot force the optimizer to switch filtering sequence.
If you wonna better filtering sequence, you should run "update statistics" occasionally.
Carlos Novo Negrillo wrote:
> --
> Carlos 'Nyman' Novo
>
> Sainsel S.A.
> C/ Manuel Velasco Pando
> Sevilla.
> e-mail : cnovo@sainsel.es
>
> ------------------------------------------------------------------------
> After de Select :
>
>
> SELECT buq.code, buq.field1,pos.fecha, pos.hora,pos.field2,
> pos.zees, cab.field3, cab.field4, cab.row_code,> cab.serie, pos.orden , pto.puerto , zes.zona
> FROM tableA mov, table5 buq,
> OUTER ( Table1 cab, table2 pos , OUTER table3 pto,
> OUTER table4 zes )
> WHERE (mov.row_code = 12345678) AND mov.code = buq.code
> AND pos.fecha >= 19980401 AND pos.fecha <= 19980401
> AND cab.row_code = mov.row_code AND pos.serie = cab.serie
> AND pto.serie = pos.serie AND pto.orden = pos.orden
> AND zes.serie = pos.serie AND zes.orden = pos.orden
> ORDER BY buq.code, pos.fecha, pos.hora
>
> The SqlExplain ON returns :
>
> Estimated Cost: 12
> Estimated # of Rows Returned: 1
> Temporary Files Required For: Order By
>
> 1) user.mov: INDEX PATH
>
> (1) Index Keys: row_code
> Lower Index Filter: user.mov.row_code = 12345678
>
> 2) user.buq: INDEX PATH
>
> (1) Index Keys: code
> Lower Index Filter: user.buq.code = cnn.mov.code
>
> 3) user.cab: INDEX PATH
>
> (1) Index Keys: row_code date1 time1
> Lower Index Filter: user.cab.row_code = cnn.mov.row_code
>
> 4) user.pos: INDEX PATH
>
> Filters: (user.pos.fecha >= 19980401 AND cnn.pos.fecha <= 19980410 )
>
> (1) Index Keys: serie
> Lower Index Filter: user.pos.serie = cnn.cab.serie
>
> 5) user.pto: INDEX PATH
>
> (1) Index Keys: serie orden puerto (Key-Only)
> Lower Index Filter: (user.pto.serie = cnn.pos.serie AND cnn.pto.orden = cnn.pos.orden )
>
> 6) user.zes: INDEX PATH
>
> (1) Index Keys: serie orden zona (Key-Only)
> Lower Index Filter: (user.zes.orden = cnn.pos.orden AND cnn.zes.serie = cnn.pos.serie )
>
> -----------------------
> My question is :
> -----------------------
>
> Why does Online do "cab.row_code = mov.row_code"
> when what I want is to get first rows where "pos.fecha >= 19980401 AND pos.fecha <= 19980410"
>
> Can I force the order of the filters I want ?
>
> Tables Table1 and table2 have about 4 million rows each one;
> other ones are quite small ( less than thousand rows each one ). And
> I think Online is wasting a lot of time joining Table1 and tableA
> by row_code ... is that correct ?
>
> I will thank any help for knowing what does it happen about !!
>
> The dbschema is :
>
> DBSCHEMA Schema Utility INFORMIX-SQL Version 7.20.UC3
> Copyright (C) Informix Software, Inc., 1984-1996
>
> { TABLE "user".tableA row size = 27 number of columns = 10 index size = 24 }
> create table "user".tableA
> (
> code char(4) not null constraint "user".n106_41,
> row_code integer not null constraint "user".n106_42,
> ...
> primary key (row_code) constraint "user".u106_257
> );
> revoke all on "user".tableA from "public";>
> { TABLE "user".Table1 row size = 48 number of columns = 16 index size = 84 }
> create table "user".Table1
> (
> serie serial not null constraint "user".n109_86,
> row_code integer not null constraint "user".n109_88,
> field3 smallint not null constraint "user".n109_99,
> ...
> primary key (serie) constraint "user".u109_85
> );
> revoke all on "user".Table1 from "public";>
> create index "informix".cabmsjrx_idmovil on "user".Table1 (row_code);
> create index "user".i_cab_fecha on "user".Table1 (row_code,date1,
> time1);
> create index "user".i_fecha1 on "user".Table1 (date1,time1);
> create index "user".i_fecha_hora on "user".Table1 (date2,row_code);
>
> { TABLE "user".table2 row size = 27 number of columns = 9 index size = 39 }
> create table "user".table2
> (
> serie integer not null constraint "user".n110_102,
> orden smallint not null constraint "user".n110_103,
> fecha integer not null constraint "user".n110_104,
> hora integer not null constraint "user".n110_105,
> pos_e integer not null constraint "user".n110_106,
> pos_n integer not null constraint "user".n110_107,
> actualizada char(1) not null constraint "user".n110_108,
> field2 smallint not null constraint "user".n110_109,
> zees smallint not null constraint "user".n110_110,
> primary key (serie,orden) constraint "user".u110_274
> );
> revoke all on "user".table2 from "public";>
> create index "informix".i_pos_fecha on "user".table2 (fecha);
> { TABLE "user".sen_msj_rx row size = 26 number of columns = 5 index size = 12 }
> create table "user".sen_msj_rx
> (
> serie integer not null constraint "user".n111_111,
> orden smallint not null constraint "user".n111_112,
> ...
> primary key (serie) constraint "informix".u111_255
> );
> revoke all on "user".sen_msj_rx from "public";>
> { TABLE "user".table4 row size = 8 number of columns = 3 index size = 33 }
> create table "user".table4
> (
> serie integer not null constraint "user".n112_116,
> orden smallint not null constraint "user".n112_117,
> zona smallint not null constraint "user".n112_118,
> primary key (serie,orden,zona) constraint "user".u112_275
> );
> revoke all on "user".table4 from "public";>
> { TABLE "user".table3 row size = 8 number of columns = 3 index size = 33 }
> create table "user".table3
> (
> serie integer not null constraint "user".n113_119,
> orden smallint not null constraint "user".n113_120,
> puerto smallint not null constraint "user".n113_121,
> primary key (serie,orden,puerto) constraint "user".u113_276
> );
> revoke all on "user".table3 from "public";>
> { TABLE "user".table5 row size = 117 number of columns = 17 index size = 96 }
> create table "user".table5
> (
> codigo char(5),
> code char(4) not null constraint "user".n133_222,
> field1 char(30) not null constraint "user".n133_223,
> ...
> primary key (code) constraint "user".u133_215
> );@@NL