Results 1 to 7 of 7
  1. #1
    Join Date
    May 2009
    Posts
    6

    Unanswered: Please help me modify below oracle sql statement to run faster

    Please help me with this below worksheet for my class ASAP:

    Look at the SQL statement below and the table below the SQL showing Oracle’s analysis of the execution plan. This is a real query from a real system that has been slightly modified. Questions to answer:

    1.The query runs in an acceptable amount of time now. It is expected, however, that the tables it queries are likely to grow substantially. What part of the query will slow down significantly as the tables grow? Explain why you chose that part.

    2.Name two methods that are likely to keep the performance of this query fast as the tables grow large. Explain what must change to maintain the performance.

    Feel free to consult outside sources (i.e. books) but not other people (we need it to be your own work).

    SQL statement:

    select
    a.target_match_id id, b.dataset_id dataset_id,
    b.dataset_name dataset_name, a.dataset_update_id dataset_update,
    lower(b.dataset_tableprefix) prefix,
    (select count(*)
    from mp_match_req_mesg
    where match_request_id = a.match_request_id
    and target_match_id = a.target_match_id
    and updateaction in (1, 2)
    ) mesg_count,
    decode(a.dataset_update_id,
    NULL,
    decode( nvl(a.external_channel_ind, 0),
    0,
    'ALL (' || (select sum(active_count)
    from dataset_update where a.dataset_id = dataset_id
    and available = 1 and
    updateaction not in (3, 4)) || ')',
    'External Query'),
    (select decode(update_name, null,
    TO_CHAR(date_source_generated, 'yyyymmdd'),
    update_name) || ' ALL (' || active_count || ')'
    from dataset_update
    where a.dataset_update_id = dataset_update_id)
    ) updateinfo,
    status_name status
    from mp_target_match a, dataset b, mp_status_code c
    where a.match_request_id = :1 and
    a.dataset_id = b.dataset_id and
    a.status_code = c.status_code


    OPERATION NAME ROWS BYTES COST PARTITION START PARTITION STOP
    SELECT STATEMENT 235 18K 19
    SORT AGGREGATE 1 12
    TABLE ACCESS BY INDEX ROWID MP_MATCH_REQ_MESG 1 12 6
    BITMAP CONVERSION TO ROWIDS
    BITMAP AND
    BITMAP CONVERSION FROM ROWIDS
    INDEX RANGE SCAN MPMRM_IDX_1 156 3
    BITMAP CONVERSION FROM ROWIDS
    INDEX RANGE SCAN MPMRM_IDX_2 156 3
    SORT AGGREGATE 1 13
    TABLE ACCESS FULL DATASET_UPDATE 13 169 214
    TABLE ACCESS BY INDEX ROWID DATASET_UPDATE 1 21 2
    INDEX UNIQUE SCAN PK_DATASET_UPD 1 1
    HASH JOIN 235 18K 19
    NESTED LOOPS 235 10K 9
    TABLE ACCESS BY INDEX ROWID MP_TARGET_MATCH 470 14K 7
    INDEX RANGE SCAN MPTMH_MPMRQ_FK 470 4
    TABLE ACCESS BY INDEX ROWID MP_STATUS_CODE 1 15 1
    INDEX UNIQUE SCAN PK_STATUS_CODE 1 0
    TABLE ACCESS FULL DATASET 998 33K 10

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SELECT a.target_match_id                                                            id,
           b.dataset_id                                                                 dataset_id,
           b.dataset_name                                                               dataset_name,
           a.dataset_update_id                                                          dataset_update,
           Lower(b.dataset_tableprefix)                                                 prefix,
           (SELECT Count(* )
            FROM   mp_match_req_mesg
            WHERE  match_request_id = a.match_request_id
                   AND target_match_id = a.target_match_id
                   AND updateaction IN (1,2)) mesg_count,
           Decode(a.dataset_update_id,NULL,Decode(Nvl(a.external_channel_ind,0),0,'ALL ('
                                                                                  ||(SELECT Sum(active_count)
                                                                                     FROM   dataset_update
                                                                                     WHERE  a.dataset_id = dataset_id
                                                                                            AND available = 1
                                                                                            AND updateaction NOT IN (3,4))
                                                                                  ||')',
                                                                                'External Query'),
                                      (SELECT Decode(update_name,NULL,To_char(date_source_generated,'yyyymmdd'),
                                                                 update_name)
                                              ||' ALL ('
                                              ||active_count
                                              ||')'
                                       FROM   dataset_update
                                       WHERE  a.dataset_update_id = dataset_update_id)) updateinfo,
           status_name                                                                  status
    FROM   mp_target_match a,
           dataset b,
           mp_status_code c
    WHERE  a.match_request_id = :1
           AND a.dataset_id = b.dataset_id
           AND a.status_code = c.status_code
    >Feel free to consult outside sources (i.e. books) but not other people (we need it to be your own work).
    Good Luck!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2009
    Posts
    6
    why did u modify it this way? i'm confused now

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >why did u modify it this way?
    In order to make the SQL more readable.

    You should eliminate "mp_status_code c" out of the FROM clause.
    It does not belong there because it contributes no data to the SELECT clause.
    It can be subordinated into the WHERE clause.

    >Feel free to consult outside sources (i.e. books) but not other people (we need it to be your own work).
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    May 2009
    Posts
    6
    Thanks for the explanation

  6. #6
    Join Date
    May 2009
    Posts
    6
    ok so i think the answer to the first question on this worksheet has been identified and makes sense to me. But i don't understand question 2 either.

    Oh and suggestion for the change of the sql:

    from mp_target_match a, dataset b
    where a.match_request_id = :1 and
    a.dataset_id = b.dataset_id and
    a.status_code in (select status_code from mp_status_code)

    if someone can also please confirm that change is correct

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >if someone can also please confirm that change is correct
    Do you get the same results?
    How did the EXPLAIN PLAN change?
    How did the performance change?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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