You are viewing a plain text version of this content. The canonical link for it is here.

Posted to notifications@asterixdb.apache.org by AsterixDB Code Review <do...@asterix-gerrit.ics.uci.edu> on 2021/09/22 17:57:17 UTC

Change in asterixdb[master]: [NO-ISSUE][DOC] Update docs for INCLUDE/EXCLUDE unknown key

From Glenn Galvizo <gg...@uci.edu>:

Glenn Galvizo has uploaded this change for review. ( https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/13345 )


Change subject: [NO-ISSUE][DOC] Update docs for INCLUDE/EXCLUDE unknown key
......................................................................

[NO-ISSUE][DOC] Update docs for INCLUDE/EXCLUDE unknown key

- user model changes: no
- storage format changes: no
- interface changes: no

Update doc grammar + docs themselves to include the INCLUDE/EXCLUDE
UNKNOWN KEY option. Also updating docs to reflect change in enabling
arrayindex compiler flag.

Change-Id: Ief37a9cb74440922c599ffb0189f44d2993f564b
---
M asterixdb/asterix-doc/src/main/grammar/sqlpp.ebnf
M asterixdb/asterix-doc/src/main/markdown/sqlpp/7_ddl_dml.md
M asterixdb/asterix-doc/src/main/markdown/sqlpp/appendix_2_arrayindex.md
M asterixdb/asterix-doc/src/site/markdown/sqlpp/arrayindex.md
4 files changed, 33 insertions(+), 31 deletions(-)



  git pull ssh://asterix-gerrit.ics.uci.edu:29418/asterixdb refs/changes/45/13345/1

diff --git a/asterixdb/asterix-doc/src/main/grammar/sqlpp.ebnf b/asterixdb/asterix-doc/src/main/grammar/sqlpp.ebnf
index becc4b1..b237f30 100644
--- a/asterixdb/asterix-doc/src/main/grammar/sqlpp.ebnf
+++ b/asterixdb/asterix-doc/src/main/grammar/sqlpp.ebnf
@@ -213,6 +213,7 @@
 
 CreateSecondaryIndex ::= "CREATE" "INDEX" Identifier ("IF" "NOT" "EXISTS")? "ON" QualifiedName
                        "(" IndexedElement ( "," IndexedElement )* ")" ("TYPE" IndexType)? ("ENFORCED")?
+                       (( "EXCLUDE" | "INCLUDE" ) "UNKNOWN" "KEY")?
 
 CreatePrimaryKeyIndex ::= "CREATE" "PRIMARY" "INDEX" Identifier? ("IF" "NOT" "EXISTS")? "ON" QualifiedName ("TYPE" "BTREE")?
 
diff --git a/asterixdb/asterix-doc/src/main/markdown/sqlpp/7_ddl_dml.md b/asterixdb/asterix-doc/src/main/markdown/sqlpp/7_ddl_dml.md
index 967b277..4ec5033 100644
--- a/asterixdb/asterix-doc/src/main/markdown/sqlpp/7_ddl_dml.md
+++ b/asterixdb/asterix-doc/src/main/markdown/sqlpp/7_ddl_dml.md
@@ -339,6 +339,13 @@
 
     CREATE INDEX cCustIdx ON orders(custid) TYPE BTREE;
 
+The following example creates a btree index called `oCNameIdx` on the `cname` field of the orders dataset, but does not insert `NULL` and `MISSING` values into the index.
+By default, if `INCLUDE/EXCLUDE UNKNOWN KEY` is not specified, unknown values will be inserted into btree indexes.
+
+##### Example
+
+    CREATE INDEX oCNametIdx ON orders(cname) EXCLUDE UNKNOWN KEY;
+
 The following example creates an open btree index called `oCreatedTimeIdx` on the (non-declared) `createdTime` field of the `orders` dataset having `datetime` type.
 This index can be useful for accelerating exact-match queries, range search queries, and joins involving the `createdTime` field.
 The index is enforced so that records that do not have the `createdTime` field or have a mismatched type on the field
@@ -370,11 +377,11 @@
 
 The following example creates an array index called `oItemsPriceIdx` on the `price` field inside the `items` array of the `orders` dataset.
 This index can be useful for accelerating membership queries, existential or universal quantification queries, or joins involving the `price` field inside this array.
-(To enable array index query optimization, be sure to set the [`arrayindex` compiler option](manual.html#ArrayIndexFlag).)
+Unknown values cannot currently be stored inside array indexes, so `EXCLUDE UNKNOWN KEY` must be specified.
 
 #### Example
 
-    CREATE INDEX oItemsPriceIdx ON orders(UNNEST items SELECT price);
+    CREATE INDEX oItemsPriceIdx ON orders(UNNEST items SELECT price) EXCLUDE UNKNOWN KEY;
 
 The following example creates an open rtree index called `oOrderLocIdx` on the order-location field of the `orders` dataset. This index can be useful for accelerating queries that use the [`spatial-intersect` function](builtins.html#spatial_intersect) in a predicate involving the sender-location field.
 
diff --git a/asterixdb/asterix-doc/src/main/markdown/sqlpp/appendix_2_arrayindex.md b/asterixdb/asterix-doc/src/main/markdown/sqlpp/appendix_2_arrayindex.md
index ee90efb..c3c13fc 100644
--- a/asterixdb/asterix-doc/src/main/markdown/sqlpp/appendix_2_arrayindex.md
+++ b/asterixdb/asterix-doc/src/main/markdown/sqlpp/appendix_2_arrayindex.md
@@ -18,15 +18,15 @@
  !-->
 
 ## <a id="ArrayIndexFlag">Controlling Array-Index Access Method Plan Parameter</a>
-By default, the system does not attempt to utilize array indexes as an access method (even if an array index is present and is applicable).
-If you believe that your query will benefit from an array index, toggle the parameter below.
+By default, the system attempts to utilize array indexes as an access method if an array index is present and is applicable.
+If you believe that your query will not benefit from an array index, toggle the parameter below.
 
-*  **compiler.arrayindex**: if this is set to true, array indexes will be considered as an access method for applicable queries; the default value is false.
+*  **compiler.arrayindex**: if this is set to true, array indexes will be considered as an access method for applicable queries; the default value is true.
 
 
 #### Example
 
-    set `compiler.arrayindex` "true";
+    set `compiler.arrayindex` "false";
 
     SELECT o.orderno
     FROM orders o
diff --git a/asterixdb/asterix-doc/src/site/markdown/sqlpp/arrayindex.md b/asterixdb/asterix-doc/src/site/markdown/sqlpp/arrayindex.md
index d91e48e..cafa381 100644
--- a/asterixdb/asterix-doc/src/site/markdown/sqlpp/arrayindex.md
+++ b/asterixdb/asterix-doc/src/site/markdown/sqlpp/arrayindex.md
@@ -70,34 +70,28 @@
 
 Let us now create an index on the `categories` multiset of the `products` dataset.
 
-    CREATE INDEX pCategoriesIdx ON products (UNNEST categories);
+    CREATE INDEX pCategoriesIdx ON products (UNNEST categories) EXCLUDE UNKNOWN KEY;
 
 Suppose we now want to find all products that have the category "Food".
 The following membership query will utilize the index we just created.
 
-    SET `compiler.arrayindex` "true";
-
     SELECT p
     FROM products p
     WHERE "Food" IN p.categories;
 
 We can also rewrite the query above as an explicit existential quantification query with an equality predicate and the index will be utilized.
 
-    SET `compiler.arrayindex` "true";
-
     SELECT p
     FROM products p
     WHERE SOME c IN p.categories SATISFIES c = "Food";
 
 Let us now create an index on the `qty` and `price` fields in the `items` array of the `orders` dataset.
 
-    CREATE INDEX oItemsQtyPriceIdx ON orders (UNNEST items SELECT qty, price);
+    CREATE INDEX oItemsQtyPriceIdx ON orders (UNNEST items SELECT qty, price) EXCLUDE UNKNOWN KEY;
 
 Now suppose we want to find all orders that only have items with large quantities and low prices, not counting orders without any items.
 The following universal quantification query will utilize the index we just created.
 
-    SET `compiler.arrayindex` "true";
-
     SELECT o
     FROM orders o
     WHERE SOME AND EVERY i IN o.items SATISFIES i.qty > 100 AND i.price < 5.00;
@@ -112,8 +106,6 @@
 We can express the same membership / existential example above using an explicit `UNNEST` query.
 (To keep the same cardinality as the query above (i.e. to undo the `UNNEST`), we add a `DISTINCT` clause, though the index would be utilized either way.)
 
-    SET `compiler.arrayindex` "true";
-
     SELECT DISTINCT p
     FROM products p, p.categories c
     WHERE c = "Food";
@@ -121,8 +113,6 @@
 As another example, suppose that we want to find all orders that have *some* item with a large quantity.
 The following query will utilize the `oItemsQtyPriceIdx` we created, using only the first field in the index `qty`.
 
-    SET `compiler.arrayindex` "true";
-
     SELECT DISTINCT o
     FROM orders o, o.items i
     WHERE i.qty > 100;
@@ -133,18 +123,16 @@
 Finally, array indexes can also be used for index nested-loop joins if the field being joined is located within an array.
 Let us create another index on the `items` array of the `orders` dataset, this time on the `productno` field.
 
-    CREATE INDEX oProductIDIdx ON orders (UNNEST items SELECT productno);
+    CREATE INDEX oProductIDIdx ON orders (UNNEST items SELECT productno) EXCLUDE UNKNOWN KEY;
 
 Now suppose we want to find all products located in a specific order.
 We can accomplish this with the join query below.
 Note that we must specify the `indexnl` join hint to tell AsterixDB that we want to optimize this specific join, as hash joins are the default join method otherwise.
 
-    SET `compiler.arrayindex` "true";
-
     SELECT DISTINCT p
-    FROM products p, orders o, o.items i
-    WHERE i.productno /*+ indexnl */ = p.productno
-          AND o.custid = "C41";
+    FROM products p, orders o
+    WHERE o.custid = "C41" AND 
+          SOME i IN o.items SATISFIES i.productno /*+ indexnl */ = p.productno;
 
 
 ## <a id="ComplexIndexingExamples">Complex Indexing Examples</a> <font size="4"><a href="#toc">[Back to TOC]</a></font> ##
@@ -154,9 +142,17 @@
 Similar to atomic indexes, array indexes are not limited to closed fields.
 The following DDLs illustrate how we could express `CREATE INDEX` statements comparable to those above if the to-be-indexed fields were not included in the their dataset's type definitions.
 
-    CREATE INDEX pCategoriesIdx ON products (UNNEST categories : string);
-    CREATE INDEX oItemsQtyPriceIdx ON orders (UNNEST items SELECT qty : int, price : int);
-    CREATE INDEX oProductIDIdx ON orders (UNNEST items SELECT productno : int);
+    CREATE INDEX pCategoriesIdx ON products (UNNEST categories : string) EXCLUDE UNKNOWN KEY;
+    CREATE INDEX oItemsQtyPriceIdx ON orders (UNNEST items SELECT qty : int, price : int) EXCLUDE UNKNOWN KEY;
+    CREATE INDEX oProductIDIdx ON orders (UNNEST items SELECT productno : int) EXCLUDE UNKNOWN KEY;
+
+### Composite Atomic-Array Indexes
+
+Indexed elements within array indexes are also not limited to fields within arrays.
+The following DDLs demonstrate indexing fields that are within an array and fields that are outside any array.
+
+    CREATE INDEX oOrderNoItemPriceIdx ON orders (orderno, ( UNNEST items SELECT price )) EXCLUDE UNKNOWN KEY;
+    CREATE INDEX oOrderItemPriceNoIdx ON orders (( UNNEST items SELECT price ), orderno) EXCLUDE UNKNOWN KEY;
 
 ### Arrays in Arrays
 
@@ -165,17 +161,15 @@
 The following DDLs describe indexing the `qty` field in an `items` array at various depths.
 
     // { orderno: ..., items0: [ { items1: [ { qty: int, ... } ] } ] }
-    CREATE INDEX oItemItemQtyIdx ON orders (UNNEST items0 UNNEST items1 SELECT qty);
+    CREATE INDEX oItemItemQtyIdx ON orders (UNNEST items0 UNNEST items1 SELECT qty) EXCLUDE UNKNOWN KEY;
 
     // { orderno: ..., items0: [ { items1: [ { items2: [ { qty: int, ... } ] } ] } ] }
-    CREATE INDEX oItemItemItemQtyIdx ON orders (UNNEST items0 UNNEST items1 UNNEST items2 SELECT qty);
+    CREATE INDEX oItemItemItemQtyIdx ON orders (UNNEST items0 UNNEST items1 UNNEST items2 SELECT qty) EXCLUDE UNKNOWN KEY;
 
 The queries below will utilize the indexes above.
 The first query utilizes the `oItemItemQtyIdx` index through nested existential quantification.
 The second query utilizes the `oItemItemItemQtyIdx` index with three unnesting clauses.
 
-    SET `compiler.arrayindex` "true";
-
     SELECT o
     FROM orders o
     WHERE SOME o0 IN o.items0 SATISFIES (

-- 
To view, visit https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/13345
To unsubscribe, or for help writing mail filters, visit https://asterix-gerrit.ics.uci.edu/settings

Gerrit-Project: asterixdb
Gerrit-Branch: master
Gerrit-Change-Id: Ief37a9cb74440922c599ffb0189f44d2993f564b
Gerrit-Change-Number: 13345
Gerrit-PatchSet: 1
Gerrit-Owner: Glenn Galvizo <gg...@uci.edu>
Gerrit-MessageType: newchange