What is View and Materialized View ? What is the difference between them ?

Definition of View:-

View is a virtual table, created using Create View command. This virtual table contains the data retrieved from a query expression, in Create View command. View can be created from one or more than one base tables or views. A view can be queried like you query the original base tables.

It is not that the View is pre-computed and stored on the disk instead, a View is computed each time it is used or accessed. Whenever a view is used the query expression in Create View command is executed at that particular moment. Hence, you always get the updated data in a View.

If you update any content in View, it is reflected in the original table, and if any changes had been done to the original base table, it would reflect in its View. But this makes the performance of a View slower. For example, a view is created from the join of two or more tables. In that case, you have to pay time to resolve Joins each time a View is used.

But it has some advantages like it do not require storage space. You can create a customized view of a complex database. You can restrict the user from accessing sensitive information in a database. Reduces the complexity of queries by getting data from several tables into a single customized View.

Now Let us see the syntax of View

Create View V As {Query Expression}

Remember all View are not updateable. Like a View created using Distinct clause, Group By clause, CHECK constraint (if check constraints violate), Read-only option can’t be updated.

Creating Views

create view emp_det as
select e.empno, e.ename,e.sal,e.deptno,d.dname,d.loc
from emp e, dept d where e.deptno=d.deptno;

Now to see the employee details and department names we don’t have to give a join query, we can just type the following simple query.

select * from emp_det;


Creating FORCE VIEWS

A view can be created even if the defining query of the view cannot be executed, as long as the CREATE VIEW command has no syntax errors. We call such a view a view with errors. For example, if a view refers to a non-existent table or an invalid column of an existing table, or if the owner of the view does not have the required privileges, then the view can still be created and entered into the data dictionary.

You can only create a view with errors by using the FORCE option of the CREATE VIEW command:

CREATE FORCE VIEW AS ...;

Replacing/Altering Views

To alter the definition of a view, you must replace the view using one of the following methods:
A view can be dropped and then re-created. When a view is dropped, all grants of corresponding view privileges are revoked from roles and users. After the view is re-created, necessary privileges must be regranted.


A view can be replaced by redefining it with a CREATE VIEW statement that contains the OR REPLACE option. This option replaces the current definition of a view, but preserves the present security authorizations.

CREATE OR REPLACE VIEW Accounts_staff AS
SELECT Empno, Ename, Deptno
FROM Emp
WHERE Deptno = 30
WITH CHECK OPTION CONSTRAINT ica_Accounts_cnst;

more details are here : -

https://www.oracle-dba-online.com/sql/create_and_manage_views.htm

Definition of Materialized View:- 

Materialized View is the Physical copy of the original base tables. The Materialized View is like a snapshot or picture of the original base tables. Like View, it also contains the data retrieved from the query expression of Create Materialized View command.

But unlike View, the Materialized View are precomputed and stored on a disk like an object, and they are not updated each time they are used. Instead, the materialized view has to be updated manually or with the help of triggers. The process of updating the Materialized View is called Materialized View Maintenance.

Materialized View responds faster in comparison to View. It is because the materialized view is precomputed and hence, it does not waste time in resolving the query or joins in the query that creates the Materialized View. Which in turn responses faster to the query made on materialized view.

Where Build clause decides, when to populate the Materialized View. Refresh type decides how to update the Materialized View and trigger decides when to update the materialized View.

Materialized Views are generally used in the data warehouse.

Let us check the syntax of Materialized View:

Create materialized view View_Name Build [Immediate/Deffered] Refresh [Fast/Complete/Force] on [Commit/Demand] as Select ..........;

Using above syntax you can create materialized views.The Syntax includes some different optional fields:

1.Build Immediate: Means materialized views(mv) created immediately.

2.Build Deffered: Means materialized views(mv) created after one refresh.

3.Refresh on commit:This option committed the data in materialized views in SQL immediately after data inserted and committed in table.This option is known as incremental refresh option.View is not fully refreshed with this option

4.Refresh on Demand:Using this option you can add the condition for refreshing data in materialized views.

You can refresh the data using fast (incremental approach),Complete,Force options.

Example:

CREATE MATERIALIZED VIEW MV_Employee BUILD immediate
REFRESH complete
on commit SELECT * FROM Employee;

Comparison Chart :- 

BASIS FOR COMPARISON	VIEW	MATERIALIZED VIEW
Basic	A View is never stored it is only displayed.	A Materialized View is stored on the disk.
Define	View is the virtual table formed from one or more base tables or views.	Materialized view is a physical copy of the base table.
Update	View is updated each time the virtual table (View) is used.	Materialized View has to be updated manually or using triggers.
Speed	Slow processing.	Fast processing.
Memory usage	View do not require memory space.	Materialized View utilizes memory space.
Syntax	Create View V As	Create Materialized View V Build [clause] Refresh [clause] On [Trigger] As

Key Differences Between View and Materialized View

1) The basic difference between View and Materialized View is that Views are not stored physically on the disk. On the other hands, Materialized Views are stored on the disc.

2) View can be defined as a virtual table created as a result of the query expression. However, Materialized View is a physical copy, picture or snapshot of the base table.

3) A view is always updated as the query creating View executes each time the View is used. On the other hands, Materialized View is updated manually or by applying triggers to it.

4) Materialized View responds faster than View as the Materialized View is precomputed.

5) Materialized View utilizes the memory space as it stored on the disk whereas, the View is just a display hence it do not require memory space.

Conclusion:

Materialized View responds faster as compared to View. But View always provides up to date information to the user.

1)   
20 things to know about views:-

 

1.  
A view is like a virtual table. It takes the output of a query and
treats it like a table.

 

2.  
A view can be based on one or more tables or other views. These
tables/views are called base tables.

 

3.  
A view takes up no storage space other than for the definition of
the view in the data dictionary.

 

4.  
A view contains no data. All the data it shows comes from the base
tables.

 

5.  
A view can provide an additional level of table security by
restricting access to a set of rows or columns of a table.

 

6.  
A view hides implementation complexity. The user can select from
the view with a simple SQL, unaware that the view is based internally on a join
between multiple tables.

 

7.  
A view lets you change the data you can access, applying
operators, aggregation functions, filters etc. on the base table.

 

8.  
A view isolates application from changes in definitions of base
tables. Suppose a view uses two columns of a base table, it makes no difference
to the view if other columns are added, modified or removed from the base
table.

 

9.  
Using views encourages the use of shared SQL, which improves
efficiency of frequently invoked SQL.

 

10. An updatable view allows you to insert, update, and delete rows by
propagating the changes to the base table. A view can be updatable provided its
definition does not contain any of the following constructs: SET or DISTINCT
operators, an aggregate or analytic function, a GROUP BY, ORDER BY, CONNECT BY,
or START WITH clause, a subquery or collection expression in a SELECT list.

 

11.The data dictionary views ALL_UPDATABLE_COLUMNS, DBA_UPDATABLE_COLUMNS,and
USER_UPDATABLE_COLUMNS indicate which view columns are updatable.

 

12Views that are not updatable can be modified using an INSTEAD OF
trigger.

 

13.A view can be created even if the defining query of the view
cannot be executed, using the CREATE FORCE VIEW command. Such a view is called
a view with errors. This option can be useful for import/installation tools to
create a view before the underlying objects are present.

 

14.A view can be replaced with a CREATE OR REPLACE VIEW statement.
The REPLACE option updates the current view definition  but preserves the present security
authorizations.

 

15. A view lets you reorder columns easily with a CREATE OR REPLACE,
rather than going into a messy drop column for the base table with data.

 

16.To know about the views in your own schema, look up user_views.

 

17. The underlying SQL definition of the view can be read via select
text from user_views for the view.

 

18. Oracle does not enforce constraints on views. Instead, views are
subject to the constraints of their base tables.

 

19.Be careful when you define views of views. Don’t do it just
because it is easy to code – it may not be the optimal query. Check if you
would be better off using the base tables directly instead.

 

 

20.To create a view in your own schema, you must have the CREATE VIEW
system privilege. To create a view in another user’s schema, you must have the
CREATE ANY VIEW system privilege.

 

2)   
Difference between Table and View.

 

Table : An RDBMS object which can store data.

View : An RDBMS object which is a product of
multiple tables, overtime you run a “SELECT *” on a view a query run’s in
background to fetch you the desired results.

Table : You can add/update/delete data from a
table.

View : You cannot add/update/delete any data from
a view, to make any changes to the view, you will have to update the data in
the source tables that are used to create the view.

Table : You can only create or drop the table,
basically you cannot replace the table object directly as its an physical entry
in the RDBMS memory.

View : You can easily use replace option to
recreate the view, as its just a pseudo name to the query which is running
behind.

Table : DML operations can be performed.

View : DML operation is not allowed for the above-mentioned
reasons.

 

3)   
Is dml possible on views ?

 

An updatable view allows you to insert, update, and delete rows by
propagating the changes to the base table. A view can be updatable provided its
definition does not contain any of the following constructs: SET or DISTINCT
operators, an aggregate or analytic function, a GROUP BY, ORDER BY, CONNECT BY,
or START WITH clause, a subquery or collection expression in a SELECT list.

What is Database Index ? Types of Indexes in Oracle.

What is Index ? 

Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book. 

An index helps to speed up SELECT queries and WHERE clauses, but it slows down data input, with the UPDATE and the INSERT statements. Indexes can be created or dropped with no effect on the data. 

Let’s say you wanted to find information for a specific topic: Labrador dogs. You could do this in a few ways: 

• 
  Start at page 1 and look through each page until you found Labrador dogs 
  
  


• 
  Flip to a random page and see if you can find Labrador dogs, and repeat until you find it. 
  
  

This would be very time consuming and not efficient at all. 

Luckily, encyclopedias use a few different methods for making it easier to find information. The topics are often sorted alphabetically. 

There is often an index at the back of the book as well, which includes all of the topics in the book. If we look for Labrador dogs in the index, we could see: 

Labrador dogs… pg 100, 125, 217 

This tells us that Labrador dogs are mentioned on these three pages. We can simply go to those pages to find out more about Labrador dogs. 

This is a similar method to how indexes in databases work. They take up a bit more space, but they can help you find what you are looking for. 

Types of Indexes :- 

There are 6 different types of indexes in oracle. 

1) B-Tree 

2) Compressed B-Tree 

3) Bitmap 

4) Function-Based 

5) Reverse Key (RKI) 

6) Index organized table (IOT) 

1) B-Tree 

B-tree indexes are used to avoid large sorting operations. For example, a SQL query requiring 10,000 rows to be presented in sorted order will often use a b-tree index to avoid the very large sort required to deliver the data to the end user. 

>B-Tree Indexes (balanced tree) are the most common type of index. 

>B-Tree index stored the ROWID and the index key value in a tree structure. 

>When creating an index, a ROOT block is created, then BRANCH blocks are created and finally LEAF blocks. 

>Each branch holds the range of data its leaf blocks hold, and each root holds the range of data its branches hold: 

>B-Tree indexes are most useful on columns that appear in the where clause (SELECT … WHERE EMPNO=1). 

>The Oracle server, keeps the tree balanced by splitting index blocks, when new data is inserted to the table. 

>Whenever a DML statement is performed on the index’s table, index activity occurs, making the index to grow (add leaf and branches). 

CREATE {UNIQUE|NON UNIQUE} INDEX {index_name} 

ON {table_name} ({column_name},{column_name}…) 

TABLESPACE {tablespace_name}; 

Example 

Create index scott.exp_idx on table scott.example( name) 

Tablespace TOOLS; 

2) Compressed B-Tree 

Compressed B-Tree Indexes are built on large tables, in a data warehouse environment. In this type of index, duplicate occurrences of the same value are eliminated, thus reducing the amount of storage space, the index requires. In a compressed B-Tree index, for each key value, a list of ROWIDs are kept: 

Specifying the COMPRESS keyword when creating an index (CREATE INDEX … COMPRESS) will create a compressed B-Tree index. A regular B-Tree index can be rebuilt using the COMPRESS keyword to compress it. 

CREATE INDEX idxname ON tabname(col1, col2, col3) COMPRESS; 

3) Bitmap 

Another index type that is common in database systems is a bitmap index. 

This is represented in the system as a two-dimensional table, which is often called a “map” of values. 

For each row, there is a ROWID, which is a unique identifier for the row. For each column, there is a separate value of a specific column. 

At the intersection of rows and columns is a single bit. This bit indicates whether that ROWID has the value mentioned in the column. 

For example, we have a table that stores clothes and each row has a size. a bitmap index could look like this: 

ROWID	S	M	L
AA01	1
AA02		1
AA03	1
AA04			1

Why does this matter? Why is it useful? 

It’s a different way of accessing the data more efficiently. If there is a small range of values (such as S/M/L shown above), then it’s often better to use this kind of index. 

When you create an index, you don’t need to specify how each value maps to each row. This is all done automatically by the database system. 

To create a bitmap index (in Oracle, anyway), the syntax is: 

CREATE BITMAP INDEX idxname ON tabname(col1, col2, col3); 

The only difference between the syntax for this bitmap index and a b-tree index is the addition of the word BITMAP. 

4) Function-Based 

Function-Based Indexes are indexes created on columns that a function is usually applied on. 

When using a function on an indexed column, the index is ignored, therefore a function-based index is very useful for these operations. 

CREATE INDEX {index_name} ON  {table_name} [Function({column_name},{column_name.}]; 

Example 

CREATE INDEX EMP_IDX on EMP(UPPER(ENAME)); 

SELECT * FROM Emp WHERE UPPER(Ename) like ‘JOHN`; 

5) Reverse Key (RKI) 

You also may see these indexes or want to use them from time to time. Consider a column, which includes names like "restaurant A", "restaurant B", "restaurant C" and so on. Perhaps a not very glamorous example, but the point is a column with many unique values but not much variation at the front. Using a reverse-key index would be ideal here, because Oracle will simple REVERSE the string before throwing it into the b-tree. So, the result will be a more balanced, useful, and ultimately fast index. 

CREATE INDEX {index_name} ON {table_name} ({column_name}) 

TABLESPACE {tablespace_name} 

REVERSE; 

Example 

CREATE INDEX emp_idx i ON emp_table (firstname,lastname) REVERSE; 

6) Index organized table (IOT) 

The index-organized table is like an ordinary table with an index on one or more of its columns, but instead of maintaining two separate storages for the table and the B-tree index, the database system maintains only a single B-tree index which contains both the encoded key value and the associated column values for the corresponding row. Rather than having a row’s rowid as the second element of the index entry, the actual data row is stored in the B-tree index. The data rows are built on the primary key for the table, and each B-tree index entry contains . Index-organized tables are suitable for accessing data by the primary key or any key that is a valid prefix of the primary key. 

There is no duplication of key values because only non-key column values are stored with the key. You can build secondary indexes to provide efficient access by other columns. Applications manipulate the index-organized table just like an ordinary table, using SQL statements. However, the database system performs all operations by manipulating the corresponding B-tree index. 

Features of Index organized table 

· Primary key uniquely identifies a row; primary key must be specified 

· Primary key based access 

· Logical rowid in ROWID pseudo column allows building secondary indexes 

· UNIQUE constraint not allowed but triggers are allowed 

· Cannot be stored in a cluster 

· Can contain LOB columns but not LONG columns 

· Distribution and replication not supported 

There are 2 benefits of using IOT: 1. table rows are indexes, access to table is done using its primary key, the row is returned quickly from IOT than heap tables. 

When Should I Create an Index? 

Often, the first thing a developer will do when looking to improve a slow-running query is to create an index. I’ve been guilty of this in the past as well. 

However, once you understand how indexes work, then you’ll know when the right time to create an index is, and how it can help you. Understanding your data and table structure is also important. 

So, you should consider a few things when creating indexes: 

->Creating indexes on columns in WHERE clauses or JOINs is a good idea. 

->If a column has a large percentage of unique records, a b-tree index is usually more suitable. 

->If a column has a low percentage of unique records, a bitmap index is usually better. 

->Consider creating an index on foreign key columns (which are often used in joins anyway), as they are not created automatically (in Oracle anyway). 

->Consider creating a function-based index if the column in your WHERE clause uses a function or expression in the criteria. 

There are advantages and disadvantages to creating indexes. We’ve mentioned the advantages in this article. The disadvantages are: 

->Indexes take up more space on the database. This can be quite significant, depending on your table, and if you’re restricted by space, it’s something to consider. 

->Indexes can slow INSERT, UPDATE, and DELETE queries, because the index and the table need to be updated whenever these statements are run. 

So, overall, indexes can be helpful and are a great way to improve the performance of your queries. There are a few different types, which are suitable in different situations, and they are pretty easy to create. Understanding the pros and cons of each, and your data structure, is helpful when deciding when and if to create indexes. 

How to recreate the Indexes/rebuild index in oracle 

We can Use the ALTER INDEX … REBUILD statement to reorganize or compact an existing index or to change its storage characteristics 

The REBUILD statement uses the existing index as the basis for the new one. 

ALTER INDEX … REBUILD is usually faster than dropping and re-creating an index. 

It reads all the index blocks using multiblock I/O then discards the branch blocks. 

A further advantage of this approach is that the old index is still available for queries while the rebuild is in progress. 

Alter index {index name} rebuild ; 

Alter index {index name} rebuild tablespace ;

https://techgoeasy.com/oracle-indexes/

What are the Slowly Changing Dimensions and how to implement it in ODI 12c ?

What are Slowly Changing Dimensions?

Slowly Changing Dimensions (SCD) - dimensions that change slowly over time, rather than changing on regular schedule, time-base. In Data Warehouse there is a need to track changes in dimension attributes in order to report historical data. In other words, implementing one of the SCD types should enable users assigning proper dimension's attribute value for given date. Example of such dimensions could be: customer, geography, employee.


There are many approaches how to deal with SCD. The most popular are:


· Type 0 - The passive method

· Type 1 - Overwriting the old value

· Type 2 - Creating a new additional record

· Type 3 - Adding a new column

· Type 4 - Using historical table

· Type 6 - Combine approaches of types 1,2,3 (1+2+3=6)



Type 0 - The passive method. In this method no special action is performed upon dimensional changes. Some dimension data can remain the same as it was first time inserted, others may be overwritten.



Type 1 - Overwriting the old value. In this method no history of dimension changes is kept in the database. The old dimension value is simply overwritten be the new one. This type is easy to maintain and is often use for data which changes are caused by processing corrections(e.g. removal special characters, correcting spelling errors).


Before the change:

Customer_ID	Customer_Name	Customer_Type
1	Cust_1	Corporate

After the change:

Customer_ID	Customer_Name	Customer_Type
1	Cust_1	Retail

Advantages:

- This is the easiest way to handle the Slowly Changing Dimension problem, since there is no need to keep track of the old information.

Disadvantages:

- All history is lost. By applying this methodology, it is not possible to trace back in history. For example, in this case, the company would not be able to know that Christina lived in Illinois before.

Usage:

About 50% of the time.

When to use Type 1:

Type 1 slowly changing dimension should be used when it is not necessary for the data warehouse to keep track of historical changes.



Type 2 - Creating a new additional record. In this methodology all history of dimension changes is kept in the database. You capture attribute change by adding a new row with a new surrogate key to the dimension table. Both the prior and new rows contain as attributes the natural key(or other durable identifier). Also 'effective date' and 'current indicator' columns are used in this method. There could be only one record with current indicator set to 'Y'. For 'effective date' columns, i.e. start_date and end_date, the end_date for current record usually is set to value 9999-12-31. Introducing changes to the dimensional model in type 2 could be very expensive database operation so it is not recommended to use it in dimensions where a new attribute could be added in the future.

Before the change:

Customer_ID	Customer_Name	Customer_Type	Start_Date	End_Date	Current_Flag
1	Cust_1	Corporate	22-07-2010	31-12-9999	Y

After the change:

Customer_ID	Customer_Name	Customer_Type	Start_Date	End_Date	Current_Flag
1	Cust_1	Corporate	22-07-2010	17-05-2012	N
2	Cust_1	Retail	18-05-2012	31-12-9999	Y

Advantages:

- This allows us to accurately keep all historical information.

Disadvantages:

- This will cause the size of the table to grow fast. In cases where the number of rows for the table is very high to start with, storage and performance can become a concern.

- This necessarily complicates the ETL process.

Usage:

About 50% of the time.

When to use Type 2:

Type 2 slowly changing dimension should be used when it is necessary for the data warehouse to track historical changes.

Type 3 - Adding a new column. In this type usually only the current and previous value of dimension is kept in the database. The new value is loaded into 'current/new' column and the old one into 'old/previous' column. Generally speaking the history is limited to the number of column created for storing historical data. This is the least commonly needed technique.



Before the change:

Customer_ID	Customer_Name	Current_Type	Previous_Type
1	Cust_1	Corporate	Corporate

After the change:

Customer_ID	Customer_Name	Current_Type	Previous_Type
1	Cust_1	Retail	Corporate

Advantages:

- This does not increase the size of the table, since new information is updated.

- This allows us to keep some part of history.

Disadvantages:

- Type 3 will not be able to keep all history where an attribute is changed more than once. For example, if Christina later moves to Texas on December 15, 2003, the California information will be lost.

Usage:
Type 3 is rarely used in actual practice.

When to use Type 3:

Type III slowly changing dimension should only be used when it is necessary for the data warehouse to track historical changes, and when such changes will only occur for a finite number of time.


Type 4 - Using historical table. In this method a separate historical table is used to track all dimension's attribute historical changes for each of the dimension. The 'main' dimension table keeps only the current data e.g. customer and customer_history tables.

Current table:

Customer_ID	Customer_Name	Customer_Type
1	Cust_1	Corporate

Historical table: 

Customer_ID	Customer_Name	Customer_Type	Start_Date	End_Date
1	Cust_1	Retail	01-01-2010	21-07-2010
1	Cust_1	Oher	22-07-2010	17-05-2012
1	Cust_1	Corporate	18-05-2012	31-12-9999

Type 6 - Combine approaches of types 1,2,3 (1+2+3=6). In this type we have in dimension table such additional columns as:



· current_type - for keeping current value of the attribute. All history records for given item of attribute have the same current value.

· historical_type - for keeping historical value of the attribute. All history records for given item of attribute could have different values.

· start_date - for keeping start date of 'effective date' of attribute's history.

· end_date - for keeping end date of 'effective date' of attribute's history.

· current_flag - for keeping information about the most recent record. ·

In this method to capture attribute change we add a new record as in type 2. The current_type information is overwritten with the new one as in type 1. We store the history in a historical_column as in type 3. 

Customer_ID	Customer_Name	Current_Type	Historical_Type	Start_Date	End_Date	Current_Flag
1	Cust_1	Corporate	Retail	01-01-2010	21-07-2010	N
2	Cust_1	Corporate	Other	22-07-2010	17-05-2012	N
3	Cust_1	Corporate	Corporate	18-05-2012	31-12-9999	Y

ODI 12c SCD Type 2 Step by Step Implementation 

ODI 12c SCD Type 2 is very easy compare to ODI 11G.



Please find the below steps for SCD Type 2 implementation.



I have created new target table to support SCD behaviour.



CREATE TABLE DEV.SCD
(
EMPLOYEE_ID NUMBER(6,0),
FIRST_NAME VARCHAR2(20 BYTE),
LAST_NAME VARCHAR2(25 BYTE) NOT NULL ENABLE,
EMAIL VARCHAR2(25 BYTE) NOT NULL ENABLE,
PHONE_NUMBER VARCHAR2(20 BYTE),
HIRE_DATE DATE NOT NULL ENABLE,
JOB_ID VARCHAR2(10 BYTE) NOT NULL ENABLE,
SALARY NUMBER(8,2),
COMMISSION_PCT NUMBER(2,2),
MANAGER_ID NUMBER(6,0),
DEPARTMENT_ID NUMBER(4,0),
STATUS_FLAG VARCHAR2(1 BYTE),
STARTING_DATE DATE,
ENDING_DATE DATE
) TABLESPACE SYSTEM ;



Step1:

------

Import IKM Oracle Slowly Changing Dimension Knowledge module.

Step2:
--------
Open Target SCD table and Change the SCD Behavior.






I have select SCD Behaviour like below options.




Step3:
-------
Creating Mapping for Loading the data from Source table (hr.employees) table to target table (dev.scd).

These three columns we are not receiving from Source , we need to map at direct target table


I have done mapping STATUS_FLAG=1 ( Default Active-1, Inactive-0),
STARTING_DATE=SYSDATE , ENDING_DATE = SYSDATE( But it will take default value from IKM SCD as 01-01-2400.



Selecting LKM SQL to SQL Knowledge module in Physical Tab

Selecting IKM Oracle Slowly Changing Dimension Knowledge Module 

Selecting CKM SQL knowledge module for Data Quality validation at I$table.

Running Interface using Run button in Top Menu

Target table is empty as of now there is not records in target SCD table.

Program executed successfully. We can see the status as Green .

110 records are inserted as STATUS_FLAG=1 as New Records or active records.

UPDATE hr.employees SET salary=77777 WHERE employee_id=100;

COMMIT;

I have update data in source table and again i am running my mapping.





Program finished successfully.



One record got inserted as salary got changed it will Add row on change behavior





We can see the modified record inserted as new records and old record updated STATUS_FLAG=0  inactive record. STATUS-FLAG=1 is active for new records.