This is an automated email from the ASF dual-hosted git repository.
dlych pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/asterixdb.git
The following commit(s) were added to refs/heads/master by this push:
new db2d0e7 [NO-ISSUE][DOC] Update docs for INCLUDE/EXCLUDE unknown key
db2d0e7 is described below
commit db2d0e70e5d6e6338bd72e04f4d62d9bde122834
Author: ggalvizo <[email protected]>
AuthorDate: Wed Sep 22 10:55:05 2021 -0700
[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
Reviewed-on: https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/13345
Integration-Tests: Jenkins <[email protected]>
Tested-by: Jenkins <[email protected]>
Reviewed-by: Glenn Galvizo <[email protected]>
Reviewed-by: Dmitry Lychagin <[email protected]>
---
asterixdb/asterix-doc/src/main/grammar/sqlpp.ebnf | 1 +
.../src/main/markdown/sqlpp/7_ddl_dml.md | 11 +++++-
.../main/markdown/sqlpp/appendix_2_arrayindex.md | 8 ++--
.../src/site/markdown/sqlpp/arrayindex.md | 44 ++++++++++------------
4 files changed, 33 insertions(+), 31 deletions(-)
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 @@ CreateIndex ::= CreateSecondaryIndex | CreatePrimaryKeyIndex
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 @@ This index can be useful for accelerating exact-match
queries, range search quer
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 @@ and joins involving the nested `orderUserName` field.
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 @@ All query examples here will use the orders and products
datasets below.
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 @@ Array indexes can also be used to accelerate queries that
involve the explicit u
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 @@ We can express the same membership / existential example
above using an explicit
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 @@ The following query will utilize the `oItemsQtyPriceIdx`
we created, using only
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 @@ Note that we must specify the `indexnl` join hint to tell
AsterixDB that we want
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 @@ We can generalize this to arrays of arbitrary depth, as
long as an object encaps
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 (
