Re: Slow query

"Alan" <alanshein_at_spambuster.erols.com> wrote in message news:8o12uu$ggp$1_at_bob.news.rcn.net...
> Thanks for the suggestions. Here's some clarification:
>
> I am using Rule Based Optimization, not CBO, so no statistics have been
 run.
> Also, without the concatenated NULL, Explain Plan showed no indexes in use
> in the Select, and it was much slower. I do have the indexes in
 trans_facts
> disabled while I do the Insert. I am aware of the 10% rule-of-thumb. Also,
> this is 7.3.4.
>
> Thanks again.
In your orginal question you are using a distributed query from two different sources.
> 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;
This hinders many optimization possiblities (like hash joining etc.). As soon as the oracle sees an _at_ sign in the from clause it assumes a distributed query and uses much more restrictive rules. You might speed the query by transporting the whole claims table to system b and do the select there.

If a and b are really the same database use the same aliase even if it is in a different schemas.

You might get better answers if you post your question to the comp.databases.oracle.server group and include the query plan.

Willem Dekker Received on Thu Aug 24 2000 - 11:05:54 CEST