Slow query
Date: Wed, 23 Aug 2000 10:37:34 -0400
Message-ID: <8o0np9$rqt$1_at_bob.news.rcn.net>
Below is a query used to insert rows into a fact table in our datamart. It takes almost an hour to run, which strikes me as a rather long time. I managed to speed it up to this point (it used to take much longer) by concatenating a NULL in the WHERE clause to force the RBO to use the index on the larger table. All columns in the WHERE clause are indexed. policy_pfx is the first column of a composite index. The same query is run for other policy_pfx values, and the length of time to return results is directly related to the number of rows _returned_, not the number of rows queried, as this is a constant. Network performance is probably not an issue (100 MB Ethernet), and I've run the query when "target" is busy and when idle. No appreciable difference. I'd rather not use a PL/SQL solution. I've also tried taking claim_numb from either table (didn't expect any difference, but it was worth a try), and of course, no difference. claim_payment has almost 10 times as many rows as claim_table. Any ideas?
21:47:59 SQL> SET TRANSACTION USE ROLLBACK SEGMENT rb_big;
Transaction set.
INSERT INTO trans_facts (
policy_pfx, policy_sfx, transaction_date, paid_claim_amount, claim_numb
)
SELECT
a.policy_pfx, a.policy_sfx, b.paid_date, b.check_amount, a.claim_numb
FROM claim_payment_at_target b, claim_table_at_target a WHERE b.claim_numb = a.claim_numb||''
AND a.policy_pfx = 'LTD'
;
696321 rows created.
22:42:23 SQL> COMMIT; Received on Wed Aug 23 2000 - 16:37:34 CEST