Help needed.
This is a multi-part message in MIME format.
--------------40461D182FD4
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
--
Carlos 'Nyman' Novo
Sainsel S.A.
C/ Manuel Velasco Pando
Sevilla.
e-mail : cnovo@sainsel.es
--------------40461D182FD4
Content-Type: text/plain; charset=us-ascii; name="mail"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline; filename="mail"
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
);
revoke all on "user".table5 from "public";
alter table "user".tableA add constraint (foreign key (code)
references "user".table5 constraint "user".r106_260);
alter table "user".Table1 add constraint (foreign key (row_code)
references "user".tableA constraint "user".r109_259);
@@NL@