Query Optimization.

[Rick Meijer]

Hello,

Just a couple of suggestions on the rollback issue.

(Do the following in a test environment)

1. Put a commit after the delete. This will reduce the rollback requirements.

2. Create a tablespace with one rollback segment. Autoextend the tablespace. Let the number of extents for the rollback segment be unlimited.

3. Use the Set Transaction statement to force the transaction to use this rollback segment.

4. Don't use the optimal option.

5. Observe the rollback resources that the transaction needs.

Best wishes.

Rick.

[Ghulam Shabir Mallah_1]

Hi, I am repeating question again.

There are two tables.
1. Account_Detail
2. Account_Detail_Delta

9M records in Account_Detail and 400000 records in Account_Detail_Delta.

Please have look query which I am applying and tell me how long it will take. I have indexes on both tables on acctnumber.

delete from account_detail where acctnumber in ( select acctnumber from account_detail_delta);

commit;

/* Insert new values */

insert into account_detail ( ACCTNUMBER,NEXT_BILL_DATE,LAST_BILL_DATE,LAST_BILL_AMOUNT,
PREV_PAYMENT,ACCT_BALANCE,PAYMENT_DUE_DATE,PREV_BILL_AMOUNT,NET_PREV_AMT,CURRENT_SE_CHARGE,
TOTAL_CALL,TOTAL_CURRENT_BILL,NET_ADJUSTMENT)
select ACCTNUMBER,NEXT_BILL_DATE,LAST_BILL_DATE,LAST_BILL_AMOUNT,PREV_PAYMENT,ACCT_BALANCE,
PAYMENT_DUE_DATE,PREV_BILL_AMOUNT,NET_PREV_AMT,CURRENT_SE_CHARGE,TOTAL_CALL,TOTAL_CURRENT_BILL,
NET_ADJUSTMENT
from account_detail_delta a;


I have 210MB rollback segment for this job.

I want to finish this task within minimum time. Please help me this is chalange for me.

I did apply many tips but not successed yet. I am using Oracle 8i 8.1.7.4 on HP 11.00.
4 CPU and 4GB Ram.

What you think about bellow?
I have used IN fuction in query. If I use EXISTS function what will be happen?

What you think about fuctiuon based indexes will it be usefull.

Please give advice.


Thanks

GSM

[Brian Crabtree]

If you are going to do this, verify that you have an index on acctnumber with the ACCOUNT_DETAIL and ACCOUNT_DETAIL_DELTA tables. This should improve your performance on the delete. The one for ACCOUNT_DETAIL_DELTA will need to be rebuilt before running it, but you will most likely see an overall time improvement.

Your insert looks good. Assuming that the information gets updated frequently, these commands might also work:

update account_detail acd set xxx = xxx where exists (select 1 from account_detail_delta acdd where acd.account_number = acdd.account_number);

insert into account_detail
(select * from account_detail_delta acdd1 where acd.account_number = acdd1.account_number)
where not exists
(select 1 from account_detail_delta acdd2
where acd.account_number = acdd2.account_number)

I doubt that these would show better improvement than what you have, but throwing them out there in case you want to try it.

Also, I would make sure that the indexes on those tables are unique. This should help with the index as well (no searching for duplicate keys).

Thanks,

Brian

[Ghulam Shabir Mallah_1]

Dear Brain,


Please have look my Tables and Indexs creation script. Please advice me.
One more thing current environment is taking more then 4 hours. As I told that 9m records are in Account_detail and 400000 records in Account_Detail_Delta.

Please find attachment.

Thanks

GSM

[Brian Crabtree]

Ok.

Try the following (I combined the two statements together, so the delete caught both duplicates from account_detail_delta and all of the accounts ending in '1. Then, you only need one insert statement, which should help some.)

delete from account_detail where acctnumber in (select acctnumber from account_detail_delta) or acctnumber like '%1';

insert into account_detail ( ACCTNUMBER, NEXT_BILL_DATE, LAST_BILL_DATE, LAST_BILL_AMOUNT, PREV_PAYMENT, ACCT_BALANCE, PAYMENT_DUE_DATE, PREV_BILL_AMOUNT, NET_PREV_AMT, CURRENT_SE_CHARGE,
TOTAL_CALL, TOTAL_CURRENT_BILL, NET_ADJUSTMENT)
select ACCTNUMBER, NEXT_BILL_DATE, LAST_BILL_DATE, LAST_BILL_AMOUNT, PREV_PAYMENT, ACCT_BALANCE,
PAYMENT_DUE_DATE, PREV_BILL_AMOUNT, NET_PREV_AMT, CURRENT_SE_CHARGE, TOTAL_CALL, TOTAL_CURRENT_BILL, NET_ADJUSTMENT
from account_detail_delta a;

Let me know how well this works. If you are getting perfomance problems on the delete, do the following:
-----------
@?/rdbms/admin/utlxplan
truncate table plan_table;
explain plan for
delete from account_detail where acctnumber in (select acctnumber from account_detail_delta) or acctnumber like '%1';

set linesize 150
set pagesize 1000
column operation format a50
column optimizer format a15
SELECT LPAD(' ',2*(LEVEL-1))||operation operation, optimizer,options, object_name,
position, cost from plan_table START WITH id = 0 CONNECT BY PRIOR id = parent_id;
-----------

If you don't think this will work, let me know.

Thanks,

Brian

[Ghulam Shabir Mallah_1]

Dear Brian,

Why this condition "like '%1'";?

Thanks

GSM

[Brian Crabtree]

I'm not sure that the substr would allow for the index to be used on the query. The like should allow the index to be used, and give the same functionality.

The like '%1' means that it would find any accounts ending in 1.

I should verify that all of your accounts are 10 characters in size (no shorter lenghts that could come up with this). If this is true, you will need to continue with the substr string. One possiblity is to create a functional index that would do this, and then hope that the delete statement will do a merge join on them (we can build a hint to force it).

Brian

[Ghulam Shabir Mallah_1]

Dear Brian,

I believe you are looking my old queries. Please have look after modification.

delete from account_detail where acctnumber in ( select acctnumber from account_detail_delta);

commit;

/* Insert new values */

insert into account_detail ( ACCTNUMBER,NEXT_BILL_DATE,LAST_BILL_DATE,LAST_BILL_AMOUNT,
PREV_PAYMENT,ACCT_BALANCE,PAYMENT_DUE_DATE,PREV_BILL_AMOUNT,NET_PREV_AMT,CURRENT_SE_CHARGE,
TOTAL_CALL,TOTAL_CURRENT_BILL,NET_ADJUSTMENT)
select ACCTNUMBER,NEXT_BILL_DATE,LAST_BILL_DATE,LAST_BILL_AMOUNT,PREV_PAYMENT,ACCT_BALANCE,
PAYMENT_DUE_DATE,PREV_BILL_AMOUNT,NET_PREV_AMT,CURRENT_SE_CHARGE,TOTAL_CALL,TOTAL_CURRENT_BILL, NET_ADJUSTMENT
from account_detail_delta a;


You see my third last reply where I have post same queries. Now have we to do work around on this query or have to think any alternative tips on it.


Thanks a lot.

GSM

[Brian Crabtree]

Can you run and post the explain plan on the query that takes the longest?

THanks,

Brian

[Ghulam Shabir Mallah_1]

Dear Brain,

I am on three days holidays. I'll post on Monday.

Thanks a lot for helping.

warm regds

GSM