All Forums Database
Cvinodh 32 posts Joined 10/11
02 Aug 2012
Count(*) Explain Plan

Hi all,

I have a doubt on the explain query generated for a simple count(*) query.

SELECT COUNT(*) FROM databasename.tablename 

The Explain Plan generated from a V13 system is as below

  1) First, we lock a distinct databasename."pseudo table" for read on a
     RowHash to prevent global deadlock for
  2) Next, we lock databasename.tablename for read.
  3) We do an all-AMPs SUM step to aggregate from
     databasename.tablename by way of an all-rows scan with no
     residual conditions.  Aggregate Intermediate Results are computed
     globally, then placed in Spool 3.  The size of Spool 3 is
     estimated with high confidence to be 1 row (23 bytes).  The
     estimated time for this step is 0.03 seconds.
  4) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
     an all-rows scan into Spool 1 (group_amps), which is built locally
     on the AMPs.  The size of Spool 1 is estimated with high
     confidence to be 1 row (25 bytes).  The estimated time for this
     step is 0.01 seconds.
  5) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> The contents of Spool 1 are sent back to the user as the result of
     statement 1.  The total estimated time is 0.04 seconds.

I believe that row count is fetched by scanning the Cylinder Index rather than the entire table.

Also from the 3'rd step of the plan i understand the all Amps compute their row count and then it is aggregated globally to derived the final row count (i percieve this from the keyword Aggregate Intermediate Results are computed globally). Also since Optimizer reports that 3'rd step spool size is one row with high confidence i believe that the third step generates the final answer which is the row count of the table.

Now i am not sure what is exactly the purpose of the 4'th step?

why the one record in Spool 3 is moved to Spool 2?

why is this a group amp operation?


dnoeth 4628 posts Joined 11/04
03 Aug 2012

You're correct.

"computed globally" -> it's not grouped by the PI columns

That step #4 is strange, but you see it in most aggregates, it's to apply some additional work (like format) to the result.

And "group AMP" means only some of the existing AMPs participate in that step (in fact it's probably a single AMP)


In TD13.10 the cylinder scan is indicated in explain:

"by way of a cylinder index scan"




You must sign in to leave a comment.