http://git-wip-us.apache.org/repos/asf/incubator-geode/blob/ff80a931/geode-docs/developing/query_additional/supported_keywords.html.md.erb ---------------------------------------------------------------------- diff --git a/geode-docs/developing/query_additional/supported_keywords.html.md.erb b/geode-docs/developing/query_additional/supported_keywords.html.md.erb deleted file mode 100644 index 1a257f6..0000000 --- a/geode-docs/developing/query_additional/supported_keywords.html.md.erb +++ /dev/null @@ -1,31 +0,0 @@ ---- -title: Supported Keywords ---- - -| Query Language Keyword | Description | Example | -|------------------------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| -| AND | Logical operator used to create complex expressions by combining two or more expressions to produce a Boolean result. When you combine two conditional expressions using the AND operator, both conditions must evaluate to true for the entire expression to be true. | See [Operators](operators.html#operators) | -| AS | Used to provide a label for a path expression so you can refer to the path by the label later. | See [Aliases and Synonyms](../query_select/the_from_clause.html#the_from_clause__section_AB1734C16DC348479C00FD6829B933AA) | -| COUNT | Returns the number of results that match the provided criteria. | See [COUNT](../query_select/the_select_statement.html#concept_85AE7D6B1E2941ED8BD2A8310A81753E__section_B2CBA00EB83F463DAF4769D7859C64C8) | -| DISTINCT | Restricts the select statement to unique results (eliminates duplicates). | See [DISTINCT](../query_select/the_select_statement.html#concept_85AE7D6B1E2941ED8BD2A8310A81753E__section_972EE73A6F3E4427B6A99DB4EDF5860D) | -| ELEMENT | Query function. Extracts a single element from a collection or array. This function throws a `FunctionDomainException ` if the argument is not a collection or array with exactly one element. | See [Preset Query Functions](../query_select/the_select_statement.html#concept_85AE7D6B1E2941ED8BD2A8310A81753E__section_69DCAD624E9640028BC86FD67649DEB2) | -| FROM | You can access any object or object attribute that is available in the current scope of the query. | See [FROM Clause](../query_select/the_from_clause.html#the_from_clause) | -| <HINT> | Keyword that instructs the query engine to prefer certain indexes. | See [Using Query Index Hints](../query_index/query_index_hints.html) | -| IMPORT | Used to establish the namescope for objects. | See [IMPORT Statement](../query_select/the_import_statement.html#concept_2E9F15B2FE9041238B54736103396BF7) | -| IN | The IN expression is a Boolean indicating whether one expression is present inside a collection of expressions of a compatible type. | See [IN and SET](../query_select/the_where_clause.html#the_where_clause__section_AC12146509F141378E493078540950C7) | -| IS\_DEFINED | Query function. Returns TRUE if the expression does not evaluate to UNDEFINED. | See [Preset Query Functions](../query_select/the_select_statement.html#concept_85AE7D6B1E2941ED8BD2A8310A81753E__section_69DCAD624E9640028BC86FD67649DEB2) | -| IS\_UNDEFINED | Query function. Returns TRUE if the expression evaluates to UNDEFINED. In most queries, undefined values are not included in the query results. The IS\_UNDEFINED function allows undefined values to be included, so you can identify element with undefined values. | See [Preset Query Functions](../query_select/the_select_statement.html#concept_85AE7D6B1E2941ED8BD2A8310A81753E__section_69DCAD624E9640028BC86FD67649DEB2) | -| LIMIT | Limits the number of returned results. If you use the limit keyword, you cannot also run operations on the query result set that perform any kind of summary activities. For example trying to run add or addAll or a SelectResult from a query with a LIMIT clause throws an exception. | See [LIMIT](../query_select/the_select_statement.html#concept_85AE7D6B1E2941ED8BD2A8310A81753E__section_25D7055B33EC47B19B1B70264B39212F) | -| LIKE | LIKE can be used to mean 'equals to', or if you terminate the string with a wildcard character (`%`), it behaves like 'starts with'. Note that the wildcard can only be used at the end of the comparison string. You can escape the wildcard character to represent the `%` character. You can also use the LIKE predicate if an index is present. | See [LIKE](../query_select/the_where_clause.html#the_where_clause__section_D91E0B06FFF6431490CC0BFA369425AD) | -| NOT | The example returns the set of portfolios that have positions. Note that NOT cannot use an index. | See [Operators](operators.html#operators) | -| NVL | Returns expr2 if expr1 is null. The expressions can be query parameters (bind arguments), path expressions, or literals. | See [Preset Query Functions](../query_select/the_select_statement.html#concept_85AE7D6B1E2941ED8BD2A8310A81753E__section_69DCAD624E9640028BC86FD67649DEB2) | -| OR | If an expression uses both AND and OR operators, the AND expression has higher precedence than OR. | See [Operators](operators.html#operators) | -| ORDER BY | Allows you to order query results (either in ascending or descending order). | See [ORDER BY](../query_select/the_select_statement.html#concept_85AE7D6B1E2941ED8BD2A8310A81753E__section_D9DF0F785CA94EF8B367C3326CC12990) | -| SELECT | Allows you to filter data from the collection of object(s) returned by a WHERE search operation. | See [SELECT Statement](../query_select/the_select_statement.html#concept_85AE7D6B1E2941ED8BD2A8310A81753E) | -| SET | Specifies a collection of values that can be compared to the returned values of query. | See [IN and SET](../query_select/the_where_clause.html#the_where_clause__section_AC12146509F141378E493078540950C7) | -| <TRACE> | Enables debugging on the following query string. | See [Query Debugging](query_debugging.html#concept_2D557E24AAB24044A3DB36B3124F6748) | -| TO\_DATE | Returns a Java Data class object. The arguments must be String S with date\_str representing the date and format\_str representing the format used by date\_str. The format\_str you provide is parsed using java.text.SimpleDateFormat. | See [Preset Query Functions](../query_select/the_select_statement.html#concept_85AE7D6B1E2941ED8BD2A8310A81753E__section_69DCAD624E9640028BC86FD67649DEB2) | -| TYPE | Specifying object type in the FROM clause helps the query engine to process the query at optimal speed. | See [Object Typing](../query_select/the_from_clause.html#the_from_clause__section_A5B42CCB7C924949954AEC2DAAD51134) | -| WHERE | Resolves to a collection of objects. The collection is then available for iteration in the query expressions that follow in the WHERE clause. | See [WHERE Clause](../query_select/the_where_clause.html#the_where_clause) | - -
http://git-wip-us.apache.org/repos/asf/incubator-geode/blob/ff80a931/geode-docs/developing/query_additional/using_query_bind_parameters.html.md.erb ---------------------------------------------------------------------- diff --git a/geode-docs/developing/query_additional/using_query_bind_parameters.html.md.erb b/geode-docs/developing/query_additional/using_query_bind_parameters.html.md.erb deleted file mode 100644 index cf9dcea..0000000 --- a/geode-docs/developing/query_additional/using_query_bind_parameters.html.md.erb +++ /dev/null @@ -1,48 +0,0 @@ ---- -title: Using Query Bind Parameters ---- - -Using query bind parameters in Geode queries is similar to using prepared statements in SQL where parameters can be set during query execution. This allows user to build a query once and execute it multiple times by passing the query conditions during run time. - -Query objects are thread-safe. - -The use of query bind parameters is now supported in Client-to-Server queries. - -The query parameters are identified by a dollar sign, $, followed by a digit that represents the parameter's position in the parameter array passed to the execute method. Counting begins at 1, so $1 references the first bound attribute, $2 the second attribute, and so on. - -The Query interface provides an overloaded execute method that accepts parameters inside an Object array. See the [Query.execute](/releases/latest/javadoc/org/apache/geode/cache/query/Query.html) JavaDocs for more details. - -The 0th element of the Object array is used for the first query parameter, and so on. If the parameter count or parameter types do not match the query specification, the execute method throws an exception. Specifically, if you pass in the wrong number of parameters, the method call throws a `QueryParameterCountInvalidException`. If a parameter object type is not compatible with what is expected, the method call throws a `TypeMismatchException`. - -In the following example, the first parameter, the integer **2**, is bound to the first element in the object array. The second parameter, **active**, is bound to the second element. - -## <a id="concept_173E775FE46B47DF9D7D1E40680D34DF__section_7F5A800E2DA643F2B30162EF45DBA390" class="no-quick-link"></a>Sample Code - -``` pre -// specify the query string -String queryString = "SELECT DISTINCT * FROM /exampleRegion p WHERE p.id = $1 and p.status = $2"; - -QueryService queryService = cache.getQueryService(); -Query query = queryService.newQuery(queryString); - -// set query bind parameters -Object[] params = new Object[2]; -params[0] = 2; -params[1] = "active"; - -// Execute the query locally. It returns the results set. -SelectResults results = (SelectResults) query.execute(params); - -// use the results of the query; this example only looks at the size - int size = results.size(); -``` - -## <a id="concept_173E775FE46B47DF9D7D1E40680D34DF__section_90B4A0010CDA481581B650AE6D9EBA34" class="no-quick-link"></a>Using Query Bind Parameters in the Path Expression - -Additionally the query engine supports the use of query bind parameter in place of a region path. When you specify a bind parameter in the query's FROM clause, the parameter's referenced value must be bound to a collection. - -For example, the following query can be used on any collection by passing in the collection as a query parameter value. In this query you could pass in a Region Object for $1, but not the String name of a region. - -``` pre -SELECT DISTINCT * FROM $1 p WHERE p.status = $2 -``` http://git-wip-us.apache.org/repos/asf/incubator-geode/blob/ff80a931/geode-docs/developing/query_index/create_multiple_indexes.html.md.erb ---------------------------------------------------------------------- diff --git a/geode-docs/developing/query_index/create_multiple_indexes.html.md.erb b/geode-docs/developing/query_index/create_multiple_indexes.html.md.erb deleted file mode 100644 index a629349..0000000 --- a/geode-docs/developing/query_index/create_multiple_indexes.html.md.erb +++ /dev/null @@ -1,61 +0,0 @@ ---- -title: Creating Multiple Indexes at Once ---- - -In order to speed and promote efficiency when creating indexes, you can define multiple indexes and then create them all at once. - -Defining multiple indexes before creating them speeds up the index creation process by iterating over region entries only once. - -You can define multiple indexes of different types at once by specifying the `--type` parameter at definition time. - -To define multiple indexes, you can use gfsh or the Java API: - -**gfsh example:** - -``` pre -gfsh> define index --name=myIndex1 --expression=exp1 --region=/exampleRegion - -gfsh> define index --name=myIndex2 --expression="c.exp2" --region="/exampleRegion e, e.collection1 c" - -gfsh> define index --name=myIndex3 --expression=exp3 --region=/exampleRegion --type=hash - -gfsh> create defined indexes - -``` - -If index creation fails, you may receive an error message in gfsh similar to the following: - -``` pre -gfsh>create defined indexes -Exception : org.apache.geode.cache.query.RegionNotFoundException , -Message : Region ' /r3' not found: from /r3Occurred on following members -1. india(s1:17866)<v1>:27809 -``` - -**Java API example:** - -``` pre - Cache cache = new CacheFactory().create(); - QueryService queryService = cache.getQueryService(); - queryService.defineIndex("name1", "indexExpr1", "regionPath1"); - queryService.defineIndex("name2", "indexExpr2", "regionPath2"); - queryService.defineHashIndex("name3", "indexExpr3", "regionPath2"); - queryService.defineKeyIndex("name4", "indexExpr4", "regionPath2"); - List<Index> indexes = queryService.createDefinedIndexes(); -``` - -If one or more index population fails, Geode collect the Exceptions and continues to populate the rest of the indexes. The collected `Exceptions` are stored in a Map of index names and exceptions that can be accessed through `MultiIndexCreationException`. - -Index definitions are stored locally on the `gfsh` client. If you want to create a new set of indexes or if one or more of the index creations fail, you might want to clear the definitions stored by using `clear defined indexes`command. The defined indexes can be cleared by using the Java API: - -``` pre -queryService.clearDefinedIndexes(); -``` - -or gfsh: - -``` pre -gfsh> clear defined indexes -``` - - http://git-wip-us.apache.org/repos/asf/incubator-geode/blob/ff80a931/geode-docs/developing/query_index/creating_an_index.html.md.erb ---------------------------------------------------------------------- diff --git a/geode-docs/developing/query_index/creating_an_index.html.md.erb b/geode-docs/developing/query_index/creating_an_index.html.md.erb deleted file mode 100644 index 8e977a7..0000000 --- a/geode-docs/developing/query_index/creating_an_index.html.md.erb +++ /dev/null @@ -1,94 +0,0 @@ ---- -title: Creating, Listing and Removing Indexes ---- - -The Geode `QueryService` API provides methods to create, list and remove the index. You can also use `gfsh` command-line interface to create, list and remove indexes, and use cache.xml to create an index. - -## <a id="indexing__section_565C080FBDD0443C8504DF372E3C32C8" class="no-quick-link"></a>Creating Indexes - -Indexes can be created programmatically, by using the `gfsh` command line interface or by using cache.xml. - -To create an index, use one of the following `QueryService` methods: - -- `createIndex`. Creates the default type of index, a range index. Use this type of index if you will be writing queries that will be doing any kind of comparison operation besides an equality comparison. -- `createKeyIndex`. Creates a key index. See [Creating Key Indexes](creating_key_indexes.html#concept_09E29507AF0D42CF81D261B030D0B7C8) for more information. -- `createHashIndex`. Creates a hash index. See [Creating Hash Indexes](creating_hash_indexes.html#concept_5C7614F71F394C62ACA1BDC5684A7AC4) for more information. -- `createDefinedIndexes`. Creates multiple indexes that were previously defined using `defineIndex`. See [Creating Multiple Indexes at Once](create_multiple_indexes.html) for more information. - -The following sections provide examples of index creation: - -**Using gfsh:** - -``` pre -gfsh> create index --name=myIndex --expression=status --region=/exampleRegion -gfsh> create index --name=myKeyIndex --type=key --expression=id --region=/exampleRegion -gfsh> create index --name=myHashIndex --type=hash --expression=mktValue --region=/exampleRegion -``` - -See [Index Commands](../../tools_modules/gfsh/quick_ref_commands_by_area.html#topic_688C66526B4649AFA51C0F72F34FA45E) for more examples. - -**Using Java API:** - -``` pre -QueryService qs = cache.getQueryService(); - qs.createIndex("myIndex", "status", "/exampleRegion"); - qs.createKeyIndex("myKeyIndex", "id", "/exampleRegion"); - qs.createHashIndex("myHashIndex", "mktValue", "/exampleRegion"); -``` - -**Using cache.xml:** - -``` pre -<region name=exampleRegion> - <region-attributes . . . > - </region-attributes> - <index name="myIndex" from-clause="/exampleRegion" expression="status"/> - <index name="myKeyIndex" from-clause="/exampleRegion" expression="id" key-index="true"/> - <index name="myHashIndex" from-clause="/exampleRegion p" expression="p.mktValue" type="hash"/> - ... -</region> -``` - -**Note:** -If you do not specify the type of index in cache.xml, the type defaults to "range". - -## <a id="indexing__section_40089270C67A49FFA0C749E287F722F8" class="no-quick-link"></a>Listing Indexes - -To retrieve a list of indexes from the cache or region, use the `QueryService.getIndexes` method or the `gfsh` command line interface. - -**Using gfsh:** - -``` pre -gfsh> list indexes -gfsh> list indexes --with-stats -``` - -**Using Java API:** - -``` pre -QueryService qs = cache.getQueryService(); - qs.getIndexes(); //returns a collection of all indexes in the cache - qs.getIndexes(exampleRegion); //returns a collection of all indexes in exampleRegion - qs.getIndexes(exampleRegion, myKeyIndex); //returns the index named myKeyIndex from the exampleRegion -``` - -## <a id="indexing__section_ACBFB76C539A405D90B05856CB79BEFF" class="no-quick-link"></a>Removing Indexes - -To remove an index or all indexes from the cache or region, use the `QueryService.removeIndexes` method or the `gfsh` command line interface. - -**Using gfsh:** - -``` pre -gfsh> destroy index -gfsh> destroy index --name=myIndex -gfsh> destroy index --region=/exampleRegion -``` - -**Using Java API:** - -``` pre -QueryService qs = cache.getQueryService(); - qs.removeIndexes(); //removes all indexes from the cache - qs.removeIndexes(myKeyIndex); //removes the index named myKeyIndex - qs.removeIndexes(exampleRegion); //removes all indexes from the exampleRegion -``` http://git-wip-us.apache.org/repos/asf/incubator-geode/blob/ff80a931/geode-docs/developing/query_index/creating_hash_indexes.html.md.erb ---------------------------------------------------------------------- diff --git a/geode-docs/developing/query_index/creating_hash_indexes.html.md.erb b/geode-docs/developing/query_index/creating_hash_indexes.html.md.erb deleted file mode 100644 index f7fe579..0000000 --- a/geode-docs/developing/query_index/creating_hash_indexes.html.md.erb +++ /dev/null @@ -1,51 +0,0 @@ ---- -title: Creating Hash Indexes ---- - -Geode supports the creation of hash indexes for the purposes of performing equality-based queries. - -## <a id="concept_5C7614F71F394C62ACA1BDC5684A7AC4__section_8A927DFB29364DA7856E7FE122FC1654" class="no-quick-link"></a>Why Create a HashIndex - -By creating a hash index, you can vastly improve the memory usage of your index. If you are doing equality based queries, your indexing footprint is significantly reduced. As an example, previously when you created an index that contained string fields, copies of the strings were included in the index. By using hash indexes, we ensure that indexed expressions are canonicalized and stored in the index as pointers to the objects lying in the region. In internal test cases we have seen improvements in the 30% range depending on the keys and data being used. - -**Note:** -Your performance results may vary depending on the keys and data being indexed. - -## <a id="concept_5C7614F71F394C62ACA1BDC5684A7AC4__section_2EF23A54378E44FFBF1BEB32E41ABD61" class="no-quick-link"></a>Performance Considerations - -The performance of put operations when using a hash index should be comparable to other indexes or slightly slower. Queries themselves are expected to be slightly slower due to the implementation of hash index and the cost of recalculating the key on request, which is the trade-off for the space savings that using a hash index provides. - -## <a id="concept_5C7614F71F394C62ACA1BDC5684A7AC4__section_800401C53E5841269F389329B8A4CDF3" class="no-quick-link"></a>Limitations - -The following limitations must be considered when creating hash indexes: - -- You can only use hash indexes with equals and not equals queries. -- Hash index maintenance will be slower than the other indexes due to synchronized add methods. -- Hash indexes cannot be maintained asynchronously. If you attempt to create a hash index on a region with asynchronous set as the maintenance mode, an exception will be thrown. -- You cannot use hash indexes for queries with multiple iterators or nested collections. - -## <a id="concept_5C7614F71F394C62ACA1BDC5684A7AC4__section_CA2476E015D14DE7B522377C931A8492" class="no-quick-link"></a>Examples of Creating a Hash Index - -**Using the Java API:** - -``` pre -QueryService qs = cache.getQueryService(); - qs.createHashIndex("myHashIndex", "mktValue", "/exampleRegion"); -``` - -**Using gfsh:** - -``` pre -gfsh> create index --name=myHashIndex --expression=mktValue --region=/exampleRegion -``` - -**Using cache.xml:** - -``` pre -<region name=exampleRegion> - <region-attributes . . . > - </region-attributes> - <index name="myHashIndex" from-clause="/exampleRegion p" expression="p.mktValue" type="hash"/> - ... -</region> -``` http://git-wip-us.apache.org/repos/asf/incubator-geode/blob/ff80a931/geode-docs/developing/query_index/creating_key_indexes.html.md.erb ---------------------------------------------------------------------- diff --git a/geode-docs/developing/query_index/creating_key_indexes.html.md.erb b/geode-docs/developing/query_index/creating_key_indexes.html.md.erb deleted file mode 100644 index fb9de44..0000000 --- a/geode-docs/developing/query_index/creating_key_indexes.html.md.erb +++ /dev/null @@ -1,49 +0,0 @@ ---- -title: Creating Key Indexes ---- - -Creating a key index is a good way to improve query performance when data is partitioned using a key or a field value. You can create key indexes by using the `createKeyIndex` method of the QueryService or by defining the index in `cache.xml`. Creating a key index makes the query service aware of the relationship between the values in the region and the keys in the region. - -The FROM clause for a primary key index must be just a region path. The indexed expression is an expression that, when applied to an entry value, produces the key. For example, if a region has Portfolios as the values and the keys are the id field of the Portfolios region, the indexed expression is id. - -You can then use the FunctionService (using the partitioned key as a filter passed to the function and as part of the query equality condition) to execute the query against the indexed data. See [Optimizing Queries on Data Partitioned by a Key or Field Value](../query_additional/partitioned_region_key_or_field_value.html#concept_3010014DFBC9479783B2B45982014454) for more details. - -There are two issues to note with key indexes: - -- The key index is not sorted. Without sorting, you can only do equality tests. Other comparisons are not possible. To obtain a sorted index on your primary keys, create a functional index on the attribute used as the primary key. -- The query service is not automatically aware of the relationship between the region values and keys. For this, you must create the key index. - -**Note:** -Using a key-index with an explicit type='range' in the cache.xml will lead to an exception. Key indexes will not be used in 'range' queries. - -## <a id="concept_09E29507AF0D42CF81D261B030D0B7C8__section_8F1B7893F6D44D9CB36679222927031C" class="no-quick-link"></a>Examples of Creating a Key Index - -**Using Java API:** - -``` pre -QueryService qs = cache.getQueryService(); - qs.createKeyIndex("myKeyIndex", "id", "/exampleRegion"); - -``` - -**Using gfsh:** - -``` pre -gfsh> create index --name=myKeyIndex --expression=id --region=/exampleRegion -``` - -**Using cache.xml:** - -``` pre -<region name=exampleRegion> - <region-attributes . . . > - </region-attributes> - <index name="myKeyIndex" from-clause="/exampleRegion" expression="id" key-index="true"/> - ... -</region> -``` - -**Note:** -If you do not specify the type of index when defining indexes using cache.xml, the type defaults to "range". - - http://git-wip-us.apache.org/repos/asf/incubator-geode/blob/ff80a931/geode-docs/developing/query_index/creating_map_indexes.html.md.erb ---------------------------------------------------------------------- diff --git a/geode-docs/developing/query_index/creating_map_indexes.html.md.erb b/geode-docs/developing/query_index/creating_map_indexes.html.md.erb deleted file mode 100644 index 32a5cf2..0000000 --- a/geode-docs/developing/query_index/creating_map_indexes.html.md.erb +++ /dev/null @@ -1,44 +0,0 @@ ---- -title: Creating Indexes on Map Fields ("Map Indexes") ---- - -To assist with the quick lookup of multiple values in a Map (or HashMap) type field, you can create an index (sometimes referred to as a "map index") on specific (or all) keys in that field. - -For example, you could create a map index to support the following query: - -``` pre -SELECT * fROM /users u WHERE u.name['first'] = 'John' OR u.name['last'] = 'Smith' -``` - -The map index extends regular range indexes created on single key by maintaining indexes for other specified keys, or for all keys if `*` is used. The underlying structure of the map index can be thought of as a wrapper around all these indexes. - -The following Java code samples provide examples of how to create a map index: - -``` pre -QueryService qs = cache.getQueryService(); - -//This will create indexes for for keys 'PVTL' and 'VMW' -qs.createIndex("indexName", "p.positions['PVTL', 'VMW']", "/portfolio p"); -``` - -``` pre -QueryService qs = cache.getQueryService(); - -//This will create indexes for all keys -qs.createIndex("indexName", "p.positions[*]", "/portfolio p"); -``` - -In gfsh, the equivalents are: - -``` pre -gfsh>create index --name="IndexName" --expression="p.positions['PVTL', 'VMW']" --region="/portfolio p" - -gfsh>create index --name="IndexName" --expression="p.positions[*]" --region="/portfolio p" -``` - -In order to create or query a map index, you must use the bracket notation to list the map field keys you wish to index or query. For example: `[*]`, `['keyX1','keyX2â]`. Note that using `p.pos.get('keyX1')` will not create or query the map index. - -**Note:** -You can still query against Map or HashMap fields without querying against a map index. For example, you can always create a regular range query on a single key in any Map or HashMap field. However, note that subsequent query lookups will be limited to a single key. - - http://git-wip-us.apache.org/repos/asf/incubator-geode/blob/ff80a931/geode-docs/developing/query_index/index_samples.html.md.erb ---------------------------------------------------------------------- diff --git a/geode-docs/developing/query_index/index_samples.html.md.erb b/geode-docs/developing/query_index/index_samples.html.md.erb deleted file mode 100644 index ce2e67c..0000000 --- a/geode-docs/developing/query_index/index_samples.html.md.erb +++ /dev/null @@ -1,63 +0,0 @@ ---- -title: Index Samples ---- - -This topic provides code samples for creating query indexes. - -``` pre - // Key index samples. The field doesn't have to be present. -createKeyIndex("pkidIndex","p.pkid1","/root/exampleRegion p"); - -createKeyIndex("Index4","ID","/portfolios"); - -// Simple index -createIndex("pkidIndex","p.pkid","/root/exampleRegion p"); -createIndex("i", "p.status", "/exampleRegion p") -createIndex("i", "p.ID", "/exampleRegion p") -createIndex("i", "p.position1.secId", "/exampleRegion p" - -// On Set type - createIndex("setIndex","s","/root/exampleRegion p, p.sp s"); - -// Positions is a map -createIndex("secIdIndex","b.secId","/portfolios pf, pf.positions.values b"); - -//... -createIndex("i", "pf.collectionHolderMap[(pf.Id).toString()].arr[pf.ID]", "/exampleRegion pf") -createIndex("i", "pf.ID", "/exampleRegion pf", "pf.positions.values pos") -createIndex("i", "pos.secId", "/exampleRegion pf", "pf.positions.values pos") -createIndex("i", "e.value.getID()", "/exampleRegion.entrySet e") -createIndex("i", "e.value.ID", "/exampleRegion.entrySet e") - -//... -createIndex("i", "entries.value.getID", "/exampleRegion.entrySet() entries") -createIndex("i", "ks.toString", "/exampleRegion.getKeys() ks") -createIndex("i", "key.status", "/exampleRegion.keys key") -createIndex("i", "secIds.length", "/exampleRegion p, p.secIds secIds") -createIndex("i", "secId", "/portfolios.asList[1].positions.values") -createIndex("i", "secId", "/portfolios['1'].positions.valules") - -//Index on Map types -createIndex("i", "p.positions['key1']", "/exampleRegion p") -createIndex("i", "p.positions['key1','key2',key3',key7']", "/exampleRegion p") -createIndex("i", "p.positions[*]", "/exampleRegion p") -``` - -The following are some sample queries on indexes. - -``` pre -SELECT * FROM (SELECT * FROM /R2 m) r2, (SELECT * FROM /exampleRegion e WHERE e.pkid IN r2.sp) p - -SELECT * FROM (SELECT * FROM /R2 m WHERE m.ID IN SET (1, 5, 10)) r2, - (SELECT * FROM /exampleRegion e WHERE e.pkid IN r2.sp) p - -//examples using position index in the collection -SELECT * FROM /exampleRegion p WHERE p.names[0] = 'aaa' - -SELECT * FROM /exampleRegion p WHERE p.position3[1].portfolioId = 2 - -SELECT DISTINCT positions.values.toArray[0], positions.values.toArray[0], status -FROM /exampleRegion -``` - - http://git-wip-us.apache.org/repos/asf/incubator-geode/blob/ff80a931/geode-docs/developing/query_index/indexes_on_single_region_queries.html.md.erb ---------------------------------------------------------------------- diff --git a/geode-docs/developing/query_index/indexes_on_single_region_queries.html.md.erb b/geode-docs/developing/query_index/indexes_on_single_region_queries.html.md.erb deleted file mode 100644 index 35662f7..0000000 --- a/geode-docs/developing/query_index/indexes_on_single_region_queries.html.md.erb +++ /dev/null @@ -1,33 +0,0 @@ ---- -title: Using Indexes on Single Region Queries ---- - -Queries with one comparison operation may be improved with either a key or range index, depending on whether the attribute being compared is also the primary key. - -<a id="concept_0210701C193A470E8C572111F6CEC3FC__section_751427643EC3480BABCE9CA44E831E27"></a> -If pkid is the key in the /exampleRegion region, creating a key index on pkid is the best choice as a key index does not have maintenance overhead. If pkid is not the key, a range index on pkid should improve performance. - -``` pre -SELECT DISTINCT * FROM /exampleRegion portfolio WHERE portfolio.pkid = '123' -``` - -With multiple comparison operations, you can create a range index on one or more of the attributes. Try the following: - -1. Create a single index on the condition you expect to have the smallest result set size. Check performance with this index. -2. Keeping the first index, add an index on a second condition. Adding the second index may degrade performance. If it does, remove it and keep only the first index. The order of the two comparisons in the query can also impact performance. Generally speaking, in OQL queries, as in SQL queries, you should order your comparisons so the earlier ones give you the fewest results on which to run subsequent comparisons. - -For this query, you would try a range index on name, age, or on both: - -``` pre -SELECT DISTINCT * FROM /exampleRegion portfolio WHERE portfolio.status = 'active' and portfolio.ID > 45 -``` - -For queries with nested levels, you may get better performance by drilling into the lower levels in the index as well as in the query. - -This query drills down one level: - -``` pre -SELECT DISTINCT * FROM /exampleRegion portfolio, portfolio.positions.values positions where positions.secId = 'AOL' and positions.MktValue > 1 -``` - - http://git-wip-us.apache.org/repos/asf/incubator-geode/blob/ff80a931/geode-docs/developing/query_index/indexes_with_overflow_regions.html.md.erb ---------------------------------------------------------------------- diff --git a/geode-docs/developing/query_index/indexes_with_overflow_regions.html.md.erb b/geode-docs/developing/query_index/indexes_with_overflow_regions.html.md.erb deleted file mode 100644 index 126b2e6..0000000 --- a/geode-docs/developing/query_index/indexes_with_overflow_regions.html.md.erb +++ /dev/null @@ -1,41 +0,0 @@ ---- -title: Using Indexes with Overflow Regions ---- - -You can use indexes when querying on overflow regions; however, there are caveats. - -The following are caveats for querying overflow regions: - -- You must use synchronous index maintenance for the region. This is the default maintenance setting. -- The index FROM clause must specify only one iterator, and it must refer to the keys or entry values. The index cannot refer to the regionâs entrySet. -- The index data itself is not stored on (overflowed to) disk . - -**Examples:** - -The following example index creation calls DO NOT work for overflow regions. - -``` pre -// This index will not work on an overflow region because there are two iterators in the FROM clause. -createIndex("secIdIndex", "b.secId","/portfolios pf, pf.positions.values b"); - -// This index will not work on an overflow region because the FROM clause specifies the entrySet -createIndex("indx1", "entries.value.getID", "/exampleRegion.entrySet() entries"); -``` - -The following example indexes will work for overflow regions. - -``` pre -createIndex("pkidIndex", "p.pkid", "/Portfolios p"); - -createIndex("indx1", "ks.toString", "/portfolio.keySet() ks"); -``` - -The same working examples in gfsh: - -``` pre -gfsh> create index -name="pkidIndex" --expression="p.pkid" --region="/Portfolios p" - -gfsh> create index -name="indx1" --expression="ks.toString" --region="/portfolio.keySet() ks" -``` - - http://git-wip-us.apache.org/repos/asf/incubator-geode/blob/ff80a931/geode-docs/developing/query_index/indexing_guidelines.html.md.erb ---------------------------------------------------------------------- diff --git a/geode-docs/developing/query_index/indexing_guidelines.html.md.erb b/geode-docs/developing/query_index/indexing_guidelines.html.md.erb deleted file mode 100644 index 8cf229f..0000000 --- a/geode-docs/developing/query_index/indexing_guidelines.html.md.erb +++ /dev/null @@ -1,24 +0,0 @@ ---- -title: Tips and Guidelines on Using Indexes ---- - -Optimizing your queries with indexes requires a cycle of careful planning, testing, and tuning. Poorly-defined indexes can degrade the performance of your queries instead of improving it. This section gives guidelines for index usage in the query service. - -<a id="indexing_guidelines__section_A8885DFC334243508C4563C9692E0801"></a> -When creating indexes, keep in mind the following: - -- Indexes incur maintenance costs as they must be updated when the indexed data changes. An index that requires many updates and is not used very often may require more system resources than using no index at all. -- Indexes consume memory. -- Indexes have limited support on overflow regions. See [Using Indexes with Overflow Regions](indexes_with_overflow_regions.html#concept_87BE7DB32C714EB0BF7532AF93569328) for details. -- If you are creating multiple indexes on the same region, first define your indexes and then create the indexes all at once to avoid iterating over the region multiple times. See [Creating Multiple Indexes at Once](create_multiple_indexes.html) for details. - -## <a id="indexing_guidelines__section_A8AFAA243B5C43DD9BB9F9235A48AF53" class="no-quick-link"></a>Tips for Writing Queries that Use Indexes - -As with query processors that run against relational databases, the way a query is written can greatly affect execution performance. Among other things, whether indexes are used depends on how each query is stated. These are some of the things to consider when optimizing your Geode queries for performance: - -- In general an index will improve query performance if the FROM clauses of the query and index match exactly. -- The query evaluation engine does not have a sophisticated cost-based optimizer. It has a simple optimizer which selects best index (one) or multiple indexes based on the index size and the operator that is being evaluated. -- For AND operators, you may get better results if the conditions that use indexes and conditions that are more selective come before other conditions in the query. -- Indexes are not used in expressions that contain NOT, so in a WHERE clause of a query, `qty >= 10` could have an index on `qty` applied for efficiency. However, `NOT(qty < 10)` could not have the same index applied. -- Whenever possible, provide a hint to allow the query engine to prefer a specific index. See [Using Query Index Hints](query_index_hints.html) - http://git-wip-us.apache.org/repos/asf/incubator-geode/blob/ff80a931/geode-docs/developing/query_index/maintaining_indexes.html.md.erb ---------------------------------------------------------------------- diff --git a/geode-docs/developing/query_index/maintaining_indexes.html.md.erb b/geode-docs/developing/query_index/maintaining_indexes.html.md.erb deleted file mode 100644 index ae8c29b..0000000 --- a/geode-docs/developing/query_index/maintaining_indexes.html.md.erb +++ /dev/null @@ -1,52 +0,0 @@ ---- -title: Maintaining Indexes (Synchronously or Asynchronously) and Index Storage ---- - -Indexes are automatically kept current with the region data they reference. The region attribute `IndexMaintenanceSynchronous` specifies whether the region indexes are updated synchronously when a region is modified or asynchronously in a background thread. - -## <a id="concept_98ED3F38919A490B9AE2553568455C35__section_FF1945F2AAFA4B158067CEE967410616" class="no-quick-link"></a>Index Maintenance Behavior - -Asynchronous index maintenance batches up multiple updates to the same region key. The default mode is synchronous, since this provides the greatest consistency with region data. - -See [RegionFactory.setIndexMaintenanceSynchronous](/releases/latest/javadoc/org/apache/geode/cache/RegionFactory.html). - -This declarative index creation sets the maintenance mode to asynchronous: - -``` pre -<region-attributes index-update-type="asynchronous"> -</region-attributes> -``` - -## <a id="concept_98ED3F38919A490B9AE2553568455C35__section_68308B5597CF4A9EAA8EC0BD83A233E6" class="no-quick-link"></a>Internal Index Structure and Storage - -Indexes are stored either as compact or non-compact data structures based on the indexed expression (even if the index key type is the same.) For example, consider the following Passenger object: - -``` pre -Passenger { - String name, - Date travelDate, - int age, - Flight flt, -} -Flight { - int flightId, - String origin, - String dest, -} -``` - -An index on the Passenger name field will have different memory space requirements in the cache than the Flight origin field even though they are both String field types. The internal data structure selected by Geode for index storage will depend on the field's level in the object. In this example, name is a top-level field and an index on name can be stored as a compact index. Since origin is a second-level field, any index that uses origin as the indexed expression will be stored as a non-compact index. - -**Compact Index** - -A compact index has simple data structures to minimize its footprint, at the expense of doing extra work at index maintenance. This index does not support the storage of projection attributes. - -Currently compact indexes are only selected only supports the creation of an index on a region path. In addition, the following conditions must be met: - -- Index maintenance is synchronous. -- The indexed expression is a path expression. -- The FROM clause has only one iterator. This implies that there is only one value in the index for each region entry and it is directly on the region values (not supported with keys, entries). - -**Non-Compact Index** - -Used whenever a compact index cannot be used. http://git-wip-us.apache.org/repos/asf/incubator-geode/blob/ff80a931/geode-docs/developing/query_index/query_index.html.md.erb ---------------------------------------------------------------------- diff --git a/geode-docs/developing/query_index/query_index.html.md.erb b/geode-docs/developing/query_index/query_index.html.md.erb deleted file mode 100644 index accbf00..0000000 --- a/geode-docs/developing/query_index/query_index.html.md.erb +++ /dev/null @@ -1,62 +0,0 @@ ---- -title: Working with Indexes ---- - -The Geode query engine supports indexing. An index can provide significant performance gains for query execution. - -<a id="indexing__section_565C080FBDD0443C8504DF372E3C32C8"></a> -A query run without the aid of an index iterates through every object in the collection. If an index is available that matches part or all of the query specification, the query iterates only over the indexed set, and query processing time can be reduced. - -- **[Tips and Guidelines on Using Indexes](../../developing/query_index/indexing_guidelines.html)** - - Optimizing your queries with indexes requires a cycle of careful planning, testing, and tuning. Poorly-defined indexes can degrade the performance of your queries instead of improving it. This section gives guidelines for index usage in the query service. - -- **[Creating, Listing and Removing Indexes](../../developing/query_index/creating_an_index.html)** - - The Geode `QueryService` API provides methods to create, list and remove the index. You can also use `gfsh` command-line interface to create, list and remove indexes, and use cache.xml to create an index. - -- **[Creating Key Indexes](../../developing/query_index/creating_key_indexes.html)** - - Creating a key index is a good way to improve query performance when data is partitioned using a key or a field value. You can create key indexes by using the `createKeyIndex` method of the QueryService or by defining the index in `cache.xml`. Creating a key index makes the query service aware of the relationship between the values in the region and the keys in the region. - -- **[Creating Hash Indexes](../../developing/query_index/creating_hash_indexes.html)** - - Geode supports the creation of hash indexes for the purposes of performing equality-based queries. - -- **[Creating Indexes on Map Fields ("Map Indexes")](../../developing/query_index/creating_map_indexes.html)** - - To assist with the quick lookup of multiple values in a Map (or HashMap) type field, you can create an index (sometimes referred to as a "map index") on specific (or all) keys in that field. - -- **[Creating Multiple Indexes at Once](../../developing/query_index/create_multiple_indexes.html)** - - In order to speed and promote efficiency when creating indexes, you can define multiple indexes and then create them all at once. - -- **[Maintaining Indexes (Synchronously or Asynchronously) and Index Storage](../../developing/query_index/maintaining_indexes.html)** - - Indexes are automatically kept current with the region data they reference. The region attribute `IndexMaintenanceSynchronous` specifies whether the region indexes are updated synchronously when a region is modified or asynchronously in a background thread. - -- **[Using Query Index Hints](../../developing/query_index/query_index_hints.html)** - - You can use the hint keyword to allow Geode's query engine to prefer certain indexes. - -- **[Using Indexes on Single Region Queries](../../developing/query_index/indexes_on_single_region_queries.html)** - - Queries with one comparison operation may be improved with either a key or range index, depending on whether the attribute being compared is also the primary key. - -- **[Using Indexes with Equi-Join Queries](../../developing/query_index/using_indexes_with_equijoin_queries.html)** - - Equi-join queries are queries in which two regions are joined through an equality condition in the WHERE clause. - -- **[Using Indexes with Overflow Regions](../../developing/query_index/indexes_with_overflow_regions.html)** - - You can use indexes when querying on overflow regions; however, there are caveats. - -- **[Using Indexes on Equi-Join Queries using Multiple Regions](../../developing/query_index/using_indexes_with_equijoin_queries_multiple_regions.html)** - - To query across multiple regions, identify all equi-join conditions. Then, create as few indexes for the equi-join conditions as you can while still joining all regions. - -- **[Index Samples](../../developing/query_index/index_samples.html)** - - This topic provides code samples for creating query indexes. - - http://git-wip-us.apache.org/repos/asf/incubator-geode/blob/ff80a931/geode-docs/developing/query_index/query_index_hints.html.md.erb ---------------------------------------------------------------------- diff --git a/geode-docs/developing/query_index/query_index_hints.html.md.erb b/geode-docs/developing/query_index/query_index_hints.html.md.erb deleted file mode 100644 index 37b1005..0000000 --- a/geode-docs/developing/query_index/query_index_hints.html.md.erb +++ /dev/null @@ -1,23 +0,0 @@ ---- -title: Using Query Index Hints ---- - -You can use the hint keyword to allow Geode's query engine to prefer certain indexes. - -In cases where one index is hinted in a query, the query engine filters off the hinted index (if possible) and then iterates and filters from the resulting values. - -**Example:** - -``` pre -<HINT 'IDIndex'> SELECT * FROM /Portfolios p WHERE p.ID > 10 AND p.owner = 'XYZ' -``` - -If multiple indexes are added as hints, then the query engine will try to use as many indexes as possible while giving a preference for the hinted indexes. - -**Example:** - -``` pre -<HINT 'IDIndex', 'OwnerIndex'> SELECT * FROM /Portfolios p WHERE p.ID > 10 AND p.owner = 'XYZ' AND p.value < 100 -``` - - http://git-wip-us.apache.org/repos/asf/incubator-geode/blob/ff80a931/geode-docs/developing/query_index/using_indexes_with_equijoin_queries.html.md.erb ---------------------------------------------------------------------- diff --git a/geode-docs/developing/query_index/using_indexes_with_equijoin_queries.html.md.erb b/geode-docs/developing/query_index/using_indexes_with_equijoin_queries.html.md.erb deleted file mode 100644 index 2d7accc..0000000 --- a/geode-docs/developing/query_index/using_indexes_with_equijoin_queries.html.md.erb +++ /dev/null @@ -1,52 +0,0 @@ ---- -title: Using Indexes with Equi-Join Queries ---- - -Equi-join queries are queries in which two regions are joined through an equality condition in the WHERE clause. - -<a id="concept_A90C5FD84FCB45B2B28D6CE78DE1D117__section_47CFF4EF4D964FCFBB772B0347C02214"></a> -To use an index with an equi-join query: - -1. Create an index for each side of the equi-join condition. The query engine can quickly evaluate the query's equi-join condition by iterating over the keys of the left-side and right-side indexes for an equality match. - - **Note:** - Equi-join queries require regular indexes. Key indexes are not applied to equi-join queries. - - For this query: - - ``` pre - SELECT DISTINCT inv.name, ord.orderID, ord.status - FROM /investors inv, /orders ord - WHERE inv.investorID = ord.investorID - ``` - - Create two indexes: - - | FROM clause | Indexed expression | - |----------------|--------------------| - | /investors inv | inv.investorID | - | /orders ord | ord.investorID | - -2. If there are additional, single-region queries in a query with an equi-join condition, create additional indexes for the single-region conditions only if you are able to create at least one such index for each region in the query. Any indexing on a subset of the regions in the query will degrade performance. - - For this example query: - - ``` pre - SELECT DISTINCT * - FROM /investors inv, /securities sc, inv.heldSecurities inv_hs - WHERE sc.status = "active" - AND inv.name = "xyz" - AND inv.age > 75 - AND inv_hs.secName = sc.secName - ``` - - Create the indexes for the equi-join condition: - - | FROM clause | Indexed expression | - |--------------------------------------------|--------------------| - | /investors inv, inv.heldSecurities inv\_hs | inv\_hs.secName | - | /securities sc | sc.secName | - - Then, if you create any more indexes, create one on `sc.status` and one on `inv.age` or `inv.name` or both. - - http://git-wip-us.apache.org/repos/asf/incubator-geode/blob/ff80a931/geode-docs/developing/query_index/using_indexes_with_equijoin_queries_multiple_regions.html.md.erb ---------------------------------------------------------------------- diff --git a/geode-docs/developing/query_index/using_indexes_with_equijoin_queries_multiple_regions.html.md.erb b/geode-docs/developing/query_index/using_indexes_with_equijoin_queries_multiple_regions.html.md.erb deleted file mode 100644 index a834b60..0000000 --- a/geode-docs/developing/query_index/using_indexes_with_equijoin_queries_multiple_regions.html.md.erb +++ /dev/null @@ -1,62 +0,0 @@ ---- -title: Using Indexes on Equi-Join Queries using Multiple Regions ---- - -To query across multiple regions, identify all equi-join conditions. Then, create as few indexes for the equi-join conditions as you can while still joining all regions. - -<a id="concept_DB2407C49F064B04AA58BC9D1DBA3666__section_70735ED43C4D47B0A19B910BB7E3A1DA"></a> -If there are equi-join conditions that redundantly join two regions (in order to more finely filter the data, for example), then creating redundant indexes for these joins will negatively impact performance. Create indexes only on one equi-join condition for each region pair. - -In this example query: - -``` pre -SELECT DISTINCT * -FROM /investors inv, /securities sc, /orders or, -inv.ordersPlaced inv_op, or.securities or_sec - WHERE inv_op.orderID = or.orderID - AND or_sec.secID = sc.secID -``` - -All conditions are required to join the regions, so you would create four indexes, two for each equi-join condition: - -| FROM clause | Indexed expression | -|------------------------------------------|--------------------| -| /investors inv, inv.ordersPlaced inv\_op | inv\_op.orderID | -| /orders or, or.securities or\_sec | or.orderID | - -| FROM clause | Indexed expression | -|-----------------------------------|--------------------| -| /orders or, or.securities or\_sec | or\_sec.secID | -| /securities sc | sc.secID | - -Adding another condition to the example: - -``` pre -SELECT DISTINCT * -FROM /investors inv, /securities sc, /orders or, -inv.ordersPlaced inv_op, or.securities or_sec, sc.investors sc_invs - WHERE inv_op.orderID = or.orderID - AND or_sec.secID = sc.secID - AND inv.investorID = sc_invs.investorID -``` - -You would still only want to use four indexes in all, as that's all you need to join all of the regions. You would need to choose the most performant two of the following three index pairs: - -| FROM clause | Indexed expression | -|------------------------------------------|--------------------| -| /investors inv, inv.ordersPlaced inv\_op | inv\_op.orderID | -| /orders or, or.securities or\_sec | or.orderID | - -| FROM clause | Indexed expression | -|---------------------------------------|--------------------| -| /orders or, or.securities or\_sec | or\_sec.secID | -| /securities sc, sc.investors sc\_invs | sc.secID | - -| FROM clause | Indexed expression | -|------------------------------------------|---------------------| -| /investors inv, inv.ordersPlaced inv\_op | inv.investorID | -| /securities sc, sc.investors sc\_invs | sc\_invs.investorID | - -The most performant set is that which narrows the data to the smallest result set possible. Examine your data and experiment with the three index pairs to see which provides the best performance. - - http://git-wip-us.apache.org/repos/asf/incubator-geode/blob/ff80a931/geode-docs/developing/query_select/aggregates.html.md.erb ---------------------------------------------------------------------- diff --git a/geode-docs/developing/query_select/aggregates.html.md.erb b/geode-docs/developing/query_select/aggregates.html.md.erb deleted file mode 100644 index 1a4e0aa..0000000 --- a/geode-docs/developing/query_select/aggregates.html.md.erb +++ /dev/null @@ -1,92 +0,0 @@ ---- -title: OQL Aggregate Functions ---- - -The aggregate functions -```MIN```, -```MAX```, -```AVG```, -```AVG``` over a DISTINCT expression, -```SUM``` over a DISTINCT expression, -```COUNT```, and -```COUNT``` over a DISTINCT expression -are supported. -The ```GROUP BY``` extension is also supported where appropriate. - -The ```MIN``` function returns the smallest of the selected -expression. -The type of the expression must evaluate to a -```java.lang.Comparable```. - -The ```MAX``` function returns the largest of the selected -expression. -The type of the expression must evaluate to a -```java.lang.Comparable```. - -The ```AVG``` function returns the arithmetic mean of the set -formed by the selected expression. -The type of the expression must evaluate to a -```java.lang.Number```. -For partitioned regions, -each node's buckets provide both a sum and the number of elements -to the node executing the query, -such that a correct average may be computed. - -The ```AVG``` function where the DISTINCT modifier is applied -to the expression returns the arithmetic mean of the set -of unique (distinct) values. -The type of the expression must evaluate to a -```java.lang.Number```. -For partitioned regions, -the distinct values in a node's buckets are returned -to the node executing the query. -The query node can then calculate the avarage over -the values that are unique across nodes, -after eliminating duplicate values that come from separate nodes. - -The ```SUM``` function returns the sum over the set -formed by the selected expression. -The type of the expression must evaluate to a -```java.lang.Number```. -For partitioned regions, -each node's buckets compute a sum over that node, -returning that sum -to the node executing the query, -when then sums across all nodes. - -The ```SUM``` function where the DISTINCT modifier is applied -to the expression returns the sum over the set -of unique (distinct) values. -The type of the expression must evaluate to a -```java.lang.Number```. -For partitioned regions, -the distinct values in a node's buckets are returned -to the node executing the query. -The query node can then calculate the sum over -the values that are unique across nodes, -after eliminating duplicate values that come from separate nodes. - -The ```COUNT``` function returns the quantity of values in the set -formed by the selected expression. -For example, to return the quantity of employees who have a -positive sales amount: - -``` pre -SELECT count(e.sales) FROM /employees e WHERE e.sales > 0.0 -``` - -The ```COUNT``` function where the DISTINCT modifier is applied -returns the quantity of unique (distinct) values in the set -formed by the selected expression. - -## GROUP BY Extension for Aggregate Functions - -```GROUP BY``` is required -when aggregate functions are used in combination -with other selected items. -It permits ordering. -For example, - -``` pre -SELECT ID, MAX(e.sales) FROM /employees e GROUP BY ID -``` http://git-wip-us.apache.org/repos/asf/incubator-geode/blob/ff80a931/geode-docs/developing/query_select/the_from_clause.html.md.erb ---------------------------------------------------------------------- diff --git a/geode-docs/developing/query_select/the_from_clause.html.md.erb b/geode-docs/developing/query_select/the_from_clause.html.md.erb deleted file mode 100644 index 2ecc2e8..0000000 --- a/geode-docs/developing/query_select/the_from_clause.html.md.erb +++ /dev/null @@ -1,86 +0,0 @@ ---- -title: FROM Clause ---- - -<a id="the_from_clause__section_FAEBC02C4E414F91B1CA8D33E11218AF"></a> -Use the FROM clause to bring the data you need into scope for the rest of your query. The FROM clause also includes object typing and iterator variables. - -The query engine resolves names and path expressions according to the name space that is currently in scope in the query. - -## <a id="the_from_clause__section_CF6063A6C5134EFC89C43D106B6A6D4D" class="no-quick-link"></a>Path Expressions - -The initial name space for any query is composed of: - -- **Regions.** In the context of a query, the name of a region is specified by its full path starting with a forward slash ( / ) and delimited by the forward slash between region names. For example, **/exampleRegion** or **/root/exampleRegion**. -- **Region querying attributes**. From a region path, you can access the Region object's public fields and methods, referred to in querying as the region's attributes. For example, **/exampleRegion.size**. -- **Top-level region data.** You can access entry keys and entry data through the region path. - 1. `/exampleRegion.keySet` returns the Set of entry keys in the region - 2. `/exampleRegion.entryset` returns the Set of Region.Entry objects - 3. `/exampleRegion.values` returns the Collection of entry values - 4. `/exampleRegion` returns the Collection of entry values - -New name spaces are brought into scope based on the FROM clause in the SELECT statement. - -**Examples:** - -Query a region for all distinct values. Return a collection of unique entry values from the region: - -``` pre -SELECT DISTINCT * FROM /exampleRegion -``` - -Query the top level region data using entrySet. Return the keys and positions of Region.Entry objects whose mktValue attribute is greater than 25.00: - -``` pre -SELECT key, positions FROM /exampleRegion.entrySet, value.positions.values positions WHERE positions.mktValue >= 25.00 -``` - -Query the region for its entry values. Return a set of unique values from Region.Entry objects that have the key equal to 1: - -``` pre -SELECT DISTINCT entry.value FROM /exampleRegion.entries entry WHERE entry.key = '1' -``` - -Query the region for its entry values. Return the set of all entry values in which the `ID` field is greater than 1000: - -``` pre -SELECT * FROM /exampleRegion.entries entry WHERE entry.value.ID > 1000 -``` - -Query entry keys in the region. Return a set of entry keys in the region that have the key equal to '1': - -``` pre -SELECT * FROM /exampleRegion.keySet key WHERE key = '1' -``` - -Query values in the region. Return a collection of entry values in the region that have the status attribute value of 'active': - -``` pre -SELECT * FROM /exampleRegion.values portfolio WHERE portfolio.status = 'active' -``` - -## <a id="the_from_clause__section_AB1734C16DC348479C00FD6829B933AA" class="no-quick-link"></a>Aliases and Synonyms - -In query strings, you can use aliases in path expressions (region and its objects) so that you can refer to the region or objects in other places in the query. - -You can also use the **AS** keyword to provide a label for joined path expressions. - -**Examples:** - -``` pre -SELECT DISTINCT * FROM /exampleRegion p WHERE p.status = 'active' -``` - -``` pre -SELECT * FROM /exampleRegion p, p.positions.values AS pos WHERE pos.secId != '1' -``` - -## <a id="the_from_clause__section_A5B42CCB7C924949954AEC2DAAD51134" class="no-quick-link"></a>Object Typing - -Specifying object type in the FROM clause helps the query engine to process the query at optimal speed. Apart from specifying the object types during configuration (using key-constraint and value-constraint), type can be explicitly specified in the query string. - -**Example:** - -``` pre -SELECT DISTINCT * FROM /exampleRegion, positions.values positions TYPE Position WHERE positions.mktValue >= 25.00 -``` http://git-wip-us.apache.org/repos/asf/incubator-geode/blob/ff80a931/geode-docs/developing/query_select/the_import_statement.html.md.erb ---------------------------------------------------------------------- diff --git a/geode-docs/developing/query_select/the_import_statement.html.md.erb b/geode-docs/developing/query_select/the_import_statement.html.md.erb deleted file mode 100644 index 1269f79..0000000 --- a/geode-docs/developing/query_select/the_import_statement.html.md.erb +++ /dev/null @@ -1,14 +0,0 @@ ---- -title: IMPORT Statement ---- - -It is sometimes necessary for an OQL query to refer to the class of an object. In cases where the same class name resides in two different namescopes (packages), you must be able to differentiate the classes having the same name. - -The **IMPORT** statement is used to establish a name for a class in a query. - -``` pre -IMPORT package.Position; -SELECT DISTINCT * FROM /exampleRegion, positions.values positions TYPE Position WHERE positions.mktValue >= 25.00 -``` - - http://git-wip-us.apache.org/repos/asf/incubator-geode/blob/ff80a931/geode-docs/developing/query_select/the_select_statement.html.md.erb ---------------------------------------------------------------------- diff --git a/geode-docs/developing/query_select/the_select_statement.html.md.erb b/geode-docs/developing/query_select/the_select_statement.html.md.erb deleted file mode 100644 index 04d14df..0000000 --- a/geode-docs/developing/query_select/the_select_statement.html.md.erb +++ /dev/null @@ -1,203 +0,0 @@ ---- -title: SELECT Statement ---- - -The SELECT statement allows you to filter data from the collection of object(s) returned by a WHERE search operation. The projection list is either specified as \* or as a comma delimited list of expressions. - -For \*, the interim results of the WHERE clause are returned from the query. - -**Examples:** - -Query all objects from the region using \*. Returns the Collection of portfolios (The exampleRegion contains Portfolio as values). - -``` pre -SELECT * FROM /exampleRegion -``` - -Query secIds from positions. Returns the Collection of secIds from the positions of active portfolios: - -``` pre -SELECT secId FROM /exampleRegion, positions.values TYPE Position -WHERE status = 'active' -``` - -Returns a Collection of struct<type: String, positions: map> for the active portfolios. The second field of the struct is a Map ( jav.utils.Map ) object, which contains the positions map as the value: - -``` pre -SELECT "type", positions FROM /exampleRegion -WHERE status = 'active' -``` - -Returns a Collection of struct<portfolios: Portfolio, values: Position> for the active portfolios: - -``` pre -SELECT * FROM /exampleRegion, positions.values -TYPE Position WHERE status = 'active' -``` - -Returns a Collection of struct<pflo: Portfolio, posn: Position> for the active portfolios: - -``` pre -SELECT * FROM /exampleRegion portfolio, positions positions -TYPE Position WHERE portfolio.status = 'active' -``` - -## <a id="concept_85AE7D6B1E2941ED8BD2A8310A81753E__section_1B7762EC686A4808B1D12E8851954E82" class="no-quick-link"></a>SELECT Statement Results - -The result of a SELECT statement is either UNDEFINED or is a Collection that implements the [SelectResults](/releases/latest/javadoc/org/apache/geode/cache/query/SelectResults.html) interface. - -The SelectResults returned from the SELECT statement is either: - -1. A collection of objects, returned for these two cases: - - When only one expression is specified by the projection list and that expression is not explicitly specified using the fieldname:expression syntax - - When the SELECT list is \* and a single collection is specified in the FROM clause - -2. A collection of Structs that contains the objects - -When a struct is returned, the name of each field in the struct is determined following this order of preference: - -1. If a field is specified explicitly using the fieldname:expression syntax, the fieldname is used. -2. If the SELECT projection list is \* and an explicit iterator expression is used in the FROM clause, the iterator variable name is used as the field name. -3. If the field is associated with a region or attribute path, the last attribute name in the path is used. -4. If names cannot be decided based on these rules, arbitrary unique names are generated by the query processor. - -## <a id="concept_85AE7D6B1E2941ED8BD2A8310A81753E__section_972EE73A6F3E4427B6A99DB4EDF5860D" class="no-quick-link"></a>DISTINCT - -Use the DISTINCT keyword if you want to limit the results set to unique rows. Note that in the current version of Geode you are no longer required to use the DISTINCT keyword in your SELECT statement. - -``` pre -SELECT DISTINCT * FROM /exampleRegion -``` - -**Note:** -If you are using DISTINCT queries, you must implement the equals and hashCode methods for the objects that you query. - -## <a id="concept_85AE7D6B1E2941ED8BD2A8310A81753E__section_25D7055B33EC47B19B1B70264B39212F" class="no-quick-link"></a>LIMIT - -You can use the LIMIT keyword at the end of the query string to limit the number of values returned. - -For example, this query returns at most 10 values: - -``` pre -SELECT * FROM /exampleRegion LIMIT 10 -``` - -## <a id="concept_85AE7D6B1E2941ED8BD2A8310A81753E__section_D9DF0F785CA94EF8B367C3326CC12990" class="no-quick-link"></a>ORDER BY - -You can order your query results in ascending or descending order by using the ORDER BY clause. You must use DISTINCT when you write ORDER BY queries. - -``` pre -SELECT DISTINCT * FROM /exampleRegion WHERE ID < 101 ORDER BY ID -``` - -The following query sorts the results in ascending order: - -``` pre -SELECT DISTINCT * FROM /exampleRegion WHERE ID < 101 ORDER BY ID asc -``` - -The following query sorts the results in descending order: - -``` pre -SELECT DISTINCT * FROM /exampleRegion WHERE ID < 101 ORDER BY ID desc -``` - -**Note:** -If you are using ORDER BY queries, you must implement the equals and hashCode methods for the objects that you query. - -## <a id="concept_85AE7D6B1E2941ED8BD2A8310A81753E__section_69DCAD624E9640028BC86FD67649DEB2" class="no-quick-link"></a>Preset Query Functions - -Geode provides several built-in functions for evaluating or filtering data returned from a query. They include the following: - -<table> -<colgroup> -<col width="33%" /> -<col width="33%" /> -<col width="33%" /> -</colgroup> -<thead> -<tr class="header"> -<th>Function</th> -<th>Description</th> -<th>Example</th> -</tr> -</thead> -<tbody> -<tr class="odd"> -<td>ELEMENT(expr)</td> -<td>Extracts a single element from a collection or array. This function throws a <code class="ph codeph">FunctionDomainException</code> if the argument is not a collection or array with exactly one element.</td> -<td><pre class="pre codeblock"><code>ELEMENT(SELECT DISTINCT * - FROM /exampleRegion - WHERE id = 'XYZ-1').status = 'active'</code></pre></td> -</tr> -<tr class="even"> -<td>IS_DEFINED(expr)</td> -<td>Returns TRUE if the expression does not evaluate to UNDEFINED.</td> -<td><pre class="pre codeblock"><code>IS_DEFINED(SELECT DISTINCT * -FROM /exampleRegion p -WHERE p.status = 'active')</code></pre></td> -</tr> -<tr class="odd"> -<td>IS_UNDEFINED (expr)</td> -<td>Returns TRUE if the expression evaluates to UNDEFINED. In most queries, undefined values are not included in the query results. The IS_UNDEFINED function allows undefined values to be included, so you can identify element with undefined values.</td> -<td><pre class="pre codeblock"><code>SELECT DISTINCT * -FROM /exampleRegion p -WHERE IS_UNDEFINED(p.status)</code></pre></td> -</tr> -<tr class="even"> -<td>NVL(expr1, expr2)</td> -<td>Returns expr2 if expr1 is null. The expressions can be query parameters (bind arguments), path expressions, or literals.</td> -<td>Â </td> -</tr> -<tr class="odd"> -<td>TO_DATE(date_str, format_str)</td> -<td>Returns a Java Data class object. The arguments must be String S with date_str representing the date and format_str representing the format used by date_str. The format_str you provide is parsed using java.text.SimpleDateFormat.</td> -<td>Â </td> -</tr> -</tbody> -</table> - -## <a id="concept_85AE7D6B1E2941ED8BD2A8310A81753E__section_B2CBA00EB83F463DAF4769D7859C64C8" class="no-quick-link"></a>COUNT - -The COUNT keyword returns the number of results that match the query selection conditions specified in the WHERE clause. Using COUNT allows you to determine the size of a results set. The COUNT statement always returns an integer as its result. - -The following queries are example COUNT queries that return region entries: - -``` pre -SELECT COUNT(*) FROM /exampleRegion -``` - -``` pre -SELECT COUNT(*) FROM /exampleRegion WHERE ID > 0 -``` - -``` pre -SELECT COUNT(*) FROM /exampleRegion WHERE ID > 0 LIMIT 50 -``` - -``` pre -SELECT COUNT(*) FROM /exampleRegion -WHERE ID >0 AND status LIKE 'act%' -``` - -``` pre -SELECT COUNT(*) FROM /exampleRegion -WHERE ID IN SET(1,2,3,4,5) -``` - -The following COUNT query returns the total number of StructTypes that match the query's selection criteria. - -``` pre -SELECT COUNT(*) -FROM /exampleRegion p, p.positions.values pos -WHERE p.ID > 0 AND pos.secId 'IBM' -``` - -The following COUNT query uses the DISTINCT keyword and eliminates duplicates from the number of results. - -``` pre -SELECT DISTINCT COUNT(*) -FROM /exampleRegion p, p.positions.values pos -WHERE p.ID > 0 OR p.status = 'active' OR pos.secId -OR pos.secId = 'IBM' -```
