Tuesday, February 16, 2016

What are big data tools used currently in market?

Big data is a broad term for data sets so large or complex that traditional data processing applications are inadequate.

Traditional SQL databases used for storing and retrieving data. It all depends on the use cases. In contrast, nonSQL databases are in-memory caches, full-text search engines, real-time streaming, graph databases, etc.

Cassandra: An open source distributed database management system originally developed at Facebook and designed to handle large amounts of data across many commodity servers, providing high availability with no single point of failure.

Redis: An open source (BSD licensed), in-memory data structure store, used as database, cache and message broker.

CouchBase: An open-source, distributed NoSQL document-oriented database that is optimized for interactive applications.

CouchDB: An open-source document-oriented NoSQL database that uses JSON to store data.

MongoDB: A popular, a cross-platform document-oriented database.

Elasticsearch: A distributed RESTful search engine built for the cloud.

Hazelcast: An open source in-memory data grid based on Java.
EHCache : A widely used open source Java distributed cache for general purpose caching, Java EE and light-weight containers.
Hadoop : An open-source software framework written in Java for distributed storage and distributed processing of very large data sets on computer clusters built from commodity hardware.
HBase : An open source, non-relational, distributed database modeled after Google's BigTable, written in Java and runs on top of HDFS.

Spark Spark : An open source cluster computing framework.

Memcached : A general-purpose distributed memory caching system.

Apache Hive : It provides an SQL-like layer on top of Hadoop.

Apache Kafka : A high-throughput, distributed, publish-subscribe messaging system originally developed at LinkedIn.

Akka: A toolkit and runtime for building highly concurrent, distributed, and resilient message-driven applications on the JVM.

Neo4j: An open-source graph database implemented in Java.

Solr: An open source enterprise search platform, written in Java, from the Apache Lucene project.

Apache Storm: An open source distributed realtime computation system.

Oracle Coherence: An in-memory data grid solution that enables organizations to predictably scale mission-critical applications by providing fast access to frequently used data.

Titan: A scalable graph database optimized for storing and querying graphs containing hundreds of billions of vertices and edges distributed across a multi-machine cluster.

Amazon DynamoDB: A fast and flexible fully managed NoSQL database service for all applications that need consistent, single-digit millisecond latency at any scale.

Amazon Kinesis: A platform for streaming data real-time on AWS.

Datomic: A fully transactional, cloud-ready, distributed database written in Clojure. 

Monday, February 8, 2016

What is big file tablespace in oracle?

A big file tablespace is a single tablespace can have  very large  - up to 4G blocks in a data file. As compare to traditional tablespaces which can contain multiple data files, but the files cannot be as large.

Some benefits of its are as under:

  • A Bigfile tablespace with 8K/16K/32K blocks can contain a 32/64/128 TB data file. 
  • The maximum number of datafiles in an Oracle Database is limited to Max. 64K files. 
  • A Bigfile tablespace can significantly improve the storage capacity of the Database.
  • Bigfile tablespaces reduce the number of data files for a database, with this DB_FILES initialization parameter and MAXDATAFILES parameter of the CREATE DATABASE and CREATE CONTROLFILE statements can be adjusted by DBA to reduce the amount of SGA space required for data files and size of the control file.
  • Bigfile tablespaces simplify database management by providing data file transparency instead of using multiple data files.
  • It supports only for locally managed tablespaces with automatic segment space management, and it’s not used for locally managed undo tablespaces, temporary tablespaces and SYSTEM tablespace.
  • It is advised to use big file tablespaces in a database where Automatic Storage Management (ASM) configured or other logical volume managers that support striping or RAID, and dynamically extensible logical volumes.
  • Avoid creating big file tablespaces on a database that is not supporting striping because of negative implications for parallel query execution and RMAN backup parallelization.


If the default tablespace type is specified to BIGFILE at a time of database creation, you need not specify the keyword BIGFILE in the CREATE TABLESPACE statement. A big file tablespace is created by default.

CREATE BIGFILE TABLESPACE tbs_bigtable_ex1
DATAFILE '/u02/oracle/data/tbsbig01.dbf' SIZE 80G;

If the default tablespace type is specified to BIGFILE at a time of database creation, but you want to create a traditional - small file tablespace, then uses a CREATE SMALLFILE TABLESPACE statement to override the default tablespace type which creating database.

CREATE SMALLFILE TABLESPACE tbs_smalltable_ex1
DATAFILE '/u02/oracle/data/tbssmall01.dbf' SIZE 80G;

Friday, February 5, 2016

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.