Help needed.

Help needed.

Post by Carlos Novo Negrill » Sun, 31 Dec 1899 09:00:00



--
Carlos 'Nyman' Novo

Sainsel S.A.
C/ Manuel Velasco Pando
Sevilla.

[ mail 6K ]
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);

alter table "user".table2 add constraint (foreign key (serie)
    references "user".Table1  constraint "informix".r110_253);

alter table "user".sen_msj_rx add constraint (foreign key (serie)
    references "user".Table1  constraint "informix".r111_256);

alter table "user".table4 add constraint (foreign key (serie,orden)
    references "user".table2  constraint "user".r112_277);

alter table "user".table3 add constraint (foreign key (serie,
    orden) references "user".table2  constraint "user".r113_278);

 
 
 

Help needed.

Post by Yang, Heung-Soo » Sun, 31 Dec 1899 09:00:00


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 : cn...@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
>   );
> 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);

> alter table "user".table2 add constraint (foreign key (serie)
>     references "user".Table1  constraint "informix".r110_253);

> alter table "user".sen_msj_rx add constraint (foreign key (serie)
>     references "user".Table1  constraint "informix".r111_256);

> alter table "user".table4 add constraint (foreign key (serie,orden)
>     references "user".table2  constraint "user".r112_277);

> alter table "user".table3 add constraint (foreign key (serie,
>     orden) references "user".table2  constraint "user".r113_278);

  vcard.vcf
< 1K Download