Hi I'm trying yo fully understand how FB takes decisions to optimize a query in my DB. Please check this two queries. The results are the same, but the second query is much better that the first one. It seems that the left outer join of the secondary table able FB to use their indexes. I could not understand why the first query reads 300k records. If I do a query to select all the records between these days I receive 48k records. ¿Why FB reads more than 300k records to do this Query?
Kind regards Diego Query #1 Select * >From maestro_turnos mt Where mt.fecha_turno >= '2011-01-01' And mt.fecha_turno <= '2011-01-31' And mt.paciente is null Number of records readed: 304,222 Number of records fetched: 5,128 Plan analisis: PLAN (MT INDEX (FK_MAESTRO_TURNOS_3)) FK_MAESTRO_TURNOS_3: indexed on field "paciente" Query #2 Select * >From maestro_turnos mt Left Outer Join maestro_pacientes mp on mp.codigo = mt.paciente Where mt.fecha_turno >= '2011-01-01' And mt.fecha_turno <= '2011-01-31' And mp.Codigo is null Plan analisis: PLAN JOIN (MT INDEX (MAESTRO_TURNOS_IDX1), MP INDEX (RDB$PRIMARY57)) Number of records readed: mt: 1,036. mp:880 Number of records fetched: 5,128 FK_MAESTRO_TURNOS_3: indexed on field "Fecha_Turno"
