Home » RDBMS Server » Performance Tuning » Need to tune SQL involving Join (Oracle 10g)
| Need to tune SQL involving Join [message #285714] |
Wed, 05 December 2007 06:49  |
hidnana
Messages: 87
Registered: November 2007
Location: chennai
|
Member |
|
|
Hi,
This is the SQL I'm tuning right now. TPD_TL_W_THAMES_INDIVIDUAL has abt 10 million records.
TPD_TL_W_PRODUCT_HOLDING has one-to-one relationship with TPD_TL_W_THAMES_INDIVIDUAL .
TPD_TL_W_PRODUCT_HOLDING has 1 to many relationship with TPD_TL_W_PRODUCT_HOLDING_ROLE (approx 1:4).
THAMES_INDIVIDUAL_ID, PRODUCT_HOLDING_ID, PRODUCT_HOLDING_ROLE_ID are the respective Primary Keys.
Is my join order right ?
I have created a b-tree index on TPD_TL_W_THAMES_INDIVIDUAL.LAST_UPDATE_DATE_TIME.
I tried with a bitmap index on THAMES_LEGAL_OWNERSHIP_IND but not much improvement
There are no other indexes.
Please suggest me a better approach.
SELECT TPD_TL_W_THAMES_INDIVIDUAL.THAMES_INDIVIDUAL_ID, TPD_TL_W_THAMES_INDIVIDUAL.SOURCE_SYSTEM_PARTY_INDV_ID, TPD_TL_W_THAMES_INDIVIDUAL.CREATE_SOURCE, TPD_TL_W_THAMES_INDIVIDUAL.FIRST_NAME, TPD_TL_W_THAMES_INDIVIDUAL.SURNAME, TPD_TL_W_THAMES_INDIVIDUAL.GENDER, TPD_TL_W_THAMES_INDIVIDUAL.BIRTH_DATE, TPD_TL_W_PRODUCT_HOLDING.OCDB_POLICY_NUMBER,
'PLACE HOLDER FOR SOURCE SYSTEM' AS SOURCE_SYSTEM_NAME
FROM
TPD_TL_W_THAMES_INDIVIDUAL, TPD_TL_W_PRODUCT_HOLDING, TPD_TL_W_PRODUCT_HOLDING_ROLE
WHERE
TPD_TL_W_THAMES_INDIVIDUAL.THAMES_INDIVIDUAL_ID=TPD_TL_W_PRODUCT_HOLDING_ROLE.THAMES_INDIVIDUAL_ID
AND TPD_TL_W_PRODUCT_HOLDING.PRODUCT_HOLDING_ID=TPD_TL_W_PRODUCT_HOLDING_ROLE.PRODUCT_HOLDING_ID
AND TPD_TL_W_PRODUCT_HOLDING_ROLE.THAMES_LEGAL_OWNERSHIP_IND='Y'
AND TPD_TL_W_THAMES_INDIVIDUAL.CREATE_SOURCE='PLACE HOLDER FOR CREATE SYSTEM'
AND TPD_TL_W_THAMES_INDIVIDUAL.LAST_UPDATE_DATE_TIME>'$$Load_Start_Time'
AND TPD_TL_W_THAMES_INDIVIDUAL.LAST_UPDATE_DATE_TIME<='$$Load_End_Time'"
CREATE TABLE TPD_TL_W_THAMES_INDIVIDUAL
(
THAMES_INDIVIDUAL_ID VARCHAR2(39 BYTE) NOT NULL,
SOURCE_SYSTEM_PARTY_INDV_ID VARCHAR2(26 BYTE),
OCDB_PARTY_INDIVIDUAL_ID NUMBER(10),
TITLE VARCHAR2(50 BYTE),
SUPERIOR_TITLE VARCHAR2(50 BYTE),
FIRST_NAME VARCHAR2(50 BYTE),
MIDDLE_INITIAL_OR_NAME VARCHAR2(50 BYTE),
SURNAME VARCHAR2(50 BYTE),
GENDER CHAR(1 BYTE),
BIRTH_DATE DATE,
DEATH_IND CHAR(1 BYTE),
NATIONAL_INSURANCE_NUMBER VARCHAR2(9 BYTE),
ADDRESS_ID VARCHAR2(39 BYTE),
GONE_AWAY_IND CHAR(1 BYTE),
THAMES_ORGANISATION_ID VARCHAR2(39 BYTE),
SOURCE_EXTRACT_DATE_TIME DATE NOT NULL,
CREATE_SOURCE VARCHAR2(5 BYTE) NOT NULL,
UPDATE_SOURCE VARCHAR2(5 BYTE) NOT NULL,
CREATE_DATE_TIME DATE NOT NULL,
LAST_UPDATE_DATE_TIME DATE NOT NULL
)
CREATE UNIQUE INDEX TPD_TL_THAMES_INDIVIDUAL_PK ON TPD_TL_W_THAMES_INDIVIDUAL
(THAMES_INDIVIDUAL_ID)
ALTER TABLE TPD_TL_W_THAMES_INDIVIDUAL ADD (
CONSTRAINT TPD_TL_THAMES_INDIVIDUAL_PK
PRIMARY KEY
(THAMES_INDIVIDUAL_ID)
=====================================================
CREATE TABLE TPD_TL_W_PRODUCT_HOLDING_ROLE
(
PRODUCT_HOLDING_ROLE_ID VARCHAR2(39 BYTE) NOT NULL,
THAMES_ORGANISATION_ID VARCHAR2(39 BYTE),
THAMES_INDIVIDUAL_ID VARCHAR2(39 BYTE) NOT NULL,
PRODUCT_HOLDING_ID VARCHAR2(35 BYTE) NOT NULL,
THAMES_LEGAL_OWNERSHIP_IND CHAR(1 BYTE) NOT NULL,
PRODUCT_HOLDING_ROLE_TYPE VARCHAR2(21 BYTE) NOT NULL,
OWNERSHIP_SPLIT_PERCENTAGE NUMBER(3),
EFF_START_DATE DATE NOT NULL,
EFF_END_DATE DATE,
SOURCE_EXTRACT_DATE_TIME DATE NOT NULL,
CREATE_SOURCE VARCHAR2(5 BYTE) NOT NULL,
UPDATE_SOURCE VARCHAR2(5 BYTE),
CREATE_DATE_TIME DATE NOT NULL,
LAST_UPDATE_DATE_TIME DATE NOT NULL
)
CREATE UNIQUE INDEX TPD_TL_W_PROD_HOLD_ROLE_PK ON TPD_TL_W_PRODUCT_HOLDING_ROLE
(PRODUCT_HOLDING_ROLE_ID)
CREATE TABLE TPD_TL_W_PRODUCT_HOLDING
(
PRODUCT_HOLDING_ID VARCHAR2(35 BYTE) NOT NULL,
PRODUCT_TYPE_ID NUMBER(10) NOT NULL,
SCHEME_ID VARCHAR2(39 BYTE),
PRODUCT_HOLDING_STATUS_ID VARCHAR2(10 BYTE) NOT NULL,
PRODUCT_HOLDING_REFERENCE_NO VARCHAR2(28 BYTE) NOT NULL,
OCDB_POLICY_NUMBER VARCHAR2(32 BYTE),
JOINT_LIFE_TYPE CHAR(1 BYTE),
OUT_OF_FORCE_DATE NUMBER(8),
OUT_OF_FORCE_REASON_ID NUMBER(10),
PARENT_PRODUCT_HOLDING_REF VARCHAR2(9 BYTE),
TAX_JURISDICTION_ID NUMBER(10) NOT NULL,
JOINT_OWNER_TYPE_IND CHAR(1 BYTE),
DUE_END_DATE DATE,
QDATE_WITH_PROFIT_STATUS_ID NUMBER(10) NOT NULL,
ADATE_WITH_PROFIT_STATUS_ID NUMBER(10) NOT NULL,
LATEST_WITH_PROFIT_STATUS_ID NUMBER(10),
NPSW_IND CHAR(1 BYTE),
DONOR_POLICY_IND CHAR(1 BYTE),
CREATE_SOURCE VARCHAR2(5 BYTE) NOT NULL,
UPDATE_SOURCE VARCHAR2(5 BYTE) NOT NULL,
SOURCE_EXTRACT_DATE_TIME DATE NOT NULL,
CREATE_DATE_TIME DATE NOT NULL,
LAST_UPDATE_DATE_TIME DATE NOT NULL
)
CREATE UNIQUE INDEX TPD_TL_W_PRODUCT_HOLDING_PK ON TPD_TL_W_PRODUCT_HOLDING
(PRODUCT_HOLDING_ID)
ALTER TABLE TPD_TL_W_PRODUCT_HOLDING ADD (
CONSTRAINT TPD_TL_W_PRODUCT_HOLDING_PK
PRIMARY KEY
(PRODUCT_HOLDING_ID)
[Updated on: Wed, 05 December 2007 07:40] Report message to a moderator |
|
|
|
|
|
| Re: Need to tune SQL involving Join [message #285842 is a reply to message #285729] |
Wed, 05 December 2007 20:58  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
- Post your Explain Plain
- How many different CREATE_SOURCE values are there?
- What proportion of the table do you TYPICALLY get with your Start/End filters?
- Dont forget 1. above
Ross Leishman
|
|
|
|
| Re: Need to tune SQL involving Join [message #286227 is a reply to message #285714] |
Thu, 06 December 2007 23:43 |
hidnana
Messages: 87
Registered: November 2007
Location: chennai
|
Member |
|
|
I've updated the statistics got this plan output. But unable to get TKprof output as DBA has not given privilege.
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3864134276
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500K| 50M| | 35366 (2)| 00:07:05 |
|* 1 | HASH JOIN | | 500K| 50M| 42M| 35366 (2)| 00:07:05 |
|* 2 | HASH JOIN | | 500K| 36M| 29M| 19367 (2)| 00:03:53 |
|* 3 | TABLE ACCESS FULL| X_TPD_TL_W_THAMES_INDIVIDUAL | 500K| 23M| | 5411 (4)| 00:0
|* 4 | TABLE ACCESS FULL| XTPD_TL_W_PRODUCT_HOLDING_ROLE | 2250K| 57M| | 7461 (2)| 00:0
| 5 | TABLE ACCESS FULL | X_TPD_TL_W_PRODUCT_HOLDING | 3000K| 80M| | 8158 (2)| 00:0
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("X_TPD_TL_W_PRODUCT_HOLDING"."PRODUCT_HOLDING_ID"="XTPD_TL_W_PRODUCT_HOLDING_ROLE"."PR
UCT_HOLDING_ID")
2 - access("X_TPD_TL_W_THAMES_INDIVIDUAL"."THAMES_INDIVIDUAL_ID"="XTPD_TL_W_PRODUCT_HOLDING_ROLE"
THAMES_INDIVIDUAL_ID")
3 - filter("X_TPD_TL_W_THAMES_INDIVIDUAL"."CREATE_SOURCE"='OBR' AND
"X_TPD_TL_W_THAMES_INDIVIDUAL"."LAST_UPDATE_DATE_TIME">=TO_DATE('2007-12-05 12:00:00', 'yyyy-
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
hh24:mi:ss') AND "X_TPD_TL_W_THAMES_INDIVIDUAL"."LAST_UPDATE_DATE_TIME"<=TO_DATE('2007-12-07
00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
4 - filter("XTPD_TL_W_PRODUCT_HOLDING_ROLE"."THAMES_LEGAL_OWNERSHIP_IND"='Y')
25 rows selected.
|
|
|
|
|
|
| Re: Need to tune SQL involving Join [message #286587 is a reply to message #285714] |
Sat, 08 December 2007 08:55 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
If your query selects about 5% of rows (based on date interval) and CREATE_SOURCE columns has 5-6 distinct values then try :
CREATE INDEX ... ON X_TPD_TL_W_THAMES_INDIVIDUAL (
CREATE_SOURCE,
LAST_UPDATE_DATE_TIME ) ... NOLOGGING;
It will access about 1% only of your data.
Post explain after new index creation.
|
|
|
|
| Re: Need to tune SQL involving Join [message #286648 is a reply to message #286587] |
Sun, 09 December 2007 05:56 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
A agree with Michael.
However, if your query will return more than 1% of the rows in the table, it is possible that the index will not make it faster. If it is returning 5% or more, it is reasonably likely that it will not be faster.
You should discuss partitioning the table with your DBA. That is the only way you will get real orders-of-magnitude improvement over a full table scan if you are selecting large volumes of data.
Ross Leishman
P.S. Get your DBA to make trace files readable. There is an initialisation parameter that can be added to the INIT.ORA file. I don't recall the name of the parameter - but someone here will surely reply with it.
|
|
|
|
| Re: Need to tune SQL involving Join [message #286717 is a reply to message #286587] |
Mon, 10 December 2007 00:00 |
hidnana
Messages: 87
Registered: November 2007
Location: chennai
|
Member |
|
|
Sorry, I was away sick. Thanks for the replies.
Here's the selectivity of predicates.
x_TPD_TL_W_THAMES_INDIVIDUAL.CREATE_SOURCE='OBR'
approx. 375000 records
x_TPD_TL_W_THAMES_INDIVIDUAL.LAST_UPDATE_DATE_TIME between
date '2007-12-06' - interval '12' hour and date '2007-12-07'
approx. 225000 records
|
|
|
|
| Re: Need to tune SQL involving Join [message #286766 is a reply to message #286717] |
Mon, 10 December 2007 01:40 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
The recommended index above will probably help, but perhaps not by a lot. But by all means you should try it and see if it is adequate for your purposes.
For real repformance improvement you will need to look into partitioning.
Ross Leishman
|
|
|
|
Goto Forum:
Current Time: Tue Nov 26 21:47:32 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
