Some of the new concepts introduced as a part of Oracle new releases are:
Analytical Functions
In addition to the normal aggregate function like Max() etc. Oracle came up with some more commonly useful analytical functions, which can be implemented by PL/SQL logic. Mainly 5 commonly used functions are there:
- rank() :– In a select statement after selecting certain data using over() function, you can apply this function to get the normal rank as separate column. It won’t take care of multiple rows with same ranks, simply returns rank on the basis.
Example: Consider the table Student<name, sub, marks, joining_date>.
Select name, marks, rank() over() (order by marks desc) st_rank from Student where st_rank = 2;
This generates the output as <name, marks, st_rank> with 1…last number. In case of repetition of ranks, next rank won’t be effected. So its like 1,2,2,3,4,4,4,5,….etc.
-
dense_rank() :– same as above rank() function, except that in case of repetition of the ranks the next coming ranks would be effected. For example, the series looks like 1,2,2,4,5,5,5,8,…etc. Syntax is same as above except to replace rank() by dense_rank().
-
LAG :– This will return the previous row of current selection. In case of selecting the consolation prizes generally, this would be the best to use.
Example: Consider same above Student table.
Select LAG( joining_date, 1 /*range operator to consider the previous record*/ ) over() (order by marks desc) from Student where marks = 100.
This will return the student who joined immediately next to the student who got the marks 100.
-
LEAD :– Just to oppose of LAG. LAG is previous record where as LEAD is next record, that’s the only change. No change in the syntax except replacing LAG with LEAD.
-
over() :– This is an intermediate function provided by Oracle to support other functions like rank(), dense_rank(), and LAG etc. For syntax and usage you can refer to above examples in detail.
-
ROW_NUMBER :– This is very commonly used function to specify the row number of interest. Our of the entire selection, you can specify the row using this function. Using this, you can even implement the above functions like rank() and dense_rank() etc.
Partition By
This is just a replacement of group by in our general SQL. It’s functionality is same as group by, you can say this syntactically replacement given by Oracle. In addition to those normal grouping functionalities, it has extra features like handling segregation of memory etc.
In other words, suppose you need some 100 MB of memory for your database schema tables. But you have 100 MB memory but not continuous, then it’s not possible in normal case. In this type of cases, you can use partition by to allocate the required memory in different memory chunks available on the basis of some given column criteria. Generally, these things are very common to DBA not for Developer.
There are two types of partitions you can create. They are:
- Using range :– While using partition by, you can mention the range of the values of the given column, on which the partitioning is going to happen.
Example: The SQL statement would be like this.
create <table_name> ( …. ) partition by (<some_column_name>) (partition p1 value 10 and 50) /* partition with name p1 has the values of given column between 10 and 50*/ (partition p2 value 50 and 100) /* similarly partition with name p2 has the range of value between 50 and 100*/
syntax presented here is not very sure. It has other functionalities like mentioning the memory size of the chunk/partition too. Need to explore for more syntaxes.
- Using hash :– Similarly, instead of the range, Oracle provided internal function hash(). Using this, we need to mention the specific column on which this hash function need to create hash code, which is used as the criteria to store the table date in different memory chunks.
Example: The SQL statement would be.
create <table_name> ( …. ) partition by hash(<some_column_name>)
/* Internally, hash function generates hash code for each record and based on that code, it would decide the memory chunk/partition to which it can be stored into.*/
This section on partition by need to be explored more to update this article.