Home » RDBMS Server » Performance Tuning » Tuning look-up tables : Suggestions needed (Oracle 10g)
Tuning look-up tables : Suggestions needed [message #281221] Fri, 16 November 2007 03:12 Go to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
The following are some of the look up tables with their approx.sizes, select condition are given.
There are no indexes right now except their PKs.
These tables are also updated based on their PK.
Would it be ipmorve performance by having bitmap index on CREATE_SOURCE/ UPDTE_SOURCE
and re-writing the SQL by not using '!='.
Please let me know.

Table name Approx. size SELECT Condition
====================================================================
TPD_TL_W_PH_LOI_CNTCTBLTY_IND 3-4million CREATE_SOURCE!='SFC'
TPD_TL_W_POSTAL_ADDRESS 20 million UPDTE_SOURCE='CE'
TPD_TL_W_PRODUCT_HOLDING 3-4million
TPD_TL_W_PRODUCT_HOLDING_ROLE 12million UPDATE_SOURCE='CE'
TPD_TL_W_REATTRIB_ELIGIBILITY 5 million
TPD_TL_W_SCHEME 5 million
TPD_TL_W_SCHEME_MEMBERSHIP 2 million UPDATE_SOURCE='CE'
TPD_TL_W_SCHEME_ROLE 1 million
TPD_TL_W_SC_LOI_CNTBLTY_STATUS 5 million CREATE_SOURCE!='SFC'
TPD_TL_W_SM_LOI_CNTBLTY_STATUS 2 million CREATE_SOURCE!='SFC'
TPD_TL_W_THAMES_INDIVIDUAL 12 million
TPD_TL_W_THAMES_ORGANISATION 4 million UPDATE_SOURCE='CE'
TPD_TL_W_VALUATION 2 million
TPD_STG_TL_CS_EXTRACTED_RECS 5 million
TPD_STG_TLE_CS_EXTRCT_REC_EXCP 5 million

Report message to a moderator

Re: Tuning look-up tables : Suggestions needed [message #281237 is a reply to message #281221] Fri, 16 November 2007 03:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

If you update the columns in OLTP way (not only with batches), don't use bitmap indexes.

Regards
Michel

Report message to a moderator

Re: Tuning look-up tables : Suggestions needed [message #284090 is a reply to message #281221] Wed, 28 November 2007 16:32 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Michel gives good advice. I have experienced first hand the high update cost of bitmaps. However, it seems this cost is (I guess not unexpectedly) a complex thing and there are several "situations" in which bitmaps work very well on active tables.

Still, maybe a better approach would be a function based index. For example, suppose you tried this.

create index x_i1 on TPD_TL_W_PH_LOI_CNTCTBLTY_IND (decode(CREATE_SOURCE,'SFC',to_number(null),1)
/

then you rewrite your query to this:

select *
from TPD_TL_W_PH_LOI_CNTCTBLTY_IND
where decode(CREATE_SOURCE,'SFC',to_number(null),1) = 1
/

It is just a thought. Kevin

Report message to a moderator

Previous Topic: oracle sequence is causing high cpu usage?
Next Topic: Issue updating a partitioned table
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Nov 26 19:54:26 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.