Diff btw hard and soft RI

Database
Enthusiast

Diff btw hard and soft RI

Hi all,

I would like to knw what is Hard and soft RI means ? Also the difference between them.

Am just curious to know about them as it has been used in few recent posts in the forum.

Regards,
Sakthi
6 REPLIES
Enthusiast

Re: Diff btw hard and soft RI

We will start with Hard RI.

It's nothing but our plain good old Referential integrity check we know of ...

so if we have two tables ...

(pardon any syntax errors)

CT DEPT
(
DEPTNO INTEGER NOT NULL,
DEPTNAME VARCHAR(50)
)UNIQUE PRIMARY INDEX(DEPTNO);

CT EMP
(
EMPID INTEGER NOT NULL,
DEPTNO INTEGER,
EMPNAME VARCHAR(50),
CONSTRAINT FKEY FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO)
)UNIQUE PRIMARY INDEX(EMPID);

Assuming you have records in DEPT for deptnos = 1, 2, 3
And you try to insert into EMP a record for deptno = 4 (which is not in DEPT), it will error out as we all know.

That's hard RI (there are two type of Hard RI ... standard & batch... but the rules of the game are the same, except some implementation differences)

So when Hard RI is present, Database has to check for existence of a parent record, and as we know any extra check is extra CPU cycle and a little notch on the performance. Not to mention the diskspace that would be used up if there are any RI subtables involved.

But Optimizer has some benefit if there are any RIs involved when generating plans for queries.

For example.

EXPLAIN
SEL E.*
FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO

will show you that the records are retrieved from EMP table and there's no join done with DEPT table at all.

This is because optimizer knows that every deptno in EMP is in DEPT and the deptno column in DEPT is unique.

But as mentioned earlier, there are extra performance overheads in maintaining Hard RI.

This where soft RI comes.

what it means is that Database assumes that you (Application) will take care that no bad records will go into the Database, so that database doesn't have to do RI checks or maintain RI subtables (so we don't have performance issues related to maintenance). But optimizer will still generate the same efficient plan.

Enthusiast

Re: Diff btw hard and soft RI

Hi Joed,

That was a gr8 explanation. I found it easy to understand by the way you have explained.

Let me tell you what I understood.

A hard RI is a general RI check that we use between child parent table and a soft RI is something that is not physically present but is done through the application side.

Regards,
Sakthi
Enthusiast

Re: Diff btw hard and soft RI

That's true, where as with Hard RI, Database is forced to make all the checks, in soft RI, Database assumes that this was already done by you before you load the data into the table.

It's not always that application "cleanses" the data before loading into a child table with soft RI.

let's take the case of an HR application for example.

HR folks can change the deptno of the employee via a webpage which provides a drop down of available dept numbers. This drop down box is populated by the webpage by retrieving the info from DEPT table. Now what that means is that HR can pick ONLY the deptnos which is present in DEPT table. So this eliminates the need for a Hard RI check on EMP table. because the application has made sure that a bad deptno will never go into emp table. So we can just have soft RI, but optimizer still makes efficient plans.

Enthusiast

Re: Diff btw hard and soft RI

Hi Joed,

Thanks for the clear explanation with example.

Regards,
Sakthi

Re: Diff btw hard and soft RI

Hi joedsilva

Thansk for the explanation. How does the soft RI will really improve the query performance, when we join the 2 tables, the rule is to have both the table data on the same AMP.

Thanks in advance

Enthusiast

Re: Diff btw hard and soft RI

You get the performance gains while loading or maintaining the tables because in case of child table the database engine doesn't look for the related parent table key rather load the table assuming the user is ensuring that the data is consistent.