Databases in Google Cloud

 We all know about different types of Databases. Just highlighting them to start off the topic.

Relational DB:

• Fixed table schema and relations (primary and foreign key)
• Strong transactional capability
• Update all table data in one transaction and commit
• Failure of one, implies rollback in all.
• Since its strong in transactions, Relational DB is preferred for OLTP (Online transaction processing) systems like banking.
• Lots of transactions (large number of users and small transactions per user)
• Data is stored in row storage
• In Google Cloud we have
• Cloud SQL
• Supports MySQL, SQL server and PostgreSQL
• Regional Database
• Multi zone for High availability
• Can't create Global Cloud SQL DB
• Data upto a few TB
• Fully managed (replication, patch management, DB management etc)
• Public IP provided to connect to the DB
• Cloud shell option also provided in console (gcloud sql command)
• Enable Cloud SQL Admin API as a pre-requisite
• Cloud Spanner
• Unlimited scale
• Horizontal scaling
• High availability
• Global applications (Globally consistent)
• Fully managed
• Multi region
• Option to add compute capacity when being configured
• 1 node = 1000 processing units
• Compute cost is the hourly cost charge for nodes or processing units.
• Storage cost is separate and billed per GB per month
• Min 100 processing units or 1 node

• Can be used for Analytics as well (OLAP - Online Analytics Processing)
• Used to analyze huge amount of data
• Reporting, data warehouse
• Uses columnar storage (not row) [High compression]
• Since data is stored via columns, it can be distributed
• Queries can run over multiple nodes (efficient execution for complex queries)
• GCP Managed service is BigQuery

NO SQL DB: (Not only SQL)

• Flexible schema
• Scales horizontally
• Highly scalable
• High performance
• GCP managed No SQL service:
• Cloud Firestore (Data store)
• Serverless
• Document DB
• Can run SQL like queries
• Used for high transactions
• Mobile and web applications
• Small to medium DB (few TB)
• BigTable
• Scalable
• Wide column DB
• Not serverless
• Data size > 10TB
• Does not support multi row transaction. Supports only single row.
• Not to be used for transactional applications

In Memory Database

• Faster to retrieve data since data is not on the disk
• Low latency (microseconds)
• Persistent data stored in memory
• Use for caching or session management
• GCP service is Managed Store

Some 12C Features

Will try to describe some of the 12C features and will keep editing this post.

Note:

• If you want to install Oracle 12 on Windows 7, refer the steps here 
• To troubleshoot invalid common user or role name after installation, go here

Feature List

 Identity Columns:

• Also known as auto-number column. This implies that the data in the column should increase when data is inserted).
• Read article on the same here.

 Invisible Columns:

• Columns can be made invisible
• It gives one the ability to introduce a change while minimizing any negative side effects of that change.
• Read article on the same here 

With Clause:

• Instead of using a stored PL/SQL function, we can include the body of the function in the SQL query
• Read article on the same here 

 Truncate statements with the cascade option

• We could not truncate table with foreign key constraints.
• Now in 12c, one can execute the truncate table command with the cascade option (to cascade through the child tables).
• Note: You would need to create the foreign key with the "on delete cascade" option

Columns can have a default value in place of NULL

• You can define a column to have a default value when NULL is inserted
• e.g. create table ......  product_id NUMBER ON NULL 0,.....
• So if one explicity inserts NULL, it will be populated by value 0

Multiple Index - same columns

• You can create multiple indexes (different types) on a same set of columns.
• Assume you need to change from B*tree to Bitmap or vice versa.
• You need not drop the index.
• Just make the first index invisible and make a new index.
• Conditions:
• Different types of indexes
• Different uniqueness
• Different partitions are used

 

12 C Feature - WITH Clause

Note: Index article on some 12c features can be seen here

The with clause is more efficient to use than sub queries.
Instead of using a stored PL/SQL function, we can include the body of the function in the SQL query.
Benefit is that it will run faster than the call of a function by the same statement.

This can be better explained with an example.
Lets create a table with some test data.

Look at the usage of the function using with - in the body of the query.

The function in the with clause over-rides the function created at schema level. In below screenshot, you can see the schema level function returning SYSDATE+1 and the with clause function returning SYSDATE+2. Check the output - its SYSDATE+2.

Note: You need to put in a slash (/) at the end else SQLPLUS waits for more input (refer lines 9-12 in the snapshot waiting when ENTER key was pressed).

12 C Feature - Invisible Columns

Note: Index article on some 12c features can be seen here

The name is self explanatory.
Invisible columns means just that -- columns that are invisible.
It gives one the ability to introduce a change while minimizing any negative side effects of that change.

So, in a table, if we add a column, it will show up in a "select *" query.
All the "insert into <table> values ...." queries will break.

When you add an invisible column, you won't be able to see it even when you execute a "DESC" or when you run a "select *" query.
The "insert into <table> values ...." queries will not break.
However, you will able to see it if you specifically query for it by name.

Lets have a look at an example.
We have a table "TEST_IDENTITY" which we had used to test the identity column. Refer article on identity column

Lets add an invisible column to it.
The command will be:

ALTER TABLE <TABLE_NAME> ADD (<COLUMN_NAME> <TYPE> INVISIBLE);

As you can see below, I added a column name as INVISIBLE and it does not show up in DESC command.
It does not show up in "select *" and has no impact on the "insert into <TABLE_NAME>" as well.

But you can insert data into it and also do a select by calling it explicitly.

You can make the column visible by running the following alter command:
ALTER TABLE <TABLE_NAME> MODIFY <INVISIBLE_COLUMN_NAME> VISIBLE;

Some questions that may help:

How to know a table exists with an invisible column?

Run a query on USER_TAB_COLS
SELECT COLUMN_NAME, HIDDEN_COLUMN FROM USER_TAB_COLS WHERE TABLE_NAME = '<TABLE_NAME>';

What if I create an invisible column as NOT NULL?
INSERT will fail. You need to create with DEFAULT option or use a trigger to populate on INSERT.

Is there any massive advantage of this feature?
Its a feature which could be used but not any with massive advantage. it could create problems for some applications when made visible, but if you have coded with proper INSERTs/SELECTs (mentioning columns), it won't harm your application.

12 C Feature - Identity Columns

Note: Index article on some 12c features can be seen here 
 
Identity Columns

What is an Identity column?
This is also known as auto-number column. This implies that the data in the column should increase when data is inserted.

In previous version of Oracle 12c, this was accomplished in the one of the following two ways:

• Create a sequence
• Insert the sequence.nextval in the INSERT STATEMENT

OR

• Create a sequence
• Create a Trigger "BEFORE INSERT ON TABLE"

Trigger created:
CREATE OR REPLACE TRIGGER test_trg
BEFORE INSERT ON testseq
FOR EACH ROW
BEGIN
 SELECT test_seq.nextval INTO :new.id FROM dual;
END;


In Oracle 12c, the same thing can be achieved without the use of sequence and triggers in two ways:

Column default clause (with sequences)

Create a sequence and use it as part of the table creation.
One can specify sequences (NEXTVAL or CURRVAL) as default column values. When a new row is inserted into the table, the sequence value is automatically inserted.

Look at the screen shot below where I have created a sequence and a table.
You can see that we can also put in out own data (data with value like 100 in the example or null) and it overrides the value from the sequence.

I have made the column not null, and we can see on inserting null, it throws an error.

Using GENERATED as IDENTITY in Column definition:

Here I will show example of identity columns which can be specified in the CREATE TABLE and ALTER TABLE statements.

Create a table using the IDENTITY clause.

Create table test_identity
(
  id NUMBER GENERATED as IDENTITY,
  name VARCHAR2(100)
);

When you do a describe, you shall see that the column is by default NOT NULL;

Running a query on DBA_SEQUENCES shows a new SEQUENCE created by the name ISEQ$$_92446 (system-generated name in the format ISEQ$$_<objectID> where objectID is the object id of the table)

Also note that you cannot alter an existing non-identity column to become an identity column.

Difference between "Column default clause" and "Using GENERATED as IDENTITY"

ORA-65096: invalid common user or role name in Oracle 12c

Note: Index article on some 12c features can be seen here 

ORA-65096: invalid common user or role name in Oracle 12c

This error usually occurs due to we are trying to create user ( common user ) under root container.

In oracle 12c there are two type of users: common and local.

Common users belong to CBD’s as well as current and future PDB’s.

The user can perform operation in Container or Pluggable according to Privileges assigned


Local users is purely database that belongs to only single PDB. This user may have administrative privileges but this only belongs to that PDB.

Refer screen shots below on creation of user's in both containers.

Note that  users in CBD$ROOT container must start with C##

For the local user:

Please do note that you will need to make an entry in the tnsnames.ora for the container (with the service name). There will already be an entry for the CBD$ROOT container by default. You can copy the same and change the name and service name.

Do "grant create session to test;"
Since this is my test schema, I have also run "Grant UNLIMITED TABLESPACE to TEST;" (else you may encounter ORA-01950: no privileges on tablespace 'USERS')

You can then connect to test using "conn test/test@pdborcl;"

Installing Oracle 12c Database on Windows 7 (64 bit)

Note: Index article on some 12c features can be seen here 


Kindly peruse through the below steps (detailed) for installing Oracle 12 c DB on Windows 7 (64 bit):

Step 1.
Download the required binaries from Oracle website

Step 2.
Once downloaded, unzip the files into one folder. I created a folder "Oracle 12c" in D: and another folder in it called "database"

So my folder structure was D:\Oracle12c\database

Step 3.
Click on setup.exe

Step 4.
You could put your email address and request for support (if needed). I have not selected this option for this install.

Step 5.
I chose to skip software updates

Step 6.
Select "create and configure a database"

Step 7.
I have chosen "server class"

Step 8.
Type of DB installation - I chose Single

Step 9.
Choose Typical install and in the Next screen create a new Windows user (you can use an existing one if you want)

Step 10.
I changed the global database name to "orcl" and created a Pluggable DB "pdborcl"

Step 11.
After verification, click on install and await for the installation to complete.

Step 12.

Open a command prompt and test your install

Note: If you try to create a schema under root container, you may encounter ORA-65096 error.
For resolution of the same, have a look at the article here.

Oracle NOSQL Architecture (Basics)

Oracle NOSQL services network requests to store and retrieve data which is organized into key-value pairs.

The typical application is a web application which is servicing requests across the traditional three-tier architecture: web server, application server and NOSQL DB.

An application makes use of Oracle NoSQL Database by performing network requests against Oracle NoSQL Database's key-value store, which is referred to as the KVStore.

The requests are made using the Oracle NoSQL Database Driver, which is linked into your application as a Java library (.jar file), and then accessed using a series of Java APIs.

By using the Oracle NoSQL Database APIs, the developer is able to perform create, read, update and delete operations on the data contained in the KVStore



 Oracle NoSQL Database is tested using Java 7.

Key Value Store (KV Store):

The KVStore is a collection of Storage Nodes which host a set of Replication Nodes. Data is spread across the Replication Nodes. The store contains multiple Storage Nodes.

A Storage Node is a physical (or virtual) machine with its own local storage. The machine is intended to be commodity hardware. It should be, but is not required to be, identical to all other Storage Nodes within the store.




Replication Nodes and Shards
A Replication Node can be thought of as a single database which contains key-value pairs.

Replication Nodes are organized into shards. A shard contains a single Replication Node, called the master node, which is responsible for performing database writes.


 




The master node copies those writes to the other Replication Nodes in the shard, called the replicas. 
These replicas obtain a full copy of the data from the corresponding master node and are used to service read-only operations. Although there can be only one master node at any given time, any of the members of the shard are capable of becoming a master node.


Note:
The more shards that your store contains, the better your write performance is because the store contains more nodes that are responsible for servicing write requests.

Replication Factor
The number of nodes belonging to a shard is called its Replication Factor.

The larger a shard's Replication Factor, the faster its read throughput (because there are more machines to service the read requests) but the slower its write performance (because there are more machines to which writes must be copied).