Interesting Database SQL Concept Question, Give it a try??

#TGIF
Today’s will try to walk you through a Database concept questions and hopefully you will learn something new. (Obviously if you are not aware of it.!)

Suppose we have below scenarios,
In Database Session 1 :
–> we are creating a table
–> adding a row
–> Drop a table that does not exists

In Database session 2 :
–> Query newly created table in session 1 and check row count.

Database Session 1 :

DROP TABLE TAB;
CREATE TABLE TAB
AS
SELECT 1 COL1 FROM DUAL;
INSERT INTO TAB VALUES(2); --inserting a values into table.


DROP TABLE TABLE_DOES_NOT_EXITS; --Dropping a table that does not exists in database.

We are dropping a table which does not exists in Database. Hence Drop command would failed with below exception.
SQL Error: ORA-00942: table or view does not exist

Database Session 2 :
In Another session we are trying to query table TAB and check total number of rows.
select count(*) from TAB;
Please note we have not perform any commit in Database Session 1.

So the questions is When we Query Select count(*) from TAB; in Database Session 2 what will be row count.

Please update your answer within below Polls.

Will update my answer on same with a reason.

Thanks a lot for voting, please find Voting results as of today(12th Dec 2016).We can also View result in poll itself.
poll_result

Correct answer for SQL concept questions is “TWO”.

Let reiterate with steps in DB Session 1, to understand it better.

Step 1 :
DROP TABLE TAB;
Table does not exists, hence it would give exceptions.

Step 2 :
CREATE TABLE TAB
AS
SELECT 1 COL1 FROM DUAL;

We are using CTAS (Create table as ) for creating new table TAB with a row.
Create table is Auto Commit statement, hence table will have one row and it persist for each and every session for database.
So after these statement, every session in database will show record count as One.

Step 3 :
INSERT INTO TAB VALUES(2);

We are inserting a record in newly created table and no Commit statement is executed.
Hence newly inserted rows won’t be visible in any other session other then current one.

Step 4 :
DROP TABLE TABLE_DOES_NOT_EXITS;
These steps is very Tricky!

We all know it will failed as table we are trying to drop does not exists in DB.
But Statement we are trying to execute is an Data Definition Language (DDL – CREATE/ALTER/DROP/TRUNCATE).
For any DDL in oracle, We perform “Implicit Two Commit“. One before executing Statement and one after executing statement.
So though Statement had failed, but First Implicit commit before executing failed statement cause data to get commit within session.
Hence though we did not have any explicit commit in Database session 1, but first implicit commit of DDL cause data to Commit and visible for each and every session in database.

Key Learning :
DDL operate as Two phase commit statement, one before executing or parsing other after successful execution.

Hope it was interesting learning, please share it with others.!

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Helper SQL, Oracle Internal and tagged , , , , , . Bookmark the permalink.

2 Responses to Interesting Database SQL Concept Question, Give it a try??

  1. Anonymous says:

    Oracle issues commit first, after that execute DDL. So irrespective of success or failure of DDL commit will happen.

    Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s