Home » RDBMS Server » Performance Tuning » Pls. help Tune this query (Oracle 10g)
| Pls. help Tune this query [message #279952] |
Mon, 12 November 2007 01:19  |
hidnana
Messages: 87
Registered: November 2007
Location: chennai
|
Member |
|
|
This is the SQL i would like to tune for performace...
The table structure is given below.
The table has about 5 million rows.
On the first day, load_flag has all the rows as 'I'.
Then from the second day onwards only around 10% of records will be between Load_Start_Time and Load_End_Time. Among these around 40% will have Record_key like 'TP%'. And among those most of the records (95%)will have load_flag as 'U' and a very few (5%) as 'I'. At present there are unique and primary key indexes on record_key. Please advice me whether it's better to go for an index on any of these columns and what type would be better. I thought it would help to have a bit map index on load_flag and a function base index on SUBSTR (RECORD_KEY).
Also pls. let me know if the order of predicates is right.
Thanks in advance.
SELECT TPD_STG_TL_CS_EXTRACTED_RECS.RECORD_KEY
, TPD_STG_TL_CS_EXTRACTED_RECS.DATA_SOURCE
, TPD_STG_TL_CS_EXTRACTED_RECS.CONTACTABLE_INDICATOR
, TPD_STG_TL_CS_EXTRACTED_RECS.LEGAL_OWNERSHIP_ISSUE_IND
, TPD_STG_TL_CS_EXTRACTED_RECS.ADMIN_CONTROL_INDICATOR
, TPD_STG_TL_CS_EXTRACTED_RECS.BANKRUPTCY_INDICATOR
, TPD_STG_TL_CS_EXTRACTED_RECS.ASSIGNED_INDICATOR
, TPD_STG_TL_CS_EXTRACTED_RECS.IN_TRUST_INDICATOR
, TPD_STG_TL_CS_EXTRACTED_RECS.DIVORCE_CASE_INDICATOR
, TPD_STG_TL_CS_EXTRACTED_RECS.POA_COP_INDICATOR
, TPD_STG_TL_CS_EXTRACTED_RECS.SOURCE_EXTRACT_DATE_TIME
FROM TPD_STG_TL_CS_EXTRACTED_RECS
WHERE LOAD_FLAG IN('I', 'U')
AND SUBSTR(RECORD_KEY, 1, 2) = 'TP'
AND STG_UPDATE_DATE_TIME > '$$Load_Start_Time'
AND STG_UPDATE_DATE_TIME <= '$$Load_End_Time'
Table structure
CREATE TABLE TPD_STG_TL_CS_EXTRACTED_RECS
(
RECORD_KEY VARCHAR2(35 BYTE),
SCHEME_NAME VARCHAR2(50 BYTE),
ORGANISATION_NAME VARCHAR2(50 BYTE),
SUPERIOR_TITLE_1 VARCHAR2(50 BYTE),
TITLE_1 VARCHAR2(50 BYTE),
FIRST_NAME_1 VARCHAR2(50 BYTE),
MIDDLE_NAME_1 VARCHAR2(50 BYTE),
SURNAME_1 VARCHAR2(50 BYTE),
ADDRESS_LINE_1_1 VARCHAR2(50 BYTE),
ADDRESS_LINE_2_1 VARCHAR2(50 BYTE),
ADDRESS_LINE_3_1 VARCHAR2(50 BYTE),
ADDRESS_LINE_4_1 VARCHAR2(50 BYTE),
ADDRESS_LINE_5_1 VARCHAR2(50 BYTE),
ADDRESS_LINE_6_1 VARCHAR2(50 BYTE),
POST_CODE_1 VARCHAR2(12 BYTE),
COUNTRY_1 VARCHAR2(50 BYTE),
OVERSEAS_INDICATOR_1 CHAR(1 BYTE),
DOB_1 NUMBER(8),
GENDER_1 CHAR(1 BYTE),
NINO_1 VARCHAR2(9 BYTE),
DEATH_INDICATOR_1 CHAR(1 BYTE),
PRODUCT_HOLDING_ROLE_TYPE_1 VARCHAR2(21 BYTE),
GONE_AWAY_INDICATOR_1 CHAR(1 BYTE),
THAMES_LEGAL_OWNERSHIP_IND_1 CHAR(1 BYTE),
SOURCE_SYSTEM_PARTY_INDV_ID_1 VARCHAR2(15 BYTE),
SUPERIOR_TITLE_2 VARCHAR2(50 BYTE),
TITLE_2 VARCHAR2(50 BYTE),
FIRST_NAME_2 VARCHAR2(50 BYTE),
MIDDLE_NAME_2 VARCHAR2(50 BYTE),
SURNAME_2 VARCHAR2(50 BYTE),
ADDRESS_LINE_1_2 VARCHAR2(50 BYTE),
ADDRESS_LINE_2_2 VARCHAR2(50 BYTE),
ADDRESS_LINE_3_2 VARCHAR2(50 BYTE),
ADDRESS_LINE_4_2 VARCHAR2(50 BYTE),
ADDRESS_LINE_5_2 VARCHAR2(50 BYTE),
ADDRESS_LINE_6_2 VARCHAR2(50 BYTE),
POST_CODE_2 VARCHAR2(12 BYTE),
COUNTRY_2 VARCHAR2(50 BYTE),
OVERSEAS_INDICATOR_2 CHAR(1 BYTE),
DOB_2 NUMBER(8),
GENDER_2 CHAR(1 BYTE),
NINO_2 VARCHAR2(9 BYTE),
DEATH_INDICATOR_2 CHAR(1 BYTE),
PRODUCT_HOLDING_ROLE_TYPE_2 VARCHAR2(21 BYTE),
GONE_AWAY_INDICATOR_2 CHAR(1 BYTE),
THAMES_LEGAL_OWNERSHIP_IND_2 CHAR(1 BYTE),
SOURCE_SYSTEM_PARTY_INDV_ID_2 VARCHAR2(15 BYTE),
JOINT_OWNER_INDICATOR CHAR(1 BYTE),
JOINT_LIFE_TYPE NUMBER(1),
SAME_ADDRESS_INDICATOR CHAR(1 BYTE),
TITLE_LA1 VARCHAR2(50 BYTE),
FIRST_NAME_LA1 VARCHAR2(50 BYTE),
SURNAME_LA1 VARCHAR2(50 BYTE),
DOB_LA1 NUMBER(8),
TITLE_LA2 VARCHAR2(50 BYTE),
FIRST_NAME_LA2 VARCHAR2(50 BYTE),
SURNAME_LA2 VARCHAR2(50 BYTE),
DOB_LA2 NUMBER(8),
PRODUCT_HOLDING_REF_NUMBER VARCHAR2(28 BYTE),
PARENT_PRODUCT_HOLDING_REF_NUM VARCHAR2(9 BYTE),
OCDB_REFERENCE_NUMBER VARCHAR2(17 BYTE),
BUSINESS_GROUP CHAR(3 BYTE),
SCHEME_NUMBER VARCHAR2(8 BYTE),
TRUSTEE_SEQUENCE_NUMBER NUMBER(10),
MEMBER_NUMBER VARCHAR2(10 BYTE),
PRSN_ID NUMBER(10),
OLD_SCHEME_NUMBER VARCHAR2(8 BYTE),
PUBLIC_SECTOR_INDICATOR CHAR(1 BYTE),
ELIGIBLE_INDICATOR CHAR(1 BYTE),
SCHEME_STATUS NUMBER(1),
SCHEME_TYPE CHAR(2 BYTE),
Q_DATE_WITH_PROFIT_STATUS NUMBER(1),
A_DATE_WITH_PROFIT_STATUS NUMBER(1),
LATEST_WITH_PROFIT_STATUS NUMBER(1),
NPSW_INDICATOR CHAR(1 BYTE),
PRODUCT_HOLDING_STATUS CHAR(1 BYTE),
MATURITY_DATE_OF_CONTRACT NUMBER(8),
DUE_END_DATE_OF_CONTRACT NUMBER(8),
OUT_OF_FORCE_DATE NUMBER(8),
OUT_OF_FORCE_REASON_CODE NUMBER(2),
DATA_SOURCE VARCHAR2(3 BYTE),
PRODUCT_TYPE VARCHAR2(30 BYTE),
PRODUCT_DESCRIPTION VARCHAR2(50 BYTE),
SERVICING_AGENT_NUMBER VARCHAR2(10 BYTE),
CONTACTABLE_INDICATOR CHAR(1 BYTE),
LEGAL_OWNERSHIP_ISSUE_IND CHAR(1 BYTE),
ADMIN_CONTROL_INDICATOR CHAR(1 BYTE),
BANKRUPTCY_INDICATOR CHAR(1 BYTE),
ASSIGNED_INDICATOR CHAR(1 BYTE),
IN_TRUST_INDICATOR CHAR(1 BYTE),
DIVORCE_CASE_INDICATOR CHAR(1 BYTE),
POA_COP_INDICATOR CHAR(1 BYTE),
DONOR_POLICY_INDICATOR CHAR(1 BYTE),
TAX_JURISDICTION NUMBER(2),
INELIGIBLE_DATE NUMBER(8),
INELIGIBILITY_REASON NUMBER(2),
A_DATE_CASH_OR_BONUS_TYPE CHAR(1 BYTE),
VALUATION_APPLICABLE_DATE NUMBER(8),
A_DATE_PIP_AMOUNT NUMBER(12),
A_DATE_NPSW_AMOUNT NUMBER(10),
A_DATE_MINIMUM_APPLIED_AMOUNT NUMBER(10),
A_DATE_MINIMUM_CALC_AMOUNT NUMBER(10),
A_DATE_POLICY_VALUE NUMBER(16),
A_DATE_DATE_PIP_CALCULATED NUMBER(8),
A_DATE_ALGORITHM_NUMBER NUMBER(2),
M_DATE_VOTING_VALUE NUMBER(12),
M_DATE_POLICY_VALUE NUMBER(16),
E_DATE_CASH_OR_BONUS_TYPE CHAR(1 BYTE),
E_DATE_PIP_AMOUNT NUMBER(12),
E_DATE_NPSW_AMOUNT NUMBER(10),
E_DATE_MINIMUM_APPLIED_AMOUNT NUMBER(10),
E_DATE_MINIMUM_CALC_AMOUNT NUMBER(10),
E_DATE_POLICY_VALUE NUMBER(16),
E_DATE_DATE_PIP_CALCULATED NUMBER(8),
E_DATE_ALGORITHM_NUMBER NUMBER(2),
P_DATE_CASH_OR_BONUS_TYPE CHAR(1 BYTE),
P_DATE_PIP_AMOUNT NUMBER(12),
P_DATE_NPSW_AMOUNT NUMBER(10),
P_DATE_MINIMUM_APPLIED_AMOUNT NUMBER(10),
P_DATE_MINIMUM_CALC_AMOUNT NUMBER(10),
P_DATE_POLICY_VALUE NUMBER(16),
P_DATE_DATE_PIP_CALCULATED NUMBER(8),
P_DATE_ALGORITHM_NUMBER NUMBER(2),
SOURCE_EXTRACT_DATE_TIME DATE,
SCHEME_SEQUENCE_NUMBER NUMBER(3),
LOAD_FLAG CHAR(1 BYTE),
STG_CREATE_DATE_TIME DATE,
STG_UPDATE_DATE_TIME DATE
)
TABLESPACE TPDBS01A_DATA
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 128K
NEXT 128K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE UNIQUE INDEX PK_STG_TL_CS_EXTRACTED_RECS ON TPD_STG_TL_CS_EXTRACTED_RECS
(RECORD_KEY)
LOGGING
TABLESPACE TPDBS01A_DATA
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 128K
NEXT 128K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
ALTER TABLE TPD_STG_TL_CS_EXTRACTED_RECS ADD (
CONSTRAINT PK_STG_TL_CS_EXTRACTED_RECS
PRIMARY KEY
(RECORD_KEY)
USING INDEX
TABLESPACE TPDBS01A_DATA
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 128K
NEXT 128K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
));
[mod-edit]Applied code tags.
[Updated on: Mon, 12 November 2007 01:28] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Pls. help Tune this query [message #280231 is a reply to message #279956] |
Mon, 12 November 2007 20:36  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
- Create a b-tree index on STG_UPDATE_DATE_TIME
- Create a bitmap index (if the table is suitable for bitmap indexing - see Oracle Performance Tuning Guide - it's not suitable for all tables).
- How many different record_keys are there? How many different combinations of the first two characters? Depending on the answers to these, you could create either a b-tree or bitmap index on either RECORD_KEY or SUBSTR(RECORD_KEY,1,2). You may also need to convert the SUBSTR to a LIKE depending on your answer.
Add an INDEX_COMBINE hint to force a bitmap path to combine the b-tree and bitmap indexes.
Ross Leishman
|
|
|
|
| Re: Pls. help Tune this query [message #280238 is a reply to message #280231] |
Mon, 12 November 2007 23:07 |
hidnana
Messages: 87
Registered: November 2007
Location: chennai
|
Member |
|
|
Thanks for your suggestions.
How about the following option. Would this be any better ?
To have a combined index over STG_CS_UPDATE_DATE_TIME and RECORD_KEY (STG_CS_UPDATE_DATE_TIME, RECORD_KEY) which would do a index range scan scanning both the predicates and result in a significantly correct no.s of rows in one shot
Reason : The index is used as a range scan on one column and as a filter operation after this access on the second column. This is like an extra select statement inside the index structure. The resulting table access is as small as possible.A faster access plan can be achieved when the first column is accessed with direct (unique) scan on the first and a range scan on the second column.
Summarizing,
1) RECORD_KEY LIKE 'TP%' instead of substr
2) Composite index can be used (STG_CS_UPDATE_DATE_TIME, RECORD_KEY) with columns accessed in that order
Please evaluate.
|
|
|
|
| Re: Pls. help Tune this query [message #280250 is a reply to message #280231] |
Mon, 12 November 2007 23:53 |
hidnana
Messages: 87
Registered: November 2007
Location: chennai
|
Member |
|
|
Also how does the scenario change if we add one more predicate
AND TPD_STG_TL_CS_EXTRACTED_RECS.SCHEME_NAME IS NOT NULL
to the existing SQL.
How do we tune it now. Any major change to our analysis thus far .
|
|
|
|
|
|
| Re: Pls. help Tune this query [message #280494 is a reply to message #280258] |
Tue, 13 November 2007 20:19 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
The DATE column is accessed with RANGE predicates. If it is the first column in a concatenated index, then subsequent columns will not contribute to the index SCAN, they will be implemented as a FILTER (which is slower, of course).
If you were to put all three WHERE columns in the index, with the most restrictive one first (the DATE col), then Oracle would SCAN on the date, FILTER on the others, then only lookup the table on matching rows. It will read more rows than it has to from the index, but that might be an acceptable trade-off.
With the single-column index-combine approach, it still has to read the entire date-range from the index (same number of rows), but this would be fewer blocks as the index is narrower. It would also have to read the other bitmap indexes. This may or may not be faster.
Ross Leishman
|
|
|
|
| Re: Pls. help Tune this query [message #280504 is a reply to message #280494] |
Tue, 13 November 2007 22:15 |
hidnana
Messages: 87
Registered: November 2007
Location: chennai
|
Member |
|
|
So the approach can be like
1) RECORD_KEY LIKE 'TP%' instead of substr
2) Composite index can be used (STG_CS_UPDATE_DATE_TIME, RECORD_KEY) with columns accessed in that order
OR
Creating a b-tree index on STG_UPDATE_DATE_TIME
Can we approach by letting CBO find the best plan and then may be we can stabilize the plan with hints. We need to update the statistics and then we can test the various selects/index strategies. Please let me know if this fine ?
|
|
|
|
| Re: Pls. help Tune this query [message #280551 is a reply to message #280504] |
Wed, 14 November 2007 01:23 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
1) RECORD_KEY LIKE 'TP%' instead of substr - this is kind of optional, because it will not scan anyway
2) Composite index can be used with columns (STG_CS_UPDATE_DATE_TIME, RECORD_KEY, LOAD_FLAG) in that order
OR
1) B-tree index on STG_CS_UPDATE_DATE_TIME
2) Bitmap index on SUBSTR(RECORD_KEY,1,2)
3) Bitmap index on LOAD_FLAG
OR
1) Composite index can be used with columns (SUBSTR(RECORD_KEY,1,2), LOAD_FLAG, STG_CS_UPDATE_DATE_TIME) in that order
The final option would certainly be the fastest, but its very specific to that query and not terribly versatile.
The middle option is more versatile, but possibly not appropriate depending on the distribution of values of SUBSTR(RECORD_KEY,1,2) and LOAD_FLAG
The first option is a reasonable fallback.
Of course, if the query returns 1-10% of the table, then a Full Table Scan should be trialled as well because it may be faster.
Ross Leishman
|
|
|
|
Goto Forum:
Current Time: Tue Nov 26 19:44:06 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
