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 Go to next message
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 #279956 is a reply to message #279952] Mon, 12 November 2007 01:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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).

Also read How to Identify Performance Problem and Bottleneck .

Regards
Michel
Re: Pls. help Tune this query [message #280231 is a reply to message #279956] Mon, 12 November 2007 20:36 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #280258 is a reply to message #279952] Tue, 13 November 2007 00:23 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>AND TPD_STG_TL_CS_EXTRACTED_RECS.SCHEME_NAME IS NOT NULL
NOT NULL precludes use of index because NULL values are not indexed
Re: Pls. help Tune this query [message #280494 is a reply to message #280258] Tue, 13 November 2007 20:19 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: Buffer Cache
Next Topic: Query problem
Goto Forum:
  


Current Time: Sun Dec 04 20:41:05 CST 2016

Total time taken to generate the page: 0.04799 seconds