Same SQL Query runs faster in postgresql but much slower in Oracle 10G

Question

Post reply

Same SQL Query runs faster in postgresql but much slower in Oracle 10G

krishnamurali2489

Mr or Mrs. 500

Points: 517

June 20, 2021 at 5:32 pm

Go to Answer

#3898576

Hi,

I am trying to run the SQL query mentioned below. The query executes within 8 seconds in Postgresql but it runs for more than 1 minute 40 seconds in Oracle. I am not able to understand what is causing the big difference.

I did some initial analysis,

The count of CONTACT_ASSIGNMENT records is 135263 and CONTACT_PRODUCER_ASSOCIATION is 4430962.

The CONTACT_PRODUCER_ASSOCIATION also has indexing and everything seems to be fine from my eyes. Can someone please throw some light on it.

SQL Query:

SELECT DISTINCT
t0.FIRST_NAM, t0.LAST_NAM, t0.PRIMARY_PHONE_NBR,
t0.PORTAL_ALT_PHONE_NBR,
t0.PRIMARY_FAX_NBR,
t0.PORTAL_ALT_FAX_NBR, t0.PRIMARY_EMAIL_ADDR, t0.PORTAL_ALT_EMAIL_ADDR, t0.SEND_NOTIFICATION_IND, t0.USER_ID,
t1.USER_ROLE
FROM
CONTACT_ASSIGNMENT t0 join CONTACT_PRODUCER_ASSOCIATION t1 on t0.user_id = t1.user_id
WHERE
((((CONCAT(t1.BRANCH_CD, t1.PRODUCER_CD) IN
('80213','91143','90212','902112','922'))
AND
(t1.USER_ROLE
IN
('Lens Agent Bookkeeper', 'Lens Agent Manager', 'Lens Agent Viewer', 'Statements')))
AND
(t0.USER_STATUS <> 'SUSPENDED')) AND (t0.USER_ID = t1.USER_ID)) ORDER BY t0.FIRST_NAM ASC;

INDEXING OF CONTACT_PRODUCER_ASSOCIATION

CREATE INDEX Lenschema.XIF1CONTACT_PRODUCER_ASSOCIATION ON Lenschema.CONTACT_PRODUCER_ASSOCIATION (BRANCH_CD ASC, PRODUCER_CD ASC)
LOGGING
TABLESPACE "LenschemaX"
PCTFREE 10
INITRANS 2
STORAGE
(
INITIAL 65536
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
)
NOPARALLEL
CREATE INDEX Lenschema.XIF2CONTACT_PRODUCER_ASSOCIATION ON Lenschema.CONTACT_PRODUCER_ASSOCIATION (CONTACT_ID ASC)
LOGGING
TABLESPACE "LenschemaX"
PCTFREE 10
INITRANS 2
STORAGE
(
INITIAL 65536
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
)
NOPARALLEL
CREATE UNIQUE INDEX Lenschema.XPKCONTACT_PRODUCER_ASSOCIATION ON Lenschema.CONTACT_PRODUCER_ASSOCIATION (BRANCH_CD ASC, PRODUCER_CD ASC, CONTACT_ID ASC, CONTACT_ROLE ASC)
LOGGING
TABLESPACE "LenschemaX"
PCTFREE 10
INITRANS 2
STORAGE
(
INITIAL 65536
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
)
NOPARALLELCREATE TABLE Lenschema.CONTACT_PRODUCER_ASSOCIATION
(
BRANCH_CD CHAR(3 BYTE) NOT NULL
, PRODUCER_CD CHAR(6 BYTE) NOT NULL
, CONTACT_ID VARCHAR2(10 BYTE) NOT NULL
, CONTACT_ROLE VARCHAR2(60 BYTE) NOT NULL
, GEN_ID NUMBER(38, 0)
, CONSTRAINT XPKCONTACT_PRODUCER_ASSOCIATION PRIMARY KEY
(
BRANCH_CD
, PRODUCER_CD
, CONTACT_ID
, CONTACT_ROLE
)
ENABLE
)
LOGGING
TABLESPACE "Lenschema"
PCTFREE 10
INITRANS 1
STORAGE
(
INITIAL 65536
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
)
NOCOMPRESS
-- Unable to render TABLE DDL for object Lenschema.CONTACT_PRODUCER_ASSOCIATION with DBMS_METADATA attempting internal generator.
CREATE INDEX Lenschema.XIF1CONTACT_PRODUCER_ASSOCIATION ON Lenschema.CONTACT_PRODUCER_ASSOCIATION (BRANCH_CD ASC, PRODUCER_CD ASC)
LOGGING
TABLESPACE "LenschemaX"
PCTFREE 10
INITRANS 2
STORAGE
(
INITIAL 65536
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
)
NOPARALLEL
CREATE INDEX Lenschema.XIF2CONTACT_PRODUCER_ASSOCIATION ON Lenschema.CONTACT_PRODUCER_ASSOCIATION (CONTACT_ID ASC)
LOGGING
TABLESPACE "LenschemaX"
PCTFREE 10
INITRANS 2
STORAGE
(
INITIAL 65536
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
)
NOPARALLEL
CREATE UNIQUE INDEX Lenschema.XPKCONTACT_PRODUCER_ASSOCIATION ON Lenschema.CONTACT_PRODUCER_ASSOCIATION (BRANCH_CD ASC, PRODUCER_CD ASC, CONTACT_ID ASC, CONTACT_ROLE ASC)
LOGGING
TABLESPACE "LenschemaX"
PCTFREE 10
INITRANS 2
STORAGE
(
INITIAL 65536
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
)
NOPARALLELCREATE TABLE Lenschema.CONTACT_PRODUCER_ASSOCIATION
(
BRANCH_CD CHAR(3 BYTE) NOT NULL
, PRODUCER_CD CHAR(6 BYTE) NOT NULL
, CONTACT_ID VARCHAR2(10 BYTE) NOT NULL
, CONTACT_ROLE VARCHAR2(60 BYTE) NOT NULL
, GEN_ID NUMBER(38, 0)
, CONSTRAINT XPKCONTACT_PRODUCER_ASSOCIATION PRIMARY KEY
(
BRANCH_CD
, PRODUCER_CD
, CONTACT_ID
, CONTACT_ROLE
)
ENABLE
)
LOGGING
TABLESPACE "Lenschema"
PCTFREE 10
INITRANS 1
STORAGE
(
INITIAL 65536
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
)
NOCOMPRESS
NOCOMPRESS
NOCOMPRESS
NOCOMPRESS
NOCOMPRESS

This topic was modified 3 years, 5 months ago by krishnamurali2489.

Erland Sommarskog

SSC-Insane

Points: 24038
More actions
June 20, 2021 at 8:20 pm
Answer
#3898610
If I am to make a guess. the Postgres optimizer has a rule, so that it can deal with the condition
```
((((CONCAT(t1.BRANCH_CD, t1.PRODUCER_CD) IN
('80213','91143','90212','902112','922'))
```
whereas Oracle 10G has not. Isn't 10G a quite old version of Oracle, by the way?
But I think that you would get better help if you ask in a forum which is targeted on Oracle. This forum is not for Oracle, nor for Postgres, but for Microsoft SQL Server. (Which certainly is not able perform any transformation of the condition above.
While I could share ideas on indexes to add and modifications to make to the query for it to run better on SQL Server, that advice may still be applicable to Oracle.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

krishnamurali2489 wrote:

Hi,
I am trying to run the SQL query mentioned below. The query executes within 8 seconds in Postgresql but it runs for more than 1 minute 40 seconds in Oracle. I am not able to understand what is causing the big difference.

Quick questions, is the setup and the hardware equal for both platforms and if you think they are, how do you assess that?

😎

krishnamurali2489 Mr or Mrs. 500 Points: 517 More actions · Answer 2

Hi Sommarskog ,

Sorry for late response !!!

I had a urgent travel..

Thanks very much for your suggestion.. As you advised I have modified the Concat query to below and it is getting executed in less than a second !!!

SELECT DISTINCT
t0.FIRST_NAM, t0.LAST_NAM, t0.PRIMARY_PHONE_NBR,
t0.PORTAL_ALT_PHONE_NBR,
t0.PRIMARY_FAX_NBR,
t0.PORTAL_ALT_FAX_NBR, t0.PRIMARY_EMAIL_ADDR, t0.PORTAL_ALT_EMAIL_ADDR, t0.SEND_NOTIFICATION_IND, t0.USER_ID,
t1.USER_ROLE
FROM
CONTACT_ASSIGNMENT t0 join CONTACT_PRODUCER_ASSOCIATION t1 on t0.user_id = t1.user_id
WHERE
(((
BRANCH_CD='XXX' AND PRODUCER_CD ='XXXXXX' OR
BRANCH_CD='XXX' AND PRODUCER_CD ='XXXXXX' OR
BRANCH_CD='XXX' AND PRODUCER_CD ='XXXXXX' OR
BRANCH_CD='XXX' AND PRODUCER_CD ='XXXXXX' )
AND
(t1.USER_ROLE
IN
('Lens Agent Bookkeeper', 'Lens Agent Manager', 'Lens Agent Viewer', 'Statements')))
AND
(t0.USER_STATUS <> 'SUSPENDED')) AND (t0.USER_ID = t1.USER_ID)) ORDER BY t0.FIRST_NAM ASC;

krishnamurali2489 Mr or Mrs. 500 Points: 517 More actions · Answer 3

Eirikur Eiriksson wrote:

krishnamurali2489 wrote:
Hi,
I am trying to run the SQL query mentioned below. The query executes within 8 seconds in Postgresql but it runs for more than 1 minute 40 seconds in Oracle. I am not able to understand what is causing the big difference.
Quick questions, is the setup and the hardware equal for both platforms and if you think they are, how do you assess that? 😎

Hi Eiriksson,

Thanks for your suggestion, The hardware is more or less same on both the platforms. It looks like the issue is with the Concat condition in my query. As @Sommarskog mentioned, I have modified it and now it is executing much faster. Thanks again 🙂