Results 1 to 5 of 5

Thread: skip index

  1. #1
    Join Date
    Jul 2003
    Posts
    9

    Lightbulb Unanswered: skip index

    Hi,

    A PL/SQL was written as follows:
    <><><><><><><><><><><><><><><><><><><><><><><>
    INSERT INTO oracle.tstfab

    SELECT TO_CHAR(flt_date,'YYYYMM'), flt_num, flt_date, sector_origin, sector_dest, spl,
    SUM(booked_wgt)-SUM(nob_wgt), SUM(booked_wgt),
    SUM(booked_cnmt)-SUM(nob_cnmt), SUM(booked_cnmt)
    FROM
    ((SELECT /*+ index (onb_load onb_load_spl1_idx,
    onb_load_spl2_idx,
    onb_load_spl3_idx) */
    flt_num, flt_date, sector_origin, sector_dest, splcode spl,
    SUM(NVL(booked_wgt,0)) booked_wgt,
    SUM(NVL(onb_bkd_cnmt,0)) booked_cnmt,
    0 nob_wgt, 0 nob_cnmt
    FROM oracle.onb_load
    WHERE (spl1 = 'OOO' OR spl2 = 'OOO' OR spl3 = 'OOO') AND
    (booked_wgt > 0)
    GROUP BY flt_num, flt_date, sector_origin, sector_dest)

    UNION ALL

    another SELECT sql statement
    <><><><><><><><><><><><><><><><><><><><><><><>

    The problem is from TKPROF information the indexes were NOT used! Full table scan on the table oracle.onb_load was performed!

    However when I just executed the following SQL statement in SQLPLUS:
    SELECT /*+ index (onb_load onb_load_spl1_idx,
    onb_load_spl2_idx,
    onb_load_spl3_idx) */
    flt_num, flt_date, sector_origin, sector_dest, splcode spl,
    SUM(NVL(booked_wgt,0)) booked_wgt,
    SUM(NVL(onb_bkd_cnmt,0)) booked_cnmt,
    0 nob_wgt, 0 nob_cnmt
    FROM oracle.onb_load
    WHERE (spl1 = 'OOO' OR spl2 = 'OOO' OR spl3 = 'OOO') AND
    (booked_wgt > 0)
    GROUP BY flt_num, flt_date, sector_origin, sector_dest

    Index IS USED!


    Could any expert give me a helping hand?

    Many Thanks!!!

  2. #2
    Join Date
    Jun 2003
    Posts
    34
    And whats is ur config set to ? CBO or RBO ?

  3. #3
    Join Date
    Jul 2003
    Posts
    9
    Originally posted by arin_am
    And whats is ur config set to ? CBO or RBO ?
    The optimizer_mode is set to CHOOSE.

    Thanks!

  4. #4
    Join Date
    Jun 2003
    Posts
    34
    Originally posted by kinsun
    The optimizer_mode is set to CHOOSE.

    Thanks!
    What I exactly wanted to know is... r u running in RBO or CBO ?

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Are statistics updated on the table and indexes ????

    If they are, what is the "skewness" of the indexes ...
    If they are skewed, you can run an analyze on the indexed columns
    and see if that helps...

    analyze table xxx estimate statistics sample 20 percent for all indexed columns;

    HTH
    Gregg

    ps... try your hint on 1 index at a time and look at the explain plan...

    /*+ index (table_name index_name) */

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •