Oracle Database 11gR2 Performance Tuning Cookbook

Using count, min/max, and group-by

In this recipe, we will see how to count rows, compute min/max aggregates, and use filters in group-by queries.

How to do it...

The following steps will demonstrate the use of count, min/max, and group-by:

1. Connect to the SH schema:

   CONNECT sh@TESTDB/sh
   

2. Show the execution plan for a MIN/MAX query:

   SET AUTOT TRACE EXP
   SELECT MAX(CUST_CREDIT_LIMIT) FROM CUSTOMERS;
   SELECT MIN(CUST_CREDIT_LIMIT) FROM CUSTOMERS;
   

3. Show the execution plan for a query which returns the MIN and the MAX:

   SELECT MAX(CUST_CREDIT_LIMIT), MIN(CUST_CREDIT_LIMIT)
   FROM CUSTOMERS;
   

4. Create an index on CUSTOMERS in the column in which we need to aggregate:

   CREATE INDEX IX_CUST_CREDIT_LIMIT
     ON CUSTOMERS (CUST_CREDIT_LIMIT);
   

5. Execute the query in step 2: ...