How and Why To Bridge between SQL and NoSQL

SQL have for long now been the synonym of "database" for us. For any sort of data management, SQL had been our instinctive choice. However, the past decade saw the emergence of NoSQL which gave rise to a fierce competition of preferences.

  

What haunts the mind of every aspiring database developer today is the question of choice: To SQL or NoSQL. We want to keep in touch with the latest trends in the technology, but don't want the established technologies to slip away either. However, the most basic point that most people seem to miss is this: SQL and NoSQL are not competitors, and most certainly not antonyms of each other.

SQL or Structured Query Language is the most standard concept of database management systems today. SQL considers data to be stored in the form of tables called Relations, that consist of tuples and attributes. While this concept had been a hugely successful improvement over the data-storage systems present at that time, like flat files, things have changed today.

NoSQL came as a breath of fresh air in an industry that was rapidly changing. The world is going digital, and the digital world is messy. We can never predict the volume, variety or velocity of incoming data. The data, apart from being unpredictable, is also unstructured. Since relational databases are not inherently adept to handle them, something else was required. At the same time, distributed computing is all the rage today, because most businesses are moving towards the cloud. The expansion of relational databases cannot keep up with the pace; thus, NoSQL entered into the scene.

Why to migrate from SQL to NoSQL

Strictly speaking, NoSQL aims to do what SQL cannot. It is not based on relations and it may sometimes even fail to follow the ACID properties! But unlike what you have been taught, ACID properties, though really useful, are not the ultimate necessity. The ultimate necessity is fault tolerance, and NoSQL manages to achieve that anyway.

NoSQL cannot be defined in a single line, as there is no single definition. While all SQL-based databases follow strict guidelines that adhere to SQL-standards, NoSQL gives the databases a free rein. With so many lacks of standards, one might wonder: Are the reasons enough to migrate to NoSQL?

Yes, because we have only touched the crux of the importance of NoSQL in modern world. The two biggest reasons why NoSQL trumps over SQL are agility and scalability.

With the rapid changes that occur daily in the industry, being agile is the only way to survive. However, Relational databases couldn't ever hope to achieve that, with their rigid schemas and complex development. The aforementioned rapid changes are also met by growing size, which require rapid scalability. However, scalability was one aspect that was blatantly ignored in SQL (as it was made in a time when web and internet were non-existent). To cope up with these issues, NoSQL seems like our best bet.

Why to Bridge SQL and NoSQL

"Now that we know how NoSQL differs from SQL, the question arises: Why to bridge them? Why not adopt NoSQL altogether?   "

Simply, because NoSQL doesn't have the same penetration as SQL. A huge number of companies have their entire existing architecture based on relational databases, which would be quite a headache to change. But that doesn't mean that one has to remain stuck with SQL forever. The best option in such scenarios is to bridge the existing SQL framework with a NoSQL database. The benefit? To put it simple, it will bring out "the best of both worlds".

As far the "bridging" goes, there is no one, simple way to do that. The easiest way would be to use third-party drivers like easysoft, which provides ODBC-like bridging capabilities. However, as it comes from a third-party vendor, it might have its own security and licensing issues.

An alternative approach would be to develop languages that could extend SQL functionality to NoSQL databases. One example would be the N1QL, introduced by Couchbase Server, which extends SQL to JSON.

The ways to bridge the gap between these two technologies may differ and evolve; but we can all agree that co-existence of the two is best for the progress of industry.

Please share your thoughts on this topic. If you like this posts, please share it on google by clicking on the Google +1 button.

Read more on NO SQL- NOT ONLY SQL here - WhatisNoSQL

Oracle’s DYN Acquisition Fits Into Its Goal To Become A Cloud Leader

Last week tech giant Oracle announced that it was acquiring DYN, the popular cloud-based traffic routing and DNS provider, for an unspecified amount. Some reports have said that it could be in the region of $600-700 million.

DYN’s cloud-based platform manages and optimises the performance of internet applications and infrastructure by using analytics and intelligent routing. Its Internet performance and Domain Name System (DNS) solution is being used by over 3,500 companies that include top digital brands like Netflix, Twitter and Reddit.  On a daily basis, it handles over 40 billion traffic optimization decisions, making it one of the leading DNS service providers

For Oracle, buying DYN offers an opportunity to challenge the current leaders of cloud computing, Amazon's AWS, Microsoft Azure and Google Cloud.  Oracle currently lags significantly behind these companies in terms of the cloud computing market share, having primarily a portfolio that’s limited to datacentres systems. 

The Enterprises services leader does has a variety of Infrastructure-as-a-Service (IaaS) and Platform-as-a-Service (PaaS) products but adding DYN’s range of scalable services would help Oracle’s customers to get access to cutting edge traffic optimization technologies, filling a gap that might have taken far more time to plug with organic product development.

Oracle has pegged it as a natural extension to its cloud solutions – a service that is the link between hosting data and incoming traffic, resulting in improved metrics for access and user satisfaction for its clients.

This latest acquisition is in keeping with Oracles strategy of buying companies that have noteworthy products in cloud computing – it has in recent times acquired cloud-based applications firm LogFire, cloud access security broker Palerra as well as NetSuite the integrated cloud business software suite.  

If looked at the pattern of these acquisitions, it clearly show the intent of Oracle to move away from its legacy software-led business towards the cloud which in recent times has significantly reshaped how businesses and IT infrastructure are built and run.

It in fact has a stated goal to become the first tech company to reach $10 billion in revenue from cloud business. The NetSuite deal alone is expected to add close to $1 billion in revenue giving a boost to its cloud business.

"With the DYN acquisition, Oracle will be able surely to leapfrog into direct competition with leaders of cloud computing, and make an attempt at taking the leadership position in the market " . 

If you like this post, please share it on google by clicking on the Google +1 button.

Please go through similar Oracle Posts @DWHLAUREATE:

WHAT IS A PARTITION IN ORACLE

  WHAT IS AN INLINE VIEW?

  WHAT ARE EXTERNAL TABLES IN ORACLE

  WHAT ARE INDEXES IN ORACLE 

How to Drop Indexes/ Unique Indexes in Oracle?

There can be multiple situations where we don’t require indexes and have to drop them.

• Sometimes it’s better to drop the indexes when there is not much performance gain for your table with indexes.
• Once the indexes becomes invalid, you must first drop the indexes before rebuilding it.
• If your indexes are too fragmented, it’s better to drop the indexes and create a new index since rebuilding an index requires twice the space of the index.

All the extents of the index segment are restored to the containing table

space once you drop the index so that it becomes available to other objects in the table space.

Below is the command to drop indexes:

SYNTAX : DROP INDEX [OWNER.]INDEXNAME [FROM [OWNER.]TABLENAME]

EXAMPLE:

SQL> DROP INDEX EMP_NAME_IDX;

INDEX DROPPED

 SQL>

Conversely, you can't drop any implicitly created index, such as those created by defining a UNIQUE key constraint on a table, with the drop index command. If you try to do so it will throw an error.

SQL> DROP INDEX EMP_NAME_IDX ;

 DROP INDEX EMP_NAME_IDX *

ERROR AT LINE 1: ORA-02429: CANNOT DROP INDEX USED FOR ENFORCEMENT OF UNIQUE/PRIMARY KEY

If you want to drop such an index you have to first drop the constraint defined on the table. In order to drop a constraint, issue the drop constraint command, as shown here:

SQL> ALTER TABLE EMP DROP CONSTRAINT emp_name_PK1;

TABLE ALTERED.

SQL>

You can query the ALL_CONSTRAINTS performance view to understand which constraint the index is used by,

SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE,

 TABLE_NAME, INDEX_OWNER, INDEX_NAME

FROM ALL_CONSTRAINTS

WHERE INDEX_NAME = 'EMP_NAME_IDX';

If you like this post, please share it on google by clicking on the Google +1 button.

Please go through similar Oracle Posts @DWHLAUREATE:

     WHAT IS A PARTITION IN ORACLE

   WHAT IS AN INLINE VIEW?

  WHAT ARE EXTERNAL TABLES IN ORACLE

 WHAT ARE INDEXES IN ORACLE 

Oracle Indexes Performance and Creation Guidelines

These guidelines will help you create and manage indexes and help improving the performance by correct usage of indexes.

DON’T ADD INDEXES WORTHLESSLY:

Addition of indexes increases performance but also ingest disk space.Based on the performance improvement add as many indexes as required sensibly.

MARK INDEXES AS UNUSABLE OR INVISIBLE RATHER THAN DROPPING

Before dropping an index think over marking the indexes as unusable and invisible. This give us an extra option to check for any performance issues before dropping the index. If there are any performance issues we can revert back by rebuilding or re-enable the index without requiring the data definition language (DDL) creation statement.

You can read more about Invisible Indexes here:

Invisible Indexes - Oracle 11g New Feature

It’s better to drop the indexes that are not used by any database objects as it would free up the physical space and improve the performance.

INDEXING METHODOLOGY:

Indexing the columns that are used in queries executed against a table will help improve the performance.

CREATE PRIMARY /UNIQUE CONSTARINTS:

Build primary constraints on all tables and unique constraints wherever applicable. This will automatically create a B-tree index if the columns are not already indexed.

USING SEPARATE TABLESPACE FOR INDEXES

Using distinct table space helps in managing indexes separately from tables. Table and index data may have different storage and/or backup and recovery requirements.

USE BITMAP INDEXES IN DATAWAREHOUSE ENVIRONMENT

Bitmap indexes are used for complex queries in a data warehouse environment to prevent spending long time to access and retrieve answers for the queries. B-Tree index technique is used for high cardinality column and Bitmap Indexes have predominantly been used for low cardinality columns.

Bitmap indexes achieve important functions in answering data warehouse’s queries because they have capability to perform operations at the index level before fetching data

To learn more about Bitmap & B-tree indexes check our previous post

http://dwhlaureate.blogspot.in/2012/08/type-of-indexing-used-in.html

USE APPROPRIATE NAMING STANDARDS

Correct naming standards would help in the maintenance and troubleshooting easier.

If you like this post, please share it on google by clicking on the Google +1 button.

Please go through similar Oracle Posts @DWHLAUREATE:

     WHAT IS A PARTITION IN ORACLE

   WHAT IS AN INLINE VIEW?

  WHAT ARE EXTERNAL TABLES IN ORACLE

 WHAT ARE INDEXES IN ORACLE 

How Nested Tables can be stored and retrieved in a Normal table-ORACLE PL/SQL

Author:VibinDas        

 As we all know, Nested Tables (especially the Oracle Collections) can be considered as one of the advanced concepts in Oracle. In many instances we may have to deal with Nested tables and Varrays in our coding where the requirement may be to save and retrieve a set or a bundle of values in database columns. One of the benefits of using this way is that, if we have multiple values to be stored corresponding to one subject or entity, these multiple values of each particular subject can be treated as a single entity and stored as a single value in database table.

Sometimes this introduction may bring a little bit of complexity in your mind; but the real fact is that, this is a very simple and interesting topic which you are going to enjoy for sure.

Let’s start with an example on this. Consider a STUDENT table which stores the information like the student id, first name, last name, his class etc... It’s very pretty simple as below.

Step 1 : First create a test table as below.

CREATE TABLE TEMP_STUDENT_DTLS

(

   STUD_ID NUMBER,

   STUD_NAME VARCHAR2(30),

   STUD_CLASS NUMBER

);

Step 2 : Insert some entries to it.

INSERT INTO TEMP_STUDENT_DTLS(STUD_ID,STUD_NAME,STUD_CLASS)

           VALUES(100, 'JAMES', 10);

INSERT INTO TEMP_STUDENT_DTLS(STUD_ID,STUD_NAME,STUD_CLASS)

           VALUES(200, 'ALBERT', 8);

INSERT INTO TEMP_STUDENT_DTLS(STUD_ID,STUD_NAME,STUD_CLASS)

           VALUES(300, 'MIKE', 5);

INSERT INTO TEMP_STUDENT_DTLS(STUD_ID,STUD_NAME,STUD_CLASS)

           VALUES(400, 'THOMAS', 2);

COMMIT;

**** 4 Rows inserted and committed.

Step 3 : Verifying the inserted records.

STUD_ID	STUD_NAME	STUD_CLASS
100	JAMES	10
200	ALBERT	8
300	MIKE	5
400	THOMAS	2

 So far everything is fine here. No weird things. But it’s going to come soon.

Suppose the requirement has got changed slightly and the additional thing is as follows

·         Need to store the subject list and corresponding mark of each student as well in the table.

Do you see anything fishy here? If each of the students are having same number of subjects, then it would been somewhat possible -  for example, if there are only 3 subjects for all students, we could have created 6 more columns such as SUB1, MARK1, SUB2, MARK2, SUB3, MARK3. But here since each of these students are in different classes, the number of subjects won’t be the same for everyone. For example, James being in 10th standard will have 12 subjects to study whereas Thomas will have only 4 subjects as he is in 2nd standard.

Here two approaches are in hand - One is to go for normalization and the second to go with Nested table concepts. If we are in a position not to split the table further, we can’t go for normalization. Now the only solution left is to go with Nested tables.

Solution Using Nested Table

Data Loading

      Since we need to store the (Subject, Mark) pair information corresponding to each subject, we need to first declare a Type object which is capable of storing Subject name and Mark as single.

Step 1 : Creation of an OBJECT Type

CREATE TYPE OBJ_TYPE_SUBJ_MARK IS OBJECT

(

   SUBJECT_NAME VARCHAR2(30),

   MARK NUMBER

);

Step 2 : Creation of a Nested Table Type

CREATE TYPE NT_SUBJ_MARK_DTLS IS TABLE OF OBJ_TYPE_SUBJ_MARK;

  By doing this, a one dimensional array ‘NT_SUBJ_MARK_DTLS’ is created where each cell can carry (SUBJECT, MARK) value combination.

NT_SUBJ_MARK_DTLS

(SUBJECT_NAME1, MARK1)

(SUBJECT_NAME2, MARK2)

(SUBJECT_NAME3, MARK3)

(SUBJECT_NAME4, MARK4)

(SUBJECT_NAME5, MARK5)

(SUBJECT_NAME6, MARK6)

.

.

.

.

        

  Actually there is no restriction on the size of this one dimensional array. For the time being, showed 6 elements only. However it can be any of any size according to the number of values we insert into the column.

Step 3 : Recreation of table with one column as this newly created Nested Table

          Now recreate the same table with one additional column which will carry the Subject and Mark details of each student in an array format. For that, the column should be having data type of “NT_SUBJ_MARK_DTLS” as given below.

DROP TABLE TEMP_STUDENT_DTLS PURGE;

CREATE TABLE TEMP_STUDENT_DTLS

(

   STUD_ID           NUMBER,

   STUD_NAME      VARCHAR2(30),

   STUD_CLASS     NUMBER,

   MARK_DTLS      NT_SUBJ_MARK_DTLS

) NESTED TABLE MARK_DTLS STORE AS SYS_GEN_TBL;

 Datatype of column is NT_SUBJ_MARK_DTLS which is nothing but the Nested table we created

Here the keyword ‘NESTED TABLE’ is used to inform Oracle that the column is having data type of a Nested table (which we have already created). One additional point to remember here is that, while declaring a database column of such a user defined data type of Nested Table, internally the contents of that column are not stored along with the remaining table column values. Instead, they are stored in another System generated table space allocated by Oracle which we need to name and in our example, the name ‘SYS_GEN_TBL’ has been given for that. This has to be given followed by the keyword ’STORE AS’.

Step 2 : Insert some entries to it.

   Suppose the Subject- Mark details of each of the students are as follows.

·         James

Marks 45, 40, 35 and 42 for Subjects Physics, Maths, English and Literature respectively.

·         Albert

Marks 48, 33 and 50 for Subjects Chemistry, Maths, and Latin respectively.

·         Thomas

Marks 38 and 42 for Subjects History and Maths respectively.

Hence the entries are like these.

INSERT INTO TEMP_STUDENT_DTLS(STUD_ID,

                               STUD_NAME,

                               STUD_CLASS,

                               MARK_DTLS)

      VALUES(100,

                  'JAMES',

                  10,

                  NT_SUBJ_MARK_DTLS(

                                              OBJ_TYPE_SUBJ_MARK ('PHYSICS', 45),

                                              OBJ_TYPE_SUBJ_MARK ('MATHS', 40),

                                              OBJ_TYPE_SUBJ_MARK ('ENGLISH', 35),

                                              OBJ_TYPE_SUBJ_MARK ('LITERATURE', 42)

                                             )

                );

INSERT INTO TEMP_STUDENT_DTLS(STUD_ID,

                               STUD_NAME,

                               STUD_CLASS,

                               MARK_DTLS)

      VALUES(200,

                  'ALBERT',

                  8,

                  NT_SUBJ_MARK_DTLS(

                                              OBJ_TYPE_SUBJ_MARK ('CHEMISTRY', 48),

                                              OBJ_TYPE_SUBJ_MARK ('MATHS', 33),

                                              OBJ_TYPE_SUBJ_MARK ('LATIN', 50)

                                             )

                );

INSERT INTO TEMP_STUDENT_DTLS(STUD_ID,

                               STUD_NAME,

                               STUD_CLASS,

                               MARK_DTLS)

      VALUES(400,

                  'THOMAS',

                  2,

                  NT_SUBJ_MARK_DTLS(

                                              OBJ_TYPE_SUBJ_MARK ('HISTORY', 38),

                                              OBJ_TYPE_SUBJ_MARK ('MATHS', 42)

                                             )

                );

COMMIT;

 *****3 Rows inserted and committed.

Step 3 : Verifying the inserted records.

SELECT * FROM TEMP_STUDENT_DTLS;

STUD_ID	STUD_NAME	STUD_CLASS	MARK_DTLS
100	JAMES	10	(DATASET)
200	ALBERT	8	(DATASET)
400	THOMAS	2	(DATASET)

As can be seen, the multiple Subjects and their corresponding marks have been loaded to a single column. Thus there is no need of any replication of same student record for each and every subject.

Data Retrieval

·        SELECT

  In order to get the subject-mark details of James, we need to use the TABLE operator as below.

SELECT *

  FROM TABLE(

                        SELECT MARK_DTLS

                           FROM TEMP_STUDENT_DTLS

                            WHERE STUD_NAME = 'JAMES'

                      );

SUBJECT_NAME	MARK
PHYSICS	45
MATHS	40
ENGLISH	35
LITERATURE	42

·        INSERT

On the other hand, if our intention is add to add one more subject-mark details to James, then

INSERT INTO TABLE(

                        SELECT MARK_DTLS

                           FROM TEMP_STUDENT_DTLS

                            WHERE STUD_NAME = 'JAMES'

                      )

                        VALUES('CHEMISTRY',46);

COMMIT;

****   1 Row inserted and committed

.

To get the subject-mark details of James,

SELECT *

  FROM TABLE(

                        SELECT MARK_DTLS

                           FROM TEMP_STUDENT_DTLS

                            WHERE STUD_NAME = 'JAMES'

                      );

SUBJECT_NAME	MARK
PHYSICS	45
MATHS	40
ENGLISH	35
LITERATURE	42
CHEMISTRY	46

·        DELETE

In order to delete the subject = ‘CHEMISTRY’ record,

DELETE

  FROM TABLE(

                        SELECT MARK_DTLS

                           FROM TEMP_STUDENT_DTLS

                            WHERE STUD_NAME = 'JAMES'

                      )

           WHERE SUBJECT_NAME = 'CHEMISTRY';

COMMIT;

   1 Row deleted and committed

To get the subject-mark details of James,

SELECT *

  FROM TABLE(

                        SELECT MARK_DTLS

                           FROM TEMP_STUDENT_DTLS

                            WHERE STUD_NAME = 'JAMES'

                      );

SUBJECT_NAME	MARK
PHYSICS	45
MATHS	40
ENGLISH	35
LITERATURE	42

·        UPDATE

In order to update the mark of  subject = ‘PHYSICS’ record from current value of 45 to 50,

UPDATE TABLE(

                        SELECT MARK_DTLS

                           FROM TEMP_STUDENT_DTLS

                            WHERE STUD_NAME = 'JAMES'

                      )

       SET MARK = 50

         WHERE SUBJECT_NAME = 'PHYSICS';

COMMIT;

   1 Row update and committed

To get the subject-mark details of James,

SELECT *

  FROM TABLE(

                        SELECT MARK_DTLS

                           FROM TEMP_STUDENT_DTLS

                            WHERE STUD_NAME = 'JAMES'

                      );

SUBJECT_NAME	MARK
PHYSICS	50
MATHS	40
ENGLISH	35
LITERATURE	42

Since we are making use of NESTED Table, the details are saved in the database itself as database table columns.

If you like this post, please share it on google by clicking on the Google +1 button.

Please go through similar Oracle Posts @DWHLAUREATE:

     WHAT IS APARTITION IN ORACLE

   WHAT IS ANINLINE VIEW?

  WHAT ARE EXTERNAL TABLES IN ORACLE

 WHAT ARE INDEXES IN ORACLE