Home » RDBMS Server » Performance Tuning » Help needed in tuning this query (Oracle 10.2.0.2, HP Unix)
| Help needed in tuning this query [message #280831] |
Wed, 14 November 2007 23:26  |
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 2 million rows.
From the second day onwards only around 10% of records will be between Load_Start_Time and Load_End_Time. Among these around 50% will have THAMES_URN_WITH_ROLE_SUFFIX as 'TP%'. At present there are unique and primary key indexes on THAMES_URN_WITH_ROLE_SUFFIX .
I'm sure that we need a b-tree index on STG_UPDATE_DATE_TIME as it is the column with good selectivity.
I'm primarily concerned about the substr, instr and reverese on THAMES_URN_WITH_ROLE_SUFFIX.
Also pls. let me know if the order of predicates is right.
Thanks in advance.
SELECT TPD_STG_TL_SF_LEGAL_OWNER.THAMES_URN, TPD_STG_TL_SF_LEGAL_OWNER.UPDATE_SOURCE, TPD_STG_TL_SF_LEGAL_OWNER.SOURCE_EXTRACT_DATE_TIME, TPD_STG_TL_SF_LEGAL_OWNER.LEGAL_OWNERSHIP_ISSUE_IND, TPD_STG_TL_SF_LEGAL_OWNER.CONTACTABLE_INDICATOR, TPD_STG_TL_SF_LEGAL_OWNER.ADMIN_CONTROL_INDICATOR, TPD_STG_TL_SF_LEGAL_OWNER.BANKRUPTCY_INDICATOR, TPD_STG_TL_SF_LEGAL_OWNER.ASSIGNED_INDICATOR, TPD_STG_TL_SF_LEGAL_OWNER.IN_TRUST_INDICATOR, TPD_STG_TL_SF_LEGAL_OWNER.DIVORCE_CASE_INDICATOR, TPD_STG_TL_SF_LEGAL_OWNER.POA_COP_INDICATOR, TPD_STG_TL_SF_LEGAL_OWNER.CLEANSING_STATUS, TPD_STG_TL_SF_LEGAL_OWNER.CATEGORY
FROM
TPD_STG_TL_SF_LEGAL_OWNER
WHERE
((TPD_STG_TL_SF_LEGAL_OWNER.STG_UPDATE_DATE_TIME > '$$Load_Start_Time'
AND
TPD_STG_TL_SF_LEGAL_OWNER.STG_UPDATE_DATE_TIME <= '$$Load_End_Time')
OR
(TPD_STG_TL_SF_LEGAL_OWNER.TPDB_PROCESSED_IDENTIFIER != 'Y') )
AND
SUBSTR(TPD_STG_TL_SF_LEGAL_OWNER.THAMES_URN_WITH_ROLE_SUFFIX,1,2)='TP'
AND
SUBSTR(REVERSE( TPD_STG_TL_SF_LEGAL_OWNER.THAMES_URN_WITH_ROLE_SUFFIX),INSTR(REVERSE( TPD_STG_TL_SF_LEGAL_OWNER.THAMES_URN_WITH_ROLE_SUFFIX),'-',1)-1,1)!='S'
========================================
CREATE TABLE TPD_STG_TL_SF_LEGAL_OWNER
(
THAMES_URN VARCHAR2(35 BYTE) NOT NULL,
UPDATE_SOURCE VARCHAR2(3 BYTE),
SOURCE_EXTRACT_DATE_TIME DATE,
LEGAL_OWNERSHIP_ISSUE_IND CHAR(1 BYTE),
CONTACTABLE_INDICATOR 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),
THAMES_URN_WITH_ROLE_SUFFIX VARCHAR2(39 BYTE),
THAMES_LEGAL_OWNERSHIP_IND CHAR(1 BYTE),
PRODUCT_HOLDING_ROLE_TYPE VARCHAR2(21 BYTE),
OWNERSHIP_SPLIT_PERCENTAGE NUMBER(3),
SOURCE_SYSTEM VARCHAR2(3 BYTE),
TITLE VARCHAR2(50 BYTE),
FIRST_NAME VARCHAR2(50 BYTE),
MIDDLE_INITIAL VARCHAR2(50 BYTE),
SURNAME VARCHAR2(50 BYTE),
GENDER CHAR(1 BYTE),
BIRTH_DATE DATE,
DEATH_INDICATOR CHAR(1 BYTE),
DEATH_RECORD_DATE DATE,
NATIONAL_INSURANCE_NUMBER VARCHAR2(9 BYTE),
GONE_AWAY_INDICATOR CHAR(1 BYTE),
ADDRESS_LINE_1 VARCHAR2(50 BYTE),
ADDRESS_LINE_2 VARCHAR2(50 BYTE),
ADDRESS_LINE_3 VARCHAR2(50 BYTE),
ADDRESS_LINE_4 VARCHAR2(50 BYTE),
ADDRESS_LINE_5 VARCHAR2(50 BYTE),
POST_CODE VARCHAR2(12 BYTE),
COUNTRY VARCHAR2(50 BYTE),
OVERSEAS_ADDRESS_INDICATOR CHAR(1 BYTE),
ORGANISATION_NAME VARCHAR2(50 BYTE),
TPDB_PROCESSED_IDENTIFIER CHAR(2 BYTE),
CLEANSING_STATUS VARCHAR2(30 BYTE),
CATEGORY VARCHAR2(50 BYTE),
SCHEME_NAME VARCHAR2(50 BYTE),
STG_CREATE_DATE_TIME DATE,
STG_UPDATE_DATE_TIME DATE,
LAST_UPDATED_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 TPD_STG_TL_SF_LEGAL_OWNER_PK ON TPD_STG_TL_SF_LEGAL_OWNER
(THAMES_URN_WITH_ROLE_SUFFIX)
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_SF_LEGAL_OWNER ADD (
CONSTRAINT TPD_STG_TL_SF_LEGAL_OWNER_PK
PRIMARY KEY
(THAMES_URN_WITH_ROLE_SUFFIX)
USING INDEX
TABLESPACE TPDBS01A_DATA
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 128K
NEXT 128K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
));
|
|
|
|
|
|
| Re: Help needed in tuning this query [message #280854 is a reply to message #280832] |
Thu, 15 November 2007 00:54  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
When you return between 1% and 10% of a table, the difference between an index scan and a full table scan will be marginal.
You could spend days identifying the best possible index strategy and STILL get virtually no improvement.
As advised in this thread, the ONLY way to get a REAL performance gain on this type of query is to partition the table.
Ross Leishman
|
|
|
|
| Re: Help needed in tuning this query [message #281021 is a reply to message #280854] |
Thu, 15 November 2007 07:22 |
hidnana
Messages: 87
Registered: November 2007
Location: chennai
|
Member |
|
|
Thanks for the reply. But is it a good option to partition tables with total no. of rows between 2 million and 5 million ? I thought partitioning is better done for tables > 20 million.
Please throw some light on that.
|
|
|
|
| Re: Help needed in tuning this query [message #281152 is a reply to message #281021] |
Thu, 15 November 2007 19:27 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Rubbish.
If a Full Table Scan on a table takes "too long" (and this is purely subjective - "too long" is different for every problem) and indexes can't help, and you need <100% of the rows, and partitioning will give you the performance you desire, then why WOULDN'T you partition.
The reason figures like 20M get bandied about is beacuse most servers will take seconds to read 20M rows, and this is rarely considered "too slow".
Ross Leishman
|
|
|
|
Goto Forum:
Current Time: Tue Nov 26 19:33:20 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
