Re: A very slow Select Statement

From: McCormick <mccormm_at_indy.net>
Date: 21 Sep 2005 12:07:24 -0700
Message-ID: <1127329644.731333.177940@g49g2000cwa.googlegroups.com>

This will teach me to post in a hurry on a Friday afternoon.

Let's try again.

My Oracle version is 8.1.6.3.0

Here is the inline view statement as it is now. This time I didn't simplify the tables names for ease of viewing.

SELECT /*+ INDEX(a ubbbhst_acct_index)

           INDEX(o uabopen_key_index_001)
           ORDERED */
       o.uabopen_cust_code,
       o.uabopen_prem_code,
       a.ubbbhst_printed_date,
       SUM(o.uabopen_balance) as balance
  FROM ubbbhst a,
       uabopen o
 WHERE o.uabopen_cust_code BETWEEN 750000 AND 800000
   AND a.ubbbhst_cust_code = o.uabopen_cust_code
   AND a.ubbbhst_prem_code = o.uabopen_prem_code

   AND UIMSMGR.f_calculate_credit_rating(o.uabopen_cust_code) IN (0,1)    AND a.ubbbhst_printed_date =

      (SELECT /*+ INDEX(b ubbbhst_acct_index) */
              MAX(ubbbhst_printed_date)
         FROM ubbbhst b
        WHERE a.ubbbhst_cust_code = b.ubbbhst_cust_code
          AND a.ubbbhst_prem_code = b.ubbbhst_prem_code
          AND b.ubbbhst_printed_date < sysdate-30
          AND b.ubbbhst_account_status_ind = 'F'
       )
  GROUP BY o.uabopen_cust_code,
           o.uabopen_prem_code,
           a.ubbbhst_printed_date

 HAVING SUM(o.uabopen_balance) > 5

It takes under 6 minutes to run with the limits I put on the customer code in the first line of the WHERE clause. When that line is removed, the query runs over an hour.

The UABOPEN table has over 200 million records. The UBBBHST table a mere 20 million.

I have a bad feeling that these tables have not been analysed with anything for a long time. DBMS_STATS would update the LAST_ANALYZED column, yes?

Here is the resultant execution plan.

Execution Plan

---

   0 SELECT STATEMENT Optimizer=RULE (Cost=12 Card=1 Bytes=58)    1 0 FILTER

   2    1     SORT (GROUP BY) (Cost=12 Card=1 Bytes=58)
   3    2       NESTED LOOPS (Cost=2 Card=1 Bytes=58)
   4    3         INDEX (RANGE SCAN) OF 'UBBBHST_ACCT_INDEX' (UNIQUE)


(Cost=2 Card=1 Bytes=27)

   5    4           SORT (AGGREGATE)
   6    5             TABLE ACCESS (BY INDEX ROWID) OF 'UBBBHST'


(Cost=1 Card=1 Bytes=29)

   7    6               INDEX (RANGE SCAN) OF 'UBBBHST_ACCT_INDEX'


(UNIQUE) (Cost=2 Card=1)

   8    3         INDEX (RANGE SCAN) OF 'UABOPEN_KEY_INDEX_001'

(NON-UNIQUE) (Cost=2 Card=1 Bytes=31)

The nice thing about this plan is that I was able to overindex the UABOPEN table. All the columns referenced in the WHERE and SELECT clauses are in that Index.

I can't do the same for the UBBBHST table. The UBBBHST_ACCOUNT_STATUS_IND column does not appear in any current index. I did a check and found that 'F' is one of only 5 distinct values
(including NULL) for that column, and that the records with 'F' take up
about 2% of all records in the table. I'm thinking I could do something useful with that.

And here are the statistics. I admit I don't fully understand these numbers yet. I just try to get them as small as possible.

Statistics

---

     926220 recursive calls
    1234960 db block gets
    2958341 consistent gets

          0  physical reads
          0  redo size
      14047  bytes sent via SQL*Net to client
       2269  bytes received via SQL*Net from client
         99  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        190  rows processed

  My main question is: Is there something efficient I can do with a statement when the same table shows up in the query and the sub-query.

  I appreciate your time!

    Mike McCormick Received on Wed Sep 21 2005 - 14:07:24 CDT