| Urgent Please [message #279606] |
Fri, 09 November 2007 06:23  |
balajisundar
Messages: 29
Registered: October 2007
|
Junior Member |
|
|
Hi Guys,
I have created an index for a table in order to increase the performance of the below query
SELECT count(pa.rowid)
FROM product_account pa
WHERE pa.org_no IN (100, 200)
AND NVL(pa.blk_rsn_2_cd,'Q') NOT IN ('D','F','G','N','Y')
AND NVL(pa.blk_rsn_1_cd,'Q') NOT IN ('D','F','G','N','Y')
AND ( pa.cur_os_bal_am > pa.crln_am
OR pa.past_due_am > 0
OR pa.status_cd = 'Z'
OR (pa.past_due_am > 0 AND pa.ppc_in = 'Y')
OR (pa.blk_rsn_1_cd = 'Q' OR pa.blk_rsn_2_cd = 'Q')
OR (pa.cur_dlq_stg_cd IN ('2','3','4') AND pa.cur_os_bal_am > 5000) );
CREATE INDEX XYZ.PAC_INDEX_TESTING ON XYZ.PRODUCT_ACCOUNT (org_no,blk_rsn_2_cd,blk_rsn_1_cd,cur_os_bal_am,past_due_am) TABLESPACE INDEX_M;
i have tried using all th possibilities of columns. But my table doesnot do an index select for the above query.
Number of records in the table : 585297
Number of records fetched by the query : 36226
Its very urgent please... I need a solution for this.. I want to tune the performance.
Thanks and Regards,
Balaji
|
|
|
|
| Re: Urgent Please [message #279608 is a reply to message #279606] |
Fri, 09 November 2007 06:29  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Your index is of relatively little use to the query.
The only field in the index that the optimiser can use for your query is the first one - ORG_NO.
The next field in the index, blk_rsn_2_cd isn't used in the query - instead you use a function NVL(pa.blk_rsn_2_cd,'Q').
You could try recreating the index as a function based index on (for example) Org_no, NVL(pa.blk_rsn_2_cd,'Q'),NVL(pa.blk_rsn_1_cd,'Q').
|
|
|
|
|
|
| Re: Urgent Please [message #279628 is a reply to message #279623] |
Fri, 09 November 2007 07:43 |
balajisundar
Messages: 29
Registered: October 2007
|
Junior Member |
|
|
Hi,
Initially the product_account table had an index PAC_INDEX with the column names "ORG_NO", "CRACCT_NO", "PROD_NO".
In order to increase the performance of the query I have created an function index on the same product_account table and the cost of the query has come down. But there is another procedure in the same package that inserts and updated the product_account table using the index PAC_INDEX.
So im rebuilding my application with the PAC_INDEX_TESTING index. will this rebuild cause insert and updates performances affected? Nor the optimiser going to choose the best index out of those two and does the insert and update??
Thanks,
Balaji
[Updated on: Fri, 09 November 2007 07:44] Report message to a moderator |
|
|
|
| Re: Urgent Please [message #279737 is a reply to message #279628] |
Fri, 09 November 2007 23:01 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Hmmm. Over 5% of the rows being returned. I would have thought even the most efficient index scan (ie. where every row scanned was kept) would be at best only marginally faster than a Full Table Scan.
Forget the cost. How about the run-time with and without the function-based index?
Also, and this is important, how does the performance compare after you flush the buffer-cache? If you are running it with an index time and again, the first run will be slow but subsequent runs faster. If you time only the subsequent runs you are not getting a true picture of the performance.
Get your DBA to flush the buffer-cache before each test.
The upshot is: I don't think an index will help (much) unless the query is run frequently. When you are pulling out large proportions of a table (like >1%), the only way to make a REAL difference in run-time is to partition the table. That's a pretty serious step though - your DBA would have to look into it.
Ross Leishman
|
|
|
|
|
|
| Re: Urgent Please [message #280439 is a reply to message #280088] |
Tue, 13 November 2007 10:53 |
balajisundar
Messages: 29
Registered: October 2007
|
Junior Member |
|
|
Hi rleishman,
When i test the above cases in reality i don't get much performance increase. sometimes my performance is affected. I'm in a mess. i have done the test with new index for three times and two times i have got a poor performance when compared to the performance that is obtained with index PAC_INDEX.
Help me..
Regards,
Balaji
|
|
|
|
| Re: Urgent Please [message #280492 is a reply to message #279737] |
Tue, 13 November 2007 20:03 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
| rleishman wrote on Sat, 10 November 2007 16:01 |
The upshot is: I don't think an index will help (much) unless the query is run frequently. When you are pulling out large proportions of a table (like >1%), the only way to make a REAL difference in run-time is to partition the table. That's a pretty serious step though - your DBA would have to look into it.
|
Ross Leishman
|
|
|
|
| Re: Urgent Please [message #280613 is a reply to message #280492] |
Wed, 14 November 2007 04:14 |
balajisundar
Messages: 29
Registered: October 2007
|
Junior Member |
|
|
Hi rleishman,
Thank you so much. I have proposed this to my DBA. Finally the query has come to me on what criteria you wanted the partition to be done? there are few other batch jobs that fetches the records from the produc_account table.so need to analyse all those to better tune every thing on a whole.
Regards,
Balaji
|
|
|
|
| Re: Urgent Please [message #280819 is a reply to message #280613] |
Wed, 14 November 2007 21:17 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
No offense intended to your DBA, but if they asked that question, then it's a good sign that you shouldn't do it.
DBA's are awfully busy people who get hounded with tons of stupid questions leaving them only minutes of their day to do some real work.
The answer should be obvious. This DBAs response tells me that they don't have time to take responsibility for the problem.
Partitioning strategy for a database is NOT the domain of a developer. It is the responsibility of the DBA or a designated architect.
The question you need to ask yourself is: are you willing to take responsibility for whatever happens as a result of restructuring your database on the advice of an unknown person on the Web?
If the answer is YES, then I would suggest
- LIST partition on ORG_NO.
- Place 100 and 200 in a single partition, and all other values in the DEFAULT partition.
- Depending on other queries on the table, you might split out 100 and 200 into different partitions, and/or split out other values from the DEFAULT partition.
- To minimise performance impact on other queries, all indexes would be rebuilt as GLOBAL NON-PARTITIONED.
- Ask for a pay-rise. If you are going to do the DBA's job, at least you should be paid like one.
Ross Leishman
|
|
|
|
| Re: Urgent Please [message #280926 is a reply to message #280819] |
Thu, 15 November 2007 03:36 |
balajisundar
Messages: 29
Registered: October 2007
|
Junior Member |
|
|
Hi rleishman,
The last point what you have mentioned sounds good. 
thank you very much for all your help that you do. Let me finalise who is going to own the responsibility and get back to you I'm stuck any where(if ball is in my court).
Regards,
Balaji
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
