複数キー索引
複数キー索引によって、配列のすべての要素、またはマップのすべての要素またはすべてのキー(あるいはその両方)が索引付けされます。このような索引の場合、表の各行の索引には、索引付けされる配列やマップ内の要素またはエントリと同じ数のエントリが含まれます。索引付けできるのは、1つの配列またはマップのみです。
sql-> create index idx_areacode on
Persons (address.phones[].areacode);
Statement completed successfully
sql-> SELECT * FROM Persons p WHERE
p.address.phones.areacode =any 339;
> Row 0
+-------------+------------------------------------------------+
| id | 2 |
+-------------+------------------------------------------------+
| firstname | John |
+-------------+------------------------------------------------+
| lastname | Anderson |
+-------------+------------------------------------------------+
| age | 35 |
+-------------+------------------------------------------------+
| income | 100000 |
+-------------+------------------------------------------------+
| lastLogin | 2016-11-28T13:01:11.2088 |
+-------------+------------------------------------------------+
| address | street | 187 Hill Street |
| | city | Beloit |
| | state | WI |
| | zipcode | 53511 |
| | phones |
| | type | home |
| | areacode | 339 |
| | number | 1684972 |
+-------------+------------------------------------------------+
| connections | 1 |
| | 3 |
+-------------+------------------------------------------------+
| expenses | books | 100 |
| | food | 1700 |
| | travel | 2100 |
+-------------+------------------------------------------------+
> Row 1
+-------------+------------------------------------------------+
| id | 4 |
+-------------+------------------------------------------------+
| firstname | Peter |
+-------------+------------------------------------------------+
| lastname | Smith |
+-------------+------------------------------------------------+
| age | 38 |
+-------------+------------------------------------------------+
| income | 80000 |
+-------------+------------------------------------------------+
| lastLogin | 2016-10-19T09:18:05.5555 |
+-------------+------------------------------------------------+
| address | street | 364 Mulberry Street |
| | city | Leominster |
| | state | MA |
| | zipcode | NULL |
| | phones |
| | type | work |
| | areacode | 339 |
| | number | 4120211 |
| | |
| | type | work |
| | areacode | 339 |
| | number | 8694021 |
| | |
| | type | home |
| | areacode | 339 |
| | number | 1205678 |
| | |
| | type | home |
| | areacode | 305 |
| | number | 8064321 |
+-------------+------------------------------------------------+
| connections | 3 |
| | 5 |
| | 1 |
| | 2 |
+-------------+------------------------------------------------+
| expenses | books | 240 |
| | clothes | 2000 |
| | food | 6000 |
| | shoes | 1200 |
+-------------+------------------------------------------------+
> Row 2
+-------------+----------------------------------------------+
| id | 5 |
+-------------+----------------------------------------------+
| firstname | Dana |
+-------------+----------------------------------------------+
| lastname | Scully |
+-------------+----------------------------------------------+
| age | 47 |
+-------------+----------------------------------------------+
| income | 400000 |
+-------------+----------------------------------------------+
| lastLogin | 2016-11-08T09:16:46.3929 |
+-------------+----------------------------------------------+
| address | street | 427 Linden Avenue |
| | city | Monroe Township |
| | state | NJ |
| | zipcode | NULL |
| | phones |
| | type | work |
| | areacode | 201 |
| | number | 3213267 |
| | |
| | type | work |
| | areacode | 201 |
| | number | 8765421 |
| | |
| | type | home |
| | areacode | 339 |
| | number | 3414578 |
+-------------+----------------------------------------------+
| connections | 2 |
| | 4 |
| | 1 |
| | 3 |
+-------------+----------------------------------------------+
| expenses | clothes | 1500 |
| | food | 900 |
| | shoes | 1000 |
+-------------+----------------------------------------------+
3 rows returned 前述の例では、Persons表のすべての市外局番に対して複数キー索引が作成され、各市外局番が、その市外局番の電話番号を持つ個人にマップされています。問合せでは、市外局番が339の電話番号を持つ個人を検索しています。問合せには索引を適用できるので、索引内でキー339が検索され、関連する表の行がすべて取得されます。
sql-> create index idx_expenses on
Persons (expenses.keys(), expenses.values());
Statement completed successfully
sql-> SELECT * FROM Persons p WHERE p.expenses.food > 1000;
> Row 0
+-------------+--------------------------------------------+
| id | 2 |
+-------------+--------------------------------------------+
| firstname | John |
+-------------+--------------------------------------------+
| lastname | Anderson |
+-------------+--------------------------------------------+
| age | 35 |
+-------------+--------------------------------------------+
| income | 100000 |
+-------------+--------------------------------------------+
| lastLogin | 2016-11-28T13:01:11.2088 |
+-------------+--------------------------------------------+
| address | street | 187 Hill Street |
| | city | Beloit |
| | state | WI |
| | zipcode | 53511 |
| | phones |
| | type | home |
| | areacode | 339 |
| | number | 1684972 |
+-------------+--------------------------------------------+
| connections | 1 |
| | 3 |
+-------------+--------------------------------------------+
| expenses | books | 100 |
| | food | 1700 |
| | travel | 2100 |
+-------------+--------------------------------------------+
> Row 1
+-------------+--------------------------------------------+
| id | 3 |
+-------------+--------------------------------------------+
| firstname | John |
+-------------+--------------------------------------------+
| lastname | Morgan |
+-------------+--------------------------------------------+
| age | 38 |
+-------------+--------------------------------------------+
| income | 100000000 |
+-------------+--------------------------------------------+
| lastLogin | 2016-11-29T08:21:35.4971 |
+-------------+--------------------------------------------+
| address | street | 187 Aspen Drive |
| | city | Middleburg |
| | state | FL |
| | zipcode | NULL |
| | phones |
| | type | work |
| | areacode | 305 |
| | number | 1234079 |
| | |
| | type | home |
| | areacode | 305 |
| | number | 2066401 |
+-------------+--------------------------------------------+
| connections | 1 |
| | 4 |
| | 2 |
+-------------+--------------------------------------------+
| expenses | food | 2000 |
| | gas | 10 |
| | travel | 700 |
+-------------+--------------------------------------------+
> Row 2
+-------------+------------------------------------------------+
| id | 4 |
+-------------+------------------------------------------------+
| firstname | Peter |
+-------------+------------------------------------------------+
| lastname | Smith |
+-------------+------------------------------------------------+
| age | 38 |
+-------------+------------------------------------------------+
| income | 80000 |
+-------------+------------------------------------------------+
| lastLogin | 2016-10-19T09:18:05.5555 |
+-------------+------------------------------------------------+
| address | street | 364 Mulberry Street |
| | city | Leominster |
| | state | MA |
| | zipcode | NULL |
| | phones |
| | type | work |
| | areacode | 339 |
| | number | 4120211 |
| | |
| | type | work |
| | areacode | 339 |
| | number | 8694021 |
| | |
| | type | home |
| | areacode | 339 |
| | number | 1205678 |
| | |
| | type | home |
| | areacode | 305 |
| | number | 8064321 |
+-------------+------------------------------------------------+
| connections | 3 |
| | 5 |
| | 1 |
| | 2 |
+-------------+------------------------------------------------+
| expenses | books | 240 |
| | clothes | 2000 |
| | food | 6000 |
| | shoes | 1200 |
+-------------+------------------------------------------------+
3 rows returned この例では、Persons表のすべてのexpensesエントリに複数キー索引が作成され、各カテゴリCとそのカテゴリに関連付けられている各金額Aが、expensesマップにエントリ(C、A)を持つ個人にマップされています。問合せでは、foodへの支出額が1000を超える個人を検索しています。問合せには索引を適用できるので、最初のフィールド(マップ・キー)がfoodに等しく、2番目のキー(金額)が1000より大きい索引エントリのみがスキャンされ、関連する行が取得されます。