- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Query Optimization.
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-05-2004 07:34 AM
тАО01-05-2004 07:34 AM
Re: Query Optimization.
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-06-2004 03:31 AM
тАО01-06-2004 03:31 AM
Re: Query Optimization.
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-06-2004 08:11 AM
тАО01-06-2004 08:11 AM
Re: Query Optimization.
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-06-2004 07:36 PM
тАО01-06-2004 07:36 PM
Re: Query Optimization.
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-07-2004 09:45 PM
тАО01-07-2004 09:45 PM
Re: Query Optimization.
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-08-2004 05:00 AM
тАО01-08-2004 05:00 AM
Re: Query Optimization.
Why this condition "like '%1'";?
Thanks
GSM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-08-2004 07:53 AM
тАО01-08-2004 07:53 AM
Re: Query Optimization.
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-08-2004 04:44 PM
тАО01-08-2004 04:44 PM
Re: Query Optimization.
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-08-2004 04:47 PM
тАО01-08-2004 04:47 PM
Re: Query Optimization.
THanks,
Brian
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-08-2004 06:22 PM
тАО01-08-2004 06:22 PM
Re: Query Optimization.
I am on three days holidays. I'll post on Monday.
Thanks a lot for helping.
warm regds
GSM
- « Previous
-
- 1
- 2
- Next »