What are Surrogate Keys, Primary Keys and Candidate keys? Where its used?

A Surrogate key is any column or set of columns that can be declared as the primary key instead of a real or natural key. Sometimes there can be several natural keys that could be declared as the primary key, and these are all called candidate keys. So we can call a  surrogate key is a candidate key.

A Surrogate key is the alternate of primary key that allows duplication of datas/records. It is an immutable set of attributes that uniquely identify a row that were generated specifically and solely to identify this row which is not in case of natural key or primary key.

A table could actually have more than one surrogate keys, although this would be unusual. A natural key is an immutable set of attributes that uniquely identify a row that occur naturally with the row itself. The most common type of surrogate key is an incrementing integer, such as an auto_increment column in MySQL, or a sequence in Oracle, or an identity column in SQL Server.

Primary key and Surrogate key are same but surrogate key is a system generated numeric or integer value to identify each row uniquely, it has a define incremental value for each row in a table.

Surrogate key does not have any business importance for the value it holds but primary key has a significant business value.

OLTP Databases are called as of  Normalised Form  whereas  Data warehouses - DWHs  are called as of De-normalised form as DWH is used to maintain the historic data for analyzing. To remain de-normalised, duplication is allowed. When data inserting in DWH, Surrogate key a new column named serial number is introduced to allow duplication.

A Surrogate key in a data warehouse is more than just a substitute for a natural key. In a data warehouse, a surrogate key is a necessary generalization of the natural production key and is one of the basic elements of data warehouse design. Surrogate Key is the solution for critical column problems.

Ex.  A customer purchases different items from stores at different locations. Here, we have to maintain historical data, by using surrogate key which introduces the row in the data warehouse to maintain historical data. Another example of its, a single mobile number is used by other person if it is not in use for more than one year, it is possible just because of this surrogate key.

Differences between B*Tree and Bitmapped Indexes.

B*Tree indexes:

• Where we need to maintain the sort order of the data, making it easy to look up range data.
• Required to use multicolumn indexes, we can use the leading edge columns to resolve a query, even if that query doesn't reference all columns of the index.
• As per its behavior, its automatically stay balanced.
• Relatively constant performance of any query.
• Can also specify reverse and unique
• Recommended for OLTP databases.

Bitmapped indexes:

• Use them to index columns with that contain a relatively small number of distinct values.
• Very compact and using lesser space.
• Designed for query intensive databases.
• Not good for range scans.
• Are available only in Enterprise Edition, in relational databases only.

What are NoSQL Databases? Why we use NoSQL Databases?

The Not Only SQL or NoSQL database is a way on which it works towards managing data as well as database design, largely suitable for huge sets of distributed data.

Since newly introduced concept of Big Data and Cloud, It consists of a number of technologies and architectures that deliver lesser data performance issues and scalability that is not performed by using traditional relational databases. Mainly used when companies and enterprises need to access and analyze large amounts of unstructured data or the data stored in multiple virtual servers in the cloud.

There is no specific definition of what NoSQL is, but we can describe it as:

•          Not using the relational model
•          Running well on clusters
•          Mostly open-source
•          Built for the 21st century web estates
•          Schema-less database

There are mainly four types of NoSQL databases – data stores in the market:

Key Value Databases:

Key value databases are the uncomplicated data stores. In a data store, we can either put in a value for a specific key, or get a value from a specific key, or delete a specific key. The key values are the primary access which gives ease of  scalability and great performance.

Document Databases:

According to its name – Documents are focused in such database. The documents that are stored and received from the data stores can be in BSON, XML, JSON, etc. the documents are usually similar to each other and are in a hierarchical tree kind of data structure that are self-describing and consists of scalar values, maps, and collections.

Column family data stores:

Column family data store having rows and a number of columns that are associated with a row key. This is a bunch of data that is related and can be accessed together.

Graph databases:

Graph database comes the storage of nodes or entities and the relationship between these nodes.

Why we Choose NoSQL Databases?

•          Distributed Computing
•          Lower cost because its open source
•          High scalability
•          Schema flexibility
•          Un structured data or semi-structured data
•          No much complex relationships.

Difference between B*Tree and Bitmap Index in Oracle.

B*Tree index & Bitmap index is very different, but in functionally they are identical while retrieving rows faster and avoiding a full-table scan. These indexes are used mainly for performance tuning, which turns in retrieving data quite fast.

B*Tree Index	Bitmap Index
A type of index that uses a balanced tree structure for efficient record retrieval.	A type of index that uses a string of bits to quickly locate rows in a table.
B*Tree index stores key data in ascending or descending order and very useful for OLTP.	Bitmap indexes are normally used to index low cardinality columns in a data warehouse environment, useful for Decision Support System.
A B*Tree index is used most when the cardinality is high and it is a default Index type.	We can use Bitmap index where there are a lot of duplicate data in the indexed column (for example Gender).
A B*Tree index does not includes any specific keyword while creating it. create index person_region on person (region);	A bitmap index includes the "bitmap" keyword while creating it. create bitmap index person_region on person (region);
B*Tree index is an index that is created on columns that contain very unique values.	A bitmap index generally consumes less space stored in db as a highly compressed index format.
B*Tree index is very useful for speeding searches in OLTP applications, when you are working with very small data sets at a time, most queries filter by ID.	A bitmap index used on a table having low insert./update/delete (DML)  activity. Updating a bitmap index takes a lot of resources, and bitmapped indexes are best for largely read-only tables and tables that are batch updated nightly.
A B*Tree index has index nodes (based on data block size), it a tree form:	Internally, a bitmap index consists of 4 columns, first - the index value, the second and third column consisting of the start and last rowid of the table, and the fourth column consisting of the bitmap.
A B*Tree index stores the index value and the physical rowid of the row. The index values are arranged in the form of leaves.	A bitmap index looks like this, a two-dimensional array with zero and one (bit) values.
In a B*Tree Index all the lower values are placed on the left side & Higher Values on the Right Side.	A bitmap index can cover a few thousand rows in a single block. When you are updating the indexed column, Oracle takes an exclusive lock on the index slot for the duration of the transaction.
A regular B*Tree index covers maybe a few hundred table rows in a single index leaf block. In a regular B*Tree index, this affects just the actual row being updated because each slot in the index covers only a single row. It is made of branch nodes and leaf nodes. Branch nodes holds prefix key value along with the link to the leaf node. The leaf node in turn contains the indexed value and rowed.	In a bit mapped index each slot covers a range of rowid's, so more table rows are locked. The chances of two different processes colliding (and deadlocking) when they are doing bulk updates are increased.
B*Tree index is good choice for most uses: maintain the sort order of the data, making it easy to look up range data multicolumn indexes: you can use the leading edge columns to resolve a query, even if that query doesn't reference all columns in the index they automatically stay balanced performance remains relatively constant can also specify reverse and unique They are very fast when you are selecting just a small very subset of the index data. They work better when you have a lot of distinct indexed values. Combining several B*Tree indexes can be done, but simpler approaches are often more efficient. They are not useful when there are few distinct values for the indexed data, or when you want to get a large subset of the data. Each B*Tree index impose a small penalty when inserting/updating values on the indexed table. This can be a problem if you have a lot of indexes in a very busy table.	A Bitmap index used in below scenarios: Are a more specialized index variant: Use them to index columns with that contain a relatively small number of distinct values They are compact, saving space Were designed for query intensive databases, so not recommended for OLTP databases Not good for range scans Are available only in Enterprise Edition. Mostly created on Transaction Tables on which the data is continuously being added. They are very inefficient when inserting/updating values. They encode indexed values as bitmaps and so are very spaces efficient. DB optimizers can combine several bitmap indexed very easily, this allows for efficient execution of complex filters in queries. Mostly used in data warehouse applications, where the database is read only except for the ETL processes and you usually need to execute complex queries against a star schema, where bitmap indexes can speed up filtering based on conditions in your dimension tables, which do not usually have too many distinct values. Bitmap indexes are not appropriate for tables that have lots of single row DML operations (inserts) and especially concurrent single row DML operations. Deadlock situations are the result of concurrent inserts as the following example shows: Open two windows, one for Session 1 and one for Session 2.

SQL execution steps in Oracle.

Below are the steps which are involved in when a SQL statement executes in Oracle.
Syntax Checking Phase

• Whether the all keywords present "select . . . from", etc . .
• DB semantic check against the data dictionary of the database.
• Whether the used table names spelled correctly, and present in db dictionary.

Parsing Phase

• Parse call does not return an error if the statement is not syntactically correct.
• Creation of all possible ways of query execution with different costs as per CBO and identifying and generation of query execution plan with lowest cost.
• This step uses database client/server cache, SGA, db datafiles, etc. all possible places from where data transition occurs.
• This is where the table - tablespace - datafile translation occurs.
• Once the execution plan is created, it is stored in Shared pool - library cache to facilitate re-execution. There are two types of parses:

Hard parse :

• A new SQL statement must be parsed from scratch. 
• Parsing can be a very expensive operation that takes a lot of resources to execute, when there is no previously parsed version of the SQL to reuse.

Soft parse :

• A reused SQL statement where the only unique feature are host variables.
• The best-case scenario is a parse to execute ratio of 100% which would indicates that the application if fully using bind or host variables in it.
• It’s like parses SQL once and executes many times.

Bind Phase

• In this phase, Binding lowest costs execution plan in db.
• Once the execution plan is syntactically created, Oracle gathers the parameters from the client/application for the execution. It makes the addresses of the program/host/bind variables are known to Oracle.

Execute Phase

• During the execute phase, Oracle executes the statement, reports any possible errors, and if everything normal, displays the result set. Unless the SQL statement being executed successfully, this is the last step of the  query execution.

Define Phase

• The Oracle OCI interface defines addresses of the output variables known to the Oracle process in order to make it possible to the fetch call to know where to put the output variables.

Fetch Phase

• During the fetch phase, Oracle displays the resultset to the application.

Once more, the define and fetch phases are relevant for queries only.The Oracle OCI interface module contain calls to facilitate each of those phases.

Unix - AWK Command by examples. Part 2

In this part, we will learn on AWK Built in Variables:

FS - Input field separator variable:

By default, AWK assumes that fields in a file are separated by space.
If the fields in the file are separated by any other column separator, we can use the FS variable for that.

Example 5. awk 'BEGIN {FS=":"} {print $2}' test_file
OR
awk -F: '{print $2} test_file

output will be as below.

39 AAAA
15 BBBB
39 EEEE
39 GGGG

We can use -F option for to change column separator in command to display output.

awk -F: '{if ($2 == "") print $1 ": no password!"}'

We can easily move from one column separator to another column separator using awk.

#!/bin/awk -f
{
if ($1 == "#START") {
FS=":";
} else if ($1 == "#STOP") {
FS=" ";
} else {
#print the Roman number in column 3
print $3
}

}


OFS - Output field separator variable: 

Example 6. awk '{print $4,$5}' test_file

When we use print statement in awk, it uses default column seperator while displaying result.

The output of this command will be
unixrun 0
unixrun 175
unixrun 430
unixrun 428

We can change this default column seperator by using the OFS variable as

awk 'BEGIN {OFS=":"} {print $4,$5}' test_file

output will be as below.

unixrun:0
unixrun:175
unixrun:430
unixrun:428

If I run same command in this way, it will not work same as earlier, Here no column separator showing in output.

awk 'BEGIN {OFS=":"} {print $4$5}' test_file

unixrun0
unixrun175
unixrun430
unixrun428

NF - Number of fields variable:

The NF used to get the number of fields in line of a file.

Example 7. awk '{print NF}' test_file

This will display the number of columns in each row, output as below.

9
9
9
9

NR - number of records variable: 

The NR used to get the line number or no of lines in the file.

Example 8. awk '{print NR}' test_file

This will display the line numbers from 1.

1

Example 9. awk 'END {print NR}' test_file

This will display the total number of lines in the file, here output will be.

4

FILENAME - The Current Filename Variable

The last variable known to regular AWK is "FILENAME", which tells you the name of the file.

#!/bin/awk -f
# reports which file is being read
BEGIN {
f="";
}
{ if (f != FILENAME) {
print "reading", FILENAME;
f=FILENAME;
}
print;
}

Unix - AWK Command by examples. Part 1

AWK command is one of the most powerful tools in Unix used for processing the rows and columns in a file. AWK has built in string functions and associative arrays and supports all functionalities of C language. We can also convert AWK scripts into Perl scripts using a2p utility, an added advantage of AWK.

awk 'BEGIN {start_action} {action} END {stop_action}' file

The actions in the begin block are performed before processing the file.
The actions in the end block are performed after processing the file.
The rest of the actions are performed while processing the file.

Create a file test_file with the following data. This file can be easily created using the output of ls -l.
-rw-rw-rw- 1 unixrun unixrun   0 Nov  26 21:39 AAAA
-rw-rw-rw- 1 unixrun unixrun 175 Nov  26 21:15 BBBB
-rw-rw-rw- 1 unixrun unixrun 430 Nov  26 21:39 EEEE
-rw-rw-rw- 1 unixrun unixrun 428 Nov  26 21:39 GGGG

Example 1. awk '{print $1}' test_file

$1, $2, $3... represents the first, second, third column,.. in a row respectively.
Here, awk command will print the first column in each row as shown below.

The variable "$0" refers to the entire line that AWK reads in. That is, if you had nine fields in a line,
print $0
is similar to
print $1, $2, $3, $4, $5, $6, $7, $8, $9

-rw-rw-rw-
-rw-rw-rw-
-rw-rw-rw-
-rw-rw-rw-

To print the 2th and 5th columns in a file use awk '{print $2,$5}' test_file

Example 2. awk 'BEGIN {cnt=0} {cnt=cnt+5+$3} END {print cnt}' test_file

This will prints the sum of the value as  (value of 3th column + 5 ).
In the Begin block the cnt variable is assigned with value 0.
In the next block the v (value of 3rd column + 5) is added to cnt variable.
In addition of this, same scenario is repeated for no of rows in a file.
When all the rows are processed the cnt variable will hold the sum of the values as per our formula (value of 3rd column + 5).
Finally, the output printed in the End block.

We can perform the above steps in another way also - by creating command file.

vi summation.awk
#!/usr/bin/awk -f
BEGIN {cnt=0}
{cnt=cnt+$3+5}
END {print cnt}
:wq

This will create a file named as summation.awk, it will be executed by using awk command as below:

awk -f summation.awk test_file.

This will run the script in summation.awk file and displays the sum as per our formula (sum of the 3rd column + 5 ) in the test_file.

Example 3. awk '{ if($5 == "time") print $0;}' test_file

Above command is used for to find a particular string in a file in a specific column.
Here, it searches the string "time" in the 5th column  of the file and prints entire line in output.

-rw-r--r-- 1 unixrun unixrun 43 Nov  26 21:39 time

Example 4. awk 'BEGIN { for(i=1;i<=4;i++) print "Cube of", i, "is",i*i*i; }'

This will print the Cube of numbers from 1 to 4, The output of the command is

Cube of 1 is 1
Cube of 2 is 8
Cube of 3 is 27
Cube of 4 is 64