Cool Database Fundas

Oracle Interview Q/A Series - III

2013-11-23T17:13:00.000+05:30

Oracle Interview Questions / Answers Series brings you some top interview questions which are frequently asked in interviews (Interview FAQ). You should try answering these questions and in other posts of this blog and check your answers against the standard answers at Interview Questions Answers

Q. What is SQL?

Q. Explain Inner Join and Outer Join with an example.

Q. What is a Predicate? What is the difference between "WHERE" and "HAVING"?

Q. What is the difference between "ROWNUM" and "ROWID"?

Q. What is the difference between Union and Join operations?

Q. Compare operators IN and EXISTS.

Q. Differentiate between DELETE and TRUNCATE commands.

Q. What is DUAL table in Oracle database?

Q. Explain the use of NVL function in Oracle?

Q. What are important aspects of ORDER BY clause?

Also Read : Oracle Interview Q/A Series - II

Oracle Interview Q/A Series - II

2013-11-23T17:01:00.000+05:30

Oracle Interview Questions / Answers Series brings you some top interview questions which are frequently asked in interviews (Interview FAQ). You should try answering these questions and in other posts of this blog and check your answers against the standard answers at Interview Questions Answers

Q. What is a Database System?

Q. What is Data?

Q. What are different models of popular database systems?

Q. State some important properties of Relational Data Base Management System (RDBMS)?

Q. What are different forms of SQL statements?

Q. Explain the term predicate with reference to a data query?

Also Read : Oracle Interview Q/A Series - I

Q. What is a Database System?

Oracle Interview Q/A Series - I

2013-11-23T16:48:00.000+05:30

Oracle Interview Q/A Series brings you those choicest questions which are frequently asked interview questions (Interview FAQ). You may try to answers these questions from your own knowledge and check your answers against those standard answers at Interview Questions Answers

Q. What do you mean by “Logical” and “Physical” storage in Oracle database system?

Q. What are the components of Physical Storage in Oracle database system?

Q. What are the components of Logical Storage in Oracle database system?

Q. What is the importance of a Control File?

Q. My Oracle database contains only one Control File. Is it OK for operating the database?

Q. Why Oracle uses Tablespace for storing tables? OR Why not oracle use concept of one table per file like storage?

Q. Explain Online and Offline Redo Log Files.

Procedure vs. Function

2012-05-26T11:33:00.001+05:30

A database (back end) programming language like PL/SQL in Oracle, has been provided to push the processing of the data to the server end where the actual data resides. This helps reduce network traffic otherwise involved in taking the data from database to front end, if the processing is done in the application in lack of back end language. The two main types of programs used in the database for supporting the data handling on clues / calls from front end are Procedures & Functions. They are source of confusion while making choice (procedure or function) to solve a problem, because in most problem cases, both may be written with equal ease, and it is the question of appropriateness to be addressed rather than correctness. Beginners are also observed to be failing to answer the question of difference and/or similarity between the two confidently and correctly in the interviews. Hence this post. Here we shall be taking the cases with PL/SQL, however other database systems also have their own back end language sharing same syntax as PL/SQL, so this should help them too.

About the PL/SQL Procedure -

A PL/SQL procedure, like procedures in any other language, are workhorse i.e. they are supposed to perform some "action" or "operation" (Query / DML). There may be some outcome of this action, in the form of one or more values, which might be required to be sent to the calling program of the procedure; which it does by assigning those values to the parameters (OUT or IN OUT mode parameters) passed to it by the calling program. However there is no compulsion of doing so on the procedures i.e. a procedure may not require to send any value to the calling program (no outcome of the action). As such, technologically the procedure does not "return" a value. The return statement in the procedure is never associated with an expression (no return <x>; , it may be only return;) but may be optionally used to end the execution and return the control to the calling program. For this different ability of the procedure to "send" the value to the calling program (without ending its execution simultaneously) does not allow it to be used in SQL statements and also can not be used in expression. For example, we can not use select p(arg) from <table>; neither we can use x := p(arg);. We may only use the procedure as an executable statement itself. For example, the procedure may be called like p(arg);.

About the PL/SQL Function -

A PL/SQL function is supposed to perform only "computation" on the values provided to it through its arguments and no "action" or "operation" (Query / DML). The result of the computation must have to be returned through a "return" statement. The return statement must be associated with an expression, which may be a hard coded value or a variable containing a value or an arithmetic / logical expression. That means the execution of the function ends simultaneous with returning the value. This makes the function able to be called in SQL statement and as a term in expression; never as an executable statement. Since the execution of the function must end with simultaneous return of the value, only one value may be returned from the function.

Why should there be a confusion to choose? -

As described above, the Procedures and Functions have diverse characteristics, so then why this discussion, "Procedure vs. Function"? The reason for this being is that, the functions may have everything that a procedure may have. A function may have a Query or DML statement, it is not illegal in the function, however they are not supposed to be used in a function. On the other hand a Procedure may be created for a problem which may be solved with a function, as the "action" in the procedure is optional. Also there is nothing like, a function may not contain an OUT or IN OUT mode parameters and nothing like it can not assign a value to them (though that is not the appropriate way the function should return the value). For example, the following two programs are technically legal but not appropriate -

create function delrows (dno emp.deptno%type)

return number is

       x number;

begin

      delete from emp where deptno = dno;

            x := sql%rowcount;

      return x;

end;

create procedure addnum(n1 number,n2 number,n out number)

is

begin

      n := n1 + n2;

end;

In the above case "delrows" should have been developed as procedure and "addnum" as a function. But even as of now, they are fully legal and they work in their own ambit. The "delrows" will indeed return the number of rows deleted and addnum will return the sum of the two numbers. Then, where the thing has gone wrong -

Function Purity - 
 

You can see from the foregoing explanation on the procedure and function, that the function is a versatile entity and ideally it should be callable from SQL as well as PL/SQL (programming) environment, whereas the procedure may only be called in PL/SQL environment as an executable statement itself. However, the function like "delrows" can not be called in SQL, but the same may be called in PL/SQL environment in an expression to return the number of rows deleted. So the function, though syntactically correct has now a restricted use. The versatility of the function actually comes by following certain rules defined in terms of its purity levels, which are - WNDS, RNDS, WNPS and RNPS. They respectively mean -

WNDS (Write No Database States) - A function may not have DML statement(s).
RNDS (Read No Database States) - A function may not read data from table(s).
WNPS (Write No Packaged States) - A function may not assign to a package variable.
RNPS (Read No Packaged States) - A function may not read a package variable.

So it means, when all the above conditions are met in a function, the function is truly pure and versatile with respect to its calling environment. In the instant case, "delrows" violates the WNDS purity level by the presence of delete statement in it, hence can not be used in SQL.

On the other hand, adding two numbers is a simple computational activity, but in the form of a procedure can not be conveniently used in SQL statement, by passing columns of a table say salary and commission of employee table for calculating the total earning of employees.

Guidelines to make a choice -

The following considerations may be made while choosing between procedure or function for solving a problem -

If the program is supposed to generate multiple values and all those if be required to be sent to the calling program, then use procedure.
If the program is supposed to perform a query and/or DML (cursor), then irrespective of the number of values required to be returned to the calling program, use procedure.
If the program is not supposed to use a query and/or DML and must return a single value then use function.
However if the purity of a function may be forgone (and hence ready to be used in restricted environment) but is a matter of some convenience, then the function may be written, in such special cases, which of course are rare (to be true never faced by me).

Other interesting reads : Data Integrity Triggers, Transaction Concurrency, Object Tables

Delete vs. Truncate

2012-05-21T12:44:00.002+05:30

Delete and Truncate SQL verbs (commands) are frequently used to empty tables. The actual difference between the two is not much clear to most users; not only beginners but also to the experienced users. There also seems to be people having very little idea about when to use what regarding these two commands. This post is an effort to clarify about the same. The bulleted list of properties of these commands may help to make out the differences between the two very easily. We also visit the situations when it will be appropriate to use "delete" or "truncate". -

Delete -

This is a DML category of command.
Being a DML category command, it stores the rollback/undo data (the original version of the data that was changed, in case of delete it is the entire set of rows which are deleted)
Being a DML category command, the operation can be undone.
Being a DML category command, it requires an explicit "commit" command to be fired post operation if the deletion is to be confirmed.
Delete command does not reset the high water mark (HWM) i.e. the level to which once the rows had reached in the table.
Delete command may be used with a "where" clause. It is only when the predicate is not used, will empty the table, and there is no freeing of space even in such case for new object(s).

Truncate -

This is a DDL category command.
Being a DDL category command, it stores no rollback / undo data.
Being a DDL category command, the operation can NOT be undone by using a "rollback" command.
Being a DDL category, it DOES NOT require an explicit "commit" command.
Truncate command resets the high water mark to the 0 level, with only preserving the initial extent allocated to the table for the existence of the table. Which means all the extents above the initial are reclaimed as free space.
Truncate command does not have a "where" clause, which means it must empty the table while returning the allocated space (except the initial extent) for new objects.

Is "Truncate" same as "Delete + commit" ? -

There is a normal perception of many users of the databases that truncate is equivalent to deleting all rows and commit rolled into one. And it is just that. Technically however, this is a big NO. As is apparent from the properties of these two commands as described above, the main difference is the reclamation of the space and resetting of the High Water Mark. Many are also seen to be advising use of Truncate over Delete + commit if the users want to empty the table, since the truncate command is "more efficient". Actually there are some considerations to be made before deciding upon the "truncate" or "delete" for emptying the table -

Delete will require huge space (same size as table) to store rollback/undo data if the table is huge, so a truncate may be more appropriate, but only if the data being deleted is for sure to be deleted and there is no possibility of afterthought for rolling back.
A moderate sized table if required to be reloaded with approximately same amount of data (to same HWM) then delete command may be recommended. Truncate will release the extents above the minimum initial extents and will require de-allocation and reallocation of space, the resources for which may outweigh the extension of rollback segments where the undo data is stored.
A truncate command may be recommended over delete if the volume of the reload to the table is much smaller than the present volume of the data, since the space above HWM of the present data may have been reclaimed in the truncate operation.
Delete command may be recommended in cases if there is a temporary replacement of data for running only "select" queries on the intermediate temporarily loaded data and a simple rollback will be able to restore the original state of data.
If no bulk load is in consideration post empty operation of the table, the truncate shall be more appropriate.

The above scenarios may not present an exhaustive list, but known the properties of the delete and truncate operations, a prudent consideration of the differences between the two, may guide the process of appropriate choice between the two.

Other suggested reading - Materialized Views, Use of External Tables , DDBMS

Materialized Views

2012-05-14T06:23:00.001+05:30

What are Materialized Views? -

Let us start with an example - A company has a database with a table "sales" maintaining millions of rows pertaining to the orders the company has received from its customers over a couple of years. The company managers are periodically interested to know breakdown of region wise total sales, or average value of region wise orders, or breakdown of customer wise total sales etc. for which you would write the queries, such as -

select region, sum(OrderValue) from sales group by region;

select region, avg(OrderValue) from sales group by region;

select CustName, sum(OrderValue) from sales natural join customer group by CustName;

When each of such query is fired, then every time the millions of rows of data shall be read and loaded in the memory, the aggregation / calculations are performed and then the results are returned to the user.

Now answer this simple question to yourself - why you used those log tables, or trigonometric tables in your school when you knew the method and formulas for calculating logs or trigonometric ratios? Used them as "ready reckoner", right? To save time!!

Then Materialized Views are such "ready reckoner" in the database, which when created with such queries, have captured and stored those summaries in them, for to be quickly referenced to, when such aggregating queries are fired. And because those Materialized Views store only the summaries, the number of records in them are obviously small. For example, if the territory over which the company carries out the business has 10 regions then the first and second of the examples above will have only 10 rows each. So not only much much less I/Os and scanning efforts are required to be performed to satisfy such queries but there will be much much less memory requirements to cache the data too; to conserve the server resources. Materialized Views are a data warehousing / decision support system tools that can decrease by many orders of magnitude the response time taken for queries involving hundreds of thousands to millions of records in calculating aggregations and performing complex joins and other operations, that too transparently. Due to the transparent use of Materialized Views, the SQL query statements still are fired in the same form & syntax at tables, so without any need for users to change them.

Another purpose for creating materialized view(s) may be to provide a local copy of the data (replication) in order to reduce network loads or enable data subsetting (based on rows and/or columns) for local sites or enable disconnected computing in the distributed database environment.

View vs. Materialized View -

The typical view is a stored query written on table(s), which implicitly gets invoked, each time when the view is referenced in the SQL query. It is only a definition and is stored as such, there is no data storage, as such view is called as virtual table. The view is created to provide an abstraction layer, hide the source (table), implement a constraint or provide a predefined visibility to the data to different users. The views provide the fresh data, but incur the overheads of executing the query (stored in the view) each time on reference.

Materialized View stores the data, in most cases precomputed summaries. You can not refer to the materialized view in a query; the queries are same fired at tables, but if they involve the same expressions based on which there exist the materialized views created on the table(s), then those queries are internally directed to the materialized views to satisfy them. Materialized Views may provide the stale data (if non-updatable or less update frequency), and since do not execute frequently, do not incur any execution overheads, but must use space on the hard disk to store the precomputed summaries unlike the views.

Materialized view updatability -
Materialized Views as such are used in Oracle, while other DBMSs use an implementation of equivalent functionality through a slightly different concept and arrangement. For example - SQL Server uses "Indexed Views" or MySQL uses "FlexiViews". The forthcoming discussion is with reference to Oracle as a model database.

Materialized views come in 3 possibilities with respect to their updatability at the remote site :

Read-Only materialized Views
Updatable materialized Views
Writable materialized views

Read-Only materialized view as the name suggests, do not allow updates to be performed at the remote materialized view site and they are simple created by omitting "FOR UPDATE" clause from their definition syntax.

Updatable materialized view allows the changes to be made at the remote site. The materialized view must belong to a "materialized view group" to push those changes back to the master site during refresh. The views use "FOR UPDATE" clause in their definition syntax.

Writable materialized view are same as the updatable, but being not made part of "materialized view group", it can't push the changes back to the master site (during refresh) rather changes are lost in the materialized view itself during refresh.

Other topics of interest - A short challenging quiz , Evolution of DB systems

Openbook Quiz - 2

2012-05-10T11:39:00.001+05:30

Challenge yourself here with the following questions. Each question has also the links provided to the text where the possible answer may be found. Please post your answers in the comments. Also please share / circulate this quiz to your contacts over social network, linked in groups, your own blogs etc. to help everybody come close to an opportunity of gathering knowledge.

Question 1 :
The requirement is to provide a form where user will enter a number which is a unique identification for an application the user has submitted to an organization. The application has to pass through various departments, each of which assigns it a status as soon as it has been processed by it. The application form id which the user enters is Primary key of the table wherein its status is updated. Which of the following Indexes will be suitable to process the query fast and efficiently - (You may click on the options to find the properties of each of those option indexes).

(a) Cluster Index (b) Descending Index (c) Bitmap Index (d) Index Organized Table (IOT)

Question 2 :
A client wants to store multiple addresses for each customer in the database and wants most of the time to list all the addresses of each customer at a time using a query by providing the unique customer id. Which of the following table(s) will be most suitable for this requirement -

(a) Heap Organized Table (b) Clustered Tables (c) Nested Table (d) Object Table

If this interests you then you may visit for a previous quiz if you have missed it. Wait for some more quizzes to come shortly on Data Integrity, so prepare yourself.

Data Integrity Using Views

2012-05-09T12:03:00.000+05:30

Data Integrity is implemented in the databases using various Constraints and Triggers. Whereas Integrity Constraints are a non-procedural, declarative way of implementing data validation rules (refer Codd's rule #10), the triggers offer the procedural method to implement data validation for such rules where the constraints show certain limitations. However there is a third method for ensuring the validity of the data, that is through creating "views". Our discussion will be with reference to the Oracle as model database.

What are Views? -

Views are created over tables to provide an abstraction layer, to mask the actual source of data and provide a limited access or visibility to the data contained in the tables. Views provide a means to simplification by allowing to store a complex query as a view in the database (hence an abstraction layer) apart from implementing a security feature (by providing limited access and / or visibility).

View is technically a Query (select statement) stored in the database in parsed and compiled form with a name, which can be used as virtual source of data. The views are referenced in the SQL statements in place of tables; and at such reference the select statement associated with the view implicitly executes on the underlying tables to present the data to the operation or for viewing. As such, the views DO NOT store any data, but they exist only in the form of a definition.

The syntax for creating the plain view (without constraint implementation) is simple -

create [or replace] view <ViewName> as <Select Statement>;

The "or replace" clause is optional (indicated by presence of [], a universal standard to indicate optional part of the syntax), when used, it will replace an existing definition of the view with the same name. The "ViewName" may be any suitable name of user's choice, but of course, must follow all the naming conventions imposed by the DBMS. The "Select Statement" may be any select statement, inclusive of where, having, group by, order by, distinct, sub-queries, joins or relational algebra expressions like Union, Intersect or Minus.

Data Integrity Implementation Example -

Consider a company who has managers, each in-charge of multiple departments. So there is a manager M1 in-charge of departments (10, 20, 30), manager M2 in-charge of departments (40, 50, 60) .. and so on. The managers may only make appointments and transfers of employees for the departments under his/her control. How do you think you could implement this rule using Integrity constraints or triggers? There comes the convenience of using views for such requirements by using a clause "with check option". The mechanism of this clause comes into play in restricting the data manipulation only within the range of "where clause" of the view i.e. any such data manipulation on the view would NOT be allowed which would put the row(s) beyond the selection of the view. For example, to implement the above rule we create two views -

create or replace view empm1 as select empid, ename, job, sal, deptno from employee
where deptno in (10, 20, 30) with check option;

create or replace view empm2 as select empid, ename, job, sal, deptno from employee
where deptno in (40, 50, 60) with check option;

Some more such views may be created for other managers for the set of departments under their control. The respective managers will be granted access only to the view created for their set of departments and none other and the table. So the view "empm1" and "empm2" will be respectively available to managers "m1" and "m2".

The following statements will be valid for manager m1 -

insert into empm1 values (101, 'peter', 'clerk', 900, 20);
update empm1 set deptno = 10 where empid = 101;

Since in these - the insert statement is inserting the employee's rows in department number 10, and the update statement is transferring an employee from department 10 to 20, so well within the scope of the view.

However, the following statements if fired by manager m1 will result into an error -

insert into empm1 values (102, 'robert', 'analyst', 2500, 60);
update empm1 set deptno = 60 where empid = 101;

since in these - the insert statement is trying to insert the employee's row in department 60, not within the scope of the view allocated to manager m1. Similarly, the update statement will fail for the same reason as after the update the row will tend to move outside the scope of the view "empm1".

This was not a complete tutorial on views, but we were only restricted to see the use of views to implement such data validation, restriction or business rules which may not be implemented through integrity constraints or triggers. Actually Oracle also allows to implement primary key, unique, rely, read only constraints through the views.

If you have liked this, Click Here for "concurrency & locking" (a one of the most liked topic by other readers).

Data Integrity - Triggers

2012-05-07T16:54:00.000+05:30

Please Click Here to continue from the background.

Integrity Constraints are the most convenient form of implementation of data validation rules, as they are purely declarative and involve no programming. They check the existing data in the columns for validity, in case if they are created after the tables are populated with the data or if for some reasons they are required to be disabled and re-enabled; they leave no chance that the column may contain any invalid data. Hence, the constraints are considered to be the most reliable way of implementing data integrity.

However, they are not without limitations. For example, constraints can not implement conditional validation or they do not have capability to perform pragmatically minor transformations to the data to make it compatible with the rule of the constraint. They may only fail the statement / transaction if the data does not conform to the rule of the constraint. How if you want that the names of the employees, departments and locations always have to be in upper case? How if you want the salary of the employees not to cross 5000 for those working in department number 30 only? How if any department in your company should have no more employees than 100? .... The list of such requirements may be long, for which the integrity constraints offer no solution. Those limitations on data (columns) however may be implemented using the triggers -

What are Triggers? -

Triggers are the programs, written in a back end programming language and reside in the database, which execute automatically in response to an event in the database.

Triggers come in different types, primarily according to their events. The triggers which fire (read execute) for system related events are called system triggers, while those which fire for a DML events on tables, are called as DML triggers. Some databases allow to write triggers to fire on DML events on views, are called as instead-of triggers. While it may not be possible to provide an entire lesson on triggers here in this small post, we shall discuss in brief about the DML triggers, the type mostly used and having context with our present topic. Our discussion is in reference to Oracle as model database, however minor differences may emerge in the other databases. The readers are requested to post freely their comments about those differences here to enrich the reading experience of the visitors.

DML Triggers - A Primer -

The trigger (read DML triggers, in all forthcoming references), may be created to fire at "row level" or "statement level"/"table level". The former, identified by the presence of "for each row" clause, executes for every row affected by the DML statement, for which it fires, the latter fires for only once irrespective of the number of rows affected by its conjugating DML statement. Row Level triggers are useful in the situation when triggering action has to refer to or use the row level values, since such reference of row level values is only possible in row level trigger (being firing for each row). Oracle provides :NEW and :OLD to refer to the new and old state of record being affected by the DML in the row level trigger. Statement level triggers can't refer to :NEW and :OLD. Any of these may be timed to fire "before" or "after". The "before" timed trigger performs its action before the conjugating DML statement performs it operation, so that automatically defines what "after" timed trigger means. The "before" trigger may be used in situations where some data transformations on the data in transition may be required or the operation is required to be aborted by aborting the trigger, whereas "after" trigger may be used in situations where the trigger action may depend upon the outcome of the DML statement or has to refer to such outcome. Oracle allows triggers to name columns for the update event, so that the trigger is saved from firing unnecessarily while only other columns are being updated. The triggers may have optionally a condition specified under a "When" clause only for which the trigger should fire. The clause can be used only in row level triggers, as this restricting clause can refer to new and old versions of the row(s). Oracle allows the trigger to be combined for insert, update and delete events or any combination thereof, if the triggers had to be same level (row or statement) and timed to fire similarly (before or after) if they were to be written separately for each event. The particular code for each separate event can be made to execute by using boolean built-in variables, which Oracle calls as "predicates" viz - inserting, updating and deleting.

That was rather a highly stuffed primer on the triggers and by no means can substitute a complete explanation, but the same has only been provided for the base, so that users will not feel the forthcoming discussion as alien. A dedicated post to triggers may sometime come in near future but meanwhile you may refer to this link.

Trigger Examples - Implementation of Data Integrity -
Example 1 - In the first example we shall see the implementation of a constraint, where we want that the data in the "dname" and "location" columns of "department" table should be always in upper case. Whereas we could simply put a check constraint, the problem with constraint would be that, it will fail the update or insert statement if the data is not provided in upper case by the user, thus consume time and resources in rejection and users then resorting to re-execute the operation. Whereas now, in the trigger, we implicitly convert the case of the data provided by the user and so trigger will ensure that the data which makes it to the columns is always uppercase, no matter in what case it was provided by the user. So the example -

create or replace trigger upper_case_dept_trig

before insert or update of dname, location on department

for each row

begin

:new.dname := upper(:new.dname);

:new.location := upper(:new.location);

end;

/

Explanation : The code in red is triggering statement - specifies the timing for the trigger "before" in this case and events which are "insert" and "update" (with column list, in absence of such column list it will fire for all updates on any column(s)) and table name "department". In blue is the clause which tells that this is "row level" trigger, since we require to manipulate the data provided by the user, we are using "before" timed and its reference may be only available from :NEW, we are using a row level trigger. The code in grey is the trigger action - in this case converting the user provided data into upper case (upper is a function) and storing it in same vars so ensuring that it will always be upper case.

Example 2 - In the second example we ensure that the salary of any employee may not be greater than limit of 5000 only in department number 30. Remember that, if any employee is having salary greater than 5000 in department number 30, before the trigger is written or some user makes it so when the trigger is in the disabled state, then the trigger CAN NOT detect this, unlike the integrity constraint. However we may not easily apply this rule using integrity constraint. So the example -

create or replace trigger limitsal
before insert or update of deptno, salary on employee
for each row

when (

(new.deptno = 30 and new.salary > 5000) or

(new.deptno = 30 and old.salary > 5000) or

(old.deptno = 30 and new.salary > 5000)

)

begin
raise_application_error (-20000, 'salary limit crossed - operation disallowed');
end;
/

Explanation : While the other things are same as explained in the above example, the text in red is the restricting "when" clause. Observe here that the when clause is the part of trigger header and its conditions are actually stored in the data dictionary. The phase of parsing on the insert and update statement decides whether or not to fire this trigger (if satisfies the conditions). The text in blue is the trigger action, which in this case is calling a built in procedure to fail the trigger and hence the conjugating DML. The error numbers allowed here are -20000 through -20999. The second argument is the error message to be passed on to the user.

Conclusion -
So here we had two very simple examples of the triggers to implement such constraints which may not have been possible with the integrity constraints. The most important limitation of the triggers however is that, they do not check the data already residing in the tables. The data validation for such data may have to be separately done, if the trigger was disabled for some time, or was created after the table was populated with the data.

Data integrity and DML restrictions, such which may not be possible with integrity constraints or triggers, may be implemented through views. Click Here to continue reading about the views.

Integrity Constraints - Creation

2012-05-02T18:05:00.001+05:30

Please Click Here for the explanation and background on the Data Integrity Constraints.

Note : The set of commands / statements used here and the explanations thereof are with respect to Oracle as a model database. Readers may experience minor differences in the other database systems.

Constraint creation with the table -
Constraints may be created along with the table. For example -

create table employee (empid    number(5) constraint pk_employee primary key,
ename varchar2(15),
job    varchar2(15),
salary number(5) not null,
cardno    number(5),
deptno    number(3) constraint fk_employee references
                                                                                    department (deptno),
constraint chk_employee check (salary between 1500 and
                                            12000),
constraint uk_employee unique(cardno)
   );

Explanation : The constraint definitions in red are called column level definitions and those in blue are called table level definitions. Here the word level should not be construed as any hierarchy but only places where those definitions are integrated. The constraints ultimately apply to the columns and their behavior is uniformly same irrespective of their level (read place) of definition. Only one constraint definition may be integrated with column (but a column may have multiple constraints), whereas there is no limitation to the number of constraints definition that can be included after all the columns have been defined (table level). So if a column is supposed to have multiple constraints then one of the constraints' definition may be integrated with column and remaining at table level. Or table level constraint definitions must be used if the constraint has reference to multiple columns, for example a composite primary key (primary key consisting of multiple columns). But some developers are of the opinion to define all the constraints at the table level, just to keep the table definition neat and clean. However remember that table level definitions of constraints are not possible for NOT NULL constraints. A constraint definition contains optional constraint clause, for example "constraint pk_employee", which is used to provide user defined name for the constraint, used conveniently for fetching the constraint metadata from catalog. In absence of "constraint <name>" clause, for example "not null" in above case, the system will implicitly provide the name for the constraint, and will have to be every time known from the metadata as such names are very difficult to remember. The check constraint in above example has been defined as table level because the column had to have two constraints and "not null" could not be defined at table level. The example of unique constraint as table level indicates that the constraint could be defined at table level too, and it may be a matter of convenience as aforesaid; we could do this with other constraints as well.

Adding constraints to an existing table -

It is possible to add the constraint to an existing table, if a certain constraint was not created with the table, or some developers also prefer to create the tables without constraint definitions and add the constraints afterwards.

Add Primary Key : alter table employee add constraint pk_employee primary key(empid);

Add Not Null : alter table employee modify salary not null;

Add Check : alter table employee add constraint chk_employee check (salary between
1500 and 12000);

Add Unique : alter table employee add constraint uk_employee unique(cardno);

Add Foreign key : alter table employee add constraint fk_employee foreign key (deptno)
references department (deptno);

Explanation : Observe that for "Not Null" constraint we are using "modify" instead of "add"; for actually not null constraint is defined as column specification as explained above. Also observe that "foreign key (deptno)" clause had to be added to the definition, because we have to tell here which column has to become FK, that was not necessary in the previous example as the constraint was being defined as column level. Here also "constraint <name>" part is optional in all the above statements.

Adding constraints when table has rows -

The ambit of constraints is not to allow any invalid data to remain in the column(s) on which they are defined. Hence, if the data is present then, it will be validated by the constraint while getting created, for the default status of the constraint is "enabled". The constraint creation fails if the data does not conform to the rule of the constraint.

Some database systems like Oracle allow to use "novalidate" key word to ignore the existing invalid data. However, this keyword is not allowed to be used with primary key constraint or unique constraint. This is because, PK or UK may be referenced by the FKs, and it may result into identity crisis as the referential integrity may be affected by it, with the possibility that the child row may result into referring multiple parent rows (important for job seekers, a possible question in the interview!!!) which has to be strictly avoided in any case. The use of "novalidate" is demonstrated for a check constraint in the example below -

alter table employee add constraint uk_employee
check(salary between 1500 and 12000) novalidate;

Disabling/enabling a constraint -

A constraint, when created, is in the enabled state by default. However, sometimes like, during bulk data load operation, or for other reasons the constraint may be required to be disabled. Disabling action is associated with declaring the data as "NOT VALIDATED" in the data dictionary, and obviously while enabling, the data will be verified before the constraint state is changed to enabled.

alter table <TableName> disable constraint <ConstraintName> [cascade];

alter table<TableName> enable [novalidate] constraint <ConstraintName>;

"Novalidate" option does not apply to primary key or unique constraint for the reasons already explained, but it may be optionally used for other constraints if you want the constraint to ignore any invalid data that was put in the column while the constraint was in the disabled state. "cascade" option may be only used while disabling the primary key. It is used/useful when there are foreign keys referencing to it, which are also supposed to be disabled. The primary key constraint is not allowed be disabled with an enabled foreign key constraint referencing it. However, "cascade" option is not available with enable action on primary key, which means that Foreign Key constraint will have to be enabled explicitly after enabling the primary key.

Dropping a constraint -
A constraint may be dropped using the following statement :

alter table <TableName> drop constraint <ConstraintName> [cascade];

Here the "cascade" option may only be used while dropping the primary key constraint. It is used/useful when there are enabled foreign key constraints referencing to it and they (FKs) are also required to be dropped. The primary key constraint is not allowed to be dropped with an enabled foreign key constraint referencing it.

Dropping a Parent Table -
A table that has a primary key constraint being referenced by an enabled foreign key constraint set on another table (child table), is called as Parent Table. It is not possible to drop a parent table, irrespective of whether the tables contain any data. However, this default behavior may be overruled by using "cascade constraints" clause in "drop table" statement as given below -

drop table <ParentTableName> [cascade constraints];

The "cascade constraints" clause not only allows to drop the parent table but also drops implicitly the referencing foreign key constraints enabled on the child tables, while retaining the child tables and its data.

Foreign key constraint options -
Foreign key constraints enforce the referential integrity. The default behavior enforces that, the parent row(s) (in parent table) can not be deleted if there are any child row(s) dependent on it (in child table). This behavior may be altered by using "on delete cascade" or "on delete set null" options in the definitions of foreign key constraints as given below -

...constraint fk_employee [foreign key (deptno)] references department (deptno)
on delete ....;

The options are mutually exclusive, as former option cascades the delete of parent row(s) on related child row(s), while the later option will set (update) the corresponding foreign key value to null without affecting the remaining part of the child row. The "foreign key (deptno)" option (without square brackets) is required when the constraint is defined as table level or while adding the constraint on an existing table.

Deferrable Constraints -
By default the constraint rules are implemented at statement level, i.e. the data is validated immediately after the statement finishes its action. Such constraints are classified as "immediate" with reference to their data validation implementation. However, some situations require the validation of the data against the rule of constraint be delayed until the end of the transaction rather than checked at statement level. Such constraints are called as "deferrable". Following examples demonstrates such a situation -

create table employee (empid     number(4)     constraint pk_employee primary key,
ename    varchar2(15) not null,
   job    varchar2(15),
   salary number(5)     not null,
   mgrid number(4) constraint fk_employee
                                                                                  references employee (empid) deferrable,
   .......
);

Explanation : In the above partial definition of an employee table, we have a column "mgrid" as foreign key referencing to "empid" column of the same table which is a primary key. This is because the "mgrid" is the "empid" of the employee whom the corresponding employee reports. This depicts an organizational hierarchy of the employees within the same table (because managers and subordinates are all employees). This case may require multiple insert statements until the subordinate and manager relationship is established among them. So the constraint checking will have to be delayed until all such rows are inserted (end of the transaction). "deferrable" constraints however are implemented as "immediate" by default and they require explicit statement to "defer" as under -

alter session set constraints = deferred ;

This above statement will "defer" (for every transaction) all the deferrable constraints in the particular session. If it is required to create the constraints as "deferred" only to start with, then replace the key word "deferrable" with "initially deferred".

Constraints are most convenient way of implementing the data integrity as we have seen from the above, but they are not without limitations. There are situations when the constraints may not be able to implement some custom business rules and we have to resort to other techniques like triggers or views. Click Here for data integrity implementation using triggers. Don't forget to leave your comments and ask your doubts in the comments section please.

Data Integrity Constraints

2012-04-28T16:59:00.001+05:30

A Relational Database Management System must implement Data Integrity in the database in declarative manner (Codd's Rule #10) to ensure the data integrity independence as proposed in the theory of RDBMS. However, though it may be possible to implement the data integrity through a non-declarative means using triggers in the database, the most convenient way of implementing the data integrity is through declaring "Integrity Constraints" on the columns of the table. Implementation of Integrity Constraints is more or less same in all the commercial database systems, however our model database for reference in this post will be Oracle.

Data Integrity Constraints -

Data Integrity Constraints is a declarative means of implementing data validation rules by creating constraint objects on the columns as a tight integration with the definition (read structure) of the table. This way, the integrity constraints are the most reliable and easiest method to prevent entry of the invalid data into the tables. When any of the data involved in a DML operation on the table violates the rule of the constraint then operation is rejected by the database system.

Mechanism of Data Validation -

An important question to be answered is, when does the system run check for the validity of data against the rule of the constraint? By default the system checks the validity of the data when the DML statement completes the execution. For example, if a delete statement fired at a table is supposed to hit some 10 rows, then those rows shall be first deleted and then system will run the check of validation and if it finds that for any of the rows so deleted would result into violation of the constraint, then the delete operation is implicitly rolled back. This is called "Statement Level Rollback" - is an implicit operation, transparent to the user. Then the database system reports the error in rejection of the operation. It is important to understand that there is something called "statement level rollback" which comes into effect when the statement has to fail (for any reason) and it is the right (prerogative) of the system an no explicit "commit" or "rollback" could supersede it. (Actually this is the reason for triggers are not allowed to contain "commit" or "rollback" statements. important question asked in interviews, attention job seekers!!!).

The foregoing mechanism of constraint checking is called as "immediate" constraint. All the constraints created by default, are immediate. However optionally you may create a constraint which may defer the validation of the data until the user fires the "commit" statement. Such constraints are called "deferrable". However deferrable constraints also behave like "immediate" by default but may be deferred by using "alter session" statement to defer them. Databases like Oracle also provide facility to create them deferred with "initially deferred" keyword. (See the syntax section for more actions). Deferred state of constraint ensures transaction level integrity of data as the validation of data is performed at the end of the transaction (when user says "commit"). If any data which violates the constraint is observed at the end of the transaction i.e. while committing, then the entire transaction is rolled back.

Types of Integrity Constraints -

Integrity constraints come in following 5 types -

Primary Key constraint is used on column or a combination of columns to designate a primary key on a table. You may (obviously) define only one primary key on a table and it ensures that the column(s) under primary key constraint must always receive a value (not null) and a unique value. It actually implements "entity integrity" and it is an important part of parent/child relationship between the tables. For example an employee id or a student's university enrollment number.

Unique constraint, as the name suggests, allows only unique values if specified, however it accepts null. Unique constraint is used for such columns in the table which must contain unique values (but they can not become primary key, which is only one in any table). For example employee's access card number.

Referential constraint, also called as Foreign Key, requires that each row of a table has a value in such column that matches to a value in the "parent key", if the value is present or it may remain null. The "parent key", also called as "referenced key", is usually a "primary key", though it may be possible for a foreign key column to refer to some "unique" constrained column. For example, a department id column in the employees' table refers to department id column in the departments' table, and since an employee must belong to an existing department, his department id must match with one of the values in the department id column of the department table or an employee may not have a department id at all (null) reflecting that the employee is yet to be allotted a department. The referential constraint implements the "referential integrity".

Check constraint allows you to specify a condition to be true for the data if specified or be there no data (allowed null) in the constrained column for every row in the table. There may be multiple check constraints defined on a single column, as such you have to take care that the conditions in the check constraints on the same column do not conflict or contradict each other. This is because, database systems do not have any mechanism to verify if the conditions specified in the different check constraints on the same column are or not mutually exclusive. Moreover there is no specific order to the evaluation of those multiple check constraints on the same column. Database systems like Oracle allow to use built-in functions in the check constraint with some limitations. Check constraints are actually "domain integrity" part of implementation of data integrity. For example you may allow only Male, Female or 'Rather Not Say' (or null) values for a gender column in a table of customers.

Not Null constraint restricts that the value for the column must be provided. Not Null constraint is actually implemented as check constraint with a built-in condition "<column name> is not null" implicitly when a column is declared as Not Null.

Referential Constraint DML restrictions -

Referential Constraint puts following restrictions on the DML performed on the existing rows in the Parent and Child tables -

Parent row(s) which has dependent child rows is(are) not allowed to be deleted by default.
Parent row(s) which has dependent child rows is(are) allowed to be deleted if the referential constraint is created with "on delete cascade" option whereby the corresponding child rows are also deleted along with the parent row(s).
Parent row(s) which has dependent child rows is(are) allowed to be deleted if the referential constraint is created with "on delete set null" option whereby the corresponding foreign key column shall be set to null when their referenced parent row(s) is(are) deleted.
Parent key value can not be updated if it has been referenced by a child row(s).
Foreign key column value(s) may be updated to a null or any other value provided the new value exists as parent key value.
Child row(s) may be deleted without any restriction.

Constraint Indexes -

Unique and Primary Key constraints generate unique indexes in the background on the columns which have these constraints declared. These indexes are necessary for implementation of uniqueness, otherwise when an insert or update operation on the constrained column is performed then the database system will have to verify the uniqueness of the new value by scanning the entire table, which will be very inefficient. They are plain b*tree indexes. No other constraints create any index on the constrained columns automatically. (this is one of very popular questions asked in interview, attention job seekers!!!)

After this theoretical background on the integrity constraints , we shall see the actual creation and administration of the constraints. Click Here to continue.

Data Integrity

2012-04-26T12:43:00.001+05:30

If you have visited an earlier post entitled "Relational Database Model - Codd's Rules", then you must have realized that the rule #10 emphasizes the need for enforcement of "data integrity independence" in commercial RDBMSs. The most important purpose of the whole RDBMS paradigm is that, the database should be independent of the applications which access it, so that there should not be any interference of changes in either of them on the other; and to achieve this, the database systems should have only a non procedural (declarative) approach. The rule #10 thus suggests that "data integrity" is the responsibility of the database systems and it must be definable as an object or in the form of a self executing program (read trigger) written within the database (not application), and their existence must be part of metadata stored in the "data dictionary" (or catalog). Also, any change in the "data integrity" be allowed in the database as and when appropriate without affecting the existing application.

What is data integrity ? -

It is important that data adhere to a predefined set of rules, as determined by database administrator or application developer and also the obvious and natural validation. For example no two customers of the bank may have same account number or a value for gender column can be one from Male, Female and Rather Not Say.

So, in short, "data integrity" ensures the quality of the data in the database and it's enforcement prevents entry of any invalid data into the database tables.

Types of Data Integrity -

Two important steps in designing a table from "data integrity" point of view is, first identifying the set of valid values for certain columns and secondly, decide how to ensure that only valid values make their way to the column(s). This entails the need for categorizing the "data integrity" -

Entity Integrity
Referential Integrity
Domain Integrity
User-Defined (custom) integrity

Entity Integrity defines a row as a unique entity for a table. This is obvious from the definition of entity - a real world object, person or a logical idea with a set of attributes and it to be identified uniquely by its own set of values (which form a row in the table i.e. entity set). So here we require to constrain for at least one column in the table to have values which may never repeat. For example student university enrollment number.

Referential Integrity preserves the real world relationships between the entities, which defines that in such relationship there is an entity with independent existence called as parent may be uniquely referenced by dependent entity (may be multiple) called child (children). The rule enforces that a child may refer uniquely to an "existing" parent only or none (may be termed orphan) and the parent may refer to multiple child entities or none (childless). For example an employee (child) may belong to one and only one existing department (parent) or may not be assigned to any department at all and a department may contain multiple employees or none.

Domain Integrity enforces that the valid values for a column follow a restriction of range or list, and type (numeric, string, date etc.). For example the only valid values for a gender column shall be Male, Female or Rather Not Say.

User-Defined (custom) integrity allows you to implement specific business rules. For example a certain level of manager in a bank may approve a loan case only to a certain maximum limit.

How to enforce Data Integrity ? -

Data Integrity may be enforced in many different ways, depending upon the requirement and suitability. Broadly there may be following ways to enforce data integrity -

Declarative Constraints on the columns of the table.
Database Triggers
Use of appropriate datatypes and precisions (width of the column)
use of views containing "with check option" clause

Each of these methods have elaborately discussed in the above list of links.

Here is a link to an interesting quiz, some food for thoughts : Click Here

Database Indexes - Usage and Types

2012-04-24T12:33:00.004+05:30

In a previous post entitled "Database Indexes - An Overview" we discussed about two major categories of the indexes - B*Tree and Bitmap. We also discussed about the difference between their structure and their suitability for different access patterns and index key cardinality. This post I am dedicating to some more types under B*Tree, and our reference database system shall be once again "Oracle". However this may be a good read for other database systems users as well.

The types of indexes a database like Oracle may potentially have are -

Index Organized Tables (IOT)
Cluster Indexes
Reverse Key Indexes
Descending Indexes
Function-based Indexes

Index Organized Tables (IOT) -

Index Organized Tables are the tables stored in B*Tree index structure based on primary key values. The same has been already discussed in a previous post related to tables as IOT is one of the table types as well. Click here to read about them in detail.

Cluster Indexes -

Cluster Indexes are used to index the cluster key and they differ from the normal index (the types of which are discussing here) in the cluster key entries in such indexes point to the block address which contains the data about the cluster key value in consideration unlike the normal index key entry which points to a row address in the related table (on which the index is created). Cluster index has been discussed in detail in a previous post. Click here to read about it in detail.

Reverse Key Indexes -

These indexes are particularly a feature used in a database set up where users connect to the database from different instances (called OPS [Oracle Parallel Server] in old version or RAC [Real Application Cluster] from version 9i onwards) contend for a same index block for inserting their entry in the index. This particularly is required when the index entries come in a sequential manner (generated by some sequence). Reverse key indexes help in reducing the I/O otherwise caused due to pinging and also reducing the amount of resources that may be required to clear the unbalance in B*Tree index arising out new index entries being stored on only one end of the index tree.

Explanation - There are users u1, u2, u3, u4 and suppose u1, u2 are connecting to the database through instance i1 and users u3, u4 are connected through instance i2 (instance is defined as a combination of common memory area which Oracle calls as SGA and supporting background processes, which together provide the resources for users' operations on the data in the database). Let us further assume that all these users at a time are performing insert operations on some table T and there is some column C (index key) in the table which takes the values from a sequence, the current values being 1021, 1022, 1023 and 1024 for the rows users u1, u2, u3 and u4 want to insert respectively. Now, as the actual data is going to be stored in Heap Organized Table (a normal table), the rows may get inserted at any location available in the table, because the table does not need to have any sequence of rows to be maintained (not even in the order in which they are inserted). However, the corresponding entries in the index created over C on table T for the above values must be located in same order (juxtaposed as index must contain the values in an order). In a B*Tree, which is the basic structure of the Reverse Key Indexes, this insert activity will focus in a particular leaf block. This will cause two problems - First, the b*tree, which uses the balance tree (nodes divide the values half way through) for guiding the search to the leaf blocks, will get skewed due to entries only on one side of the balance tree (hence reorganization will be required continuously / frequently). Secondly, in the OPS or RAC the pinging of block (writing the block to the disk modified by one user when accessed by user from another instance and reloading it in memory) will cause heavy I/O, and hence should be avoided, by avoiding the multiple users colliding in the same block. Now if you reverse the above values (1021, 1022, 1023, 1024), they will become 1201, 2201, 3201 and 4201 and hence no more sequential and because of the vast gap between them, now will be stored in different blocks, thereby avoiding the concentration of activity on single block and hence pinging and also the skewing phenomenon on the index. The "sequential" index key values will get distributed over multiple leaf blocks. Of course at the time of search too the values provide by the users will also have to be reversed and then searched through the index.

Drawback - The reverse key indexes are not suitable for range scans i.e. predicates like "where c > 1234 " will not use index. The only queries that may be benefited will be using the predicates like "where c = 1234".

Descending Indexes -

The default ordering of the index key values in the normal B*Tree index is ascending i.e. from lower value to higher. Those indexes are useful in all situations of queries which use unidirectional ordering clause whether ascending or descending, as the databases are known to have the capacity to scan the index in forward or reverse according to unidirectional "asc" or "desc" options specified on the columns in "order by" clause. The descending indexes allow to store the index key values in order from higher to lower. Oracle 8i onwards and SQL Server 2000 are known to use the descending indexes, to deal with a situation when the order by clause in MOST queries may contain different directions for the key columns. With the descending indexes the last step of sorting in the phase of optimization is eliminated and hence there is considerable enhancement in the performance of the query.

Explanation - If in most of the queries you use the ordering clause of the form "order by col1, col2 DESC" or "order by col1 DESC, col2" then you should prefer to create the index of the form "create index <name> on <tablename> (col1, col2 DESC)". If you create the index without using "DESC" option on any one of the columns then the queries using the order by clause of the aforesaid patterns will have a "sort" step in their execution plan. However if the ordering clause is of the form "order by col1 desc, col2 desc" or "order by col1, col2" then you don't have to use "desc" with any of the columns while creating the index.

Function-Based Indexes -

The database optimizers have tendency to ignore the indexes created on bare column(s) when in actual query the predicate uses some function around the column(s) (or if the column(s) is(are) used in some expression or equation). It means that the indexes should be created on just the same expressions or equations to effectively make use of them rather than the bare column(s) involved in them. So function-based indexes give us the ability to index computed columns, which allows us to have searches on different expressions for example - case insensitive searches, search on an equation involving columns etc as explained below.

Explanation - Imagine if you are allowing the user to store the data in "ename" column of "emp" table without any case restriction. However, because the search is case sensitive, you may require to use the predicate with a function around ename. For example "where upper(ename) = 'SCOTT'" to hit all the rows of "Scott" irrespective of the case in which the name is stored. If you have only created the index on "ename" then, for such predicates the index will be ignored and despite the presence of index the optimizer will go for a full table scan. So we may create the index itself on "upper(ename)" rather than just "ename". We may also create index on some expressions like "(x /y + z)" where x, y and z may be the columns of some table on which index is being created.

Create Index Statements -

For a normal B*Tree index -
create index <name> on <tname> (col1[,col2,col3,...coln]);

For a bitmap index -
create bitmap index <name> on <tname> (col1[,col2, col3,...coln]);

For a reverse key index -
create index <name> on <tname> (col1) reverse;

For descending index -
create index <name> on <tname> (col1 , col2 DESC);

For Function-Based index -
create index <name> on <tname> (F(col1));

Why not take this small quiz and challenge yourself to test your DB knowledge or get it if you don't have this information : click here

Choose a suitable table type - Challenge 1

2012-04-19T09:55:00.001+05:30

Examination must be an integral part of learning process or other way round, learning is incomplete without an examination. But not such examinations, which just declare a learner pass or fail, but those which give an opportunity to learn. In my opinion, when we call some person as "experienced" means, the person has learned by facing and solving problems (examinations). Truly, experience is a great teacher, it gives the exercise first and leaves it up to you to draw a lesson from it, which as we all know from "experience" we never forget in life. What part of lessons you really remember from your schooling or college? A very small indeed. Because, the first thing is, we did not know its application in the real life when the lessons were given to us and secondly, the answer lies in this very first reason, that we did not learn the lessons through exposure to problems, but just lessons were in a way "taught" (say read out) to us.

Here the questions I am going to post are going to be simple but practical questions faced in real life as developer. I am liberal to provide you links in the options of the questions itself to different pages / posts of this same blog and may be some other blogs too or you are free to search the answers elsewhere in books, docs, manuals or from your own experience. So you have an opportunity to thoroughly read or critically learn from the pages in search of the proper answer. I would of course like you to post your answers with reasons / explanations in the comments of the respective posts, and let there be a technical analysis in those reasons / explanations. So I hope you might be eager and ready as well to face this first challenge (many more to come, become a follower of this blog so that you will get automatic updates and of course such posers) -

Choose a suitable type of table to be created for following situation -

I want to store the data about employees and departments of a company. The data about the employees consists of (empid, name, job, salary, commission, departmentid) and about the departments consist of (departmentid, department name, location). The requirement is, while I need to access and/or manipulate the data of employees and departments individually a few times, but more frequently I list the names of the employees, their salaries along with the names of the departments and location of the departments in my application. Which of the following table / structures you would prefer to use and why? (this poser assumes the Oracle database, but the professionals from other DB implementations are invited to answer from their own area for readers to have a comparative study, which of course leads to cross platform knowledge and that is also the purpose behind the posts of this type). Your options / links are -

(a) Heap Organized Tables (b) Nested Tables (c) Object Tables (d) External Tables (e) Clustered Tables (f) Temporary Tables (g) Index Organized Tables

If you have liked it, then another more interesting quiz : Click Here

PS : Share this post generously on your social or professional network.

Database Indexes - An Overview

2012-04-17T11:25:00.001+05:30

Why create index? -
DBMS is all about storing the data, allowing for the easy access and perform transaction on the data. RDBMS insists on such storage to be in the structure of a Normalized Relation(s) that obey certain rules, popularly referred as Codd's Rules. Of course, as the volume of the data increases manifold on the top of increase in the number of users, the query execution time and also the amount of resources consumed in carrying out such data operations (search) increase exponentially. "Indexes" are another prime set of objects, created along with the tables in the RDBMS, to cut down on both, the execution time and the resources. Database indexes are analogous to the indexes or list of contents we normally visit in "large" books to help us locate a particular topic of interest. The alphabetical order on the titles along with the page numbers against them reflect in the database index in the form of ordered values from a column(s), usually called as index key, and a pointer to the exact address of the row in the database, respectively. Just like if you don't have index or table of contents in a book will require you to read the entire book to locate and read a topic; an absence of index on the "search column" of a table will require to read ALL the table data to find what the user is looking for, giving considerable rise in I/O.

Indexes don't serve any mandatory functionality in the database and hence are purely optional objects. Yet, there may be no small or big databases without them. However, use of indexes and that too of an "appropriate" type is extremely crucial issue as using (or not using or not using an appropriate type) involves a trade off between speedy retrieval of query results and slower DML operations. For most of those DML operations would require reorganization of the index and since time spent in such reorganization would be directly proportional to its size and volume, density and distribution of the transaction, the decision requires prudence on the part of developers and DBAs as well. There is no limit to the number of indexes you may create on table, however there may only ONE index you can create on a particular column or combination of columns (index key). The use of index is transparent to the application (and of course user) and does not require any change in the SQL, but it is required for an application developer to be well aware of the subject of indexes, their types, how they work and specifically their suitability to a pattern of access while framing SQL.

It is observed that the creation of indexes is reactive part of the tuning process unfortunately, though in small proportion this may be the approach, but by and large it should not be so. Creation of the index should be the proactive part of the tuning strategy to be considered during the development of the application itself and not as an afterthought.

Index Schemes -
Basically there are broadly two types of indexes, based on their organization characteristics, used in the commercial database systems like Oracle (this DBMS would be our reference for our further discussions) namely - B*Tree and Bitmap.

B*Tree Index Structure

B*Tree Indexes, considered as a more conventional type of indexes and default types in most database systems, including Oracle, are implemented in similar way as binary search tree. The goal is just not to minimize the time taken to search value(s) but also to equalize it. The search process to reach to page (block) containing your value is something analogous to searching a word in a dictionary(with some discipline/ rule). For example if you want to search a word "tiger" - you open the dictionary half way through (the discipline / rule) and say we land on a page of words that begin with "g", it means there are no chances for "tiger" to be in the left hand side pages (you eliminated 1/2 of the pages from your search). On the right hand side pages you open half way through again (remember the discipline / rule) and you land on some page of "s" words, so there is no chance of "tiger" being in left side 1/4 pages of the dictionary. You continue the process thus cutting each time half of the remaining pages either on left or right side of the landing page, you hit a small number of pages each having two words on the top, one on the left and other on right, that helps you guess if "tiger" could be on the page or not without fully reading it. The pages in this example which are scanned at the end are "leaf blocks" in our B*Tree indexes and the landing pages, where we decide to move right or left are called "nodes" in our indexes. The pictorial representation as above, would be like there are multiple levels of branches, each called as "Blevel".
Bitmap Indexes, were for the first time proposed in the year 1985 in a research paper entitled "Storage and Retrieval Considerations of Binary Databases", published by Professor Israel Spiegler and Rafi Maayan. The first commercial use was made in 1987 in a database product called Model 204 from Computer Corporation of America. Oracle implemented it in their version 7.3. The principle of bitmap index is simple - First, the index works very well for low cardinality i.e. for the column where the number of possible values are small (so they appear repeatedly in that column) as compared to the number of rows in the table. Second, the index contains, bit vectors equal in number as those possible values and have 1 bit placed in the jth position if the jth row contains the value being indexed. Each vector contains bits equal in number as the number of rows in the table so there is one-one positional correspondence between the bit and the row(in table). So it does not have to store row addresses (in Oracle "rowid").

Example : If we are maintaining information about cars in an application developed for a regional transport registration office and say there are 100,000 cars so far registered (and many more to be registered in time to come) and one of the columns in the table is vehicle type, which may have five possible values - sedan, hutchback, SUV, MUV, and van. Then indexing "VehicleType" column is a perfect case for using "bitmap" index. There will be five bit vectors, one each for sedan, hutchback, SUV, MUV, and van. Each such vector will have 100,000 bits (equal in number as the number of registered vehicles and will increase as new vehicle register). For instance, vector for sedan could be 00010001000001000000..... Observe here that there is bit "1" in positions 4,8,14..., so the car type at the rows 4,8,14 ... are of sedan type. Another vector say for hutchback could be 0000010100000010000.... Observe here that there is bit "1" in positions 6,8,15..., so the car type at the rows 6,8,15... are of hutchback type. And so on.

B*Tree v/s Bitmap -

B*Tree indexes in general are best suited for more selective queries (requiring to select very small % of the total rows from table) and particularly for high cardinality index keys (the column(s) to be indexed). Bitmap indexes are best suited for the low cardinality index keys. Bitmap indexes take very small space, because they store only bit vectors, for example in the above case each vector will store 100,000 bits i.e. equivalent to 12.5KB and the total size of index will be 12.5 * 5 = 62.5 KB. whereas the size of B*Tree index for same number of rows in the table will be at least 976 KB (that is minimum space required to store only rowids) and will further take space to store index key values (not much predictable at this stage). Bitmap indexes are considered very well for queries of ad-hoc type and particularly aggregation like count whereas B*Tree are considered suitable for selection of small portion of rows and based on range of values on index key column and may contain a fair mix of selection and DML. Queries like "select count(*) from cars where vehicletype = 'sedan';" may be answered from the bitmap index without even visiting table and same would be the case if we use some column "c" of a table "t" in the query by replacing cars with t and vehicletype with c and some appropriate value in place of sedan, where c is indexed as per B*Tree scheme. But here the matter is the volume of data scanned (hence loaded in memory) which will be much less in Bitmap index than in B*Tree case. Because OLTP and DWH databases involve more of aggregation/ad-hoc queries and close to zero transactions, the bitmap indexes are more suitable for most cases, whereas OLTP databases are observed to have more of B*Tree indexes, however this should not be construed to be a rule.

When to Index and What -
Indexes generally are known to enhance the query performance but only if proper attention is paid to when (access pattern), what (choice of index key) and which type (described above), or the indexes may prove detrimental to the performance. Though there are no concrete rules, but a few guidelines do provide the clue -

Index generally when you need to access data no more than 10 / 15 % from the table or some aggregation queries like count are frequently required to be answered (from the index alone).
Indexes should be generally avoided on small tables as full table scan may be much more economical in such case than scan index and then table.
Index those columns that may be frequently involved in joins, where clause, order by, group by, union or distinct operations.
The index key columns should be fairly static (normally not involved in update operations) or overheads in reorganization will be considerable.
In composite index, the driving column (first column in composite index key) should be more selective.

Apart from the above "general" two types of index schemes, we can see that sophisticated database management systems like Oracle provide you with some more flavors of B*Tree indexes. Click here for an elaborate description of more special indexes as listed below and their usage:

Also Visit : Career Articles

What are Nested Tables ?

2012-04-12T16:37:00.000+05:30

"Nested Table", as the name suggests is table within a table. A normal Heap Organized Table , if has a column whose datatype is table like object, then such column is called as "column object" and since it being a table may store "unordered" set of data elements, all of the same type as the datatype of the column, hence it is very much like a table within the table and is called as "Nested Table". Actually creating nested table is sort of going out of bounds that allows us to create a relationship within a table that could have been easily done with normal two separate "parent" and "child" like relational tables. At least some of the authors like Thomas Kyte believe it so and do not much encourage to use nested tables in this way.

"Nested Table", as a part of Object Relational Extension, in Oracle, has been provided as one of the type of "collection objects" (read array but somewhat different in some sense), the other being "varray". The only points of differences between these two types of collections are, the former is virtually unbounded (with no upper limit to number of elements it may contain) whereas latter is bounded and also while the former may be manipulated easily by "Extended" SQL, the latter may not and requires PL/SQL program to manipulate. So the discussion about Nested Table is considered enough to give a fair idea about the varrays.

As has been said above, the Nested Table is very much similar to a child table in a traditional parent/child pair in the relational model but just has been facilitated with being defined as an object data type to be used to create a column in the normal table. So in a way it gives an illusion that each row in parent table has its own child table. Visually the structure could somewhat resemble as follows -

Table deptemp :

deptno	dname	loc	empt
			empno	ename	job	salary	darate	itxrate	xemplim
10	acct	NY	1	Peter	manager	3000	120	10	24000
			2	George	clerk	800	140	5	6000
			...	...	....	....	....	....	....
20	research	CA	15	Scott	analyst	2900	110	9	22000
			25	Julia	analyst	3200	120	10	23000
			....	....	....	....	....	....	....

A Live Example -

We shall consider the same object (empitax) that we created in our post about Object Table and use it to create our Nested Table type collection (emptaxtab) and its column (empt) in a "deptemp" table.

Step 1 -
Of course creating the object type empitax which we did in out post about Object Table. (not to be repeated here)

Step 2 -
We shall create "Nested Table" type collection

create or replace type emptaxtab as table of empitax;
/

Step 3 -
Creating the table "deptemp"

create table deptemp (deptno number(3) primary key,
dname varchar2(15),
loc varchar2(2),
empt emptaxtab)
nested table empt store as empnest;

alter table empnest add constraint u_empnest unique (empno);

Observe here that we're creating the column "empt" as type "emptaxtab" of nested table type collection we created in step 2. We are adding "unique" constraint in the statement "alter table ..." to force the "empno" to be unique. The nested table however does not allow to add any foreign key constraint, even the one to refer to its own unique column. The clause "nested table ... store as ..." has been explained at the bottom of this post.

Step 4 -
Data manipulation -
We may insert the rows in this table as -

insert into deptemp
values(10,'acct','NY',emptaxtab(empitax(1,'peter','manager',3000,120,10,24000),
empitax(2,'George','clerk',800,140,5,6000),
empitax(......)));

In this same way we may put rows for all the departments and their employees. (observe the constructors "emptaxtab" and "empitax" being called. "constructors" are functions used for initializing (read providing) values for object attributes)

Now suppose we have recruited one more employee in deptno 10, then to add such employee row we require to use an operator "the" or "table" as given below -

insert into the(select empt from deptemp where deptno = 10)

values (5,'Bobby','accountant',1200,130,5,9000);

Now suppose we want to change salary of Peter working in department 10 from 3000 to 4000 then

update the (select empt from deptemp where deptno = 10)

set salary = 4000 

where ename = 'Peter';

Now if George leaves our company, so to delete his row -

delete from the(select empt from deptemp where deptno = 10)

where ename = 'George';

The "Table" or "the" operator (in fact a function) may be used to list the data in an un-nest way (similar to relational table join) -

select d.deptno, d.dname, d.loc, e.*

from deptemp d, table(d.empt) e;

Nested Table storage -

Recall the clause "nested table ... store as ...". Actually it implicitly did create a real physical table "empnest" apart from our table "deptemp" and stored it separately. Also look at each "select" statement in "the" or "table" operator. The predicate contains only an equality condition, "select empt from deptemp where deptno = 10". This is virtually selecting the "nested table" for one row in the table, here for department 10. And it must return at least one row or it will result into error "ORA-22908:reference to NULL object value". If this same predicate (for example deptno > 10) returns multiple nested tables, it will return error "ORA-01427:Single row subquery returns more than one row".

Nested Table Limitations -

So we may conclude - Oracle considers each row in "deptemp" table to point to a "table" (a nested table virtually only meant for it) and not a set of rows from another table as it happens in parent/child like relational tables. In relational model there may be set of rows in the child table for one parent row. This basic difference has some limitations in the use of nested tables.

The most important limitation is that we can not access the nested table directly, it has to be accessed through the main table (deptemp). So it is not easily possible to answer queries like "what department does peter work in". Also "how do you transfer an employee working in department 10 to department 30" in nested table? In relational table we just may update the "deptno" (a reference column in emp table to point to deptno column of dept table) column of the emp table from 10 to 30.

What are Object Tables ?

2012-04-10T14:16:00.002+05:30

For an introduction to the table types and list visit : What are Index Organized Tables?

The era of Relational Database Management System (RDBMS) is now left far behind and since last around a decade or so we have been treading the era of Object Relational Database Management System (ORDBMS) when some of the commercial database systems (Informix) and Oracle in particular (since version 8) provided the SQL to implement "Object Relational Model" in the databases running out of their systems. Before we delve into "Object Relational Extension" again with reference to Oracle as our model DBMS, we need to have a brief primer in what "Object Oriented" is all about.

Object Oriented Concepts -
"Object Oriented" programming approach was introduced in late 80s but if going on the spiritual lines one should say the creator of this universe had this same approach in designing this universe; this statement I am making on the same lines as "this world is worth living because it's non-procedural" I made in an earlier post. Now I must also say "the world is worth living because it runs the object oriented way" (of course apart from being non-procedural).

Technically, object oriented approach is defined as "encapsulation" of attributes (variables) and methods (programs) which manipulate those variables to produce certain output depending upon which of the methods have been revoked. An example is the need of the order for this definition to make sense to the beginners or juniors.

What do you do when you feel like having a "brick oven pizza" on your lunch? You call a nearby pizza hut service, right? and then you provide the service with the toppings (technically each topping is a variable and you provide a value for it) of your choice and then tell the service the type of pizza you want, in this case "brick oven pizza". Now you know each of those classified pizzas are made in certain way or process (read Method), so technically you invoke the method. And you get the pizza delivered (the output). And so call "pizza hut" an object.

Actually we may use and extend this same example to understand various properties of an "object" like inheritance, polymorphism, overloading etc. but not for now, as we have got enough of dose of object oriented concept for our job at hand "creating an Object Table".

One important point before we leave this item is that, in Oracle, we actually create a "type" rather than a "class" (a terminology used by other object oriented languages). In a way class and type have no difference conceptually, as both are a "template" (read definition) of an object. The Oracle's "type" terminology is picked from the idea of "datatype" as technically declaring a variable of a datatype and assigning a value to it, if studied carefully, will reveal that it is very much an object.

Defining an Oracle Object Type -
Oracle programmers use PL/SQL back end language to define an "object type", which is written in two parts - header and body. Simply put the header contains all the "declarations" and the body, "code" for all the methods declared in the header. The header has to be successfully compiled before you attempt to write the body. Here in this example we are creating an object type "empitax" containing the salary details of employee along with the methods to calculate the CTC, taxable income and tax amount. -

-- Type Header
create or replace type empitax as object (
empno   number(4),
ename varchar2(15),
job varchar2(15),
salary number(4),
darate number(3),
itxrate number(3),
xemplim number(8),
member function ctc return number,
member function taxincome return number,

member function taxamt return number);
/

-- Type Body
create or replace type body empitax is
member function ctc return number is
   totearning number;
begin
   totearning := self.salary * 12; -- annual salary
   totearning := totearning * (toearning * self.darate / 100);-- sal + allowance
   return totearning;
end;

member function taxincome return number is
   tincome number;
begin
   tincome := self.ctc - xemplim; -- taxable income salary minus exemption limit
   return tincome;
end;

member function taxamt return number is
   tamt number;
begin
   tamt := self.taxincome * self.itxrate / 100; -- tax amount x taxrate percent
   return tamt;
end;
end;
/
Some explanations - When an object type has to refer to its own element then it must be qualified with "self" (self.salary, self.darate, self.ctc, etc). The calculation formulas are arbitrary and not standard. The "or replace" clause is optional; used to overwrite the old definition with new one.

Creating Object Table -
Once you have created the object type now you may create the object table simply by using following form of "create table" statement -

create table emptaxtab of empitax;

This statement will create a table named emptaxtab with the attributes of empitax to become the columns of the table. The table is called "Object Table" and each row stored in the table is called as "row object". All the normal SQL statements may be used to handle the data in this table.

Inserting rows -
insert into emptaxtab values (101, 'peter', 'manager', 3000, 120, 10, 24000);
insert into emptaxtab values (101, 'diana', 'analyst', 2500, 110, 8, 20000);

Observe that the insert statement is same as would be used against a normal heap organized table. Other statements, select, update or delete would also be same. But then what is so special about it? Well, remember? There are some methods integrated in the object from which the table has been created. So we can write a statement -

select ename, x.taxamt() as taxamount from emptaxtab x;

and thus you could list the names of the employees along with their tax amount (or even CTC or taxable income by calling appropriate methods) so easily. The methods are integrated and remain part of the table. (you may use appropriate predicate, "where clause" to list only the names of choice also in this select statement).

Object Oriented is a great concept; so next time when order a pizza of your choice, thank the god for creating this world the object oriented way, and enjoy it. Click Here to continue reading on "Nested Tables" another implementation of Object Oriented concepts.

What are External Tables ?

2012-04-07T18:04:00.001+05:30

For an introduction to the table types and list visit : What are Index Organized Tables?

"External Tables" are probably the types of tables used in Oracle only (other database systems might use some other mechanism, and I request users of those systems may share their knowledge through posting comments / links), as an intermediate staging area during bulk data load operations, particularly in Data Warehouses. They were introduced in Oracle version 9i and the limitation of being only "read only" has been removed since version 10g. The "External Tables" feature is a complement to existing SQL*Loader functionality. It enables the user to access the data in external sources as if it were in a table in the database, and also allows data transformations.

What are External Tables? -
Contrary to what their name indicates, "external tables" are not really the tables. They are only the objects in the form of a definition stored in the data dictionary (catalog). They are capable of providing the data in the normal table format while the data is actually stored in operating system files "external" to the database (hence the name "external tables"). So dropping of the external tables only removes its definition from the data dictionary while data still remains safe in the files. These tables allow all sorts of operations like normal tables do - queries, joins, and DML (from Oracle 10g onwards). So external tables are objects in the form of a definition which do not store any data in themselves but only enable you to view externally stored data as if it were inside a table in the database. The trick is actually the data fields in the external file are merely "mapped" to the external table columns, not actually loaded into them.

External tables are said to imitate functionality of a bulk data load utility called "SQL*Loader" and is considered to be equivalent to storing "SQL*Loader Control File" in the form of a definition of table and hence the same plain SQL (and also PL/SQL, a server side programming language in Oracle) is also allowed to be used against the External Tables. However data transformation and filtering capabilities are considered to be much more flexible than SQL*Loader.

A working example of External Tables -

Step 1 - creating a directory object
A directory object is object defined in Oracle database to point to a file system location where our source flat files are stored. It is the way of protecting the source files from unauthorized access as there are also privileges associated with the directory objects which are granted from the database with "grant" command -

create directory extdir as 'c:\oracle\dfile'; -- directory creation

grant read, write on directory extdir to scott; --read & write privilege granted

Step 2 - Creating external table

create table empxt (
   empno number(4),
   ename varchar2(15),
   job    varchar2(15),
.....
   )
organization external
(
type oracle_loader
default directory extdir
access parameters
(
   records delimited by newline
   badfile empxt.bad
   logfile empxt.log
   discardfile empxt.dis
   fields terminated by ','
   missing field values are null
)
location ('emp.dat')
)
reject limit unlimited;

Explanation : The part of definition above "organization external" is plain create table with definitions of colulmns, "organization external" clause tells oracle that the table is to be created as external table, "type" clause specifies the engine - oracle_loader is used for loading data from file to the internal table, "default directory" points to the directory where all of our source data files and rejection & logfiles will be housed, "location" provides the name of the input data file.

Step 3 - the input data file (external file)
The input data files may contain the records in two formats - (1) fields delimited by a character for example comma as is used here (2) or fields with fixed width format [in which case the access parameters of this example will change accordingly]. For this example the data may in the following way -

7788,scott,analyst,....,20
7839,king,president,....,10
   .......
Step 4 - we can now query the external table by simple SQL

select * from empxt;

Step 5 - We can populate our EMP table (internal)

insert into EMP select * from empxt;

This was a very simple example but it is possible to perform a lot of transformation operations, use oracle's built-in functions, in-line formulas etc. for a more exiting run of the bulk data load operation.

The most important of the advantages of using the external tables is that you can use the common SQL to query the data from external tables which mask the source data files with its structure and almost any operation that we normally do in query may be performed in this query too against the external tables.

What are temporary tables ?

2012-04-05T12:50:00.002+05:30

For an introduction to the table types and list visit : What are Index Organized Tables?

Temporary Tables -

"Temporary Tables" are a great way to increase the performance by materializing (say aggregating and holding) the intermediate data required to solve an extremely complex query. The intermediate data may come from the results of some inline queries (also called in-line views) in the complex query but when temporary tables are used they may represent these inline queries' data. Traditionally views are used to store the complex queries as objects in the database - "view" being a select statement (query) stored in the database in compiled form which is simply invoked when the view is referenced. However, this approach requires that the intermediate data processing (fetch / aggregation) is carried out every time the view is invoked, as the views do not store any data, they are only in definition form. Views only simplify the call to the complex query which is stored as its definition but does not help optimize it, that temporary tables do.

The generalized concept of temporary tables as described above as a role to store the sub results of 3 or 4 subqueries (which make the complex query) and then joining such temporary tables to get the final result of the original complex query is not believed to be an appropriate application of temporary tables in Oracle, unlike other database systems (Tom Kyte, Expert one-on-one Oracle, wrox publication). However for a simplified understanding of the concept, using this as an example is not wrong either.

Oracle Temporary Tables -
In Oracle, they are called "Global Temporary Tables" (GTT) rather than just "Temporary Tables". A GTT is just really a template for the table itself. They are "statically defined" to exist permanently in the database (data dictionary) as objects; and as the term "global" indicates in their type name they are created only once per database and not once per stored procedure. Temporary table will allocate storage from the currently logged user's temporary tablespace, or if they are accessed from a "definer's right procedure" then temporary tablespace of the owner of the procedure. Actually every user referring to "same" GTT actually creates a temporary segment in his/her temporary tablespace in the background when their sessions put the first data in the GTT. This arrangement avoids interference and visibility of data and transactions across concurrent sessions. Also there are no possibilities of one session blocking another session in concurrent transaction and extremely small amount of redo is generated as compared to the transactions made against the normal "Heap Organized Tables" The GTT may be either created to hold the data for the duration of a transaction or session as per the requirement and application design.

GTT can have many of the attributes of a normal "Heap Organized Table" like triggers, check constraints, indexes, use same SQL for all database query and manipulation; but may not have referential integrity constraint (Foreign key), neither can be a target of a foreign key, no varray or nested table columns, not be part of cluster or partitioned and analyzed for optimization.

GTT Example -
With that little background let us have an example with explanation to understand the use of GTT.

There are two tables "store" and "sales" and we have to answer "show all stores with above average sales". There are three aggregate operations needed -

Total number of stores.
Total sale for each store.
Total sale for all the stores.

Here is the normal query -

SELECT

store_name, sum(quantity) AS store_sales,

(select sum(quantity) from sales)/(select count(*) from store) AS avg_sales

FROM store s, sales sl

WHERE

s.store_key = sl.store_key

HAVING

sum(quantity) > (SELECT sum(quantity) FROM sales)/(SELECT count(*) FROM store)

GROUP BY store_name;

Here we create GTT to have the aggregation stored (for the highlighted queries in the above) -

create global temporary table store_qty

on commit preserve rows

select sum(quantity) as all_sales from sales;

create global temporary table store_cnt

on commit preserve rows

select count(*) as nbr_stores from store;

create global temporary table store_sales

on commit preserve rows

select store_name, sum(quantity) as store_sales

from store natural join sales

group by store_name;

These GTT as above will be created with the data pulled from their respective tables. The "preserve rows" option of the "on commit" clause will retain the pulled data until the session ends. The other option is "delete rows" which empties the global tables when users commits the transactions.

Anyway in the above example if the session is closed the data in the GTT will be deleted and we may require to re-populate those tables with the data on opening of the new session, so in such every subsequent opening of the session we may use following simple insert statements -

insert into store_qty select sum(quantity) from sales;

insert into store_cnt select count(*) from store;

insert into store_sales select store_name, sum(quantity)

from store natural join sales

group by store_name;

And of course our final solution producing query -

select store_name, store_sales, all_sales/nbr_stores as avg_sales

from store_qty, store_cnt, store_sales

where store_sales > (all_sales/nbr_stores);

What are Clustered Tables ?

2012-04-04T11:53:00.001+05:30

For an introduction to the table types and list visit What are Index Organized Tables?

Clustered Tables -

A "Cluster" is a way to store a group of tables that share some common column(s) (related tables) in the same database blocks rather than at different locations. So in a way "cluster" is a way of storing the "related tables" in a "pre-joined" (not exactly joined) form at a common place. The tables are still accessible individually and the SQL obviously remains unaffected. This is DIFFERENT than the "clustered index" used by some other database systems (comments invited from users of such database systems).

In normal case, for example the "emp" and "dept" tables are stored as their own segments like -

Table : emp

empno	ename	job	deptno
1	ward	clerk	10
2	allen	analyst	20
3	king	president	10
4	miller	analyst	20
5	scott	accountant	10

Table :dept

deptno	dname
10	accounting
20	research
30	sales
40	operations
50	training

however in cluster named "empdept" the same tables will be stored thus -

Cluster empdept : block1

deptno	dname
10	accounting
empno	ename	job
1	ward	clerk
3	king	president
5	scott	accountant

Cluster empdept : block2

deptno	dname
20	research
empno	ename	job
2	allen	analyst
4	miller	analyst
...	....	......

Now here observe that you are storing data together grouped by some column (on every block). For example, all of the employees working in department 10 will be stored in the same block (there may be multiple blocks for one particular department if all the employees are not accommodated in one block.). However it does not amount storing the data sorted, as is done in the Index Organized Table, but data is segregated according to the "cluster key" i.e. deptno in a heap on every block and also deptno 50 may be right next to deptno 10 and physically stored quite apart on the disk.

With that basic concept, now there are two types of clusters namely - Index Clustered Tables and Hash Clustered Tables. Also remember that, no matter what type, the existing tables can not be clustered, the process of creating clustered tables is to first create the cluster and then create the tables as part of the cluster. (Watch the syntax and steps below)

Index Clustered Tables -

Actually the above example is of this same type of table cluster only. This cluster is so called (Index Clustered Table) because you are required to create an index on the "cluster" based on the "cluster key" i.e. the column which is common to both the tables and helpful in grouping the rows on blocks. This index, called "cluster index" consists of block addresses (not row ids unlike regular table/column indexes) of the blocks that contain the data about cluster key values i.e each cluster key value points to a block address and that same is resolved from the "cluster index" when a user fires a query referring to a cluster key value. Steps and general syntax for creating the Index Clustered Tables is -

Creating the cluster -

Create cluster empdept (deptno number(3)) size 1024;

Explanation : the cluster name is "empdept", the cluster key is deptno, size 1024 indicates that we expect about 1024 bytes of data to be associated with each cluster key value.

Creating the "cluster index" -

Create index empdeptidx on cluster empdept;

Explanation : the index is created on "cluster key" however it is not allowed to specify it here in its create statement because this command may pick it up from definition of the cluster (metadata).

Creating the tables in the cluster -

Create table dept (deptno number(3) constraint pk_dept primary key,

dname varchar2(10),

loc varchar2(10))

cluster empdept (deptno);

Create table emp (empno number(4) constraint pk_emp primary key,

ename varchar2(10),

........

deptno number(3) constraint fk_emp references dept (deptno))

cluster empdept (deptno);

Explanation : The non-bold portion of the create table statement is just a normal create table statement. The emboldened part of the statement tells Oracle to create these tables in cluster "empdept" and map the column "deptno" to the cluster key (also "deptno"). The column of the table which maps to the cluster key must have same datatype and precision specifications. Also remember that the constraint definitions are NOT mandatory.

Hash Clustered Tables -

The only difference from the index cluster is that the "cluster index" is replaced with a "hash function". The cluster key value is passed to a hash function (either internal or user defined) to produce a hash value in the output which points to the block location of the data corresponding to the value. Hence no index (and hence no index scan) is required, but with the limitation that, the hash function can take only one value at a time for input, that makes hash clusters unsuitable for range queries based on "cluster key" but suitable for "equality" queries and is more suitable for high cardinality (distinctly valued) of cluster key values. Steps and general syntax for creating "hash clustered tables" is -

Creating the cluster -

create cluster empdept (deptno number(3))

hashkeys 1000

hash is deptno

size 1024;

Explanation : The "hashkeys" indicate the number of possible distinct values of cluster key. "Hash is" is used to point to the hash function to be used - for internal hash function you have to specify simply the name of the hash key or omit this clause to default to it. The meaning of "size" is same as above.

Creating the tables -

There is no change in the syntax for creating the tables.

Why use "Clustered Tables"? -

Clusters help read "join" intensive queries on related tables with reduced I/O. However unless the size parameter and hashkeys parameter (in hash clusters) are not correctly estimated then the space utilization factor may be affected adversely also clusters slow down the DML.

Can user Cluster on Single table ? -

Yes, sometimes Hash Cluster may used for single table with the advantage of faster access in high cardinality cluster key (based on primary key). Such cluster may be called "Single Table" hash cluster and only requires "single table" clause to the above syntax. It then restricts you from creating a second table in the cluster. When there is one-to-one mapping between hash keys and data rows then the access is somewhat faster.

Dropping a Table Cluster -

You will have to drop all the tables first from the cluster using the normal "drop table" command or use "drop cluster <name> including tables" statement. Tables contain the data or empty does not matter for dropping the cluster.

Visit this if your are a Careerholic

What are Index Organized Tables (IOT)?

2012-04-03T13:57:00.000+05:30

Introduction -
Tables are what, RDBMS is all about. Tables provide a higher level abstraction layer for the logical storage of data, so much so as reference to the tablename is all required for specifying the source (read location) of data in the non-procedural SQL language. We all commonly know about the structure of the table simply to consist of columns and rows. But this basic table may not serve the purpose of different access patterns and data handling needs from optimization point of view. Hence sophisticated commercial RDBMS like Oracle may have some more types of tables than the "Regular" or "Normal" table; which by the way is called as "Heap Organized Table". We are going to look into these many different types of tables used in Oracle as our reference database system. There are about the same types of tables also used in database systems from other vendors, but they may be named differently or may use slightly different terminology. The visitors are free to post the links or comments to enrich the article in that respect.

Here is the list of different types of tables used in Oracle -

Heap Organized Tables
Index Organized Tables
Clustered Tables
Temporary Tables
External Tables
Object Tables
Nested Tables

Heap Organized Tables -

This is the basic or "normal" standard table type based on the concepts of Codd's Rules #1 and #2. "Heap" is a bunch of space used in somewhat random fashion, and that is how the rows are managed (stored) in these tables and hence the name. A deleted row will makes the space available to the new row and the new data (row) is stored in the first free space slot found in the table and not necessarily at the end or bottom of it. So there is no definite order ultimately observed in the rows stored in such table; not even the order in which the rows were inserted. Further, those rows will come out (against a query) in a equally random order and depending upon other options being used (like parallel query or different optimizer mode, etc.), may come out in a different order with the same query. If you want to receive the rows in any particular order based on values of column(s) then use "order by" clause around such column(s) in the query. Most of the tables in the database are of this type as they satisfy the most of the application needs.

While the "Heap Organization" of these tables comes as a boon in re-utilization of the space released by deleted rows, hence effective on space utilization, the drawback is that, when the query has search criteria not based on a key (primary key) or unique column will require the database to perform a full table scan for locating the rows. This could be a serious resource bottleneck for tables containing huge number of rows, until appropriate indexes are created on the relevant column(s). So heap organized tables almost always to be associated with indexes on the search relevant column(s) and hence consume space.

Index-Organized Tables -
Tables stored in an index structure are called as "Index-Organized Tables" (IOT). Unlike heap-organized tables, where data (rows) gets stored randomly at any available free space, the index-organized tables store the data sorted on the primary key. This organization does not make any change in the SQL though.

Organization of a table in the form of index however requires lot of work to manage and maintain (with a volume of transaction and resulting frequent re-organization) since B-Tree organization of index is rather a complex structure (in the form of an inverted tree). This one aspect therefor justifies why each table (anyway in RDBMS would have primary key) is not created as IOT. Heap tables on the other hand are easy to manage given a transaction volume even with associated indexes that might take more space than IOT (which may not have a separate index) but much less or close to no requirement for re-organization. In short, response to a transaction is much slower in IOT along with more resources consumption in reorganization as compared to heap table. So that is the reason for not creating every table as IOT.

The implementations that beg for an IOT are where the lookup is based mostly on primary key for single row at time (or sequential fetch for multiple rows) and the tables do not have much of transactions (particularly deletes and inserts) but only selects. For example - in a train reservation system there may be a lookup table with train number and train name and some other data like starting point and termination. The data hardly changes and the lookup is always based on train number which is unique (PK). Similarly zip code to state may also be a good candidate for IOT.

Creating Index-Organized Tables -

You may create an IOT using the following command structure -

create table sampleIOT (col1 number primary key,

col2 varchar2(20),

.....

coln <datatype>(<precision>))

organization index tablespace tbs1

pctthreshold 10

overflow tablespace tbs2);

Command Explanation -

Before we discuss about the different options used in the preceding "create table" statement there is one most important observation to be made - The primary key constraint is mandatory in the creation of the IOT since the index organization of IOT is based on primary key as aforesaid. You may not even disable the primary key constraint in such table. Now there are some key options used here -

organization index tells database system (Oracle) to create this table not as normal heap organized but an Index Organized.
pctthreshold specifies what % part of the row in IOT to be stored in "index block". This block contains the part of the row to go with primary key column (which includes those columns which are accessed frequently in the query)
overflow is the remaining part of the row which is made of columns which are not mentioned in the query frequently and to be treated as overflow and stored separately in specified separate tablespace (in overflow blocks).

What are Tablespaces?

2012-03-31T18:41:00.000+05:30

"Tablespace" is part of storage architecture of some of the commercial database systems like Oracle or DB2. Some of the database systems (like Microsoft SQL Server) are not known to use the concept of tablespace (I invite MS SQL Server or other database system users to elaborate on this in comments or provide links to a detail information if they know). Here we are considering Oracle as the model for learning the concept of tablespaces, which provides a layer of abstraction between physical and logical data. Rather, the cause of discussion about the tablespaces is that we are going to study the storage architecture of Oracle database system.

Oracle Storage Architecture -

We all know, there are two agencies at work when we come to storing and accessing the data in the databases - The OS (Operating System) and DBMS (Oracle). The calls are made for the data handling / manipulation from the DBMS in the form of (SQL) statements which requires a "view level" abstraction for referring to the data, whereas when it actually comes to read/write operation, it is the OS which plays that role, has to deal with "physical level" of the data storage. And thus the overall study of storage architecture would involve "Logical" and "Physical" perspective, the former from DBMS point of view and later from OS's side. Incidentally, we formally define the terms - "Physical" as something, the existence of which can be proved in terms of it occupying some space (area or volume) and "Logical" which exists in terms of an assumption or idea that does not have a shape.

As such the OS identifies files as the containers to hold or store the data. The files (datafiles) have an area and occupy a certain space on the storage device (hard drive), hence physical. "Tablespace" however is the logical storage as perceived by the DBMS and is well defined as "group of datafiles".

The concept of "group" itself is logical which exists in terms of its members (physical). For example your friend circle - each member is physical and the notion of belonging to the "circle" is there in the mind of each friend and has no physical existence. I have been insisting on this analogy for this would help you understand and digest the forthcoming discussion.

Why use Tablespaces? -

Having defined the tablespaces and accepting it as "group of datafiles", there are two main reasons, among others for using the tablespaces - Optimize the performance and provide virtually unlimited size of the segment (say Table) to grow.

Tablespace allows its data files to be located on different physical hard drives (do all you friends always move together?) and this arrangement comes as a great relief in reducing read/write contention in different parts of the segment in a multi-user environment, as the segment can spread within the imaginary boundary of the tablespace which contains (read groups) those data files. Simply put, a segment can not remain confined to a data file but to a tablespace. So the "chunks of spaces" (say extents) allocated to a segment scatter over all the data files belonging to a tablespace. For example consider a tablespace named "tbs1" has datafiles named "df1", "df2" and "df3" and they may be residing on hard drives "hd1", "hd2" and "hd3", then for some table "T" the extents will scatter over those datafiles (and hence on multiple drives) and as such will reduce the R/W contention. So for creating a tablespace of a size of say 500MB (just example), instead of creating a single file of that size to become part of this tablespace, have 5 files of 100MB each located on different drives. It has been observed some times that a lot of query optimization efforts go in vain if this basic tip about the tablespace layout is not followed.

Secondly, you may have virtually unlimited size of a segment (table) as it may no more remain confined to the maximum allowable size of the file. A segment can spread over all the files of a tablespace which may potentially have 1023 datafiles. Moreover partitioning of tables or indexes also allow them to be stored over multiple tablespaces with each partition being treated as a segment and hence can reside in different tablespace.

The other few advantages of tablespace are -

Database systems provide the facility of performing the backup / recovery at the tablespace level.
A tablespace may be taken offline for addressing any maintenance issues or reorganization of data files, so no need to stop entire database.
Tablespaces with "read only" status help reduce the backup time, size, resources and efforts as such tablespaces need not be backed up in every backup cycle (as they do not allow any change in the data in the segments stored in them).
Tablespaces allow tables / indexes with different access patterns to group them.

Tablespace Types -

Oracle has classified the tablespaces in different ways -

System & Non-System - This fundamental classification identifies the "system" tablespace as the first, mandatory and single tablespace with the same name as its type created in the database (created at the time of creation of the database, rather creating database summarily is creating a system tablespace at its minimum), which houses the data dictionary or database repository or catalog. Theoretically it is possible to have this only one tablespace in the database to also hold user's tables, but that is something discouraged in practice and hence a set of tablespaces created for holding users' data are called "non-system" tablespaces. There may be several of them in the database.
Permanent, Temporary and Undo- A "Permanent" tablespace is that contains "permanent" type of segments i.e. those which are created using an explicit "create ..." statement and dropped using an explicit "drop ..." statement. Such segments are for example Tables, Indexes, Clusters and Partitions which are created / dropped with an explicit command. The "Temporary" tablespaces are used to hold the "temporary" segments, which are used as scratchpad during a sorting event and only created implicitly whenever required as a SQL statement needs to sort (Query statements with distinct, group by or order by clauses). The "Undo" tablespace category was introduced in Oracle 9i to support automatic undo management feature i.e rollback segments and its contents and creation are managed implicitly by the system and also it supports Flashback Query (FBQ); a feature which allows to "view" the data as it was at particular time in past within the scope of "retention time". Prior to version 9i Oracle DBA's would store the rollback segments in some "permanent" type of tablespaces by creating them explicitly. The "temporary" tablespaces are said to contain "tempfiles" rather than data files, which have same format & structure as data files but only do not generate "redo" (history of changes to the data, required for future recovery of database) as the same is not required to be stored for a trivial event like sorting of the data that does not amount to any change to the database as such.
Dictionary Managed and Locally Managed - The later type was only introduced in 8i (version 8.1.5) as option and now in later versions used mostly as default type of tablespaces created in Oracle database. "Dictionary Managed" tablespaces had to "consult" the data dictionary for the account of allocation / de-allocation of the extents. This would involve some "recursive sql" to be fired (at data dictionary) to fetch and/or update the catalog and also would, in a volatile database, tend to serialize the operation causing overheads and delays in operations which particularly required extension of space (for segments). Another fact with the "Dictionary Managed" tablespaces was a marginal under-utilization of space due to "bubble free space" getting trapped in non-uniform sized extents of different segments. The problems are removed in "Locally Managed" tablespace by having a bitmap stored in headers of the datafiles to indicate vacancy or occupancy of the extents and also by uniformly sizing the extents or auto allocation feature(system will decide the size of extent) has eliminated the possibility of under utilization. However some DBAs have reservations about this new type as they opine that space management and growth of segments is their job as administrator and to that scope locally managed tablespaces are an encroachment on their role of space administration.

Data Files -

The discussion could be incomplete without considering the "Data Files" which are the "Physical" entities which make the tablespaces and it is here where the data is actually stored and read from. There is no free existence of the data file and it has to belong to some tablespace as Oracle refers to storage from its own stand point of view as we have discussed so far. Data files in Oracle are formatted according to Oracle's own block size as specified in db_block_size parameter. The standard allowable sizes are 2K, 4K, 8K, 16K, 32K. Normally only one of these block sizes should be used but as from Oracle version 9i onwards, Oracle allows multiple block sizes and hence there may be tablespaces with different block sizes created in latest of the databases mounted on these versions. These block sizes must be in multiples of OS block sizes as the data is ultimately read/written by the OS and for an abnormal size of Oracle block (not in multiple of OS block) there may be wastage of space and increased I/O for same amount of data.

How to determine the data block size -

The choice of the correct block size is a very critical decision. And there are certain guidelines to be followed to arrive at the most appropriate size.

In general Oracle recommends 2K & 4K sizes for an OLTP type of database, because the applications which run from them usually has fair mix of all types of data access statements and deal with small amount of data for each operation. The larger range 16K & 32K are recommended for DSS systems (Decision Support Systems aka data warehouses) as the access pattern is mostly in the form of "select" statements with aggregate functions which require bulk of data to be read in the memory. And of course for mixed workload environment 8K is normally preferred.

The block size should also be large enough to accommodate entire size of the rows and the rows need not to be fragmented. The phenomenon is called "row chaining" and has to be avoided to reduce the I/O or otherwise the fragments have to be read from different blocks.

Small range of blocks are also considered to be suitable for random access for small average row sizes, whereas for sequential access pattern for the same small sized rows, larger range of block sizes may be recommended.

You have to also consider the volatility of the databases and density of operations, since small block size with high density of operations may cause contention and hence may not be suitable for highly volatile data tables.

Larger block sizes are obviously more suitable for LOB type columns.

Conclusion -
The knowledge of the storage structure of the database is extremely crucial not only from the point of view of space management but also from performance optimization.

Transaction Locks

2012-03-29T14:37:00.000+05:30

In a previous post we discussed about why we need our databases to allow for concurrent transactions and the related problems and different transaction isolation levels, their outcome with respect to data integrity/consistency, as part of basics of "Transaction Concurrency". This post is supposed to take it further by considering how databases use data locks for providing such concurrency, and the implications of such locks, if any, for writing multi-user applications, multi-version read consistency and granularity to which locks may be implemented. Again wherever required, this discussion will refer to implementation in Oracle DBMS as an example.

Concurrency Control -

There are different methods database systems may use in combination to implement the concurrency; they include locking mechanisms to guarantee exclusive use of a table by a transaction, time-stamping methods that enable serialization of transactions, and the validation-based scheduling of transactions.

Locking methods prevent unserializable schedules by keeping away more than one transaction from accessing the same data elements. Time-stamping methods assign timestamps to each transaction and enforce serializability by ensuring that the transaction timestamps match the schedule for transactions. Validation methods maintain a record of transaction activity and the changed data is "validated" against the changed items of all currently active transactions to eliminate any unserialized schedules, before committing a transaction. Most of popular commercial databases like Oracle use a combination of available methods.

What are Locks ? -

Simply put, a "lock" is a mechanism used to regulate concurrent access to a "shared resource". The term "shared resource" has a very wide meaning - it not only includes the table rows but everything that should be required to be accessed concurrently at any level. There are two methods of locking - Pessimistic Locking and Optimistic Locking - depending upon how the applications would like to go about changing the data.

Pessimistic Locking -

"Pessimistic Locking" method allows for declaring an intention to "update" a row(s) from a set of preselected rows fetched without locking. The sequence of operations is thus -

Application simply queries a set of rows from a table (a plain select statement).
Then user picks a row(s) which he/she wants to update and then declares intent for update by some means (may be hitting some relevant button in the form interface).
The system re-queries the picked row(s) from the database with a difference this time that locks are requested on the row(s) for protection against update by other sessions.
An update statement actually updates the row(s).

There are three possible outcomes of this sequence of operations in "Pessimistic Locking" -

The re-query (step 3) as above is successful and now our user locks the rows (to prevent others from updating).
The re-query is blocked for other session has already started with a transaction on the same row(s) and system makes us wait for other session to finish.
If the row(s) have been changed (and committed) by other session meanwhile which our user re-queried, then no rows will show up (the data queried in step 1 is stale) and our user has to start over again from step 1 as listed above.

Thus using "Pessimistic Locking" our user could safely change the row(s), without any possibility of overwriting some other user's changes as it has been already verified in the session of our user that the data did not change meanwhile before requesting for the lock (in step 3 of sequence of operations).

Optimistic Locking -

"Optimistic Locking" method is based on the belief that data in the rows is not changing / changed by other session while our user is trying to update. The chances are that our user may or may not have luck to update the row(s) - in later of the cases our user will have to re-key the transaction.

Pessimistic v/s Optimistic -

The question is difficult to answer and will be answered by DBMS vendor as each have different approach. However wherever possible, to use "Pessimistic" locking mode would work better. It provides the users with the confidence that the data they are modifying are on their screen and hence currently "owned" by them. However the chances in this case may be a user may not further update and finish the transaction immediately after the locks have been obtained, which has to be taken care by killing such idle (non-performing) session after a reasonable time. Oracle favors the applications to use this approach as the locking of a row does not prevent any normal activity like reading of the same rows etc. The other databases are known to have NO favor with Pessimistic, but this needs to be authenticated by long term users of other databases in the comments to this post or posting a link to a similar blog post explaining the same.

Lock Escalation -

Concept of "Lock Escalation" relates to "granularity". Granularity is the level at which the data is locked in the database / tables. For example Oracle implements "row level" locking, which means that locks are placed on rows, NOT on page (block) or table. So in this case granularity is "row". "Lock Escalation" is the name given to decrease in the granularity. In some databases locks are considered to be scarce resource and hence considered as overhead to be avoided if they happen to be many in number. For example if for users operation say 100 rows are locked then the system may prefer to lock the entire table instead of each of 100 rows. So this is the case of "lock escalation".

Oracle DOES NOT use lock escalation. However it does use "lock conversion" or "lock promotion", the terms which are considered synonyms. This allows Oracle to make a "less restrictive" lock to "more restrictive". A case of "lock conversion" has been described below as an example -

You select a row(s) from a table with "FOR UPDATE" clause.
One "exclusive lock" is placed on the row while "row share table" lock is placed on the table to allow to perform any operation on the table except changing its structure (which will lock the entire table itself).
When the actual command to update the locked row(s) is issued Oracle will convert "row share table" lock to more restrictive "row exclusive table" lock.

Actually "lock escalation" is not considered as a good feature in database systems because it implies there is some inherent overhead in its locking mechanism.

In this post we have discussed about general theory behind the "locking" mechanism. Visit again for explanation on some more database related concepts. Coming back shortly.

Transaction Concurrency

2012-03-27T12:47:00.001+05:30

Introduction -

Consistent read / write operation while maximising the concurrent access is the key challenge in developing multi-user database-driven applications. The database management systems implement different schemes of "locking" and "concurrency" control to achieve this feat. It is very important to understand that there are as many ways to implement locking as there are different database management systems (from different vendors). While the entire scope of this subject being extremely vast and the mathematical models of those schemes are beyond the scope of not just one or two posts like this but may require an entire book to define, we shall have to remember that here we are not creating a database management system but only understanding an ideal implementation. Again what is ideal is really a big debatable question; we shall consider the implementation with reference to most widely used commercial database management system, Oracle where ever required.

Transaction -

A "transaction" is a logical unit of work consisting of one or more SQL statements and more importantly concerned with the changes to the data or database. It is traditionally defined in terms of its 4 properties described as an acronym - ACID

Atomicity - smallest unit of changes, which either has to happen completely or not happen at all and which does not have a subset with respect to following (next) property.
Consistency - it takes the data from one consistent state to next consistent state. The consistent state of the data is definable and justified in terms of validity of the data with respect to certain obvious, natural or mundane rules.
Isolation - it ensures that the effect of one transaction are not visible or potentially impact the other concurrent transaction, as if the each transaction is alone being carried out the entire database.
Durability - assures that the transactions once committed are saved permanently in the database and are not subject to loss under any condition (including cycles of failure and recovery).

What is concurrency control ? -

"Concurrency Control" are the collection of functions that the database provides in order to allow many people to access and modify data simultaneously. Concurrency obviously increase the throughput of a database management system, but it brings along its own set of problems as following -

Dirty Read - A "dirty read" is said to have occurred in a transaction when it reads an "updated" data from another transaction before it is committed. The reason this should not be allowed is because the data that has just been updated is in the intermediate phase of change and has the potential to be rolled back by the transaction who made the change; so in case of a reversal another transaction had already read the changed data which is really now incorrect.
Phantom Read - "Phantom read" causes new rows to appear in the result when the same query is run second time after a gap when it was run the first time. It differs from "non-repeatable read" (explained below) in the sense that this is not about the change in the data but rather more data satisfies the same criteria of selection of data as compared to first query.
Non-Repeatable Read - "Non-Repeatable Read" causes the same rows that were read in previous operation appear changed when queried after a time gap. It is possible even that the rows might disappear in the second read operation rather than just appear to have changed.
Lost Update - "Lost Update" problem causes a transaction to overwrite a pending transaction started earlier by another transaction and hence make it disappear (say Lost).

What are Transaction Isolation Levels ? -

The ANSI/ISO transaction standards use the term "Isolation Level" to indicate the extent to which the databases either permit or not permit top 3 of the 4 of concurrency phenomena (say outcome of a concurrent pair of transactions) as described above. The "Lost Update" is invariably NOT permitted in any of the standard isolation levels.

Read Uncommitted transaction isolation level is the most non-restrictive of all the transaction isolation levels. It permits all the three reads - Dirty Read, Phantom Read and Non-Repeatable Read. In any case the "Dirty Read" should not be allowed to happen. So this isolation level is not used in any of the known commercial database systems. However it is interesting to know that "Read Uncommitted" isolation is to provide a standards-based definition that caters for non-blocking reads. However, commercial databases like Oracle do not need "dirty read" since it has been designed to take a detour around the lock (the changed data is locked until the transaction is complete) and it can reconstruct it from a place called "Rollback Segments" (where original version of data is stored for facilitating rollback) and so Oracle can make consistent data available to the user without waiting for the transaction to commit.
Read Committed transaction isolation level allows a statement to see only the data that was committed prior to its commencement. There are NO dirty reads allowed but non-repeatable reads and phantom read are. "Read Committed" isolation level is the most widely used transaction isolation level in commercial databases. However there is NO reason to perceive that all is well with "Read Committed" isolation level. It will still require non-blocking reads as designed in Oracle (detour around the lock... as said above) for transaction to produce acceptable output.
Repeatable Read transaction isolation level guarantees read consistency i.e. a transaction that reads the data twice from a table at two different points in time will find the same value each time. It only allows "phantom read". You avoid both - the "dirty read" and "non-repeatable read" problems through this level of isolation. Some databases except Oracle achieve "Repeatable Reads" via the use of row level shared "read" locks, which prevents other sessions from modifying the data that a user has read. This is in addition to the fact that, in these systems, writers of data will bock readers of the data. This of course decreases concurrency. A "Repeatable Read" may be used optionally using "SELECT FOR UPDATE".
Serializable transaction isolation level is the most restrictive level of transaction isolation (NO dirty, non-repeatable and phantom reads allowed) and obviously to provide the highest degree of isolation. "Serializable transaction" isolation level only means that each transaction executes as if it is the only transaction executing in the database at that point of time. It does NOT imply that there is some serial ordering of the transactions. Rather it is based on the concept of "Schedule". A "schedule" is a sequence of operations from one or more transactions. If all the transactions executed serially, one after another, the schedule will also be "serial". If you can produce a schedule that is equivalent in its effect to a serial schedule, even though it may be derived from a set of concurrent transactions, is called a "serializable schedule". In short "serializable schedule" consists of a series of intermingled database operations drawn from several transactions, and the final outcome of a serializable sequence is a consistent database. Database systems like Oracle allows to use "Serializable isolation level" as an option.

Read Committed v/s Serializable level of isolation -

"Read committed" isolation level provides transaction consistency on a per-statement basis which means that non-repeatable read and phantom read problem is present in this level of isolation. Ideally we require transaction level consistency. Readers will not block writers and vice-versa. So we can consider that "read committed" isolation level only provides a good trade off between data consistency and data concurrency and hence suitable for standard OLTP applications, which consist of high-volume, concurrent, short-lived transactions, with few transactions likely to conflict with each other (and close to NO repeatation of queries), but provides better performance. A "serializable isolation level" is suited for databases where multiple consistent queries need to be issued. The overall throughput is much less this being a blocking isolation. As such serializable mode of concurrency is more appropriate for databases with mostly read-only transactions that run for a long time (OLAP systems).

Read-Only transaction -

Oracle provides a variation of the "serializable isolation level" in the form of "Read-Only transaction" which permits a transaction to only "see" the data that was committed before the transaction started and does not allow any changes to be done once started. The only difference between "read-only" and "serializable" is that, the later also allows the changes to be done. Read-only transactions are intended to support reporting needs, where the contents of the report needs to be consistent with respect to a single point in time (when the generation of report is started). If a certain database system does not implement "Read-Only" transactions then they use "Repeatable Read" isolation level and suffer the associated effect of shared "read" lock as said before.

So to conclude, we had discussion on the transaction concurrency in this post. The concurrency has to be implemented through different "Locking" schemes. Click Here to continue reading on locking.

Distributed Database System - Principles

2012-03-23T19:53:00.000+05:30

Please spend a moment to read the previous post "Distributed Database System - Introduction" as the text below may contain some references to it.

Continuing from the last post where we underlined the need for the Distributed Database Management Systems (DDBMS) for the today's world of universal dependence on information systems in which all sorts of people; the company's management, employees, customers, shareholders, franchisees, channel partners, including general public (potential customers / investors) need to have a quick access to the company's data; we now need to understand the technology that works behind the DDBMS.

However, since this blog is entitled "Cool Database Fundas" and the target audience includes the curious apart from the professionals, I have decided to drive through using an example which I hope will make the various technical terms understand easily for some even technically challenged readers.

Our example Distributed Database -

We have to implement a distributed database system for a fictitious company having its offices at Los Angeles, Singapore, New York (HQ), Paris and Tokyo. We have six large tables in our database namely A, B, C, D, E, and F and the motivation behind our client asking us to implement DDBMS solution is the response time has to be reasonably small apart from ensuring maximum availability, reducing the network traffic and providing the site autonomy.

Centralized Database approach -

Though our aim here is to build a DDB, we start our approach with a non-distributed centralized database at just one site say New York, it being the Head Quarter of the company and everybody else may access this data over the net. Though there are lot of problems arising out of concurrency implementation, making joins on the tables, security implementation etc. NOT showing up in this setup, the major issue being that if this single site fails then the entire system will come to stand still and of course there will be huge network traffic involved from other locations to the centralized database and no site autonomy for data is applicable in this approach.

Distribution based on data access frequency -

In our next approach, it makes sense to locate the tables at the sites where they are more frequently accessed than the other sites. Accordingly we are locating our tables - A & B at New York, C at Singapore, D & E at Tokyo, F at Paris and no tables at Los Angeles. This distribution has brought about "local autonomy" over the data, each site taking responsibility for its security, backup and recovery and concurrency control (for local transactions). However, the problems of centralized database don't seem to have been solved, rather escalated.

The system is still prone to single point failure - for example if the Paris site is down the table F is not available to anybody (so also same case with the other tables). Secondly, the problem may arise with joins if the query requires tables located at different sites. With the dispersed data the security is also vulnerable to a little extent as compared to when all the tables were in centralized database.

Distribution with replication -

"Replication" is the name given to the practice of maintaining synchronized copies of an object (say table) at multiple locations (say sites). We can have two considerations while replicating the tables -

A table may be best replicated to another site where it is frequently required to minimize the network traffic.
A table may be replicated to a site where it is convenient to join it with the table on that site when users use join queries again not only to minimize the network traffic than otherwise if the tables were to be stored on different sites, but also remove the complexity in join when tables are dispersed.

Of course now we get advantage of replication in availability; if a table is replicated at two or more sites and one of those sites goes down, still nobody (users) is affected since the table is accessible from other site(s).

The replication however had introduced another problem; the problem of concurrency control and data consistency across multiple copies of the tables. The security risk is also higher than the preceding case of "distribution based on data access frequency".

If we decide to have only one another copy of each table at a different site with the entire six tables at New York (being HQ), then the security and concurrency exposures are limited (as compared to replicating a table at more than one location). So here we have all tables at New York (A, B, C, D, E). Then we have B & F at Paris, D at Singapore, A & E at Los Angeles, and C at Tokyo. There are two bottlenecks in this arrangement -

If a table is accessed heavily at more than two sites then we violate our own premise. (security and concurrency control exposure will escalate).
The centralized place where all the tables are residing (New York) will be heavily loaded for all join requests will have to be handled there along with many of the other access requests.

So going by the considerations of locating the replications (earlier two points) and avoid the bottlenecks (the just preceding two points) we may arrive at a solution - We may have tables A, B & F at New York, D & E at Singapore, C & D at Los Angeles, A & F at Tokyo and B, D & E at Paris. Here we are considering table C is only single (not replicated as we think the table is not very important and infrequently used but only mostly accessed at Los Angeles. Also this is a case to show that there is a flexibility that not each of the tables must be replicated).

Concurrency Control with Two Phase Commit -

"Concurrency" is the name given to allowing two more users to perform transactions concurrently on the database rows without violating the integrity and consistency of the data. Databases use transaction locks to limit the concurrent operations by many different users from causing inconsistency in the data. The implementation of the concurrency is much more difficult in the replicated environment such as above than dealing with all the tables in a single server and only accessed from there. For example if a user at New York updates a particular value in a record of table B while at the same time another user updates the very same value in the very same record in Paris may result into an inconsistency according to the definition of transaction.

It is possible to implement the transmission of update on a table to its copies in different ways, particularly if the nature of the data and of the applications that use it can tolerate delay (backlash) in such update. Those methods are said to be "asynchronous". It could be either done by sending the message from site of actual update to the remote site where copy of the table exists and apply it there right away, or alternatively choosing one of the sites to accumulate all the updates (for all the tables) and transmission of changes may be scheduled. In a third "asynchronous" approach, each table may have one of the sites declared as "dominant" (for that table) to which all of the updates may be sent and from there transmit it to other copies of the table on scheduled basis.

However some popular commercial DBMS systems like Oracle use Two Phase Commit (2PC) process which is a "synchronous" algorithm. The "2PC" consists of two phases namely "Prepare Phase" and "Commit" phase. The mechanism uses a special "log" file to hold the update temporarily at all the sites. When an update is initialized at one site in a certain table (say D at Singapore) then the actual update is first written temporarily to the log file at every site (say Los Angeles & Paris) and NOT the copies of the table anywhere at this stage. The "Prepare Phase" first confirms with the remote locations (LA & Paris) having the copies of a table (D) under update at one site (update initializing site, Singapore) if they are in an operating condition and if they could lock the respective data to be updated (Oracle implements row level locking) in their copy of the table (D at LA & Paris) and if any of this condition is not met then either the transaction waits until favorable conditions are reached or transaction is aborted or otherwise (if conditions are met) the data makes way to the copies of the tables at all sites from the respective log files; which is part of the "commit" phase. So in this way all the copies of the table either stay at the original state if the update proceeds to fail at any location or get updated "synchronously".

Distributed Joins -
"Distributed Joins" involve tables at discreet locations required to be joined to satisfy a "join query" (query that required data being combined from two or more tables in a join condition). Various permutations and combinations may emerge for transmitting the data from one location to another or even third depending upon -

if or not the location from where the request for join originated has copy of any of the tables involved in the join.
the location where the join may be actually performed, transmitting the final or intermediate result.

A DDBMS system comes with a query optimizer which works hand-in-hand with relational query optimizer to determine the optimal way of performing the join operation in consideration to the following -

The number and size of the records from each table involved in the join.
The distances and costs of transmitting the records from one location to another to execute the join.
The distance and cost of shipping the result of the join back to the location that initialized the join query.

Partitioning or Fragmentation -

When a table is divided then it is called as "partitioning" or "Fragmenting". There are two basic ways in which the tables in the DDBMS could be fragmented - Horizontally and Vertically. There may be third type that is hybrid of these both.

In the "horizontal partitioning" a table may be split in such a way that each site may contain some records. Essentially the structure of the table at each site is exactly the same. For example, for our fictitious company we may have an employee table horizontally at each location to store the data of employees working in the vicinity of that location. So the structure of the "employee" table will be identical at every site but only the records. This arrangement makes sense in such case when a site has to deal more with particularly their employees and say location like New York (HQ) requires to access the data of all the employees of the enterprise and so it has to be fetched from all the different locations and had to be put together (Union).

In the "vertical partitioning" we divide the columns of a table among several sites. However each such fragment of table containing a subset of columns of the full large table must include a primary key column of the full large table and rows in each such fragment are coordinated through the common value of primary key. This arrangement can make sense when different sites are responsible for processing different columns (attribute functions) of same entity. However in such case a multi-site join will have to processed if some site requires all the data of each entity.

Distributed Directory -

The notion of "location transparency" in DDBMS comes from that when a query is fired then the system simply knows the location of different tables, their partitions, columns (or objects in general and their privilege sets etc.) and the other metadata required to satisfy the query. The set of system tables (data dictionary, directory or catalog) provides this knowledge to the system. However the question of location of the directory is as complex as the distributed data itself in the DDBMS and there are n number of possibilities. However in most cases maintaining a "synchronously updatabase" copy of the directory at each location is considered to be the best option.

So here we have walked through the "principles" of the DDBMS with a example and practical approach, however there is a systematic mathematical treatment to each of the concept discussed here.

Next time we meet with a new topic and it will be equally interesting one. Keep coming back.