| Tuning look-up tables : Suggestions needed [message #281221] |
Fri, 16 November 2007 03:12  |
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
|
|
|
|
|
|
| Re: Tuning look-up tables : Suggestions needed [message #284090 is a reply to message #281221] |
Wed, 28 November 2007 16:32  |
 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
