Key preserved means that 1 key value goes to 1 table. Giving counter examples may help you understand this concept better.
Example1:
Your view contains aggregation. Suppose you have following view structure.
GroupID, AverageSalary
1 , 10000
2, 12000
3, 14000
In this example: your values comes from more than one rows. If you try to update AverageSalary in this view, database has no way to find WHICH rows to update.
Example2:
Your view shows values from more than one table. Your view shows values from PERSON and PERSON_CONTACT_DETAILS(ID,PersonID,ContactType,ContactValue) table.
Example rows :
1,1,email,ddd@example.com
1,1,phone,898-98-99
You join this 2 table and show more business friendly information in view.
PersonId,Name,LastName, Phone1,Email1
Here you would like to update Phone1 and Email1. But your personID maps to two different rows, may be more rows, in this example.
In this view, again, database has no way to find WHICH rows to update.
Note: If you restrict your view sql and makes it clear to find which rows to update it may work.
This two example is first examples which comes to my mind. They can be increased. But concept is clear. Database needs to map 1 key value to 1 table. For example you have one to one PERSON, PERSON_DETAILS tables. Here view and update will work since it is one to one.
Following is a short graphic which I will be using to explain what incarnation is used for in the answer:
restore db +-----+ +-----+ +-----+
recover db | 2>3 | --------> | 3 | --> | 3 | -->
resetlogs +-----+ +-----+ +-----+
^ Incarn. 3 3
/ SCN # 400 500
/
/
restore db +-----+ +-----+ +-----+
recover db | 1>2 | -------> | 2 | --> | 2 | --> ...
resetlogs +-----+ +-----+ +-----+ ^
^ Incarn. 2 2 | 2
/ SCN # 300 400 | 500
/ |
/ |
+-----+ +-----+ +-----+ |
--> | 1 | --> | 1 | --> | 1 | --> ... |
+-----+ +-----+ +-----+ ^ |
Incarn. 1 1 1 | 1 |
SCN # 100 200 300 | 400 |
| |
Backup 11:00 ----- 12:00 ----- 13:00 ----- 14:00 ----- 15:00 ----- 16:00 ----- 17:00
| |
Restore/ (1) (2)
Recovery
Basics
When an Oracle database is created it receives an initial incarnation number. The incarnation number is stored in the database itself, the control file(s) of the database instance and in the central RMAN catalog database (if you have one).
New Incarnations
Let's take a look at the above graphic. It is a slight modification of the graphic which Oracle uses to explain what incarnations are.
Reference: RMAN Media Recovery (Oracle Docs)
Archive Log Backups
Now let's assume the Archive Log backups are occuring on an hourly basis. Oh, and the datbase is in the ARCHIVE_LOGS
mode, which allows us to backup and restore archive logs which enables us to recover the database to a certain point in time. We'll also assume that the database has received a FULL backup at 04:00 (a.m.) the same day. Further, the database has nevery been restored since it was initiailly created and the current INCARNATION# of the database is at 1
.
SCN
We'll further assume that 100 transcations occur each hour so that at the time of the backup the SCN# will be a multiple of 100.
Time
The time is only here to increase the readability of the graphic. It plays a role in understanding why Oracle creates a new incarnation for a database that has been restored to a certain point-in-time.
Restoring the Database to Point-in-Time (1)
Somewhere slightly after 13:00 (1pm) somebody decides that the database has to be restored to 12:00 (12 o'clock midday). The DBA either sets of a bunch of RMAN commands to restore the database to that point-in-time our clicks his way through a fantastic GUI to initiate a restore/recovery from a 3rd-party vendor.
RMAN retrieves the FULL backup of the database and all Archive Log backups from disk/tape and restores them to the disk. In the recovery phase RMAN will check that all relevant information is available and roll-forward all finished transactions to the Point-in-Time and roll-back all unfinished transactions to the Point-in-Time, to ensure the database is in a consistent state.
Before the database can be opened to the general public, the database has to ensure that all future backups don't conflict with the previous backups. This is when a new incarnation should be created and it happens when you execute the following command to open the database:
ALTER DATABASE OPEN RESETLOGS;
Why?
Ok. If you look at the graphic, the SCN# for 13:00 (1 pm) in the original INCARNATION# 1 path is 300. We never reached the backup at 14:00 (2pm) which would have recorded the SCN# 400 in the backup. During the restore/recovery process, no backup is going to happen and (let's assume) if the restore process takes an hour, then no new data is going to be inserted. When the database is ready to go back online and the next backup occurs at 14:00 (2pm) then the backup will contain the SCN# 300. This is the same SCN# of the database as it was before the restore/recovery was initiated, which was recorded for the backup at 13:00 (1pm).
How Does Oracle Know Which Backup to Use?
This is why the INCARNATION# is set to a new value. It allows Oracle to discern between the Archive Log backup at 13:00 (1pm) with the following information:
INCARNATION# 1
SCN# 300
Time......... 13:00
...and the backup at 14:00 (2pm) with the following information:
INCARNATION# 2
SCN# 300
Time......... 14:00
Restoring the Database to Point-in-Time (2)
Let's assume the database keeps on running after the first restore/recovery action and slightly after 15:00 (3pm) somebody decides there needs to be a new restore/recovery back to 14:00 (2pm) the same day.
RMAN will restore the files, recovery the database and set off an ALTER DATABASE OPEN RESETLOGS
to bring the database back online. The INCARNATION# will be now set to 3 and the first backup at 16:00 will contain the information:
INCARNATION# 3
SCN# 400
Time......... 16:00
...which would otherwise conflict with the backup of the database at INCARNATION# 2 and SCN#400 from 15:00 (3pm). But, because of the new INCARNATION# everything is fine.
Summary
That is the (albeit short) explanation of what the incarnations are used for. It allows RMAN (and the heroic DBA) to determine which path to take to restore and recover the database that has previously been backed up and restored more than once.
Next Up
This short explanation of incarnations is by no means complete. There are other factors that result in ORPHANED
incarnations and OBSOLETE
backups. Some of these topics will be discussed in a follow up answer either in this thread or in a separate Q & A.
For Example...
When looking at incarnations on a larger time-scale, then restoring and recovering may require you to set off a RESET DATABASE TO INCARNATION <number>
before you can start recovering the database, because you might be restoring and recovering the database to a point-in-time which is located on the path of the INCARNATION# 1 and which may require you to restore an AUTOBACKUP copy of the control file, before you can start even thinking about restoring and recovering the database.
Stay tuned...
Best Answer
The concept of cardinality does not change whether it is used in reference to the Oracle database or some other SQL database. Cardinality is the size of a set of some elements. From the mathematics point of view a set contains only distinct elements. When you reference values of a certain column as a set its cardinality is the number of unique values. When you reference rows returned by an operation they all comprise the result set (each has a unique row ID, to put it simply) and therefore the result set cardinality is the number of rows returned.