What's the concept of the OVER clause in Oracle?

It's area of the Oracle analytic functions.

The OVER clause identifies the partitioning, ordering &lifier window "thatInch the analytic function works.

For instance, this computes a moving average:

AVG(amt) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

date   amt   avg_amt
=====  ====  =======
1-Jan  10.0   7.0
2-Jan  11.0  17.0
3-Jan  30.0  17.0
4-Jan  10.0  18.0
5-Jan  14.0   8.0

It works on the moving window (3 rows wide) within the rows, purchased by date.

This computes a running balance:

SUM(amt) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

date   amt   sum_amt
=====  ====  =======
1-Jan  10.0  10.0
2-Jan  11.0  21.0
3-Jan  30.0  51.0
4-Jan  10.0  61.0
5-Jan  14.0  75.0

It works on the window which includes the present row and all sorts of prior rows.

This computes the utmost, individually for every "dept":

MAX(amt) OVER (PARTITION BY dept)

dept  amt   max_amt
====  ====  =======
ACCT   5.0   7.0
ACCT   7.0   7.0
ACCT   6.0   7.0
MRKT  10.0  11.0
MRKT  11.0  11.0
SLES   2.0   2.0

It works on the window which includes all rows for the dept.

Technology-not only to change some aggregate functions into analytic:

SELECT  MAX(date)
FROM    mytable

will return 1 row having a single maximum,

SELECT  MAX(date) OVER (ORDER BY id)
FROM    mytable

will return all rows having a running maximum.