Welcome Message

"The difference between a successful person and others is not a lack of strength,
not a lack of knowledge, but rather a lack in will."
-Vince Lombardi

November 24, 2009

Use of Indexes in Foreign Key

Normally we used to avoid creating indexes in the foreign keys, with unindexed foreign

key columns; Oracle will lock the child table

WHEN TO INDEX:

In addition to the table lock issue that might hit you, an unindexed foreign key
is bad in the following cases as well:
 
à When you have an on delete cascade and have not indexed the child table. 
   For example EMP is child of DEPT. 
   Delete deptno = 10 should cascade to EMP.
   If deptno in emp is not indexed, you will get a full table scan of EMP.
This full scan is probably
   undesirable and if you delete many rows from the parent table,
the child table will be scanned once for each parent row deleted.
 
à When you query from the PARENT to the CHILD.  
  Consider the EMP, DEPT example again. It is very common to query the EMP table
in the context of a deptno.
  If you frequently query:
  
     select * from dept, emp
     where emp.deptno = dept.deptno and dept.deptno = :X;
 
 to generate a report or something, you'll find not having the index in place
will slow down the queries.
 
WHEN NOT TO INDEX:
 
So, when do you NOT need to index a foreign key?  In general when the following
conditions are met:
 
à You do NOT delete from the parent table. (Especially with delete cascade)
 
à You do NOT update the parent table’s unique/primary key value.
 
à You do NOT join from the PARENT to the CHILD (like DEPT->EMP).
 
If you satisfy all three above, feel free to skip the index, it is not needed.
If you do any of the above, be aware of the consequences.
 
As for the effect of an index on a foreign key index on an OLTP system
-- it depends.
If you not update the foreign key the overhead is during the insert
and might not be noticed. If you update it frequently, it might be worse

We will go through the following example which is having the delete statement which deletes from the parent table which is having a reference with the child table.

Example without index on child table

-----------------------------------------------------------------------------

-- Create a Parent Table.

CREATE TABLE PARENT(NO NUMBER);

-- Insert some sample data in to the Parent table.

INSERT INTO PARENT(NO) SELECT ROWNUM FROM ALL_OBJECTS WHERE ROWNUM < 10001;

COMMIT;

-- Add Primary Key Constraint to it.

ALTER TABLE PARENT ADD CONSTRAINT PARENT_PK PRIMARY KEY (NO);

-- Create a Child table.

CREATE TABLE CHILD(NO NUMBER,NAME VARCHAR2(10));

-- Insert some sample records wit the value from the parent table

INSERT INTO CHILD(NO,NAME)

SELECT A.NO,A.NAME FROM

(SELECT NO,'RAFEEK' AS NAME FROM PARENT WHERE ROWNUM < 10000)A, (SELECT 1 FROM DUAL

UNION ALL

SELECT 2 FROM DUAL);

-- Commit the transaction

COMMIT;

-- Add foreign key constraint to it.

ALTER TABLE CHILD ADD CONSTRAINT REFPARENT FOREIGN KEY (NO) REFERENCES PARENT (NO);

-- Analyze both the tables (Gather Statistics)

ANALYZE TABLE CHILD COMPUTE STATISTICS;

ANALYZE TABLE PARENT COMPUTE STATISTICS;

--NOW WE WILL CHECK THE PERFORMANCE PROBLEM

-- delete All the records from the child table

DELETE FROM CHILD;

COMMIT;

ANALYZE TABLE CHILD COMPUTE STATISTICS;

--TO CAPTURE THE TIMING

SET TIMING ON

--DELET FROM THE PARENT TABLE

DELETE FROM PARENT;

COMMIT;

SET TIMING OFF

--NOTE DOWN THE TIME TAKEN

---------------------------------------------------------------------------

Example with Index on child table

DROP TABLE CHILD;

DROP TABLE PARENT;

CREATE TABLE PARENT (NO NUMBER);

INSERT INTO PARENT (NO) SELECT ROWNUM FROM ALL_OBJECTS WHERE ROWNUM < 50001;

COMMIT;

ALTER TABLE PARENT ADD CONSTRAINT PARENT_PK PRIMARY KEY (NO);

CREATE TABLE CHILD (NO NUMBER, NAME VARCHAR2 (10));

INSERT INTO CHILD (NO, NAME)

SELECT A.NO, A.NAME FROM

(SELECT NO,'RAFEEK' AS NAME FROM PARENT WHERE ROWNUM < 10000)A, (SELECT 1 FROM DUAL

UNION ALL

SELECT 2 FROM DUAL);

COMMIT;

ALTER TABLE CHILD ADD CONSTRAINT REFPARENT FOREIGN KEY (NO) REFERENCES PARENT (NO);

CREATE INDEX CHILD1_IX ON CHILD (NO);

ANALYZE TABLE CHILD COMPUTE STATISTICS;

ANALYZE TABLE PARENT COMPUTE STATISTICS;

ANALYZE INDEX CHILD1_IX COMPUTE STATISTICS;

DELETE FROM CHILD;

COMMIT;

ANALYZE TABLE CHILD COMPUTE STATISTICS;

SET TIMING ON

DELETE FROM PARENT;

COMMIT;

SET TIMING OFF

--NOTE DOWN THE TIME TAKEN

Thanks & Regards,

Mohamed Rafeek, S

November 23, 2009

SQL DBO SQL Server Database Object Owner dbo

Who is "dbo?

There are two "dbo" in SQL Server, one is Database Owner and one is Database Object Owner, dbo is a user with permissions to perform all activities in a database. Each database has this special user called dbo, user dbo cannot be deleted and is always present in every database. Database owners and object owners have no separate logins the user dbo is part of sysadmin fixed role or db_owner fixed database role. Any objects created by members of the sysadmin fixed server role (or by the dbo user) belong to dbo.

Objects created by any other user who is not also a member of the sysadmin fixed server role including members of the db_owner fixed database role belong to the user creating the object, not dbo. The creator of an object is granted all permissions implicitly but must give explicit permissions to other users before they can access the object. When accessing objects an owner name should be qualified before object name like "object_owner.object_name"

SELECT * FROM Object_Owner.Table_Name

If object owner is not specified when referencing the object then SQL Server looks for objects owned by current user and then objects owned by dbo, if the object is not owned by current user or dbo then object owner must be specified other wise an error will occur. Windows NT users and groups can be owner of objects. If you want to remove a database owner from current database the objects owned must be dropped or ownership must be changed.

Check these two stored procedures in books online.

sp_changeobjectowner
sp_changedbowner