Re: WHY SQL-Statement without bind-prameters runs 50 times faster as with ??????

Yes, to_date is a function, and when you put a function on the left side of your where clause statements, Oracle does full table scans.

There is a good book on how to tune SQL, SQL High Performance Tuning by Guy Harrison, he goes into a lot of detail.

Bind variables are annoying because you can't use explain plan with them, you have to trace the session, see in the trace file what variables were substituted, and plug them into the statement. Even then there is no guarantee that Oracle will do what the query's explain plan shows with the values placed directly inside it.

(I wish I could speak German!)

Pat.

"Karl R." wrote:

> ORACLE 7.3.4.1, AIX, PRO*C 2.2
>
> WHY SQL-Statement without bind-prameters runs 50 times faster
> as with ??????
> The index ist used in both cases! Is this prbably a problem of
> the to_date-Function ?
>
> All Tables have rows < 500
>
> This Statements runs with 0.250 Secs
>
> SELECT
> KETT.ID ,
> KETT.KETTENCODE ,
> KETT.NAME ,
> KETT.KETTENKLASSE ,
> KETT.AUSWEISUNG ,
> KETT.FK_ORGP_BETREUT_VON ,
> TO_CHAR(KETT.GUELTIG_VON,
> 'DD.MM.YYYY') ,
> TO_CHAR(KETT.GUELTIG_BIS,
> 'DD.MM.YYYY') ,
> KETT.FK_AGEN_ZAHLUNGSEMPF ,
> KETT.FK_VERW_ZAHLUNGSEMPF ,
> VERW.ID ,
> VERW.NAME ,
> VERW.SHORT_ID ,
> VERW.PLZ ,
> VERW.ORT ,
> VERW.FK_ORGP_BETREUT_VON ,
> VERW.FK_KETT ,
> VERW.VERW_TYPE ,
> VERW.FK_VERW_ZENTRALE ,
> TO_CHAR(VERW.GUELTIG_VON,
> 'DD.MM.YYYY') ,
> TO_CHAR(VERW.GUELTIG_BIS,
> 'DD.MM.YYYY') ,
> ORGP.CODE
> FROM
> KETT ,
> VERW ,
> ORGP
> WHERE
> ((((((((KETT.ID=VERW.FK_KETT and
> ORGP.ID=VERW.FK_ORGP_BETREUT_VON) and
> KETT.KETTENKLASSE='M') and
> SYSDATE<=KETT.GUELTIG_BIS) and
> SYSDATE>=KETT.GUELTIG_VON) and
> SYSDATE<=ORGP.GUELTIG_BIS) and
> SYSDATE>=ORGP.GUELTIG_VON) and
> SYSDATE<=VERW.GUELTIG_BIS) and
> SYSDATE>=VERW.GUELTIG_VON)
> order by KETT.KETTENCODE,KETT.NAME,VERW.FK_VERW_ZENTRALE
> desc ,VERW.SHORT_ID
>
> !!!!!!!!!!1This statment runs 9.000 secs !!!!!!!!!!!
>
> SELECT
> KETT.ID ,
> KETT.KETTENCODE ,
> KETT.NAME ,
> KETT.KETTENKLASSE ,
> KETT.AUSWEISUNG ,
> KETT.FK_ORGP_BETREUT_VON ,
> TO_CHAR(KETT.GUELTIG_VON,
> 'DD.MM.YYYY') ,
> TO_CHAR(KETT.GUELTIG_BIS,
> 'DD.MM.YYYY') ,
> KETT.FK_AGEN_ZAHLUNGSEMPF ,
> KETT.FK_VERW_ZAHLUNGSEMPF ,
> VERW.ID ,
> VERW.NAME ,
> VERW.SHORT_ID ,
> VERW.PLZ ,
> VERW.ORT ,
> VERW.FK_ORGP_BETREUT_VON ,
> VERW.FK_KETT ,
> VERW.VERW_TYPE ,
> VERW.FK_VERW_ZENTRALE ,
> TO_CHAR(VERW.GUELTIG_VON,
> 'DD.MM.YYYY') ,
> TO_CHAR(VERW.GUELTIG_BIS,
> 'DD.MM.YYYY') ,
> ORGP.CODE
> FROM
> KETT ,
> VERW ,
> ORGP
> WHERE
> ((((((((KETT.ID=VERW.FK_KETT and
> ORGP.ID=VERW.FK_ORGP_BETREUT_VON) and
> KETT.KETTENKLASSE='M') and
> TO_DATE(:b0,'DD.MM.YYYY')<=KETT.GUELTIG_BIS) and
> TO_DATE(:b0,'DD.MM.YYYY')>=KETT.GUELTIG_VON) and
> TO_DATE(:b0,'DD.MM.YYYY')<=ORGP.GUELTIG_BIS) and
> TO_DATE(:b0,'DD.MM.YYYY')>=ORGP.GUELTIG_VON) and
> TO_DATE(:b0,'DD.MM.YYYY')<=VERW.GUELTIG_BIS) and
> TO_DATE(:b0,'DD.MM.YYYY')>=VERW.GUELTIG_VON)
> order by KETT.KETTENCODE,KETT.NAME,VERW.FK_VERW_ZENTRALE
> desc ,VERW.SHORT_ID
>
> Karl Reitschuster
> Senior Consultant CSC Ploenzke AG
> Oracle Databases, Implementation, Performance-Tuning
> <!Jesus is Lord!>
> * Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *
> The fastest and easiest way to search and participate in Usenet - Free!
Received on Wed May 03 2000 - 00:00:00 CDT