Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Location
    Singapore
    Posts
    3

    Unanswered: The following query take ages...

    Hi,

    The following query is having performance issue(in fact it took so long that I've terminate it after running for more than 12 hrs!):

    update EMF_JOIN a
    set annotation =
    (select /*+ FIRST_ROWS */
    '; 1#' || lpad(TO_CHAR(rental_tariff_grp),10,' ')
    from EXTERNAL_ID_EQUIP_MAP@CATALOG b, OP_SVC_INST@ORDER c
    where a.subscr_no = b.subscr_no
    and b.external_id_type = 14
    and b.external_id = TO_CHAR(c.svc_inst_id)

    For info:
    EMF_JOIN is in the CUST database with approx 1.23 millions rows
    EXTERNAL_ID_EQUIP_MAP is in another CATALOG database with also approx 1.23 millions rows for external_id_type 14
    OP_SVC_INST is in ORDER database will approx 1.25 millions rows.

    Can anybody please help to advice if any improvement can be made.

    Thank you in advance!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: The following query take ages...

    Originally posted by SoonPheng
    Hi,

    The following query is having performance issue(in fact it took so long that I've terminate it after running for more than 12 hrs!):

    update EMF_JOIN a
    set annotation =
    (select /*+ FIRST_ROWS */
    '; 1#' || lpad(TO_CHAR(rental_tariff_grp),10,' ')
    from EXTERNAL_ID_EQUIP_MAP@CATALOG b, OP_SVC_INST@ORDER c
    where a.subscr_no = b.subscr_no
    and b.external_id_type = 14
    and b.external_id = TO_CHAR(c.svc_inst_id)

    For info:
    EMF_JOIN is in the CUST database with approx 1.23 millions rows
    EXTERNAL_ID_EQUIP_MAP is in another CATALOG database with also approx 1.23 millions rows for external_id_type 14
    OP_SVC_INST is in ORDER database will approx 1.25 millions rows.

    Can anybody please help to advice if any improvement can be made.

    Thank you in advance!
    The TO_CHAR on the last line is a performance killer - unless you have a function-based index on c.svc_inst_id? Without an FBI, you will perform a full table scan on OP_SVC_INST@ORDER 1.23 million times!

    Preferably, remove the need for the TO_CHAR (e.g. use TO_NUMBER on left hand side instead). Then make sure you have these indexes:

    CREATE INDEX x1 ON EXTERNAL_ID_EQUIP_MAP(subscr_no); -- perhaps followed by external_id_type

    CREATE INDEX x2 ON OP_SVC_INST(svc_inst_id); -- or TO_CHAR(svc_inst_id) if you must keep the TO_CHAR

    If you intend to use a function based index, check that your database is set up to use them (see documentation).

    Another consideration is that getting data across dblinks is relatively slow. Is there any way you can avoid that?

  3. #3
    Join Date
    Sep 2003
    Location
    Singapore
    Posts
    3
    Hi Tony,

    Thanks a millions for your advice! The function-based index on the TO_CHAR(svc_inst_id) indeed speed up the whole query. The query now took 1.5 hrs to complete.

    cheers!

  4. #4
    Join Date
    Aug 2003
    Posts
    123
    Hi,
    What did you do to improve the performance.

    --Jaggu

  5. #5
    Join Date
    Sep 2003
    Location
    Singapore
    Posts
    3
    hi,

    I've created 2 indexes as suggested by Tony:
    - CREATE INDEX x1 ON EXTERNAL_ID_EQUIP_MAP(subscr_no)
    - CREATE INDEX x2 ON OP_SVC_INST TO_CHAR(svc_inst_id) -> this is a function-based index.

    cheers!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •