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

From: Karl R. <kreitsch_at_zdnetonebox.com>
Date: 2000/05/02
Message-ID: <24b2b424.c4731a9d@usw-ex0105-034.remarq.com>#1/1

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 Tue May 02 2000 - 00:00:00 CDT