{"id":1572,"date":"2019-08-07T11:27:57","date_gmt":"2019-08-07T18:27:57","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/cost-based-optimizer-for-couchbase-n1ql-sql-for-json\/"},"modified":"2019-08-07T11:27:57","modified_gmt":"2019-08-07T18:27:57","slug":"cost-based-optimizer-for-couchbase-n1ql-sql-for-json","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/pt\/cost-based-optimizer-for-couchbase-n1ql-sql-for-json\/","title":{"rendered":"Cost Based Optimizer for Couchbase N1QL (SQL for JSON)"},"content":{"rendered":"\n

Authors & Engineering Team: Bingjie Miao, Keshav Murthy,\u00a0 Marco Greco, Prathibha Bisarahalli. <\/span>Couchbase, Inc.\u00a0<\/span><\/p>\n\n\n\n

\n

A rule-based optimizer knows rules for everything and cost for nothing – Oscar Wilde<\/a><\/span><\/p>\n<\/blockquote>\n\n\n\n

Abstract<\/strong><\/h3>\n\n\n\n

Couchbase is a distributed JSON database. It provides distributed data processing, high performance indexing and declarative query language N1QL along with search index, eventing and analytics.\u00a0 N1QL (Non First Normal Query Language) is SQL for JSON. N1QL is to JSON what SQL is to relations. Developers say what needs to be done and the N1QL engine will figure out the \u201chow\u201d. Current N1QL optimizer is a <\/span>rule based optimizer<\/span><\/a>. \u00a0 Cost Based Optimizer(CBO) for SQL was invented at <\/span>IBM some 40 years ago<\/span><\/a> and has been critical to the success of RDBMS and the developer\/DBA productivity.\u00a0 NoSQL databases were invented some 10 years ago. Shortly after their invention, NoSQL databases started adding SQL-like query language with limited access paths and optimizer capabilities. \u00a0 Most use rule based optimizer or simply support cost based optimization on simple scalar values (strings, numbers, boolean,etc) only.<\/span><\/p>\n\n\n\n

To create a CBO on JSON model, you first need to collect and organize the statistics. How do you collect, store and use statistics on JSON\u2019s flexible schema? How do you collect statistics on objects, arrays, elements within objects? How do you use them efficiently within your optimizer?<\/span><\/p>\n\n\n\n

Lukas Eder<\/span><\/a> once told me, \u201cCost-based optimizer makes SQL fully declarative.\u201d \u00a0 He has a point. Couchbase 6.5 (now GA) has Cost Based Optimizer for N1QL.\u00a0 This article presents the introduction of N1QL Cost Based Optimizer (CBO) in <\/span>Couchbase 6.5<\/span><\/a>.\u00a0 CBO is a patent-pending developer-preview feature.\u00a0 In this article, we describe on how you can use CBO as well as\u00a0 its implementation details.<\/span><\/p>\n\n\n\n

A PDF version of this article can be downloaded here<\/a>.<\/p>\n\n\n\n

Table of Contents<\/strong><\/h3>\n\n\n\n
    \n
  1. Abstract<\/strong><\/li>\n\n\n
  2. Introduction to N1QL<\/strong><\/li>\n\n\n
  3. Using Cost-Based Optimizer for N1QL<\/strong><\/li>\n\n\n
  4. N1QL Optimizer<\/strong><\/li>\n\n\n
  5. Cost-Based Optimizer for N1QL<\/strong><\/li>\n\n\n
  6. Statistics Collection for N1QL CBO<\/strong><\/li>\n\n\n
  7. Summary<\/strong><\/li>\n\n\n
  8. Resources N1QL rule-based Optimizer<\/strong><\/li>\n\n\n
  9. References<\/strong><\/li>\n\n<\/ol>\n\n\n\n

    Introduction to N1QL<\/b><\/h3>\n\n\n\n

    As JSON has been increasingly accepted by the information technology industry as the lingua franca for information change, there has been an exponential rise in the need for repositories that natively store, update and query JSON documents.\u00a0 SQL has added features manipulating JSON in <\/span>SQL:2016<\/span><\/a>. SQL:2016 adds new scalar and table functions to manipulate JSON.\u00a0 An alternative approach is to treat JSON as the data model and to design the language to operate on JSON natively.\u00a0 <\/span>N1QL<\/span><\/a> and <\/span>SQL++<\/span><\/a> use the latter approach and provide natural access to scalars, objects, arrays, arrays of objects, arrays within objects and so on.\u00a0<\/span><\/p>\n\n\n

    [crayon theme=”github” font=”consolas” font-size=”15″ wrap=”true” whitespace-before=”2″ whitespace-after=”2″ lang=”mysql” decode=”true”]SQL:2016: From: Microsoft SQL Server. <\/p>\n

    SELECT Name,Surname,
    \n JSON_VALUE(jsonCol,’$.info.address.PostCode’) AS PostCode,
    \n JSON_VALUE(jsonCol,’$.info.address.”Address Line 1″‘)+’ ‘
    \n +JSON_VALUE(jsonCol,’$.info.address.”Address Line 2″‘) AS Address,
    \n JSON_QUERY(jsonCol,’$.info.skills’) AS Skills
    \nFROM People
    \nWHERE ISJSON(jsonCol) > 0
    \n AND JSON_VALUE(jsonCol,’$.info.address.Town’)=’Belgrade’
    \n AND Status=’Active’
    \nORDER BY JSON_VALUE(jsonCol,’$.info.address.PostCode’)
    \n[\/crayon]<\/p>\n\n\n

    [crayon theme=”github” font=”consolas” font-size=”15″ lang=”mysql” decode=”true”]N1QL: Same query written in N1QL on the JSON model
    \nSELECT Name,Surname, jsonCol.info.address.PostCode AS PostCode,
    \n (jsonCol.info.address.`Address Line 1` + ‘ ‘
    \n + jsonCol.`info.address.`Address Line 2`) AS Address,
    \n jsonCol.info.skills’ AS Skills
    \nFROM People
    \nWHERE
    \n jsonCol.info.address.Town = ‘Belgrade’
    \n AND Status=’Active’
    \nORDER BY jsonCol.info.address.PostCode
    \n[\/crayon]<\/p>\n\n\n\n

    Learn N1QL at <\/span>https:\/\/query-tutorial.couchbase.com\/<\/span><\/a>\u00a0<\/b><\/p>\n\n\n\n

    Couchbase Server:<\/b>\"\"<\/a><\/p>\n\n\n\n

    Couchbase has engines supporting N1QL:<\/span><\/p>\n\n\n\n

      \n
    1. N1QL for interactive applications in the Query service.<\/span><\/li>\n\n\n
    2. N1QL for Analytics in the Analytics service.<\/span><\/li>\n\n<\/ol>\n\n\n\n

      In this article, we focus on N1QL for Query (interactive applications) implemented in the query service. \u00a0 All of the data manipulated by N1QL is saved in JSON within Couchbase data store managed by the data service.<\/span><\/p>\n\n\n\n

      To support query processing on JSON, N1QL extends the SQL language in many ways:\u00a0<\/span><\/p>\n\n\n\n