Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Location
    India
    Posts
    87

    Unanswered: Query Tuning : Clarification

    Hi,
    I need small clarification.
    Explain Plan for the following query is given below

    select distinct b.sub_ip_code from tcs_mis_project b,dim_ip_practice a
    where b.sub_ip_name=a.sub_ip_name

    -------------------------------------------------------------------------------
    | Operation | PHV/Object Name | Rows | Bytes| Cost |
    --------------------------------------------------------------------------------
    |UPDATE STATEMENT |----- 543392659 -----| | | 5 |
    |UPDATE | | | | |
    | TABLE ACCESS FULL |DIM_IP_PRACTICE | 58 | 1K| 5 |
    | SORT UNIQUE | | 52 | 780 | 765 |
    | TABLE ACCESS FULL |TCS_MIS_PROJECT | 1K| 27K| 762 |
    --------------------------------------------------------------------------------

    What exactly is meant by rows in the explain plan table.
    The table TCS_MIS_PROJECT has around 23000 rows and the table DIM_IP_PRACTICE has 58 rows and the total number of records returned by the query is also 58.

    Thanks!

  2. #2
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    The update stement is
    update dim_ip_practice a set a.sub_ip_code=
    (select distinct b.sub_ip_code from tcs_mis_project b
    where b.sub_ip_name=a.sub_ip_name)

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I think what it means is that the full scan of TCS_MIS_PROJECT is expected to return 1000 records that match, and that these will have 52 unique values when DISTINCT is applied.

  4. #4
    Join Date
    Oct 2003
    Posts
    87
    You need to analyze the two tables and make sure there are indexes on the two join predicates (sub_ip_name).
    Oracle - DB2 - MS Access -

Posting Permissions

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