Re: A very slow Select Statement

McCormick wrote:
> 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

Any reason you can't get to 8.1.7.4? Still unsupported but at least the optimizer wasn't brain dead.

Given the explain plan I can't believe this takes 6 seconds much less six minutes.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)