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

  • 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
  • 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]

  • 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 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;

     

  • 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 🙂

     

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

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