Oracle SQL Multi-row functions

Multi Row SQL Functions

They operate on a set of rows and returns one result or one result per group.  We will cover groups in Chapter 3.  This is a powerful feature because it allows you to generate subtotals, sums and averages within the SQL that is retrieving the data.  For now, we will apply the functions to all the rows we return.  In the next chapter, we will break up our returning rows into groups and apply the functions to each of the groups independently.

count

The count function counts the number of rows it processes and returns that number.  You can use the distinct clause to count only distinct rows.

First, we count the number of rows using count(*).  In the second example, we do the same thing.  Some DBAs believe that count(1) is more efficient than count(*), but this is a myth.  In example three, we count the number of store_keys.  If a row is processed with a NULL store_key, it will be counted in example one and two but not in three or four.  In example four, we count distinct store_keys.  So there are 100 rows in the sales table, each row has a store_key (no NULL store_keys) and there are ten distinct store_keys in the table (listed below).

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.