A colleague of mine had the problem to group continuous entries of same type to be able to count number of continuos entries.
So basically the problem was to build groups on continuous identical returncodes and increase group number with every change. After those groups have been built the count column can be calculated using a COUNT(*) OVER (PARTITION BY the group-identification).
This solution compares the returncode value of the previous row (within same username ordered by timestamp) with the current value and returns “1” in case of a difference if the current value is unchanged NULL is returned. In the second step a running summary over this 1/NULL result is built.
The model clauses has the possibility to compare the values of different rows and generate a value for a different column based on this comparision. In our case I compare the value of the returncode of the previous row to the value of the returncode of the current row. In case of identical values i reuse the value of the grp column of the previous row to set the grp value of the current row, in case of a difference I increase the previous rows value by 1.
With the MATCH_RECOGNIZE clause we can do a count of the group members simultaneously to the group assignement.
Prior to ORACLE 12c truncating a table referenced by enabled foreign keys was no possible (even if the tables were empty).
REM This will not work unless there is a FK with on delete cascade.
ORA-14705: unique or primary keys referenced by enabled foreign keys in table "O12TEST"."EMP_TRUNC_CASC_DEMO"
The new row_limiting_clause allows us to perform Top-N queries without having to use analytic functions or nested inline views. We can define rows to be skipped (offset), how to handle ties, percent and absolute number of rows to be returned – just about everything we need.
There is one thing we have to take care by ourselves … the data should be ordered to get a decent result.
Skip first 5 rows and fetch next 2 rows.
When we take a look at the result of the first query, we can see, that besides Nancy Greenberg two more rows exist with exactly the same salary (ties) – to get them by the second query we have to tell ORACLE how to handle ties.
A top-N query per cent is also possible. The number of rows to be returned is always a ceiled value (eg. 5% of 107 rows equals 5.35 which will be ceiled to 6 rows).
ORACLE does not care whether you write ROWS or ROW. Furthermore FIRST and NEXT do not have a semantic meaning to the SQL statement and are used for readability issues.
The execution plans for the native top-n queries have fewer steps than the implementations using analytic functions or nested inline views.
Looking at the filters in the execution plans, we will find some “analytic-function-like” implementations.
In my opinion native top-n query is a very handy feature helping us to write code that is more maintainable and readable. It should be used as soon as we are on ORACLE 12c.
For a long time the ORACLE community has waited for something like an auto-increment column on tables. With the latest release of ORACLE this feature was implemented – but what benefits do identity columns have over the good old trigger/sequence solution?
A identity column is always generated to be NOT NULL.
if generated always is used as the option you are not allowed to pass a value for the identity column.
Is an identity column the primary key per default?
Using an identity (surrogate key) column as the primary key of a table makes perfect sense but ORACLE has not implemented an automatism for that, which is perfectly ok. If we want to use the identity column as primary key we have to do it on our own.
All identity column statement create a sequence with the name ISEQ$$_[ObjectNumber]. Where ObjectNumber is the number of the object the sequence serves for.
Dropping the table having an identity column will not affect the sequence as long as the recycle bin is not purged or the table is not dropped using a drop table … purge command. As soon as the recycle bin is purged the sequence is dropped too. The advantage of this behaviour is, that if a table is undropped using a flashback command the sequence does not need to be recreated as it is still around (and has the correct value).
While renaming the sequence was possible in the first releases of ORACLE 12c this does not longer work – which is a good thing.
Now, the declaration effort to be taken is smaller. We do not longer have to write trigger code to populate the surrogate key.
A default value may not become invalid – this makes the default value assignment more stable than triggers.
With the new default value ON NULL clause the default value will also be taken, when a NULL value is passed.
Sequence is dropped when the table it is used for is dropped – so no zombie-sequences will exist in the future.
We do not have control over the name of the sequence.
I have seen that many surrogate key columns have been sourced using a single sequence. This will not be possible using the “IDENTITY COLUMN” feature.
Will I personally make use of the new “identity column” feature?
Even though I am not really a friend of having objects with artificial names in the application schema I do not see a reason why I should create the sequence and the default assignment by my own.
Up to ORACLE 11g DEFAULT values on columns had no effect when a NULL value was passed for the column. Furthermore it was not possible to use a sequence nextval as the default value for a column.
This has changed with ORACLE 12c. A default declaration may now have an instruction towards NULL handling.
It is also possible to assign a sequence to a default value.This is also done when defining a column as identity column. The advantage of creating your own sequence and using it in a default assignment is the possibility to choose a decent name for the sequence and to be able to use one sequence to populate the surrogate of many tables (if this is something you want to do).
Performance comparison using a sequence where the value is assigned to the column using DEFAULT keyword and the “old-fashioned” trigger-solution.
In my opinion the changes to the DEFAULT behaviour is a big progress and I am pretty sure, that this will influence the usage of this feature.