Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: A very slow Select Statement
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_codeAND 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
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'
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