Mindmap: Oracle to Couchbase for developers
•
4 likes•2,576 views
This deck provides a high-level comparison between Oracle and Couchbase: Architecture, database objects, types, data model, SQL & N1QL statements, indexing, optimizer, transactions, SDK and deployment options.
Recommended
Recommended
More Related Content
What's hot
What's hot (20)
Similar to Mindmap: Oracle to Couchbase for developers
Similar to Mindmap: Oracle to Couchbase for developers (20)
More from Keshav Murthy
More from Keshav Murthy (20)
Recently uploaded
Recently uploaded (20)
Mindmap: Oracle to Couchbase for developers
- 1. MINDMAP: ORACLE TO COUCHBASE FOR DEVELOPERS Raju Suravarjjala Keshav Murthy Couchbase R&D
- 2. 2 SQL is English for Relational Database SQL Invented by Don Chamberlin & Raymond Boyce at IBM N1QL is English for JSON N1QL was invented by Gerald Sangudi at Couchbase SQL Instance Database Table Row Column Index Datatypes N1QL Cluster Bucket Bucket, Keyspace Document Attribute Index JSON Datatypes SQL Input and Output: Set(s) of Tuples N1QL Input and Output: Set(s) of JSON N1QL STMT CREATE BUCKET CREATE INDEX None SELECT INSERT UPDATE DELETE MERGE Subqueries JOIN GROUP BY ORDER BY OFFSET, LIMIT EXPLAIN PREPARE EXECUTE GRANT ROLE REVOKE ROLE INFER PREPARE EXECUTE FLUSH Tuples SQL Model Set of JSON N1QL Model Set of Tuples Set of JSON N1QL Tooling Web Console Monitoring Profiling Dev workbench SDK Simba, Cdata BI Slamdata SQL Tooling ODBC, JDBC, .NET Hibernate BI Tools erwin TOAD N1QLResources query.couchbase.com SQL Indexes Primary Key Secondary Key Composite Range Partitioned Expression (Functional) Spatial Search N1QL Indexes Primary Secondary Composite Partial Expression (Functional) Array Index Replica(HA) Adaptive Spatial SQL Logic 3 valued logic TRUE, FALSE, NULL/UNKNOWN N1QL Logic 4 valued logic TRUE, FALSE, NULL/UNKNOWN, MISSING SQL Transactions ACID Multi-Statement Savepoints Commit/Rollback Redo, Undo N1QL Transactions Single Document atomicity SQL Datatypes Numeric Decimal Character Date Time Timezone BLOB Spatial JSON N1QL Datatype Numeric Boolean Character Array Object Null JSON Conversion Functions SQL Optimizer Rule Based Cost Based Index Selection Query Rewrites NL, Hash, Merge join N1QL Optimizer Rule based Index Selection NL Join SQL ACID ATOMIC Consistent Isolated Durable N1QL BASE Single doc Atomic Consistent Data* Optimistic Concurrency N1QL Index Scan Consistency* Unbounded AT_PLUS REQUEST_PLUS SQL Engine (SMP Scale UP) N1QL Engine (MPP Cluste Scale OUT) Additional SQL Features Triggers Stored Procedures XML Constraints RAC SQL STMT CREATE TABLE CREATE INDEX ALTER TABLE SELECT INSERT UPDATE DELETE MERGE Subqueries JOIN GROUP BY ORDER BY OFFSET, LIMIT EXPLAIN PLAN PREPARE EXECUTE GRANT REVOKE DESCRIBE PREPARE EXECUTE TRUNCATE
- 3. AGENDA 01 02 03 Architecture Database Objects Data Types Statements and Features Indexes Optimizer 04 05 06 Transactions 07 Data Model 08 Physical Space Management 09 SDK10 Deployment Options11
- 4. 4 Architecture App App Oracle App App Oracle RAC App App Oracle RAC App App Oracle RAC App App Couchbase App App Couchbase App App Couchbase App App Couchbase Couchbase Cluster App App Couchbase Oracle RAC AppApp
- 5. 5 Couchbase Architecture STORAGE Couchbase Server 1 SHARD 7 SHARD 9 SHARD 5 SHARDSHARDSHARD Managed Cache Cluster ManagerCluster Manager Managed Cache Storage Data Service STORAGE Couchbase Server 2 Managed Cache Cluster ManagerCluster Manager Query Service STORAGE Couchbase Server 3 SHARD 7 SHARD 9 SHARD 5 SHARDSHARDSHARD Managed Cache Cluster ManagerCluster Manager Index Service STORAGE Couchbase Server 4 SHARD 7 SHARD 9 SHARD 5 SHARDSHARDSHARD Managed Cache Cluster ManagerCluster Manager Search Service STORAGE Couchbase Server 5 SHARD 7 SHARD 9 SHARD 5 SHARDSHARDSHARD Managed Cache Cluster ManagerCluster Manager Analytics Service* Managed Cache Storage SDK SDK
- 6. 6 DATATYPES MAPPING Product Oracle Couchbase System Architecture SMP Shared disk RAC MPP: Shared Nothing MDS: Multi Dimensional Scaling Data Service: MPP, Hash Partitioning Indexing, FTS: MDS Scale-out Query: MDS Scale-out Query SQL, search, XQuery, JSON extensions N1QL, Key-value, Full text search High Availability Exadata storage, Golden Gate, Log Shipping, Standby server Built-in intranode replication (up to 3 copies) Built-in XDCR (cross data center replication) Transactions ACID Multi-statement Single document atomicity Data Service consistency, Index - eventual consistency optimistic locking (CAS) additional confirmation for durability Drivers JDBC, ODBC, .NET, LINQ Couchbase SDK (Java, .NET, LINQ, PHP, Python, Go), Simba JDBC/ODBC Data Model Normalized, Denormalized Denormalized JSON model Architecture
- 7. 7 DATATYPES MAPPING Data Feature/Type Oracle Couchbase Database Database Bucket Table Table Bucket, Keyspace Row Row Document Column Column Field/Attribute Partition Partition (manual) Partition (hash automatic) Database Objects
- 8. 8 DATATYPES MAPPING Data Type Oracle Couchbase JSON Numbers INT, BIGINT, NUMBER, FLOAT, LONG, DECIMAL JSON Number { "id": 5, "balance":2942.59 } String CHAR, VARCHAR, VARCHAR2 , NVARCHAR, JSON String { "name": "Joe","city": "Morrisville" } boolean CHAR(1) JSON Boolean { "premium": true, ”pending": false} datetime DATE, TIMESTAMP WITH TIMEZONE, INTERVAL JSON ISO 8901 String with extract, convert and arithmetic functions { “soldat”: "2017-10-12T13:47:41.068-07:00" } spatial data SDO_Geometry, SDO_Topo_Geometry, SDO_GeoRaster Supports nearest neighbor and spatial distance. "geometry": {"type": "Point", "coordinates": [- 104.99404, 39.75621]} MISSING Fixed Schema. MISSING NULL NULL JSON Null { "last_address": Null } Objects Fixed pre-defined COLLECTION types Flexible JSON Objects { "address": {"street": "1, Main street", "city": Morrisville, "zip":"94824"}} Arrays Flexible JSON Arrays { "hobbies": ["tennis", "skiing", "lego"]} DATA TYPES
- 9. 9 DATA TYPE Couchbase MISSING Value of a field absent in the JSON document or literal. {“name”:”joe”} Everything but the field “name” is missing from the document. IS MISSING Returns true if the document does not have status field FROM CUSTOMER WHERE status is MISSING; IS NOT MISSING Returns true if the document has status field FROM CUSTOMER WHERE status is NOT MISSING; MISSING AND NULL MISSING is a known missing quantity NULL is a known UNKNOWN. Valid JSON: {“status”: Null} MISSING value Simply make the field of any type to disappear by setting it to MISSING UPDATE CUSTOMER SET status = MISSING WHERE cxid = “xyz232”
- 10. 10 A B A OR B A AND B TRUE NULL TRUE NULL FALSE NULL FALSE NULL TRUE MISSING TRUE MISSING FALSE MISSING TRUE MISSING NULL MISSING NULL MISSING NULL NULL NULL NULL MISSING MISSING MISSING MISSING Couchbase: 4-valued boolean logic
- 11. 11 DATATYPES MAPPING Relationsh ip Oracle Couchbase 1:1 Foreign Key Denormalize Embedded Object (implicit) Document Key Reference 1:N Foreign Key Embedded Array of Objects Document key Reference N:M Foreign Key Embedded Array of Objects Arrays of objects with references Data Modeling
- 12. 12 DATATYPES MAPPING Index Type Oracle Couchbase Table Storage Table Space File system directory Index Storage Table Space File system directory Partitioning - Table Range, Interval, List, Hybrid, etc Hash partitioning Stored in 1024 vbuckets Partitioning - Index Range, Interval, List, Hybrid, etc Always detached from Bucket Global Index Manually partitioned range or hash index Physical Space Management:
- 13. 13 DATATYPES MAPPING Feature Oracle Couchbase CREATE TABLE CREATE TABLE couchbase-cli bucket-create ALTER TABLE ALTER TABLE UPDATE customer SET, UNSET CREATE INDEX i1 on t(a, b, c DESC); CREATE INDEX i1 on t(a, b, c DESC); CREATE INDEX i1 on t(a, b, c DESC); INSERT INTO INSERT INTO INSERT INTO SELECT SELECT SELECT JOINS JOINS JOIN – INNER JOIN, LEFT OUTER JOIN GROUP BY, HAVING GROUP BY, HAVING GROUP BY, HAVING ORDER BY a ASC, b DESC ORDER BY a ASC, b DESC ORDER BY a ASC, b DESC OFFSET, LIMIT OFFSET, FETCH FIRST/NEXT ROW ONLY OFFSET, LIMIT Subqueries Subqueries Subqueries Statements
- 14. 14 DATATYPES MAPPING Statement Oracle Couchbase SELECT SELECT * FROM CUSTOMER WHERE zip = 94040 SELECT * FROM CUSTOMER WHERE zip = 94040; INSERT INSERT INTO CUSTOMER(id, name, status, zip) VALUES (‘xyz124’, ‘Joe Montana’, ‘Premium’, 94040) INSERT INTO CUSTOMER(KEY, VALUE) VALUES(‘xyz124’, {“id”: “xyz124”, “name”: “Joe Montana”, “status”: “Premium”, “zip”: 94040}) UPDATE UPDATE CUSTOMER SET zip = 94587 WHERE id = ‘xyz124’ UPDATE CUSTOMER SET zip = 94587 WHERE id = ‘xyz124’ DELETE DELETE FROM CUSTOMER WHERE id = ‘pqr482’ DELETE FROM CUSTOMER WHERE id = ‘pqr482’; DELETE FROM CUSTOMER WHERE META().id = ‘pqr482’; MERGE MERGE into CUSTOMER using (select id from Cx where x < 10) as CN on (CUSTOMER.id = CN.id) when matched then update set CUSTOMER.o4=1; merge into CUSTOMER using (select id from CN where x < 10) as CN on key CN.id when matched then update set CUSTOMER.o4=1; DESCRIBE DESCRIBE CUSTOMER INFER CUSTOMER EXPLAIN EXPLAIN PLAN SELECT * FROM CUSTOMER WHERE zip = 94040 EXPLAIN SELECT * FROM CUSTOMER WHERE zip = 94040; Statements
- 15. 15 DATATYPES MAPPING Statement Oracle Couchbase GROUP BY GROUP BY a, b, c GROUP BY a, b, c ORDER BY ORDER BY SUM(d) DESC, b ORDER BY SUM(d) DESC, b OFFSET, LIMIT OFFSET 400 ROWS FETCH NEXT 50 ROWS ONLY; OFFSET 400 LIMIT 50 JOINs INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER INNER, LEFT OUTER. ON clause is equi joins with one side document key. Subqueries Projection sub queries can return only one (scalar or collection) value Projection subqueries can return zero, one or more values, treated as MISSING, scalar, object or arrays Table Expressions (subqueries in FROM clause) Table query expressions Query expressions can be the first expression in FROM clause SELECT
- 16. 16 DATATYPES MAPPING JOIN Type Oracle Couchbase INNER JOIN Full ANSI join ON clause requires document key reference. Equi-join only LEFT OUTER JOIN Full ANSI join ON clause requires document key reference. Equi-join only RIGHT OUTER JOIN Full ANSI join Unsupported FULL OUTER JOIN Full ANSI join Unsupported LATERAL JOIN, +++ Full ANSI join Unsupported JOINs
- 17. 17 DATATYPES MAPPING Statement Oracle Couchbase PREPARE EXEC SQL PREPARE p1 FROM SELECT * FROM CUSTOMER PREPARE p1 FROM SELECT * FROM CUSTOMER EXECUTE EXECUTE IMMEDIATE :p1 EXECUTE P1 GRANT GRANT SELECT ON oe.customers_seq TO hr; GRANT query_select ON orders, customers TO bill, linda; REVOKE REVOKE UPDATE ON hr.employees FROM oe; REVOKE query_update ON `travel-sample` FROM debby Statements
- 18. 18 DATATYPES MAPPING Index Type Oracle Couchbase Primary Index Table Scans, Primary Index Primary Index Secondary Index Secondary Index Secondary Index Composite Index Composite Index Composite Index Functional Index (Expression Index) Functional Index Functional Index, Expression Index Partial Index Partial Index Partial Index Range Partitioned Index Range partitioned, Interval, List, Ref, Hash, Hybrid partitioned Index Manual range partitioned using partial Index ARRAY Index None Yes. Nested array keys to the index is alwowed. Array Index on Expressions None Yes Objects/Arrays None Yes Indexes
- 19. 19 DATATYPES MAPPING Feature Oracle Couchbase Optimizer Type Rule Based Cost Based (default) Rule based Query Rewrite Yes No JOIN Order Cost based User Specified (Left to Right) HINTS Yes Yes (USE INDEX) EXPLAIN EXPLAIN FOR EXPLAIN Visual Explain Oracle Studio Built-in to web console Query Profiling Oracle Studio Built into query engine and web console Optimizer
- 20. 20 Data Updates and Transaction Clients Scan Request; with consistency level Index Service Query Service Data Service UPDATE CUSTOMER SET entitlement = ‘A’ WHERE address.city = ‘ny’ AND sttatus = ‘premium’ RETURNING META().id; “id” : [ ”id": ”cx:39293", ”id": ”cx:49283", ”id": ”cx:942948", ”id": ”cx:492982”, } Snapshot isolation. Implement the consistency levels Consistency Levels Unbounded AT_PLUS REQUEST_PLUS Always CONSISTENT MODIFY THE DOCUMENT UPDATE THE DOCUMENT
- 21. 21 Data Updates and Transaction Clients Scan Request; with consistency level Index Service Query Service Data Service UPDATE CUSTOMER SET entitlement = ‘A’ WHERE address.city = ‘ny’ AND sttatus = ‘premium’ RETURNING META().id; “id” : [ ”id": ”cx:39293", ”id": ”cx:49283", ”id": ”cx:942948", ”id": ”cx:492982”, } Snapshot isolation. Implement the consistency levels Consistency Levels Unbounded AT_PLUS REQUEST_PLUS Always CONSISTENT MODIFY THE DOCUMENT UPDATE THE DOCUMENT 3 1 4 5 2 6 7
- 22. 22 DATATYPES MAPPING Feature Oracle Couchbase Index updates Index is synchronously maintained Index is asynchronously maintained Multi Statement Transaction Yes BEGIN, COMMIT, ROLLBACK, SAVEPOINT No Atomicity Multi update, Multi statement Single Document Consistency Consistent Includes Dirty read support Data access is always consistent Index has multiple consistency levels (UNBOUNDED, AT_PLUS, REQUEST_PLUS) Isolation Pessimistic locking with distributed lock manager Optimistic locking with CAS checking Durability Durable Durable with confirmation after replication Transactions
- 23. 23 DATATYPES MAPPING SDK Oracle Couchbase Java JDBC Driver Couchbase Java SDK, Simba & CDATA JDBC C ODBC Couchbase C SDK, Simba & CDATA ODBC .NET, LINQ Oracle .NET provider LINQ provider Couchbase .NET provider LINQ provider PHP, Python, Perl, Node.js SDK on all these languages SDK on all these languages golang Nothing official SDK available SDKs
- 24. 24 DATATYPES MAPPING Deployment Oracle Couchbase Your laptop Linux, Windows, OS X Linux, Windows, OS X Single Node Yes Yes Multi-node Shared-disk Cluster (RAC) Shared Nothing Cluster VM, DOCKER Only the non RAC version. Yes Kubernetes Yes Yes in Developer Preview Openshift Yes Yes in Developer Preview Cloud Deployment AWS, Azure, GC, Oracle AWS, Azure, GC Application Upgrade without downtime Downtime < 15 min No downtime Deployment Options:
- 25. 25 Feedback Features Stored procedures: PL/SQL CURSORS Cost based optimizer and other optimizer improvements FULL ANSI joins Tools: Index Advisor
- 26. 26 SQL is English for Relational Database SQL Invented by Don Chamberlin & Raymond Boyce at IBM N1QL is English for JSON N1QL was invented by Gerald Sangudi at Couchbase SQL Instance Database Table Row Column Index Datatypes N1QL Cluster Bucket Bucket, Keyspace Document Attribute Index JSON Datatypes SQL Input and Output: Set(s) of Tuples N1QL Input and Output: Set(s) of JSON N1QL STMT CREATE BUCKET CREATE INDEX None SELECT INSERT UPDATE DELETE MERGE Subqueries JOIN GROUP BY ORDER BY OFFSET, LIMIT EXPLAIN PREPARE EXECUTE GRANT ROLE REVOKE ROLE INFER PREPARE EXECUTE FLUSH Tuples SQL Model Set of JSON N1QL Model Set of Tuples Set of JSON N1QL Tooling Web Console Monitoring Profiling Dev workbench SDK Simba, Cdata BI Slamdata SQL Tooling ODBC, JDBC, .NET Hibernate BI Tools erwin TOAD N1QLResources query.couchbase.com SQL Indexes Primary Key Secondary Key Composite Range Partitioned Expression (Functional) Spatial Search N1QL Indexes Primary Secondary Composite Partial Expression (Functional) Array Index Replica(HA) Adaptive Spatial SQL Logic 3 valued logic TRUE, FALSE, NULL/UNKNOWN N1QL Logic 4 valued logic TRUE, FALSE, NULL/UNKNOWN, MISSING SQL Transactions ACID Multi-Statement Savepoints Commit/Rollback Redo, Undo N1QL Transactions Single Document atomicity SQL Datatypes Numeric Decimal Character Date Time Timezone BLOB Spatial JSON N1QL Datatype Numeric Boolean Character Array Object Null JSON Conversion Functions SQL Optimizer Rule Based Cost Based Index Selection Query Rewrites NL, Hash, Merge join N1QL Optimizer Rule based Index Selection NL Join SQL ACID ATOMIC Consistent Isolated Durable N1QL BASE Single doc Atomic Consistent Data* Optimistic Concurrency N1QL Index Scan Consistency* Unbounded AT_PLUS REQUEST_PLUS SQL Engine (SMP Scale UP) N1QL Engine (MPP Cluste Scale OUT) Additional SQL Features Triggers Stored Procedures XML Constraints RAC SQL STMT CREATE TABLE CREATE INDEX ALTER TABLE SELECT INSERT UPDATE DELETE MERGE Subqueries JOIN GROUP BY ORDER BY OFFSET, LIMIT EXPLAIN PLAN PREPARE EXECUTE GRANT REVOKE DESCRIBE PREPARE EXECUTE TRUNCATE