Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Posts
    1

    Unanswered: subquery returns over 120 million rows?

    We have basically 2 tables A and B. A is pretty small table with less than 20 rows. B is big table with over 200,000 rows.

    TEST 1 is with production data and TEST 2 is with our testing machine data. Both tables have almost same number of rows on both testings.

    We ran trace and tkprof on two testing machines, got very different situation.

    My question is in TEST 1, table B has about 255,183 rows, but why step 'TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)' returns over 120 million records?

    Any help would be highly appreciated.


    TEST 1:

    Rows Execution Plan
    ------- ---------------------------------------------------
    0 SELECT STATEMENT GOAL: CHOOSE
    17 SORT (GROUP BY)
    44819 FILTER
    255184 HASH JOIN
    14 TABLE ACCESS GOAL: ANALYZED (FULL) OF
    'A'
    255183 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'B'
    508424 SORT (AGGREGATE)
    120983347 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'B'
    121237559 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IDX_B_ID(NON-UNIQUE)


    TEST 2:

    Rows Execution Plan
    ------- ---------------------------------------------------
    0 SELECT STATEMENT GOAL: CHOOSE
    7 SORT (GROUP BY)
    85248 FILTER
    229105 HASH JOIN
    9 TABLE ACCESS GOAL: ANALYZED (FULL) OF
    'A'
    229104 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'B'
    205256 SORT (AGGREGATE)
    299782 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'B'
    402410 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IDX_B_ID' (NON-UNIQUE)


    -----------------------------------------------------------
    My query

    SELECT DISTINCT t2.processkey,
    t2.processkey processkeydecode,
    t12.processlabel,
    t2.status,
    t2.status statusdecode,
    t12.deploymenttime,
    t12.deploymenttime deploymenttimestamp,
    t12.enabled,
    count(t2.status) count
    FROM B t2,
    A t12
    WHERE t2.audittimestamp =
    (
    SELECT max(t18.audittimestamp)
    FROM B t18
    WHERE t2.instanceid = t18.instanceid
    )
    AND t2.processkey = t12.processkey
    GROUP BY t2.processkey,
    t2.status,
    t12.processlabel,
    t12.deploymenttime,
    t12.enabled;

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212

    Re: subquery returns over 120 million rows?

    Originally posted by sunblade
    We have basically 2 tables A and B. A is pretty small table with less than 20 rows. B is big table with over 200,000 rows.

    TEST 1 is with production data and TEST 2 is with our testing machine data. Both tables have almost same number of rows on both testings.

    We ran trace and tkprof on two testing machines, got very different situation.

    My question is in TEST 1, table B has about 255,183 rows, but why step 'TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)' returns over 120 million records?

    Any help would be highly appreciated.


    TEST 1:

    Rows Execution Plan
    ------- ---------------------------------------------------
    0 SELECT STATEMENT GOAL: CHOOSE
    17 SORT (GROUP BY)
    44819 FILTER
    255184 HASH JOIN
    14 TABLE ACCESS GOAL: ANALYZED (FULL) OF
    'A'
    255183 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'B'
    508424 SORT (AGGREGATE)
    120983347 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'B'
    121237559 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IDX_B_ID(NON-UNIQUE)


    TEST 2:

    Rows Execution Plan
    ------- ---------------------------------------------------
    0 SELECT STATEMENT GOAL: CHOOSE
    7 SORT (GROUP BY)
    85248 FILTER
    229105 HASH JOIN
    9 TABLE ACCESS GOAL: ANALYZED (FULL) OF
    'A'
    229104 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'B'
    205256 SORT (AGGREGATE)
    299782 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'B'
    402410 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IDX_B_ID' (NON-UNIQUE)


    -----------------------------------------------------------
    My query

    SELECT DISTINCT t2.processkey,
    t2.processkey processkeydecode,
    t12.processlabel,
    t2.status,
    t2.status statusdecode,
    t12.deploymenttime,
    t12.deploymenttime deploymenttimestamp,
    t12.enabled,
    count(t2.status) count
    FROM B t2,
    A t12
    WHERE t2.audittimestamp =
    (
    SELECT max(t18.audittimestamp)
    FROM B t18
    WHERE t2.instanceid = t18.instanceid
    )
    AND t2.processkey = t12.processkey
    GROUP BY t2.processkey,
    t2.status,
    t12.processlabel,
    t12.deploymenttime,
    t12.enabled;
    in my opinion you join tabe B with itself (or something like this) probably becouse of B t2 in 1. select and B t18 in subquery.

Posting Permissions

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