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 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 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 #280832 is a reply to message #280831] Wed, 14 November 2007 23:31 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>This is the SQL i would like to tune for performace...
So proceed to do so.
You don't need permission from any anonymous stranger on the 'Net
Your benchmarks will show you when you have the right solution.
Re: Help needed in tuning this query [message #280854 is a reply to message #280832] Thu, 15 November 2007 00:54 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: How to decide on SGA sizing ???
Next Topic: consistent gets > segment size during FTS
Goto Forum:
  


Current Time: Fri Dec 02 22:46:59 CST 2016

Total time taken to generate the page: 0.07476 seconds