sql tuning

From: Fabio <fabio_at_denhaag.org>
Date: Tue, 03 Oct 2000 11:42:29 GMT
Message-ID: <8rcgn5$ha6$1_at_nnrp1.deja.com>

Hello all,

I'm trying to tune a series of sql statements, all worked fine until I encountered this query:

       UPDATE &tbic_kbf_hlp_n    kbfh
       SET    kbfl_max_prod    = r_upd_1.l_max_prod1


       ,      kbfl_min_afst    = r_upd_1.l_min_afst1


       WHERE  kbfh.bwk_boekjaar_nr = r_upd_1.bwk_boekjaar_nr
       AND    kbfh.mnd_maand_nr    = r_upd_1.mnd_maand_nr
       AND    kbfh.kadm_code       = r_upd_1.kadm_code
       AND    kbfh.kdbr_nr         = r_upd_1.kdbr_nr
       AND    kbfh.kbfl_omschr     = r_upd_1.kbfl_omschr
       AND    kbfh.kbfl_pin_code   = r_upd_1.kbfl_pin_code;

I translated the query to a readable format for explain plan:

       UPDATE tbic_kbf_hlp_1     kbfh
       SET    kbfl_max_prod    = x


       ,      kbfl_min_afst    = x


       WHERE  kbfh.bwk_boekjaar_nr = x
       AND    kbfh.mnd_maand_nr    = x
       AND    kbfh.kadm_code       = x
       AND    kbfh.kdbr_nr         = x
       AND    kbfh.kbfl_omschr     = x
       AND    kbfh.kbfl_pin_code   = x

And the indexes on this table are(non unique):

table                  index_name                 column_name          u
---------------------- -------------------------- -------------------- -
tbic_kbf_hlp_1         tbic_kbf_hlp_1_idx1        bwk_boekjaar_nr      n
tbic_kbf_hlp_1         tbic_kbf_hlp_1_idx1        mnd_maand_nr         n
tbic_kbf_hlp_1         tbic_kbf_hlp_1_idx1        kadm_code            n
tbic_kbf_hlp_1         tbic_kbf_hlp_1_idx1        kdbr_nr              n
tbic_kbf_hlp_1         tbic_kbf_hlp_1_idx1        kbfl_omschr          n
tbic_kbf_hlp_1         tbic_kbf_hlp_1_idx1        kbfl_pin_code        n
tbic_kbf_hlp_1         tbic_kbf_hlp_1_idx2        kbfl_max_prod        n
tbic_kbf_hlp_1         tbic_kbf_hlp_1_idx2        kbfl_min_afst        n

Which seems logical to me. When I run explain plan the results are:

sql> select lpad(' ',2*(level-1))||operation||' '||options

  2     ||' '||object_name
  3     ||' '||decode(id, 0, 'cost = '||position) "query plan"
  4     from plan_table
  5     start with id = 0 and statement_id = 'emp_sal'
  6     connect by prior id = parent_id and statement_id ='emp_sal';

query plan

---

---

update statement cost =
  table access full tbic_kbf_hlp_1

And I don't want the full scan. This table holds 3.000.000 rows and the query doesn't perform. Does anybody have a tip for me? Am I missing something? The indexes seem logical to me. They should be used, imo. I'm working on Oracle 7.1 on VMS.

Thanks in advance.

Regards,
Fabio Bruna

--
email fabio at denhaag dot org


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Tue Oct 03 2000 - 13:42:29 CEST