Re: Unexpected Tkprof Row Count

On 27 Aug 2004 01:15:14 -0700, mccmx_at_hotmail.com (Matt) wrote:

>Oracle 8.1.7.2.1 EE on HP-UX 11
>
>Does anyone know of a reason why a SORT (GROUP BY) row source
>operation would increase the number of rows returned from a query...
>
>I thought that a GROUP BY would reduce the number of rows, not
>increase them...
>
>Here is the TKPROF output which shows that a JOIN between 2 tables
>('PS_TM_PEFF_GPQCAL' and 'PS_TM_PEFF_BNCHMRK') returns about 110,000
>rows but when this is passed up to the GROUP BY operation, the row
>count jumps up to 7.3 million.
>
>Rows Execution Plan
>------- ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: CHOOSE
> 61 SORT (ORDER BY)
> 61 VIEW
> 1464 WINDOW (SORT)
> 1464 SORT (GROUP BY)
> 1464 HASH JOIN (OUTER)
> 1464 HASH JOIN
> 1464 VIEW OF 'PS_TM_PEFF_V_SHDAY'
> 1464 SORT (UNIQUE)
> 1464 UNION-ALL
> 1176 FILTER
> 1177 NESTED LOOPS
> 1177 NESTED LOOPS (OUTER)
> 1177 HASH JOIN
> 42 MERGE JOIN (CARTESIAN)
> 2 NESTED LOOPS
> 2 INDEX GOAL: ANALYZED (UNIQUE
> SCAN) OF 'PS_TM_PEFF_SHPMSTR' (UNIQUE)
> 2 TABLE ACCESS GOAL: ANALYZED
> (FULL) OF 'PS_TM_PEFF_TCJR'
> 42 SORT (JOIN)
> 42 TABLE ACCESS GOAL: ANALYZED
> (FULL) OF 'PS_TM_PEFF_T_CONTR'
> 6220 TABLE ACCESS GOAL: ANALYZED (FULL)
> OF 'PS_TM_PEFF_TWTCAL'
> 1176 TABLE ACCESS GOAL: ANALYZED (FULL) OF
> 'PS_TM_PEFF_COEF'
> 2352 VIEW OF 'PS_TM_PEFF_V_CCEH'
>7314720 SORT (GROUP BY)
> 110312 FILTER
> 145807 NESTED LOOPS (OUTER)
> 110465 TABLE ACCESS GOAL: ANALYZED (BY
> INDEX ROWID) OF 'PS_TM_PEFF_GPQCAL'
> 114449 INDEX GOAL: ANALYZED (RANGE
> SCAN) OF 'PS_TM_PEFF_GPQCAL' (UNIQUE)
> 136124 TABLE ACCESS GOAL: ANALYZED (BY
> INDEX ROWID) OF 'PS_TM_PEFF_BNCHMRK'
> 246588 INDEX (RANGE SCAN) OF
> 'PSATM_PEFF_BNCHMRK' (UNIQUE)
> 138 SORT (AGGREGATE)
> 69 TABLE ACCESS GOAL: ANALYZED (FULL) OF
> 'PS_TM_PEFF_TCJR'
> 1556 SORT (AGGREGATE)
> 868 INDEX GOAL: ANALYZED (FAST FULL SCAN) OF
> 'PS_TM_PEFF_T_CONTR' (UNIQUE)
> 96 SORT (AGGREGATE)
> 48 FIRST ROW
> 48 INDEX GOAL: ANALYZED (RANGE SCAN
> (MIN/MAX)) OF 'PS_TM_PEFF_COEF' (UNIQUE)
> 288 NESTED LOOPS
> 43 MERGE JOIN (CARTESIAN)
> 2 TABLE ACCESS GOAL: ANALYZED (FULL) OF
> 'PS_TM_PEFF_TCJR'
> 43 SORT (JOIN)
> 42 TABLE ACCESS GOAL: ANALYZED (FULL) OF
> 'PS_TM_PEFF_T_CONTR'
> 288 TABLE ACCESS GOAL: ANALYZED (BY INDEX
> ROWID) OF 'PS_TM_PEFF_TWTCAL'
> 1650 INDEX GOAL: ANALYZED (RANGE SCAN) OF
> 'PS_TM_PEFF_TWTCAL' (UNIQUE)
> 1536 SORT (AGGREGATE)
> 1824 INDEX GOAL: ANALYZED (RANGE SCAN) OF
> 'PS_TM_PEFF_CONTRIB' (UNIQUE)
> 110 SORT (AGGREGATE)
> 55 TABLE ACCESS GOAL: ANALYZED (FULL)
> OF 'PS_TM_PEFF_TCJR'
> 24 VIEW OF 'PS_TM_PEFF_V_MFCAL'
> 1095 WINDOW (SORT)
> 1095 TABLE ACCESS GOAL: ANALYZED (FULL) OF
> 'PS_TM_PEFF_MFCAL'
> 2248 VIEW OF 'PS_TM_PEFF_V_TGTCC'
> 2248 HASH JOIN (OUTER)
> 476 TABLE ACCESS GOAL: ANALYZED (FULL) OF
> 'PS_CMS_DPTDPTS'
> 7248 INDEX GOAL: ANALYZED (FAST FULL SCAN) OF
> 'PS_TM_PEFF_TRGT_CC' (UNIQUE)
I have the same experience with this. Inexplicable and seemingly unreconcilable row counts.
Couldn't find anything on this matter on Metalink. The docs also leave you wondering what this number actually should show: the number of processed records by a step or the number of resulting records from a step.

Maybe a participant from this newsgroup can shed some light.

Jaap. Received on Fri Aug 27 2004 - 15:37:50 CDT