Optimizing Sort Operations - Oracle Database 11gR2 Performance Tuning - page 226

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 AUTOT TRACE EXP STAT
SELECT MAX(CUST_CREDIT_LIMIT) FROM CUSTOMERS;
SELECT MIN(CUST_CREDIT_LIMIT) FROM CUSTOMERS;

6. Execute the query in step 3:

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

7. Use different ways to count the rows in a table:

SELECT COUNT(*) FROM CUSTOMERS;
SELECT COUNT(1) FROM CUSTOMERS;