O'Reilly logo

Oracle Database 11gR2 Performance Tuning Cookbook by Ciro Fiorillo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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:
    SET ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required