[Oracle Cloud] NoSQL Database Cloud Service の クエリーとIndexの関係性を検証してみた

# はじめに

Oracle Cloud Infrastructure(以下OCI)では、NoSQL Database Cloud Service が提供されています。フルマネージドな NoSQL サービスです。ユーザーはインスタンスを気にする必要なくデータストアを使うことが出来るのが特徴的です。

NoSQL Database Cloud Service には、SQLのようにデータを取得できるクエリー機能があります。[Document](https://docs.cloud.oracle.com/en-us/iaas/nosql-database/doc/query-language-reference.html) から引用しますが、SELECT文 と UPDATE文が利用できます。

```
SELECT <expression>
FROM <table name>
[WHERE <expression>]
[GROUP BY <expression>]
[ORDER BY <expression> [<sort order>]]
[OFFSET <number>]
[LIMIT <number>]; 

For example:
SELECT * FROM Users;
SELECT id, firstname, lastname FROM Users WHERE firstname = "Taylor";
```

```
UPDATE <table_name> [AS <table_alias>]
    <update_clause>[, <update_clause>]*
WHERE <expr>[<returning_clause>];

For example:
UPDATE JSONPersons $j
  SET TTL 1 DAYS
  WHERE id = 6
  RETURNING remaining_days($j) AS Expires;
```

また、上の例には書かれていない Built-in されている関数が使えます。似たような文字を検索するための、[regex_like 関数](https://docs.oracle.com/en/database/other-databases/nosql-database/19.5/sqlreferencefornosql/regexlike_function.html)など便利なものがあるため、一度確認してみるとよいでしょう。
注意点があります。regex_like 関数が書かれているのは、オンプレミスで提供されている NoSQL Database 製品の Document です。一部、クラウド上のサービスとは動作が異なる部分もあると思います。
なので、調べたいものがあるときは、まずは「Oracle Cloud Infrastructure の Document」を見たあとに、よくわからないものがあれば、「オンプレミスの Document」を見るのが良いと思います。

Oracle Cloud Infrastructure の Document
https://docs.cloud.oracle.com/en-us/iaas/nosql-database/index.html

オンプレミスの Document
https://docs.oracle.com/en/database/other-databases/nosql-database/19.5/index.html
https://docs.oracle.com/en/database/other-databases/nosql-database/19.5/sqlreferencefornosql/sql-query-management.html



それでは本題です。この記事では、クエリーを発行する方法をハマりポイントに合わせて紹介していきます。一部の句を使うときには、Index の設定が必要なものもあるので、そのあたりも紹介します。



# 事前準備

クエリーを発行するために、Table とスキーマと実際のデータを準備します。
`member` という名前のテーブルを作成し、次のスキーマを指定します。

![1591420339739.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/5ca69c2d-19d9-4f4c-7809-7130862d5daf.png)



Index は、age 列に設定をします。1個のテーブルにつき、5個までの Index 指定が出来ます。上限の詳細は次のDocument に記載されています。
https://docs.cloud.oracle.com/en-us/iaas/nosql-database/doc/cloud-limits.html

![1591422587948.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/1ebbe4e6-16d2-9881-d636-fb01131ad35b.png)



Table にデータをデータ格納します。手動でポチポチするのは面倒だったので、Java SDK で put をします。

```java
package com.example;

import java.io.File;

import java.util.ArrayList;

import oracle.nosql.driver.NoSQLHandle;
import oracle.nosql.driver.NoSQLHandleConfig;
import oracle.nosql.driver.NoSQLHandleFactory;
import oracle.nosql.driver.Region;
import oracle.nosql.driver.iam.SignatureProvider;
import oracle.nosql.driver.ops.PutRequest;
import oracle.nosql.driver.ops.PutResult;
import oracle.nosql.driver.ops.DeleteRequest;
import oracle.nosql.driver.ops.DeleteResult;
import oracle.nosql.driver.values.MapValue;

public class RowGenerate {
    /* Name of your table */
    final static String tableName = "member";

    public static void main(String[] args) throws Exception {
        System.out.println("Start!");

        NoSQLHandle handle = generateNoSQLHandle();

        try {
            deleteAllRows(handle);
            generateRows(handle);
        } catch (Exception e) {
            System.err.print(e);
        } finally {
            handle.close();
        }

        System.out.println("End!");
    }

    /* Create a NoSQL handle to access the cloud service */
    private static NoSQLHandle generateNoSQLHandle() throws Exception {

        SignatureProvider ap = new SignatureProvider(
                "ocid1.tenancy.oc1..secret",
                "ocid1.user.oc1..secret",
                "06:3b:a5:d2:52:00:9b:04:ed:fe:9d:9e:5c:b4:c5:00", new File("/home/opc/.oci/oci_api_key.pem"),
                "".toCharArray());

        /* Create a NoSQL handle to access the cloud service */
        NoSQLHandleConfig config = new NoSQLHandleConfig(Region.AP_TOKYO_1, ap);
        NoSQLHandle handle = NoSQLHandleFactory.createNoSQLHandle(config);
        return handle;
    }

    private static void deleteAllRows(NoSQLHandle handle) throws Exception {
        MapValue key = new MapValue();

        for (int i = 1; i < 11; i++) {
            key.put("id", i);

            DeleteRequest delRequest = new DeleteRequest().setKey(key).setTableName(tableName);

            DeleteResult del = handle.delete(delRequest);
            if (del.getSuccess()) {
                System.out.println("Delete succeed");
            }
        }
    }

    private static void generateRows(NoSQLHandle handle) throws Exception {
        ArrayList<MapValue> valueList = new ArrayList<MapValue>();

        valueList.add(new MapValue().put("id", 1).put("name", "杉山").put("address", "北海道札幌市").put("rank", "ブロンズ")
                .put("age", 30).put("income", 10000));
        valueList.add(new MapValue().put("id", 2).put("name", "鈴木").put("address", "東京都千代田区").put("rank", "シルバー")
                .put("age", 25).put("income", 20000));
        valueList.add(new MapValue().put("id", 3).put("name", "田中").put("address", "北海道千歳市").put("rank", "ブロンズ")
                .put("age", 35).put("income", 25000));
        valueList.add(new MapValue().put("id", 4).put("name", "加藤").put("address", "北海道名寄市").put("rank", "ゴールド")
                .put("age", 40).put("income", 15000));
        valueList.add(new MapValue().put("id", 5).put("name", "大塚").put("address", "北海道室蘭市").put("rank", "プラチナ")
                .put("age", 45).put("income", 11000));
        valueList.add(new MapValue().put("id", 6).put("name", "江藤").put("address", "北海道札幌市").put("rank", "ブロンズ")
                .put("age", 25).put("income", 21000));
        valueList.add(new MapValue().put("id", 7).put("name", "田野").put("address", "東京都豊島区").put("rank", "シルバー")
                .put("age", 25).put("income", 50000));
        valueList.add(new MapValue().put("id", 8).put("name", "田代").put("address", "北海道稚内市").put("rank", "ブロンズ")
                .put("age", 25).put("income", 9000));
        valueList.add(new MapValue().put("id", 9).put("name", "大石").put("address", "北海道登別市").put("rank", "ゴールド")
                .put("age", 25).put("income", 1000));
        valueList.add(new MapValue().put("id", 10).put("name", "町田").put("address", "北海道函館市").put("rank", "プラチナ")
                .put("age", 45).put("income", 10000));

        for (MapValue value : valueList) {
            PutRequest putRequest = new PutRequest().setValue(value).setTableName(tableName);

            PutResult putRes = handle.put(putRequest);
            System.out.println("Put row: " + value + " result=" + putRes);
        }
    }
}
```



# Primary Key で取得

**Primary Key 指定で取得**

クエリーに入る前に、Primary Key を使った通常(?)の取得方法を確認します。id が 1 のデータを取得します。

```java
    private static void readRows(NoSQLHandle handle) throws Exception {
        MapValue key = new MapValue().put("id", 1);
        GetRequest getRequest = new GetRequest().setKey(key).setTableName(tableName);
        GetResult getRes = handle.get(getRequest);
        System.out.println("Read " + getRes.getValue());
    }
```



実行結果です。正常に取得できました。

```
[opc@maven nosql]$  cd /home/opc/java/nosql ; /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.252.b09-2.el7_8.x86_64/bin/java -Dfile.encoding=UTF-8 -cp /tmp/cp_2qd8pp7cihzbqrkegu8ef384z.jar com.example.App 
Start!
Read {"id":1,"name":"杉山","address":"北海道札幌市","rank":"ブロンズ"}
End!
```



**通常 Column 指定で取得**

この取得方法を通常の Column を指定してみるとどうなるか確認してみましょう。name が 杉山 のデータを取得しようとしてみます。

```java
    private static void readRows(NoSQLHandle handle) throws Exception {
        MapValue key = new MapValue().put("name", "杉山");
        GetRequest getRequest = new GetRequest().setKey(key).setTableName(tableName);
        GetResult getRes = handle.get(getRequest);
        System.out.println("Read " + getRes.getValue());
    }
```



例外が発生しました。通常の Column を指定した取得は出来ません。Primary Key ではないのでそりゃそうです。

```
[opc@maven nosql]$  cd /home/opc/java/nosql ; /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.252.b09-2.el7_8.x86_64/bin/java -Dfile.encoding=UTF-8 -cp /tmp/cp_2qd8pp7cihzbqrkegu8ef384z.jar com.example.App 
Start!
java.lang.IllegalArgumentException: GET: Illegal Argument: Missing primary key field: id
End!
[opc@maven nosql]$ 
```



**Index な Column 指定で取得**

じゃあ、次は Index 付の Column で取得してみましょう。Index 付だとどうなるのか。age 列に Index を指定しています。

```java
    private static void readRows(NoSQLHandle handle) throws Exception {
        MapValue key = new MapValue().put("age", 30);
        GetRequest getRequest = new GetRequest().setKey(key).setTableName("member");
        GetResult getRes = handle.get(getRequest);
        System.out.println("Read " + getRes.getValue());
    }
```



こちらも同様の例外です。Index は関係ないようですね。

```
[opc@maven nosql]$  cd /home/opc/java/nosql ; /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.252.b09-2.el7_8.x86_64/bin/java -Dfile.encoding=UTF-8 -cp /tmp/cp_2qd8pp7cihzbqrkegu8ef384z.jar com.example.App 
Start!
java.lang.IllegalArgumentException: GET: Illegal Argument: Missing primary key field: id
End!
```



# クエリー で取得

## Simple Select

それでは、次に SQL ライクなクエリーで取得をしてみましょう。まずはシンプルに全てのデータを取得してみます。

```java
    private static void queryRows(NoSQLHandle handle) throws Exception {
        String query = "SELECT * from member";

        List<MapValue> results = runQuery(handle, query);

        System.out.println("Number of query results for " + query + " \nRows:" + results.size());
        for (MapValue qval : results) {
            System.out.println("\t" + qval.toString());
        }
    }
```



実行結果です。全てのデータが取得できました。SQL ライクに取得できるのは直感的でよいですね。

```
[opc@maven nosql]$  cd /home/opc/java/nosql ; /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.252.b09-2.el7_8.x86_64/bin/java -Dfile.encoding=UTF-8 -cp /tmp/cp_2qd8pp7cihzbqrkegu8ef384z.jar com.example.App 
Start!
Number of query results for SELECT * from member 
Rows:10
        {"id":3,"name":"田中","address":"北海道千歳市","rank":"ブロンズ","age":35,"income":25000}
        {"id":5,"name":"大塚","address":"北海道室蘭市","rank":"プラチナ","age":45,"income":11000}
        {"id":7,"name":"田野","address":"東京都豊島区","rank":"シルバー","age":25,"income":50000}
        {"id":8,"name":"田代","address":"北海道稚内市","rank":"ブロンズ","age":25,"income":9000}
        {"id":6,"name":"江藤","address":"北海道札幌市","rank":"ブロンズ","age":25,"income":21000}
        {"id":10,"name":"町田","address":"北海道函館市","rank":"プラチナ","age":45,"income":10000}
        {"id":1,"name":"杉山","address":"北海道札幌市","rank":"ブロンズ","age":30,"income":10000}
        {"id":4,"name":"加藤","address":"北海道名寄市","rank":"ゴールド","age":40,"income":15000}
        {"id":2,"name":"鈴木","address":"東京都千代田区","rank":"シルバー","age":25,"income":20000}
        {"id":9,"name":"大石","address":"北海道登別市","rank":"ゴールド","age":25,"income":1000}
End!
```



## WHERE (Index無し)

それでは次に、WHERE 句で取得するデータを制御してみましょう。Index を指定していない name 列を使ってみます。

```java
    private static void queryRows(NoSQLHandle handle) throws Exception {
        String query = "SELECT * from member WHERE name = '杉山'";

        List<MapValue> results = runQuery(handle, query);

        System.out.println("Number of query results for " + query + " \nRows:" + results.size());
        for (MapValue qval : results) {
            System.out.println("\t" + qval.toString());
        }
    }
```



実行結果です。Index に指定していない列にもかかわらず、クエリーが出来ました。データ量が多くなってきた時の効率性まではこの検証ではわかりませんが、非常に便利でよさそうですね。データ量が多くなってきて効率が落ちるようなら、Index を指定すると良さそうな気がします。(試していません)

```
[opc@maven nosql]$  cd /home/opc/java/nosql ; /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.252.b09-2.el7_8.x86_64/bin/java -Dfile.encoding=UTF-8 -cp /tmp/cp_2qd8pp7cihzbqrkegu8ef384z.jar com.example.App 
Start!
Number of query results for SELECT * from member WHERE name = '杉山' 
Rows:1
        {"id":1,"name":"杉山","address":"北海道札幌市","rank":"ブロンズ","age":30,"income":10000}
End!
```



## WHERE (Index有り)

念のため、Index 有りでもクエリーが実行出来ることを確認します。ちょっと複雑な条件にしてみましょう。AND 条件を付けます。

```java
    private static void queryRows(NoSQLHandle handle) throws Exception {
        String query = "SELECT * from member WHERE age > 20 AND age < 30";

        List<MapValue> results = runQuery(handle, query);

        System.out.println("Number of query results for " + query + " \nRows:" + results.size());
        for (MapValue qval : results) {
            System.out.println("\t" + qval.toString());
        }
    }
```



AND 条件の結果がちゃんと反映されていますね。便利。

```
[opc@maven nosql]$  cd /home/opc/java/nosql ; /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.252.b09-2.el7_8.x86_64/bin/java -Dfile.encoding=UTF-8 -cp /tmp/cp_2qd8pp7cihzbqrkegu8ef384z.jar com.example.App 
Start!
Number of query results for SELECT * from member WHERE age > 20 AND age < 30 
Rows:5
        {"id":7,"name":"田野","address":"東京都豊島区","rank":"シルバー","age":25,"income":50000}
        {"id":8,"name":"田代","address":"北海道稚内市","rank":"ブロンズ","age":25,"income":9000}
        {"id":6,"name":"江藤","address":"北海道札幌市","rank":"ブロンズ","age":25,"income":21000}
        {"id":2,"name":"鈴木","address":"東京都千代田区","rank":"シルバー","age":25,"income":20000}
        {"id":9,"name":"大石","address":"北海道登別市","rank":"ゴールド","age":25,"income":1000}
End!
```



## LIKE (regrex_like)

正規表現による文字列検索が出来ます。LIKE句ではないですが、regrex_like関数が提供されています。Index が設定されていない列でも、regrex_likeが使えます。
https://docs.oracle.com/en/database/other-databases/nosql-database/19.5/sqlreferencefornosql/regexlike_function.html

北海道から始まる住所の人で絞り込んで検索してみます。

```java
    private static void queryRows(NoSQLHandle handle) throws Exception {
        String query = "SELECT * from member WHERE regex_like(address, '北海道.*')";

        List<MapValue> results = runQuery(handle, query);

        System.out.println("Number of query results for " + query + " \nRows:" + results.size());
        for (MapValue qval : results) {
            System.out.println("\t" + qval.toString());
        }
    }
```



正常に絞り込めています。すごい便利ですね。

```
[opc@maven nosql]$  cd /home/opc/java/nosql ; /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.252.b09-2.el7_8.x86_64/bin/java -Dfile.encoding=UTF-8 -cp /tmp/cp_2qd8pp7cihzbqrkegu8ef384z.jar com.example.App 
Start!
Number of query results for SELECT * from member WHERE regex_like(address, '北海道.*') 
Rows:8
        {"id":3,"name":"田中","address":"北海道千歳市","rank":"ブロンズ","age":35,"income":25000}
        {"id":5,"name":"大塚","address":"北海道室蘭市","rank":"プラチナ","age":45,"income":11000}
        {"id":8,"name":"田代","address":"北海道稚内市","rank":"ブロンズ","age":25,"income":9000}
        {"id":6,"name":"江藤","address":"北海道札幌市","rank":"ブロンズ","age":25,"income":21000}
        {"id":10,"name":"町田","address":"北海道函館市","rank":"プラチナ","age":45,"income":10000}
        {"id":1,"name":"杉山","address":"北海道札幌市","rank":"ブロンズ","age":30,"income":10000}
        {"id":4,"name":"加藤","address":"北海道名寄市","rank":"ゴールド","age":40,"income":15000}
        {"id":9,"name":"大石","address":"北海道登別市","rank":"ゴールド","age":25,"income":1000}
End!
```



ちなみに、NOT も使えます。北海道から始まらない人たちを検索です。

```java
    private static void queryRows(NoSQLHandle handle) throws Exception {
        String query = "SELECT * from member WHERE NOT regex_like(address, '北海道.*')";

        List<MapValue> results = runQuery(handle, query);

        System.out.println("Number of query results for " + query + " \nRows:" + results.size());
        for (MapValue qval : results) {
            System.out.println("\t" + qval.toString());
        }
    }
```



北海道から始まらない人たち全員が検索できました。

```
[opc@maven nosql]$  cd /home/opc/java/nosql ; /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.252.b09-2.el7_8.x86_64/bin/java -Dfile.encoding=UTF-8 -cp /tmp/cp_2qd8pp7cihzbqrkegu8ef384z.jar com.example.App 
Start!
Number of query results for SELECT * from member WHERE NOT regex_like(address, '北海道.*') 
Rows:2
        {"id":7,"name":"田野","address":"東京都豊島区","rank":"シルバー","age":25,"income":50000}
        {"id":2,"name":"鈴木","address":"東京都千代田区","rank":"シルバー","age":25,"income":20000}
End!
```





## ORDER BY

ORDER BY でソートが出来ます。ただし、**ORDER BY で指定する列には、Indexが必要です。**Index の追加はポチポチと数クリックで簡単にできるので、そうそう問題になることはないと思います。唯一あるとすると、Table あたりに 5個までしか Index が設定できません。

https://docs.oracle.com/en/database/other-databases/nosql-database/19.5/sqlreferencefornosql/order-clause.html



10 才以上を id でソートしてみます。

```java
    private static void queryRows(NoSQLHandle handle) throws Exception {
        String query = "SELECT * FROM member where age > 10 ORDER BY id";

        List<MapValue> results = runQuery(handle, query);

        System.out.println("Number of query results for " + query + " \nRows:" + results.size());
        for (MapValue qval : results) {
            System.out.println("\t" + qval.toString());
        }
    }
```



Result

```
[opc@maven nosql]$  cd /home/opc/java/nosql ; /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.252.b09-2.el7_8.x86_64/bin/java -Dfile.encoding=UTF-8 -cp /tmp/cp_2qd8pp7cihzbqrkegu8ef384z.jar com.example.App 
Start!
Number of query results for SELECT * FROM member where age > 10 ORDER BY id 
Rows:10
        {"id":1,"name":"杉山","address":"北海道札幌市","rank":"ブロンズ","age":30,"income":10000}
        {"id":2,"name":"鈴木","address":"東京都千代田区","rank":"シルバー","age":25,"income":20000}
        {"id":3,"name":"田中","address":"北海道千歳市","rank":"ブロンズ","age":35,"income":25000}
        {"id":4,"name":"加藤","address":"北海道名寄市","rank":"ゴールド","age":40,"income":15000}
        {"id":5,"name":"大塚","address":"北海道室蘭市","rank":"プラチナ","age":45,"income":11000}
        {"id":6,"name":"江藤","address":"北海道札幌市","rank":"ブロンズ","age":25,"income":21000}
        {"id":7,"name":"田野","address":"東京都豊島区","rank":"シルバー","age":25,"income":50000}
        {"id":8,"name":"田代","address":"北海道稚内市","rank":"ブロンズ","age":25,"income":9000}
        {"id":9,"name":"大石","address":"北海道登別市","rank":"ゴールド","age":25,"income":1000}
        {"id":10,"name":"町田","address":"北海道函館市","rank":"プラチナ","age":45,"income":10000}
End!
[opc@maven nosql]$ 
```



逆順も当然できます。

```java
    private static void queryRows(NoSQLHandle handle) throws Exception {
        String query = "SELECT * FROM member where age > 10 ORDER BY id DESC";

        List<MapValue> results = runQuery(handle, query);

        System.out.println("Number of query results for " + query + " \nRows:" + results.size());
        for (MapValue qval : results) {
            System.out.println("\t" + qval.toString());
        }
    }
```



Result

```
[opc@maven nosql]$  cd /home/opc/java/nosql ; /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.252.b09-2.el7_8.x86_64/bin/java -Dfile.encoding=UTF-8 -cp /tmp/cp_2qd8pp7cihzbqrkegu8ef384z.jar com.example.App 
Start!
Number of query results for SELECT * FROM member where age > 10 ORDER BY id DESC 
Rows:10
        {"id":10,"name":"町田","address":"北海道函館市","rank":"プラチナ","age":45,"income":10000}
        {"id":9,"name":"大石","address":"北海道登別市","rank":"ゴールド","age":25,"income":1000}
        {"id":8,"name":"田代","address":"北海道稚内市","rank":"ブロンズ","age":25,"income":9000}
        {"id":7,"name":"田野","address":"東京都豊島区","rank":"シルバー","age":25,"income":50000}
        {"id":6,"name":"江藤","address":"北海道札幌市","rank":"ブロンズ","age":25,"income":21000}
        {"id":5,"name":"大塚","address":"北海道室蘭市","rank":"プラチナ","age":45,"income":11000}
        {"id":4,"name":"加藤","address":"北海道名寄市","rank":"ゴールド","age":40,"income":15000}
        {"id":3,"name":"田中","address":"北海道千歳市","rank":"ブロンズ","age":35,"income":25000}
        {"id":2,"name":"鈴木","address":"東京都千代田区","rank":"シルバー","age":25,"income":20000}
        {"id":1,"name":"杉山","address":"北海道札幌市","rank":"ブロンズ","age":30,"income":10000}
End!
```



ここで1つハマりポイントがあります。Java SDK 上だと問題なく ORDER BY でソートが出来ますが、OCI コンソール上だとエラーとなります。エラーの内容は次の内容となっていて、なにやら自分が作った クエリーの文字列に誤りがありそうな気がしますが、そんなことはないです。**Java SDK と OCI Console では挙動が違います。**

```
QUERY: Illegal Argument: The driver or SDK being used does not support queries with order by expressions that do not include a "where" clause specifying a complete shard key
```



実際の画面ではこんな感じです。このエラーが出たときは、是非 Java SDK でやってみましょう。

![1591168677941.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/3bd93e39-e79c-1aaf-e9d7-1a8e674e4cfe.png)



## GROUP BY

GROUP BY でグループごとに集計が出来ます。ただし、**GROUP BY で指定する列には、Indexが必要です。**

https://docs.oracle.com/en/database/other-databases/nosql-database/19.5/sqlreferencefornosql/group-clause.html



年齢ごとの人数と収入(income)を集計します。count も使えますし、avg も使えます。

```java
    private static void queryRows(NoSQLHandle handle) throws Exception {
        String query = "SELECT age, count(age) AS count, avg(income) AS income FROM member GROUP BY age";

        List<MapValue> results = runQuery(handle, query);

        System.out.println("Number of query results for " + query + " \nRows:" + results.size());
        for (MapValue qval : results) {
            System.out.println("\t" + qval.toString());
        }
    }
```



実行結果です。

```
[opc@maven nosql]$  cd /home/opc/java/nosql ; /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.252.b09-2.el7_8.x86_64/bin/java -Dfile.encoding=UTF-8 -cp /tmp/cp_2qd8pp7cihzbqrkegu8ef384z.jar com.example.App 
Start!
Number of query results for SELECT age, count(age) AS count, avg(income) AS income FROM member GROUP BY age 
Rows:5
        {"income":20200.0,"count":5,"age":25}
        {"income":10000.0,"count":1,"age":30}
        {"income":25000.0,"count":1,"age":35}
        {"income":15000.0,"count":1,"age":40}
        {"income":10500.0,"count":2,"age":45}
End!
```



リリースノートを見ると、次の5種類の function が使えると書いています。実際試したところ、5種類全て使えました。

- count
- sum
- avg
- min
- max

https://docs.oracle.com/en/database/other-databases/nosql-database/19.5/changelog/changes-18.1.13.html



## LIMIT

https://docs.oracle.com/en/database/other-databases/nosql-database/19.5/sqlreferencefornosql/limit-clause.html

LIMIT 句では、取得する行数を制限することが出来ます。LIMIT 句自体はIndex は特に関係ありません。ORDER BY と併用するときは、そちらには必要です。
id を昇順に並べたときの、先頭の３人を取得します

```java
    private static void queryRows(NoSQLHandle handle) throws Exception {
        String query = "SELECT * FROM member ORDER BY id LIMIT 3";

        List<MapValue> results = runQuery(handle, query);

        System.out.println("Number of query results for " + query + " \nRows:" + results.size());
        for (MapValue qval : results) {
            System.out.println("\t" + qval.toString());
        }
    }
```



実行結果です。1, 2, 3 の id の人が取得できましたね。

```
[opc@maven nosql]$  cd /home/opc/java/nosql ; /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.252.b09-2.el7_8.x86_64/bin/java -Dfile.encoding=UTF-8 -cp /tmp/cp_2qd8pp7cihzbqrkegu8ef384z.jar com.example.App 
Start!
Number of query results for SELECT * FROM member ORDER BY id LIMIT 3 
Rows:3
        {"id":1,"name":"杉山","address":"北海道札幌市","rank":"ブロンズ","age":30,"income":10000}
        {"id":2,"name":"鈴木","address":"東京都千代田区","rank":"シルバー","age":25,"income":20000}
        {"id":3,"name":"田中","address":"北海道千歳市","rank":"ブロンズ","age":35,"income":25000}
End!
```



逆順でも見てみましょう。

```java
    private static void queryRows(NoSQLHandle handle) throws Exception {
        String query = "SELECT * FROM member ORDER BY id DESC LIMIT 3";

        List<MapValue> results = runQuery(handle, query);

        System.out.println("Number of query results for " + query + " \nRows:" + results.size());
        for (MapValue qval : results) {
            System.out.println("\t" + qval.toString());
        }
    }
```



実行結果です。10, 9, 8 の id の人が取得できましたね。

```
[opc@maven nosql]$  cd /home/opc/java/nosql ; /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.252.b09-2.el7_8.x86_64/bin/java -Dfile.encoding=UTF-8 -cp /tmp/cp_2qd8pp7cihzbqrkegu8ef384z.jar com.example.App 
Start!
Number of query results for SELECT * FROM member ORDER BY id DESC LIMIT 3 
Rows:3
        {"id":10,"name":"町田","address":"北海道函館市","rank":"プラチナ","age":45,"income":10000}
        {"id":9,"name":"大石","address":"北海道登別市","rank":"ゴールド","age":25,"income":1000}
        {"id":8,"name":"田代","address":"北海道稚内市","rank":"ブロンズ","age":25,"income":9000}
End!
```





## OFFSET

https://docs.oracle.com/en/database/other-databases/nosql-database/19.5/sqlreferencefornosql/offset-clause.html

OFFSET 句では指定した数分、取得する行をスキップが出来ます。OFFSET句自体は Index は不要です。
まず、OFFSET 句無しで全員分を取得してみます。

```java
    private static void queryRows(NoSQLHandle handle) throws Exception {
        String query = "SELECT * FROM member ORDER BY id";

        List<MapValue> results = runQuery(handle, query);

        System.out.println("Number of query results for " + query + " \nRows:" + results.size());
        for (MapValue qval : results) {
            System.out.println("\t" + qval.toString());
        }
    }
```



1 ~ 10 人の member が検索できました。

```
[opc@maven nosql]$  cd /home/opc/java/nosql ; /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.252.b09-2.el7_8.x86_64/bin/java -Dfile.encoding=UTF-8 -cp /tmp/cp_2qd8pp7cihzbqrkegu8ef384z.jar com.example.App 
Start!
Number of query results for SELECT * FROM member ORDER BY id 
Rows:10
        {"id":1,"name":"杉山","address":"北海道札幌市","rank":"ブロンズ","age":30,"income":10000}
        {"id":2,"name":"鈴木","address":"東京都千代田区","rank":"シルバー","age":25,"income":20000}
        {"id":3,"name":"田中","address":"北海道千歳市","rank":"ブロンズ","age":35,"income":25000}
        {"id":4,"name":"加藤","address":"北海道名寄市","rank":"ゴールド","age":40,"income":15000}
        {"id":5,"name":"大塚","address":"北海道室蘭市","rank":"プラチナ","age":45,"income":11000}
        {"id":6,"name":"江藤","address":"北海道札幌市","rank":"ブロンズ","age":25,"income":21000}
        {"id":7,"name":"田野","address":"東京都豊島区","rank":"シルバー","age":25,"income":50000}
        {"id":8,"name":"田代","address":"北海道稚内市","rank":"ブロンズ","age":25,"income":9000}
        {"id":9,"name":"大石","address":"北海道登別市","rank":"ゴールド","age":25,"income":1000}
        {"id":10,"name":"町田","address":"北海道函館市","rank":"プラチナ","age":45,"income":10000}
End!
[opc@maven nosql]$ 
```



OFFSET 1 を指定してみます

```java
    private static void queryRows(NoSQLHandle handle) throws Exception {
        String query = "SELECT * FROM member ORDER BY id OFFSET 1";

        List<MapValue> results = runQuery(handle, query);

        System.out.println("Number of query results for " + query + " \nRows:" + results.size());
        for (MapValue qval : results) {
            System.out.println("\t" + qval.toString());
        }
    }
```



実行結果です。1行分スキップできました。

```
[opc@maven nosql]$  cd /home/opc/java/nosql ; /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.252.b09-2.el7_8.x86_64/bin/java -Dfile.encoding=UTF-8 -cp /tmp/cp_2qd8pp7cihzbqrkegu8ef384z.jar com.example.App 
Start!
Number of query results for SELECT * FROM member ORDER BY id OFFSET 1 
Rows:9
        {"id":2,"name":"鈴木","address":"東京都千代田区","rank":"シルバー","age":25,"income":20000}
        {"id":3,"name":"田中","address":"北海道千歳市","rank":"ブロンズ","age":35,"income":25000}
        {"id":4,"name":"加藤","address":"北海道名寄市","rank":"ゴールド","age":40,"income":15000}
        {"id":5,"name":"大塚","address":"北海道室蘭市","rank":"プラチナ","age":45,"income":11000}
        {"id":6,"name":"江藤","address":"北海道札幌市","rank":"ブロンズ","age":25,"income":21000}
        {"id":7,"name":"田野","address":"東京都豊島区","rank":"シルバー","age":25,"income":50000}
        {"id":8,"name":"田代","address":"北海道稚内市","rank":"ブロンズ","age":25,"income":9000}
        {"id":9,"name":"大石","address":"北海道登別市","rank":"ゴールド","age":25,"income":1000}
        {"id":10,"name":"町田","address":"北海道函館市","rank":"プラチナ","age":45,"income":10000}
End!
[opc@maven nosql]$ 
```



## LIMIT と OFFSET

では次に、LIMIT と OFFSET 句を両方指定してみます。

```java
    private static void queryRows(NoSQLHandle handle) throws Exception {
        String query = "SELECT * FROM member ORDER BY id LIMIT 3 OFFSET 1";

        List<MapValue> results = runQuery(handle, query);

        System.out.println("Number of query results for " + query + " \nRows:" + results.size());
        for (MapValue qval : results) {
            System.out.println("\t" + qval.toString());
        }
    }
```



実行結果です。OFFSET で1行分取得をスキップして、そこから LIMIT で 3行分取得しました。ページャー実装がやりやすくなるようなクエリーが出来ましたね。便利です。

```
[opc@maven nosql]$  cd /home/opc/java/nosql ; /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.252.b09-2.el7_8.x86_64/bin/java -Dfile.encoding=UTF-8 -cp /tmp/cp_2qd8pp7cihzbqrkegu8ef384z.jar com.example.App 
Start!
Number of query results for SELECT * FROM member ORDER BY id LIMIT 3 OFFSET 1 
Rows:3
        {"id":2,"name":"鈴木","address":"東京都千代田区","rank":"シルバー","age":25,"income":20000}
        {"id":3,"name":"田中","address":"北海道千歳市","rank":"ブロンズ","age":35,"income":25000}
        {"id":4,"name":"加藤","address":"北海道名寄市","rank":"ゴールド","age":40,"income":15000}
End!
```



# まとめ

クエリーの使い方や Index の必要有無を検証しました。ORDER BY や GROUP BY 句は Index が必要ではあるものの、他の句は Index は不要です。NoSQL なので若干の制限はあるものの、かなり柔軟にデータを取得できることがわかりました。また、Index の追加自体も後から簡単に追加出来るので、ORDER BY や GROUP BY などで必要になったら追加する運用も出来ます。(5個までの制限はありますが、、、)

しかし、複数の Table をまたがった JOIN やトランザクション処理は、2020年6月現在、出来ません。
1 Table 内で済む、単純な処理をフルマネージドに行うユースケースは NoSQL Database Cloud Service が得意な処理でしょう。
複雑な Tableを使用したトランザクション処理を実行するシステムは、従来の RDBMS が得意な処置です。

システムの特性に応じて、使い分ける必要がありますね。



# 付録 : ソースコード

```java
package com.example;

import java.io.File;

import java.util.ArrayList;
import java.util.List;

import oracle.nosql.driver.NoSQLHandle;
import oracle.nosql.driver.NoSQLHandleConfig;
import oracle.nosql.driver.NoSQLHandleFactory;
import oracle.nosql.driver.Region;
import oracle.nosql.driver.iam.SignatureProvider;
import oracle.nosql.driver.ops.GetRequest;
import oracle.nosql.driver.ops.GetResult;
// import oracle.nosql.driver.ops.PutRequest;
// import oracle.nosql.driver.ops.PutResult;
// import oracle.nosql.driver.ops.TableLimits;
// import oracle.nosql.driver.ops.TableRequest;
// import oracle.nosql.driver.ops.TableResult;
import oracle.nosql.driver.values.MapValue;
import oracle.nosql.driver.ops.PrepareRequest;
import oracle.nosql.driver.ops.PrepareResult;
import oracle.nosql.driver.ops.QueryRequest;
import oracle.nosql.driver.ops.QueryResult;
import oracle.nosql.driver.ReadThrottlingException;

/**
 * Hello world!
 *
 */
public class App {
    /* Name of your table */
    // final static String tableName = "member";

    public static void main(String[] args) throws Exception {
        System.out.println("Start!");

        NoSQLHandle handle = generateNoSQLHandle();

        try {
            // readRows(handle);
            queryRows(handle);
        } catch (Exception e) {
            System.err.println(e);
        } finally {
            handle.close();
        }

        System.out.println("End!");
    }

    /* Create a NoSQL handle to access the cloud service */
    private static NoSQLHandle generateNoSQLHandle() throws Exception {

        SignatureProvider ap = new SignatureProvider(
                "ocid1.tenancy.oc1..secret",
                "ocid1.user.oc1..secret",
                "06:3b:XX:XX:XX:XX:XX:04:ed:fe:9d:9e:5c:b4:c5:a5", new File("/home/opc/.oci/oci_api_key.pem"),
                "".toCharArray());

        /* Create a NoSQL handle to access the cloud service */
        NoSQLHandleConfig config = new NoSQLHandleConfig(Region.AP_TOKYO_1, ap);
        NoSQLHandle handle = NoSQLHandleFactory.createNoSQLHandle(config);
        return handle;
    }

    /**
     * Make a row in the table and write it
     */
    // private static void writeRows(NoSQLHandle handle) throws Exception {
    // MapValue value = new MapValue().put("employeeid", 1).put("name", "Tracy");
    // PutRequest putRequest = new
    // PutRequest().setValue(value).setTableName(tableName);
    // PutResult putResult = handle.put(putRequest);
    // if (putResult.getVersion() != null) {
    // System.out.println("Wrote " + value);
    // } else {
    // System.out.println("Put failed");
    // }
    // }

    /**
     * Make a key and read the row from the table
     */
    private static void readRows(NoSQLHandle handle) throws Exception {
        MapValue key = new MapValue().put("age", 30);
        GetRequest getRequest = new GetRequest().setKey(key).setTableName("member");
        GetResult getRes = handle.get(getRequest);
        System.out.println("Read " + getRes.getValue());
    }

    /*
     * QUERY the table. The table name is inferred from the query statement.
     */
    private static void queryRows(NoSQLHandle handle) throws Exception {
        String query = "SELECT * FROM member ORDER BY id LIMIT 3 OFFSET 1";

        List<MapValue> results = runQuery(handle, query);

        System.out.println("Number of query results for " + query + " \nRows:" + results.size());
        for (MapValue qval : results) {
            System.out.println("\t" + qval.toString());
        }
    }

    /**
     * Runs a query in a loop to be sure that all results have been returned. This
     * method returns a single list of results, which is not recommended for queries
     * that return a large number of results. This method mostly demonstrates the
     * need to loop if a query result includes a continuation key.
     *
     * This is a common occurrence because each query request will only read a
     * limited amount of data before returning a response. It is valid, and expected
     * that a query result can have a continuation key but not contain any actual
     * results. This occurs if the read limit is reached before any results match a
     * predicate in the query. It is also expected for count(*) types of queries.
     *
     * NOTE: for better performance and less throughput consumption prepared queries
     * should be used
     */
    public static List<MapValue> runQuery(NoSQLHandle handle, String query) {

        /* A List to contain the results */
        List<MapValue> results = new ArrayList<MapValue>();

        /*
         * A prepared statement is used as it is the most efficient way to handle
         * queries that are run multiple times.
         */
        PrepareRequest pReq = new PrepareRequest().setStatement(query);
        PrepareResult pRes = handle.prepare(pReq);
        QueryRequest qr = new QueryRequest().setPreparedStatement(pRes.getPreparedStatement());

        try {
            do {
                QueryResult res = handle.query(qr);
                int num = res.getResults().size();
                if (num > 0) {
                    results.addAll(res.getResults());
                }
                /*
                 * Maybe add a delay or other synchronization here for rate-limiting.
                 */
            } while (!qr.isDone());
        } catch (ReadThrottlingException rte) {
            /*
             * Applications need to be able to handle throttling exceptions. The default
             * retry handler may retry these internally. This can result in slow
             * performance. It is best if an application rate-limits itself to stay under a
             * table's read and write limits.
             */
        }
        return results;
    }
}
```





# 参考URL

https://docs.oracle.com/en/cloud/paas/nosql-cloud/csnjv/index.html?oracle/nosql/driver/ops/package-summary.html

ops
https://docs.oracle.com/en/cloud/paas/nosql-cloud/csnjv/index.html?oracle/nosql/driver/ops/package-summary.html

Get Request
https://docs.oracle.com/en/cloud/paas/nosql-cloud/csnjv/oracle/nosql/driver/ops/GetRequest.html

Query Language Reference
https://docs.cloud.oracle.com/en-us/iaas/nosql-database/doc/query-language-reference.html

Query Language Reference Detail
https://docs.oracle.com/en/database/other-databases/nosql-database/19.5/sqlreferencefornosql/sql-query-management.html