AGGREGATE FUNCTIONS

Aggregate functions return a single value based upon a
set of other values.

If used among other expressions in the item list of a SELECT
statement, the SELECT must have a GROUP BY or HAVING clause.
No GROUP BY or HAVING clause is required if the aggregate
function is the only value retrieved by the SELECT statement.

EX:


SUM(expression) - Computes the sum of column values given by
expression.

COUNT(expression) - Counts the rows defined by the expression.

COUNT(*) Counts all rows in the specified table or
view.


MIN(expression) - Finds the minimum value in a column given
by expression.

MAX(expression) - Finds the maximum value in a column given
by expression.

RANK(value_list) WITHIN GROUP (ORDER BY sort_list)

Generates a rank for a hypothetical row (value_list) in a group
of rows generated by GROUP BY.


The number of values processed by an aggregate function varies
depending on the number of rows queried from the table.
This behavior makes aggregate functions different from scalar
functions, which can only operate on the values of a single row
per invocation.

The general syntax of an aggregate function is:

aggregate_function_name ( [ALL | DISTINCT] expression )


The aggregate function name may be AVG, COUNT, MAX, MIN, or SUM.

All aggregate functions except COUNT(*) will ignore NULL values
when computing their results.

Power of Sets : Performance Improvement Tip

its really practical,

recently i had a task of writing a sql statement to fetch records
in the source table that did not exist in the target table for a
particular column filter criteria like ( ex: KPI_ID in (1,2,3) ).

initially i used following approach:


create table test_table as
SELECT decode(kpi_id,1,9,2,9,3,9) from source table
WHERE KPI_ID not in
(
SELECT * from TARGET_TABLE
WHERE KPI_ID IN(1,2,3)
)


The above approach was taking considerable time to execute.
as it involves decode operation as well as not in (can't exactly determine the performance loss

then i remembered set theory

like


if A = B union C

implies B = A minus C


so i used approach


CREATE TABLE test_table as
(
SELECT decode(kpi_id,1,9,2,9,3,9) from source table
minus
SELECT decode(kpi_id,1,9,2,9,3,9) from TARGET_TABLE
WHERE KPI_ID IN(1,2,3)
)

here the performance improved dramatically.
i would call it as wonder of set theory...

Sql Constraints

Constraints allow you to automatically enforce the integrity of data and to filter the data that is placed in a database. In a sense, constraints are rules that define which data values are valid during INSERT, UPDATE, and DELETE operations. When a data modification transaction breaks the rules of a constraint, the transaction is rejected.

Scope

Constraints may be applied at the column-level or the table-level:

Column-level constraints

Declared as part of a column definition and apply only to that column.


Table-level constraints

Declared independently from any column definitions and may apply to one or more columns in the table. A table constraint is required when you wish to define a constraint that applies to more than one column.