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 Go to next message
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 #285729 is a reply to message #285714] Wed, 05 December 2007 07:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
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: Need to tune SQL involving Join [message #285842 is a reply to message #285729] Wed, 05 December 2007 20:58 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member

  1. Post your Explain Plain
  2. How many different CREATE_SOURCE values are there?
  3. What proportion of the table do you TYPICALLY get with your Start/End filters?
  4. 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 Go to previous messageGo to next message
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 #286228 is a reply to message #285842] Thu, 06 December 2007 23:45 Go to previous messageGo to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
Other inputs.
create_source has got 6 different values.
Between Start and end time, approx. 5% of records.
Re: Need to tune SQL involving Join [message #286587 is a reply to message #285714] Sat, 08 December 2007 08:55 Go to previous messageGo to next message
michael_bialik
Messages: 611
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: A question about SQL * net message in a batch program
Next Topic: Use of RBO in 10g
Goto Forum:
  


Current Time: Thu Dec 08 14:42:33 CST 2016

Total time taken to generate the page: 0.09075 seconds