RANK vs ROW_NUMBER Function

create table student_subject (
id number(4),
student_id varchar2(20),
subject_name varchar2(20),
part varchar2(20)
);

insert into student_subject values (1,'std1','Bengali','part1');
insert into student_subject values (2,'std1','English','part1');
insert into student_subject values (3,'std1','English','part2');

insert into student_subject values (4,'std2','Bengali','part1');

insert into student_subject values (5,'std3','Bengali','part1');
insert into student_subject values (6,'std3','English','part1');
insert into student_subject values (7,'std3','English','part2');
insert into student_subject values (8,'std3','English','part3');

insert into student_subject values (9,'std4','Bengali','part1');
insert into student_subject values (10,'std4','English','part1');
insert into student_subject values (11,'std4','Math','part1');

insert into student_subject values (12,'std5','Bengali','part1');
insert into student_subject values (13,'std5','Math','part1');

ID	STUDENT_ID	SUBJECT_NAME	PART
1	std1	Bengali	part1
2	std1	English	part1
3	std1	English	part2
4	std2	Bengali	part1
5	std3	Bengali	part1
6	std3	English	part1
7	std3	English	part2
8	std3	English	part3
9	std4	Bengali	part1
10	std4	English	part1
11	std4	Math	part1
12	std5	Bengali	part1
13	std5	Math	part1

In my report I have to pull all students with the subject "English", if available. If no "English", then pulling "Bengali".
Here "Bengali" is there mandatory subject and every student has "Bengali".
If multiple "English" available for each student then all rows should come.

SELECT student_id,subject_name, part
FROM (
    SELECT DISTINCT ss.student_id
    , ss.subject_name
    , part
    , RANK() OVER (PARTITION BY ss.student_id ORDER BY decode(ss.subject_name, 'English', 1, 'Bengali', 2, 3)) rn
    FROM student_subject ss
    WHERE ss.subject_name in ('Bengali','English')
) WHERE rn=1
ORDER BY student_id,part
;

Output
std1 English part1
std1 English part2
std2 Bengali part1
std3 English part1
std3 English part2
std3 English part3
std4 English part1
std5 Bengali part1


In my report I have to pull all students with the subject "English", if available. If no "English", then pulling "Bengali".
Here "Bengali" is there mandatory subject and every student has "Bengali".
If multiple "English" available for each student then one row should come.

SELECT student_id,subject_name,part
FROM (
    SELECT DISTINCT ss.student_id
    , ss.subject_name
    , part
    , ROW_NUMBER() OVER (PARTITION BY ss.student_id ORDER BY decode(ss.subject_name, 'English', 1, 'Bengali', 2, 3)) rn
    FROM student_subject ss
    WHERE ss.subject_name in ('Bengali','English')
) WHERE rn=1
ORDER BY student_id
;

Output
std1 English part1
std2 Bengali part1
std3 English part1
std4 English part1
std5 Bengali part1

Frequently Asked Questions in Oracle

Basic

Architecture - Oracle Database Architecture

INDEX

JOIN

ROWID Vs ROWNUM




From My Experiences

How to Enable Archive Log ?

How to make a Read Only Database User ?

How to Back Deleted Datafiles ?

How to recover a dropped table ?

Moving table(s) to a different tablespace ?

How to partition a Big table ?

 

Frequently Asked Questions about Joining

Q: How the Optimizer Executes Join Statements ?

Q: What do you know about Inner Join ? 

Q: What do you know about Outer Join ?

Q: What is the difference between the "JOIN" and "INNER JOIN" ?

Q: What is the difference between the "Left Join" and "Left outer Join" ?

Q: What is the difference between the right and left outer joins ?

JOIN

The join clause combines the output from two row sources, such as tables or views, and returns one row source. The returned row source is the Data Set.

Whenever multiple tables exist in the FROM clause, Oracle Database performs a join.

The optimizer must make the decision depending on the following interrelated terms in oracle:

1. Join Type           
    - Inner (EquiJoin, Non-EquiJoin),  
    - Outer (Left, Right, Full),
    - Semi ,
    - Anti
    - Self Join   
    - Cross Join

2. Join Condition  - ON
3. Join Methods    - Nested loop, Sort merge, Hash joins
4. Join Order         - Table order   
5. Access path      - Full table scan, Index scan


More

 

Join Methods

To join each pair of row sources, Oracle Database must decide how to do it. The "how" is the join method.
 

The possible join methods are - 

•  Nested Loop Join
•  Hash Join
•  Sort Merge Join

A Cartesian join requires one of the preceding join methods.
Each join method has specific situations in which it is more suitable than the others.

NESTED LOOP

Suppose somebody gave you a telephone book and a list of 20 names to look up, and then asked you to write down each person's name and corresponding telephone number. You would probably go down the list of names, looking up each one in the telephone book one at a time. This task would be pretty easy because the telephone book is alphabetized by name. Moreover, somebody looking over your shoulder could start calling to the first few numbers you write down, while you are still looking up the rest. This scene describes a NESTED LOOPS join.

Q. What are the Nested Loop processing Steps ?

Step-1:
Oracle reads the first row from the first row source,

Step-2:
and then checks the second row source for matches.

Step-3:
All matches are then placed in the result set and,

Step-4:
Oracle goes on to the next row from the first row source.

Step-n:
This continues until all rows in the first row source have been processed.
The first row source is often called the outer or driving table, whereas the second row source is called the inner table.

Using a NESTED LOOPS join is one of the fastest methods of receiving the first records back from a join.

Q: When Oracle Nested Loop Joins Are Ideal ?

NESTED LOOPS joins are ideal when the driving row source (the records you are looking for) is small and the joined columns of the inner row source are uniquely indexed.

NESTED LOOPS joins have an advantage over other join methods in that they can quickly retrieve the first few rows of the result set without having to wait for the entire result set to be determined. This situation is ideal for query screens where an end user can read the first few records retrieved while the rest are being fetched.

However, NESTED LOOPS joins can be very inefficient if the inner row source (second table accessed) does not have an index on the joined columns or if the index is not highly selective. If the driving row source (the records retrieved from the driving table) is quite large, other join methods may be more efficient.

Video link for Nested loop and Nested loop with Index -
https://www.youtube.com/watch?v=o1dMJ6-CKzU

Reference - http://logicalread.solarwinds.com/oracle-11g-when-nested-loop-joins-are-ideal-mc02/#.VQ5kx0l5OPI 

HASH JOIN

A method for joining large data sets.
 

The database uses the smaller of two data sets to build a hash table on the join key in memory. It then scans the larger data set, probing the hash table to find the joined rows.
In cases where a very small table is being joined to a large table, the oracle HASH JOIN will often dramatically speed-up the query.

Q. When optimizer considers a hash join ?

In general, the optimizer considers a hash join when the following conditions are true:

- A relatively large amount of data must be joined, or a large fraction of a small table must be joined.
- The join is an equijoin, that mean, you cannot use hash joins unless there is an equality condition.

This method is best when the smaller table fits in available memory.

Q. What are the Hash Join processing Steps ?

Step-1:
Oracle select the smaller of two data sets.

Step-2:
The database performs a full scan of the selected smaller data set, and then applies a Hash Function to the join key in each row to build a Hash Table in the PGA (memory).
In this step, oracle perform a nested loop to build the hash table, from the smaller table.
 
For example -

Step-3:
The hast table then scans the larger data set, to find the joined rows.

---
The HASH join is similar to a NESTED LOOPS join in the sense that there is a nested loop that occurs when Oracle first builds a hash table to facilitate the operation and then loops through the hash table.

When using an ORDERED hint, the first table in the FROM clause is the table used to build the hash table.

HASH joins can be effective when the lack of a useful index renders NESTED LOOPS joins inefficient. The HASH join might be faster than a SORT-MERGE join, in this case, because only one row source needs to be sorted, and it could possibly be faster than a NESTED LOOPS join because probing a hash table in memory can be faster than traversing a b-tree index.

As with SORT-MERGE joins and CLUSTER joins, HASH joins work only on equijoins. As with SORT-MERGE joins, HASH joins use memory resources and can drive up I/O in the temporary tablespace if the sort memory is not sufficient (which can cause this join method to be extremely slow).

Finally, HASH joins are available only when cost-based optimization is used (which should be 100 percent of the time for your application running on Oracle 11g).

Because the hash table is in the PGA, Oracle Database can access rows without latching them. This technique reduces logical I/O by avoiding the necessity of repeatedly latching and reading blocks in the database buffer cache.

If the data sets do not fit in memory, then the database partitions the row sources, and the join proceeds partition by partition. This can use a lot of sort area memory, and I/O to the temporary tablespace. This method can still be the most cost effective, especially when parallel query servers are used.

Q. What is a hash table ?

An in-memory data structure that associates join keys with rows in a hash join.
For example, in a join of the employees and departments tables, the join key might be the department ID. A hash function uses the join key to generate a hash value. This hash value is an index in an array, which is the hash table.

Q. What is in-memory scan ?

A table scan that retrieves rows from the In-Memory Column Store.

Q. How to size your PGA for Hash Join ?

The rule is quite different depending on your system. You need to focus on HASH_AREA_SIZE or PGA_AGGREGATE_TARGET parameter.

Oracle uses a HASH join efficiently only if the parameter PGA_AGGREGATE_TARGET is set to a large enough value. If MEMORY_TARGET is used, the PGA_AGGREGATE_TARGET is included in the MEMORY_TARGET, but you may still want to set a minimum.

If you set the SGA_TARGET, you must set the PGA_AGGREGATE_TARGET as the SGA_TARGET does not include the PGA (unless you use MEMORY_TARGET as just described).

And, Oracle places limits of the amount of RAM available for HASH JOIN so that no single session will "Hog" all the RAM.

Unfortunately, the oracle Hash Join is more memory intensive than Nested Loop join Join.
For large tables, hash joins requires lots of RAM so we must set the HASH_AREA_SIZE large enough to hold the entire hash table in memory.

In a production database with very large tables, it is not always easy to get your database to invoke hash joins without increasing the RAM regions that control hash joins. 
For large tables, hash joins requires lots of RAM.

Q. How to use Hash Join Hints ?

Apply the USE_HASH hint to instruct the optimizer to use a hash join when joining two tables together.

More -
http://logicalread.solarwinds.com/oracle-11g-hash-joins-mc02/#.VQ6D1Ul5OPI

Still I have a big confusion about Hash Join!! 

The most fundamental difference between hash and nested loop joins -
http://blog.tanelpoder.com/2010/10/06/a-the-most-fundamental-difference-between-hash-and-nested-loop-joins/

SORT MARGE JOIN

Sort merge joins can join rows from two independent sources.

Hash joins generally perform better than sort merge joins. However, sort merge 
joins can perform better than hash joins if both of the following conditions exist:
- The row sources are sorted already.
- A sort operation does not have to be done.

However, if a sort merge join involves choosing a slower access method (an index scan as opposed to a full table scan), then the benefit of using a sort merge might be lost.

Sort merge joins are useful when the join condition between two tables is an inequality condition such as <, <=, >, or >=.
 
Sort merge joins perform better than nested loop joins for large data sets.
You cannot use hash joins unless there is an equality condition.

Q. What are the Sort Marge Join processing Steps ?

In a merge join, there is no concept of a driving table. The join consists of two steps:
- Sort join operation: Both the inputs are sorted on the join key.
- Merge join operation: The sorted lists are merged together.

If the input is sorted by the join column, then a sort join operation is not performed for that row source.
 
However, a sort merge join always creates a positionable sort buffer for the right side of the join so that it can seek back to the last match in the case where duplicate join key values come out of the left side of the join.

Q. When the Optimizer Uses Sort Merge Joins ?

The optimizer can choose a sort merge join over a hash join for joining large amounts of data if any of the following conditions are true:
- The join condition between two tables is not an equijoin.
- Because of sorts required by other operations, the optimizer finds it is cheaper to use a sort merge than a hash join.


Q. How to use Sort Merge Join Hints ?

To instruct the optimizer to use a sort merge join, apply the USE_MERGE hint. You might also need to give hints to force an access path.

There are situations where it makes sense to override the optimizer with the USE_MERGE hint.
For example, the optimizer can choose a full scan on a table and avoid a sort operation in a query.
 
However, there is an increased cost because a large table is accessed through an index and single block reads, as opposed to faster access through a full table scan.

More -
http://docs.oracle.com/cd/E29505_01/server.1111/e16638/optimops.htm#autoId42
https://docs.oracle.com/database/121/TGSQL/tgsql_join.htm#TGSQL94690
 

Cartesian Joins

The database uses a Cartesian join when one or more of the tables does not have any join conditions to any other tables in the statement.
 

The optimizer joins every row from one data source with every row from the other data source, creating the Cartesian product of the two sets.

Q. When the Optimizer Uses Cartesian Joins ?

The optimizer uses Cartesian joins when it is asked to join two tables with no join conditions.
 
In some cases, a common filter condition between the two tables could be picked up by the optimizer as a possible join condition.
 
In other cases, the optimizer may decide to generate a Cartesian product of two very small tables that are both joined to the same large table.

Q. How to use Cartesian Join Hints ?

Applying the ORDERED hint, instructs the optimizer to use a Cartesian join.
By specifying a table before its join table is specified, the optimizer does a Cartesian join.

Reference - http://logicalread.solarwinds.com/oracle-11g-when-nested-loop-joins-are-ideal-mc02/#.VR0RcEAW2Kk

SemiJoin & AntiJoin

Semi-Join

A semijoin is a join between two data sets that returns a row from the first set when a matching row exists in the sub-query data set.

The database stops processing the second data set at the first match.

Thus, optimization does not duplicate rows from the first data set when multiple rows in the second data set satisfy the sub-query criteria.

A semi-join avoids returning a huge number of rows when a query only needs to determine whether a match exists.

With large data sets, this optimization can result in significant time savings over a nested loops join that must loop through every record returned by the inner query for every row in the outer query.

The optimizer can apply the semijoin optimization to nested loops joins, hash joins, and sort merge joins.

The optimizer may choose a semijoin in the following circumstances:

• The statement uses either an IN or EXISTS clause.

• The statement contains a subquery in the IN or EXISTS clause.

• The IN or EXISTS clause is not contained inside an OR branch.

Anti-Join

An antijoin is a join between two data sets that returns a row from the first set when a matching row does not exist in the subquery data set.

Like a semijoin, an antijoin stops processing the subquery data set when the first match is found.

Unlike a semijoin, the antijoin only returns a row when no match is found.

An antijoin avoids unnecessary processing when a query only needs to return a row when a match does not exist.

With large data sets, this optimization can result in significant time savings over a nested loops join that must loop through every record returned by the inner query for every row in the outer query.

The optimizer can apply the antijoin optimization to nested loops joins, hash joins, and sort merge joins.

The optimizer may choose an anti-join in the following circumstances:

• The statement uses either the NOT IN or NOT EXISTS clause.

• The statement has a sub-query in the NOT IN or NOT EXISTS clause.

• The NOT IN or NOT EXISTS clause is not contained inside an OR branch.

Outer Join

An outer join does not require each record in the two join tables to have a matching record.  

An outer join returns rows depending on the join condition (ON).

The resulting data set can keep each records from the joined tables even if no other matching record exists.

In the FROM clause, the left table appears to the left of the OUTER JOIN keywords, and the right table appears to the right of these keywords.

The left table is also called the outer table, and the right table is also called the inner table.

For example

In the following statement the employees table is the LEFT or OUTER table:

SELECT employee_id, last_name, first_name

FROM   employee LEFT OUTER JOIN department

ON     (employee.department_id=department.department_id);

Outer joins require the outer joined table to be the driving table. In the preceding example, employees is the DRIVING table, and departments is the DRIVEN-TO table.

The driving table keeps each record even if no other matching record exists.

Types of Outer Join

There are different join conditions or types in Outer join -

1. LEFT outer join

2. RIGHT outer join

3. FULL outer join

For Example, we have two tables Employee and Location.

Table -  Employee

Employee_id	First_Name
13	Jason
8	Alex
3	Ram
17	Babu
25	Johnson

Table -  Location

Employee_id	Location
13	San Jose
8	Los Angeles
3	Pune, India
17	Chennai, India
39	Bangalore

1. LEFT outer join

SELECT employee.employee_id,location.location 

FROM employee 

LEFT OUTER JOIN location 

ON employee.employee_id = location.employee_id;

Result Set -

Employee_id	Location
13	San Jose
8	Los Angeles
3	Pune, India
17	Chennai, India
25

So, a left outer join retains all of the rows of the LEFT/DRIVING table, regardless of whether there is a row that matches on the RIGHT/DRIVEN-TO table.
 


2. RIGHT outer join

SELECT employee.employee_id,location.location 

FROM employee 

RIGHT OUTER JOIN location 

ON employee.employee_id = location.employee_id;

Result Set - 

Employee_id	Location
13	San Jose
8	Los Angeles
3	Pune, India
17	Chennai, India
	Bangalore

So, a right outer join retains all of the rows of the RIGHT/DRIVING table, regardless of whether there is a row that matches on the LEFT/DRIVEN-TO table. 

 
3. FULL outer join

SELECT employee.employee_id,location.location 

FROM employee 

FULL OUTER JOIN location 

ON employee.employee_id = location.employee_id;

Result Set -

Employee_id	Location
13	San Jose
8	Los Angeles
3	Pune, India
17	Chennai, India
25
	Bangalore

Q: What is the difference between the right and left outer joins?

The difference is simple – in a LEFT OUTER JOIN, all of the rows from the LEFT table will be displayed, regardless of whether there are any matching columns in the RIGHT table. In a right outer join, all of the rows from the RIGHT table will be displayed, regardless of whether there are any matching columns in the LEFT table.

Q: What is the difference between the "Left Join" and "Left outer Join"?

No difference actually!

Suppose we have a table A and a table B. 
So. here a list of equivalent Join syntax:

A LEFT JOIN B    =   A LEFT OUTER JOIN B  
A RIGHT JOIN B  =   A RIGHT OUTER JOIN B
A FULL JOIN B    =   A FULL OUTER JOIN B  
A JOIN B              =   A INNER JOIN B            

Inner Join

Q. What is Inner Join? What are the different types of Inner Join?

Inner join is the default JOIN type. Sometimes it is called Simple Join.

For example -

An inner join will perform when we write like -

SELECT e.employee_id, e.last_name, d.department_name
FROM   employees e, departments d;

Look, here we even didn't write the "JOIN" word.

Here, the inner Join query compares each row of table employees with each row of table departments.

"JOIN" / "INNER JOIN" - is the same thing.

There are Two types Inner join -

•  Equijoins

•  Nonequijoins

EquiJoin

An equijoin is an inner join whose join condition contains an equality operator.

The following example is an equijoin because the join condition contains equality operator (=).

SELECT e.employee_id, e.last_name, d.department_name
FROM   employees e, departments d
WHERE  e.department_id = d.department_id;

Here, the join condition is e.department_id=d.department_id. If a row in the employees table has a department ID that matches the value in a row in the departments table, then the database returns the joined result; otherwise, the database does not return a result.

Non-equiJoin

A nonequijoin is an inner join whose join condition contains an operator that is not an equality operator. The following query lists all employees whose hire date occurred when employee 176 (who is listed in job_history because he changed jobs in 2007) was working at the company:

SELECT e.employee_id, e.first_name, e.last_name, e.hire_date
FROM   employees e, job_history h
WHERE  h.employee_id = 176
AND    e.hire_date BETWEEN h.start_date AND h.end_date;

In the preceding example, the condition joining employees and job_history does not contain an equality operator, so it is a nonequijoin. Nonequijoins are relatively rare.

A CARTESIAN PRODUCT will result if there are no matching join columns specify, that mean, no Join Condition exists. In this scenario, a row will be returned for every combination of the two tables. If one table has 1000 rows and the second table has 3000 rows, the result set will return 3,000,000 (3 million) rows!

SELECT * FROM employees, departments;

 
Q: What is the difference between the "JOIN" and "INNER JOIN"?

No difference actually!

Suppose we have a table A and a table B. 
So. here a list of equivalent Join syntax:

A JOIN B           =   A INNER JOIN B       

A LEFT JOIN B    =   A LEFT OUTER JOIN B  
A RIGHT JOIN B  =   A RIGHT OUTER JOIN B
A FULL JOIN B    =   A FULL OUTER JOIN B  

 

JOIN

Q: How the Optimizer Executes Join Statements ?

The join clause combines the output from two row sources, such as tables or views, and returns one row source. The returned row source is the Data Set.

Whenever multiple tables exist in the FROM clause, Oracle Database performs a join.

When multiple tables exist in the FROM clause, the optimizer must determine which join operation is most efficient for each pair. 


The optimizer must make the decision depending on the following interrelated terms in oracle:

1. Join Type        
2. Join Condition
3. Join Methods
4. Join Order

5. Access Path



1. Join Types

There are different Join types in oracle - 

• Inner join  - Equijoin, Nonequijoin
• Outer join  - Left, Right, Full 
• Semi join - IN, EXISTS 
• Anti join   - NOT IN, NOT EXISTS

2. Join Conditions

Usually, when you write a join query, you specify a condition that conveys a relationship between the tables specified in the FROM clause. This condition is referred to as the join condition.
 

The join condition specifies how the rows from one table will be combined with the rows of another table.
 

This join condition is usually applied to the foreign key columns of one table and the primary or unique key columns of another table.

Example - 1:

SELECT d.dept_id, d.name, l.regional_group
FROM department d
JOIN location l
ON (d.location_id = l.location_id);

Here, the ON clause specifies the join condition by which the location_id column of the department table is equated with the location_id column of the location table.
To perform this Inner Equijoin, Oracle picks up one combination of rows from the two tables, and checks to see whether the join condition is true. If the join condition is true, Oracle includes this combination of rows in the result set. This process is repeated for all combinations of rows from the two tables.

Example - 2:

SELECT employee_id, last_name, first_name
FROM   employees
LEFT OUTER JOIN departments
ON (employees.department_id=departments.departments_id);

Here, the ON clause specifies the join condition.
To perform this LEFT OUTER JOIN, oracle picks up all rows that satisfy the join condition and also returns some or all of those rows from left/driving table for which no rows from the other satisfy the join condition.

Note:
The columns specified in a join condition need not be specified in the SELECT list.



3. Join Order

To execute a statement that joins more than two tables, oracle database joins two tables and then joins the resulting row source to the next table. This process continues until all tables are joined into the result.
 

For example - 
The database joins two tables, and then joins the result to a third table, and then joins this result to a fourth table, and so on.


4. Join Methods

To join each pair of row sources, Oracle Database must decide how to do it. 
The "how" is the join method. 

The possible join methods are -  

•  Nested Loop
•  Sort Merge
•  Hash Join

A Cartesian join requires one of the preceding join methods. 

Each join method has specific situations in which it is more suitable than the others.



5. Access Paths

As for simple statements, the optimizer must choose an access path to retrieve data from each table in the join statement.

For example
The optimizer might choose between a full table scan or an index scan.

TDE

Transparent Data Encryption
 

If you have the datafiles, you can see the data though you have no access in the database. Therefore file level security is required to protect the data. 

 

TDE encrypts specific table columns or a tablespace that are stored in data files.

TDE can also encrypt indexes, materialized views etc.

TDE is a key-based access control system. Even if the encrypted data is retrieved, it cannot be understood until authorized decryption occurs, which is automatic for users authorized to access the table.

 
In 10g Release 2, Oracle first introduce TDE. Here transparent means database users or application need not to do any changes to achieve TDE.

To prevent unauthorized decryption, TDE stores the Master Encryption Keys in wallet.

Why using Wallet?

As shown in Figure, the master encryption key is stored in an external security module (Wallet) that is outside the database and accessible only to the security administrator. Using an Wallet, making it possible to divide duties between database administrators and security administrators. Security is enhanced because the wallet password can be unknown to the database administrator.

Types of TDE

There are Two types of TDE -
1. Column Encryption
2. Tablespace Encryption

Column Encryption

Use column encryption when you want to encrypt one or more specific columns in a table.

When a table contains encrypted columns, a single key is used regardless of the number of encrypted columns. This key is called the column encryption key. The column encryption keys for all tables, containing encrypted columns, are encrypted with the database server master encryption key and stored in a dictionary table in the database. No keys are stored in the clear.

This feature introduce in Oracle 10g R2.

Creating a table that has a column encrypted is as easy as using the ENCRYPT keyword:

CREATE TABLE scott.emp( 
empno NUMBER(9), 
ssn NUMBER(9) ENCRYPT USING 'AES256'
);

Restrictions

Do not use TDE column encryption with the following database features:

1. Index types other than B-tree
2. Range scan search through an index
3. Transportable Tablespaces
4. Materialized View Logs
5. Synchronous Change Data Capture

6. Encrypted columns can't be use as foreign key constraints.

7. BFILE
8. Original import/export utilities doesn't support. Use Oracle Data Pump.

 

Tablespace Encryption

Data encryption-decryption for all objects that stored into the encrypted tablespace. It encrypts/decrypts data during read/write operations that mean, Database itself will encrypt data while writing into datafiles and decrypt then while reading.

Oracle Database version must be 11g release 1 (11.1) or higher.

Restrictions

1. External Large Objects (BFILEs) can not be encrypted using TDE tablespace encryption. This is because these files reside outside the database.

2. Original import/export utilities doesn't support. Use Oracle Data Pump.



Considerable things for TDE Implementation

1. Need New Tablespace

Existing Tablespace can not be converted to encrypted tablespace. You need to use table reorganize/redefinition or datapump Export-Import for Migration.

2. Performance Issue

4-8% performance impact in end-user response time and an increase of 1-5% in CPU usage. Though it ensure below 10% performance impact.

3. Storage overheads  

Increase disk consumption. Encrypting a single column would require between 32 and 48 bytes of additional storage for each row, on average. Though TDE tablespace encryption has no storage overheads.

4. Replication issue

Require Goldengate 11.1.1.1 for replication

5. Backup Time

Increase RMAN(backup/restore) & DataPump(export/import) duration.

6. Wallet Issue

Need Separate backup task for Wallet that contains encryption decryption master key.

7. Licencing

Need Oracle Advanced Security Licence (11,500 USD/Core).

8. Using PKI

A PKI key pair (PKI certificate) as a master encryption key may result in greater performance degradation when accessing encrypted columns in the database.

9. Database version

If you wish to test the Enhanced Tablespace Encryption features, Oracle 11.2 is required.

TDE integrates directly with frequently used Oracle Database tools and technologies including Oracle Advanced Compression, Automatic Storage Management (ASM), Recovery Manager (RMAN), Data Pump, GoldenGate and more.