Introduction to Couchbase for Oracle Developers and Experts: Part 2 - Database Objects
In this blog series, we will cover various topics to compare Oracle and Couchbase from a developer perspective. Today, we'll talk about database objects.
Join the DZone community and get the full member experience.
Join For Free
Oracle DBAs work with physical clusters, machines, instances, storage systems, disks, etc. All Oracles users, developers, and their applications work with logical entities: databases, tables, rows, columns, partitions, users, data types. There are a lot of similarities and some differences. Let’s compare and contrast how this is done on Couchbase.
Here’s the first part of this series.
Oracle Organization
A full list of Oracle schema objects is listed here. A database instance can have many databases; Databases can have many schemas; A schema can have many tables, indexes, functions, etc.
Couchbase Organization
A Couchbase instance can have many buckets; A bucket can have many scopes; A Scope can have many collections, indexes, functions, search indexes, analytic collections, eventing functions. Each bucket comes with a _default scope and _default collection mainly for backward compatibility. You can create new scopes, collections, indexes, and functions using the respective CREATE statements.
ORACLE | COUCHBASE | |
DATABASE | BUCKET Within a Couchbase instance (single node or multi-node cluster), you can create one or more buckets. Within each bucket, you can have one or more scopes and within each scope one or more collections, indexes, functions, search indexes, analytic collections, and eventing functions (similar to AFTER TRIGGERS). CREATE BUCKET: You create a bucket either via Couchbase web console or via REST API. In the web console, you provide the information below. The user provides the following: |
|
TABLE | COLLECTION Hierarchy: Bucket->Scope->Collections In RDBMS, a table is a set of rows. In Couchbase, a collection is a set of JSON documents. While buckets and scopes provide namespaces, collections provide a mechanism to store and manipulate a set of JSON documents. Since JSON is self-describing, you don't need to define the schema before inserting or loading data into the collection. Example document INSERT via N1QL: CREATE SCOPE mybucket.myscope; CREATE COLLECTION mybucket.myscope.mycustomers
JSON
The INSERT statement looks similar to Oracle's INSERT statement except you specify the data in a slightly different way: You specify the document key and give the whole JSON document to insert. The table is automatically sharded (uses consistent hash partitioning) -- nothing for the user to do. Couchbase SDKs also provide a simpler way to INSERT, UPDATE, UPSERT individual documents directly in each of the SDKs. Here's the Java SDK example. |
|
ROW | JSON DOCUMENT or a binary object JSON Document, with its document key. Each document can have a varying number of fields, data types, and structures. Since each JSON document is self describing, the field name (column name) is derived from the document and the type of the data is interpreted according to rules of JSON spec. Document key (user generated): "CX:3424"
JSON
Considerations for document key design: http://bit.ly/2GnRwwV |
|
COLUMN | Key-Value pairs (or Field) JSON is made up of key-value pairs. The key name in individual fields is similar to column names. In a relational world, you declare the column name upfront whereas, in JSON, each document describes the column name. And therefore, each document in a collection can have arbitrary fields with any valid JSON typed values. Example:
In these documents, “name” is a key, also known as an attribute. Its value can be scalar (full name) or an object (name) or array (hobbies). In Oracle, when you create the table, you specify column names and their data types. In Couchbase, you simply insert JSON documents. Each document self-describes the attribute (column) names. This gives you the flexibility to evolve the schema without having to lockdown the table. The data types are simply interpreted from the value itself: String, number, null, true, false, object or an array. |
|
Views can be created with CREATE VIEW statement. Once created, these are simply relations that can be used anywhere a table (set of relations) can be used. There are some additional requirements for updates on views (e.g. instead of triggers for insert, updates on complex views). | Couchbase does not have a dynamic SQL-based view like Oracle. Couchbase does have a technology we call, “Couchbase Views”, based on map reduce framework. These are similar to materialized views and not the SQL View and cannot be used with N1QL. | |
Materialized Views | Couchbase VIEW provides a flexible map-reduce framework for you to create pre-aggregations, indexes, anything else you want to create. See more details at: http://bit.ly/2EhIFfF | |
Sequences | Unavailable | |
TRIGGERS | EVENTING FUNCTIONS Couchbase Eventing provides a easy way to program down the stream actions. The big difference between database triggers and eventing actions are the eventing action is done asynchronously and outside the transaction that's updating the data. See detailed use cases and docs here. |
|
Constraints: primary key, unique, Check, referential, not null, | Couchbase requires and enforces unique constraints on the document key within a bucket. Documents can have reference to other document's key. It’s simply storing the data and can be used in JOIN operations. The reference itself isn’t checked or enforced. | |
INDEXES | INDEXES Indexes in Couchbase come in two varieties: In memory and standard secondary. Standard secondary indexes are similar to the Oracle indexes. You can have large number of indexes with large number of keys. Required portions of the indexes will be paged in as required. In-memory indexes are completely kept in memory and therefore will require proportional memory allocation. Indexes in Couchbase are also referred to as GSI - Global Secondary Indexes. A collection is a hash partitioned automatically, but the index indexes the whole (global) data of a collection. Both types of indexes are eventually consistent. Remember, Couchbase is a distributed system. The changes to the documents are available immediately (Couchbase is a CP system in the CAP regime). For each type of index, you can use variety of indexes using multiple features:
All these indexes shouldn't worry you. Couchbase has an indexer advisor as a service and the feature is built into the product query workbench as well (enterprise version). |
|
Functions (PL/SQL functions and stored procedures) |
User-Defined Functions (UDFs) Couchbase query service has two types of functions: - Simple SQL-based functions that can return expressions or run a SELECT statement to return values. - Javascript functions can be elaborate programs with complex logic and compute. Starting in Couchbase 7.1, the Javascript functions can also issue all of N1QL statements, can be part of transactions and can run transactions themselves -- just like Oracle PL/SQL and SQL Server T-SQL. And the developers agree Javascript is an easier-to-use language to the program! |
Opinions expressed by DZone contributors are their own.
Comments