Re: Weird behaviour in query execution plan

From: JTommaney <cdos_jtommaney_at_comcast.net>
Date: 7 Oct 2005 09:10:24 -0700
Message-ID: <1128701424.807622.251290@g47g2000cwa.googlegroups.com>

Hmm,

There are some scalability issues inherent in the using the views as done here to derive the totals. Implied in that design is a second scan (index or full) of the table to do the totals. In addition, we're grouping on that view, generating a sort. So we're about 2x extra work on the logical io's and sort behavior won't scale under some circumstances.

Why not try the rollup functionality:

select
  num_col1,
  varchar_col1,
  decode(grouping( VARCHAR_COL2),1, 'total',varchar_col2)

                varchar_col2,
  SUM(NUM_COL4) row_TOT_NUM_COL4,
  COUNT(1) ROW_COUNT
from a
WHERE (VARCHAR_COL1, NUM_COL1, VARCHAR_COL2) IN

	(SELECT VARCHAR_COL1, NUM_COL1, VARCHAR_COL2
			FROM B )

group by rollup (VARCHAR_COL1, NUM_COL1, VARCHAR_COL2) having count(1) >= case

    when varchar_col2 = 'total' then 250     else 100
    end
or sum(num_col4) >= case

    when varchar_col2 = 'total' then 10000     else sum(num_col4) + 1
end; Received on Fri Oct 07 2005 - 11:10:24 CDT