geode-docs/getting_started/querying_quick_reference.html.md.erb

---
title: Querying FAQ and Examples
---

<!--
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements.  See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License.  You may obtain a copy of the License at

     http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
-->

This topic answers some frequently asked questions on querying functionality. It provides examples to help you get started with <%=vars.product_name%> querying.

<a id="reference_D5CE64F5FD6F4A808AEFB748C867189E__section_AFAD97A4BA2D45CF91ED1525A54DDFD6"></a>
For additional information on <%=vars.product_name%> querying, see [Querying](../developing/querying_basics/chapter_overview.html).

-   [How do I write and execute a query against a <%=vars.product_name%> region?](querying_quick_reference.html#reference_D5CE64F5FD6F4A808AEFB748C867189E__section_7A4D2C6A4E2C4F4384C158FFCA9CA1C0)
-   [Can I see query string examples, listed by query type?](querying_quick_reference.html#reference_D5CE64F5FD6F4A808AEFB748C867189E__section_641D97CD874D4182961C85429ACA1B05)
-   [Which APIs should I use to write my queries?](querying_quick_reference.html#reference_D5CE64F5FD6F4A808AEFB748C867189E__section_5383407F9D004D4EB4E695252EBA1EF0)
-   [How do I invoke an object's method in a query?](querying_quick_reference.html#reference_D5CE64F5FD6F4A808AEFB748C867189E__section_3E6E4B33D57846008EF4404D2B687597)
-   [Can I invoke a static method on an object in a query?](querying_quick_reference.html#reference_D5CE64F5FD6F4A808AEFB748C867189E__section_9221C29BC1FD49D7BBD26BB34D5BDEB8)
-   [How do I write a reusable query?](querying_quick_reference.html#reference_D5CE64F5FD6F4A808AEFB748C867189E__section_907DBBBA1AEC4570A15B3491B0A7DF0E)
-   [When should I create indexes to use in my queries?](querying_quick_reference.html#reference_D5CE64F5FD6F4A808AEFB748C867189E__section_3A9528E8E43140BAA0D5A1457CCAB2D2)
-   [How do I create an index?](querying_quick_reference.html#reference_D5CE64F5FD6F4A808AEFB748C867189E__section_76CDCCFBDB134A339DBE556C28D48F11)
-   [Can I query a partitioned region? Can I perform a join query on a partitioned region?](querying_quick_reference.html#reference_D5CE64F5FD6F4A808AEFB748C867189E__section_EDD17817450C4FC0B510CD87DB2FCD16)
-   [How can I improve the performance of a partitioned region query?](querying_quick_reference.html#reference_D5CE64F5FD6F4A808AEFB748C867189E__section_5FF905E0D10D4CDF9E6F49A70848AF69)
-   [Which query language elements are supported in <%=vars.product_name%>?](querying_quick_reference.html#reference_D5CE64F5FD6F4A808AEFB748C867189E__section_FBC59A5420FD40D6907A302A1D50DF7E)
-   [How do I debug queries?](querying_quick_reference.html#reference_D5CE64F5FD6F4A808AEFB748C867189E__section_314B88A55B514B88A12DC36227A2D4EF)
-   [Can I use implicit attributes or methods in my query?](#reference_D5CE64F5FD6F4A808AEFB748C867189E__implicit_attributes)
-   [How do I perform a case-insensitive search on a field in OQL?](#reference_D5CE64F5FD6F4A808AEFB748C867189E__section_ayq_hqw_1r)

## <a id="reference_D5CE64F5FD6F4A808AEFB748C867189E__section_7A4D2C6A4E2C4F4384C158FFCA9CA1C0" class="no-quick-link"></a>How do I write and execute a query against a <%=vars.product_name%> region?

To write and execute a query in <%=vars.product_name%>, you can use any of the following mechanisms. Sample query code follows.

-   <%=vars.product_name%> querying APIs
-   [gfsh](../tools_modules/gfsh/chapter_overview.html) command-line interface; in particular the [query](../tools_modules/gfsh/command-pages/query.html) command
-   REST API [query endpoints](../rest_apps/rest_queries.html#concept_mmg_d35_m4)

**Sample <%=vars.product_name%> Query Code (Java)**

``` pre
// Identify your query string.
 String queryString = "SELECT * FROM /exampleRegion";
 
 // Get QueryService from Cache.
 QueryService queryService = cache.getQueryService();
 
 // Create the Query Object.
 Query query = queryService.newQuery(queryString);
 
 // Execute Query locally. Returns results set.
 SelectResults results = (SelectResults)query.execute();
 
 // Find the Size of the ResultSet.
 int size = results.size();
 
 // Iterate through your ResultSet.
 Portfolio p = (Portfolio)results.iterator().next(); /* Region containing Portfolio object. */
```

## <a id="reference_D5CE64F5FD6F4A808AEFB748C867189E__section_641D97CD874D4182961C85429ACA1B05" class="no-quick-link"></a>Can I see query string examples, listed by query type?

The following example query strings use the `/exampleRegion` whose keys are the portfolio ID and whose values correspond to the summarized data shown in the following class definitions:

``` pre
class Portfolio implements DataSerializable {
   int ID;
   String type;
   String status;
   Map positions;
}
class Position implements DataSerializable {
   String secId;
   double mktValue;
   double qty;
}
```

**Basic WHERE Clause Examples**

In the following examples, the status field is type String and the ID field is type int. See [Supported Literals](../developing/query_additional/literals.html#literals) for a complete list of literals supported in <%=vars.product_name%> querying.

1.  Select all active portfolios.

    ``` pre
    SELECT * FROM /exampleRegion WHERE status = 'active'
    ```

2.  Select all portfolios whose status begins with 'activ'.

    ``` pre
    SELECT * FROM /exampleRegion p WHERE p.status LIKE 'activ%'
    ```

3.  Select all portfolios whose ID is greater than 100.

    ``` pre
    SELECT * from /exampleRegion p WHERE p.ID > 100
    ```

**Using DISTINCT**

Select distinct Objects from the region that satisfy the where clause condition of status = 'active'.

``` pre
SELECT DISTINCT * FROM /exampleRegion WHERE status = 'active'
```

**Aliases and Synonyms**

In the query string, the path expressions (region and its objects) can be defined using an alias. This alias can be used or referred to in other places in the query.

``` pre
SELECT DISTINCT * FROM /exampleRegion p WHERE p.status = 'active'
```

``` pre
SELECT p.ID, p.status FROM /exampleRegion p WHERE p.ID > 0
```

**Using the NOT Operator**

See [Operators](../developing/query_additional/operators.html#operators) for a complete list of supported operators.

``` pre
SELECT DISTINCT * FROM /exampleRegion WHERE NOT (status = 'active') AND ID = 2
```

``` pre
SELECT * FROM /exampleRegion WHERE NOT (ID IN SET(1,2))
```

**Using the AND and OR Operators**

See [Operators](../developing/query_additional/operators.html#operators) for a complete list of supported operators.

``` pre
SELECT * FROM /exampleRegion WHERE ID > 4 AND ID < 9
```

``` pre
SELECT * FROM /exampleRegion WHERE ID = 0 OR ID = 1
```

``` pre
SELECT DISTINCT p.status FROM /exampleRegion p 
WHERE (p.createTime IN SET (10|) OR p.status IN SET ('active')) AND p.ID > 0
```

**Using not equal to**

``` pre
SELECT * FROM /exampleRegion portfolio WHERE portfolio.ID <> 2
```

``` pre
SELECT * FROM /exampleRegion portfolio WHERE portfolio.ID != 2
```

**Projection attribute example**

``` pre
SELECT p.get('account') FROM /exampleRegion p
```

**Querying nested collections**

The following query uses Positions of type HashMap.

``` pre
SELECT p, pos FROM /exampleRegion p, p.positions.values pos WHERE pos.secId = 'VMW'
```

**Using LIMIT**

``` pre
SELECT * FROM /exampleRegion p WHERE p.ID > 0 LIMIT 2
```


**Using MIN and MAX**

See [MIN](../developing/query_select/aggregates.html#min) and [MAX](../developing/query_select/aggregates.html#max)for more information.

``` pre
SELECT MIN(ID)
FROM /exampleRegion
WHERE ID > 0
```

``` pre
SELECT MAX(ID)
FROM /exampleRegion
WHERE ID > 0 AND status LIKE 'act%'
```

```pre
SELECT MIN(pos.mktValue)
FROM /exampleRegion p, p.positions.values pos
WHERE p.ID = 10
```

```pre
SELECT MAX(p.ID)
FROM /exampleRegion p, p.positions.values pos
WHERE p.ID > 0 OR p.status = 'active' OR pos.secId = 'IBM'
```

**Using AVG**

See [AVG](../developing/query_select/aggregates.html#avg) for more information.

``` pre
SELECT AVG(ID)
FROM /exampleRegion
WHERE ID > 0
```

``` pre
SELECT AVG(ID)
FROM /exampleRegion
WHERE ID > 0 AND status LIKE 'act%'
```

``` pre
SELECT AVG(pos.mktValue)
FROM /exampleRegion p, p.positions.values pos
WHERE p.isActive()
```

```pre
SELECT AVG(DISTINCT p.ID)
FROM /exampleRegion p, p.positions.values pos
WHERE p.ID > 0 OR p.status = 'active' OR pos.secId = 'IBM'
```

**Using COUNT**

See [COUNT](../developing/query_select/aggregates.html#count) for more information.

``` 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)
```

```pre
SELECT COUNT(DISTINCT p.status)
FROM /exampleRegion p
WHERE p.ID > 0
```

``` pre
SELECT COUNT(*)
FROM /exampleRegion p, p.positions.values pos 
WHERE p.ID > 0 AND pos.secId 'IBM'
```

``` pre
SELECT DISTINCT COUNT(*)
FROM /exampleRegion p, p.positions.values pos
WHERE p.ID > 0 OR p.status = 'active' OR pos.secId = 'IBM'
```

**Using SUM**

See [SUM](../developing/query_select/aggregates.html#sum) for more information.

``` pre
SELECT SUM(ID)
FROM /exampleRegion
WHERE ID > 0
```

``` pre
SELECT SUM(ID)
FROM /exampleRegion
WHERE ID > 0 AND status LIKE 'act%'
```

``` pre
SELECT SUM(pos.mktValue)
FROM /exampleRegion p, p.positions.values pos
WHERE p.status = 'active'
```

```pre
SELECT SUM(DISTINCT p.ID)
FROM /exampleRegion p, p.positions.values pos
WHERE p.ID > 0 OR p.status = 'active' OR pos.secId = 'IBM'
```

**Using GROUP BY**

See [GROUP BY](../developing/query_select/aggregates.html#groupBy) for more information.

``` pre
SELECT p.status, MAX(p.ID) 
FROM /exampleRegion p
WHERE p.ID > 0
GROUP BY p.status
```

```pre
SELECT p.ID, MIN(pos.qty) AS lessQty
FROM /exampleRegion p, p.positions.values pos
WHERE p.ID > 0 AND p.status = 'active'
GROUP BY p.ID
ORDER BY lessQty ASC
```

```pre
SELECT p.ID, MAX(pos.mktValue) AS maxValue
FROM /exampleRegion p, p.positions.values pos
WHERE p.ID > 0 AND p.status = 'active'
GROUP BY p.ID
ORDER BY maxValue DESC
```

``` pre
SELECT p.status, AVG(p.ID) 
FROM /exampleRegion p
WHERE p.ID > 0
GROUP BY p.status
```

```pre
SELECT p.ID, pos.secId, AVG(pos.mktValue)
FROM /exampleRegion p, p.positions.values pos
WHERE p.ID > 0 OR p.status = 'active'
GROUP BY p.ID, pos.secId
```

``` pre
SELECT p.status, AVG(p.ID) as sm 
FROM /exampleRegion p
WHERE p.ID > 0
GROUP BY p.status
ORDER BY sm DESC
```

``` pre
SELECT p.status, COUNT(*) 
FROM /exampleRegion p
WHERE p.ID > 0
GROUP BY p.status
```

```pre
SELECT p.ID, COUNT(pos) AS positionsAmount
FROM /exampleRegion p, p.positions.values pos
WHERE p.ID > 0 OR p.status = 'active'
GROUP BY p.ID
ORDER BY positionsAmount
```

``` pre
SELECT p.status, SUM(p.ID) 
FROM /exampleRegion p
WHERE p.ID > 0
GROUP BY p.status
```

```pre
SELECT p.ID, pos.secId, SUM(pos.mktValue)
FROM /exampleRegion p, p.positions.values pos
WHERE p.ID > 0 OR p.status = 'active'
GROUP BY p.ID, pos.secId
```

``` pre
SELECT p.status, SUM(p.ID) as sm 
FROM /exampleRegion p
WHERE p.ID > 0
GROUP BY p.status
ORDER BY sm DESC
```

```pre
SELECT p.ID, SUM(pos.mktValue) AS marketValue
FROM /exampleRegion p, p.positions.values pos
WHERE p.isActive()
GROUP BY p.ID
ORDER BY marketValue DESC
```

**Using LIKE**

``` pre
SELECT * FROM /exampleRegion ps WHERE ps.pkid LIKE '_bc'
```

``` pre
SELECT * FROM /exampleRegion ps WHERE ps.status LIKE '_b_' OR ps.pkid = '2'
```

``` pre
SELECT * FROM /exampleRegion ps WHERE ps.status LIKE '%b%
```

**Using Region Entry Keys and Values**

``` pre
SELECT * FROM /exampleRegion.keys k WHERE k.ID = 1
```

``` pre
SELECT entry.value FROM /exampleRegion.entries entry WHERE entry.key = '1'
```

``` pre
SELECT key, positions FROM /exampleRegion.entrySet, value.positions.values positions 
WHERE positions.mktValue >= 25.00
```

``` pre
SELECT DISTINCT entry.value FROM /exampleRegion.entries entry WHERE entry.key = '1'
```

``` pre
SELECT * FROM /exampleRegion.entries entry WHERE entry.value.ID > 1
```

``` pre
SELECT * FROM /exampleRegion.keySet key WHERE key = '1'
```

``` pre
SELECT * FROM /exampleRegion.values portfolio 
WHERE portfolio.status = 'active'
```

**Nested Queries**

``` pre
IMPORT "query".Portfolio;
SELECT * FROM /exampleRegion, (SELECT DISTINCT * FROM /exampleRegion p TYPE Portfolio, p.positions 
WHERE value!=null)
```

``` pre
SELECT DISTINCT * FROM (SELECT DISTINCT * FROM /exampleRegion portfolios, positions pos) 
WHERE pos.value.secId = 'IBM'
```

``` pre
SELECT * FROM /exampleRegion portfolio 
WHERE portfolio.ID IN (SELECT p2.ID FROM /exampleRegion2 p2 WHERE p2.ID > 1)
```

``` pre
SELECT DISTINCT * FROM /exampleRegion p, (SELECT DISTINCT pos 
FROM /exampleRegion x, x.positions.values pos WHERE x.ID = p.ID ) AS itrX
```

**Query the results of a FROM clause expression**

``` pre
SELECT DISTINCT * FROM (SELECT DISTINCT * FROM /Portfolios ptf, positions pos) p 
WHERE p.get('pos').value.secId = 'IBM'
```

**Hash Map Query**

Query using a hashmap. In the following examples, 'version' is one of the keys in the hashmap.

``` pre
SELECT * FROM /exampleRegion p WHERE p['version'] = '1.0'
```

``` pre
SELECT entry.key, entry.value FROM /exampleRegion.entries entry 
WHERE entry.value['version'] = '100'
```

**Map example where "map" is a nested HashMap object**

``` pre
SELECT DISTINCT * FROM /exampleRegion p WHERE p.portfolios['key2'] >= 3
```

**Example Queries that Fetch Array Values**

``` pre
SELECT * FROM /exampleRegion p WHERE p.names[0] = 'aaa'
```

``` pre
SELECT * FROM /exampleRegion p WHERE p.collectionHolderMap.get('1').arr[0] = '0'
```

**Using ORDER BY (and ORDER BY with LIMIT)**

You must use the DISTINCT keyword with ORDER BY queries.

``` pre
SELECT DISTINCT * FROM /exampleRegion WHERE ID < 101 ORDER BY ID
```

``` pre
SELECT DISTINCT * FROM /exampleRegion WHERE ID < 101 ORDER BY ID asc
```

``` pre
SELECT DISTINCT * FROM /exampleRegion WHERE ID < 101 ORDER BY ID desc
```

``` pre
SELECT DISTINCT key.ID, key.status AS st FROM /exampleRegion.keys key 
WHERE key.status = 'inactive' ORDER BY key.status desc, key.ID LIMIT 1
```

``` pre
SELECT DISTINCT * FROM /exampleRegion p ORDER BY p.getP1().secId, p.ID dec, p.ID LIMIT 9
```

``` pre
SELECT DISTINCT * FROM /exampleRegion p ORDER BY p.ID, val.secId LIMIT 1
```

``` pre
SELECT DISTINCT e.key FROM /exampleRegion.entrySet e ORDER BY e.key.ID desc, e.key.pkid desc
```

``` pre
SELECT DISTINCT p.names[1] FROM /exampleRegion p ORDER BY p.names[1]
```

**Join Queries**

``` pre
SELECT * FROM /exampleRegion portfolio1, /exampleRegion2 portfolio2 
WHERE portfolio1.status = portfolio2.status
```

``` pre
SELECT portfolio1.ID, portfolio2.status FROM /exampleRegion portfolio1, /exampleRegion2 portfolio2 
WHERE portfolio1.status = portfolio2.status
```

``` pre
SELECT * FROM /exampleRegion portfolio1, portfolio1.positions.values positions1, 
/exampleRegion2 portfolio2, portfolio2.positions.values positions2 WHERE positions1.secId = positions1.secId
```

``` pre
SELECT * FROM /exampleRegion portfolio1, portfolio1.positions.values positions1, 
/exampleRegion2 portfolio2, portfolio2.positions.values positions2 WHERE portfolio1.ID = 1 
AND positions1.secId = positions1.secId
```

``` pre
SELECT DISTINCT a, b.price FROM /exampleRegoin1 a, /exampleRegion2 b WHERE a.price = b.price
```

**Using AS**

``` pre
SELECT * FROM /exampleRegion p, p.positions.values AS pos WHERE pos.secId != '1'
```

**Using TRUE**

``` pre
SELECT DISTINCT * FROM /Portfolios WHERE TRUE
```

**Using IN and SET**

See also [IN and SET](../developing/query_select/the_where_clause.html#the_where_clause__section_AC12146509F141378E493078540950C7).

``` pre
SELECT * FROM /exampleRegion portfolio WHERE portfolio.ID IN SET(1, 2)
```

``` pre
SELECT * FROM /exampleRegion portfolio, portfolio.positions.values positions 
WHERE portfolio.Pk IN SET ('1', '2') AND positions.secId = '1'
```

``` pre
SELECT * FROM /exampleRegion portfolio, portfolio.positions.values positions 
WHERE portfolio.Pk IN SET ('1', '2') OR positions.secId IN SET ('1', '2', '3')
```

``` pre
SELECT * FROM /exampleRegion portfolio, portfolio.positions.values positions 
WHERE portfolio.Pk IN SET ('1', '2') OR positions.secId IN SET ('1', '2', '3') 
AND portfolio.status = 'active'
```

**Querying for Set values**

In the following query, sp is of type Set.

``` pre
SELECT * FROM /exampleRegion WHERE sp = set('20', '21', '22')
```

If the Set (sp) only contains '20' and '21', then the query will evaluate to false. The query compares the two sets and looks for the presence of elements in both sets.

For other collection types like list (sp is of type List), the query can be written as follows:

``` pre
SELECT * FROM /exampleRegion WHERE sp.containsAll(set('20', '21', '22'))
```

**Invoking Methods on Objects**

See [Method Invocations](../developing/query_select/the_where_clause.html#the_where_clause__section_D2F8D17B52B04895B672E2FCD675A676) for more information.

``` pre
SELECT * FROM /exampleRegion p WHERE p.length > 1
```

``` pre
SELECT DISTINCT * FROM /exampleRegion p WHERE p.positions.size >= 2
```

``` pre
SELECT DISTINCT * FROM /exampleRegion p WHERE p.positions.isEmpty
```

``` pre
SELECT DISTINCT * FROM /exampleRegion p WHERE p.name.startsWith('Bo')
```

**Using Query-Level Debugging**

To set debugging on the query level, add the **&lt;trace&gt;** keyword before the query. (If you are using an IMPORT statement, include it before the IMPORT).

``` pre
<trace>
SELECT * from /exampleRegion, positions.values TYPE myclass
```

**Using Reserved Words in Queries**

To access any method, attribute, or named object that has the same name as a query language reserved word, enclose the name within double quotation marks.

``` pre
SELECT * FROM /exampleRegion WHERE status = 'active' AND "type" = 'XYZ'
```

``` pre
SELECT DISTINCT "type" FROM /exampleRegion WHERE status = 'active'
```

**Using IMPORT**

In the case where the same class name resides in two different namescopes (packages), there needs to be a means of referring to different classes of the same name. The IMPORT statement is used to establish a namescope for a class in a query.

``` pre
IMPORT package.Position;
SELECT DISTINCT * FROM /exampleRegion, positions.values positions TYPE Position WHERE positions.mktValue >= 25.00
```

**Using TYPE**

Specifying object type 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.

``` pre
SELECT DISTINCT * FROM /exampleRegion, positions.values positions TYPE Position WHERE positions.mktValue >= 25.00
```

**Using ELEMENT**

Using ELEMENT(expr) 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.

``` pre
ELEMENT(SELECT DISTINCT * FROM /exampleRegion WHERE id = 'XYZ-1').status = 'active'
```

## <a id="reference_D5CE64F5FD6F4A808AEFB748C867189E__section_5383407F9D004D4EB4E695252EBA1EF0" class="no-quick-link"></a>Which APIs should I use to write my queries?

If you are querying a Java application’s local cache or querying other members, use [org.apache.geode.cache.Cache.getQueryService](/releases/latest/javadoc/org/apache/geode/cache/query/QueryService.html).

If you are writing a Java client to server query, use [org.apache.geode.cache.client.Pool.getQueryService](/releases/latest/javadoc/org/apache/geode/cache/client/Pool.html).

## <a id="reference_D5CE64F5FD6F4A808AEFB748C867189E__section_3E6E4B33D57846008EF4404D2B687597" class="no-quick-link"></a>How do I invoke an object's method in a query?

To use a method in a query, use the attribute name that maps to the public method you want to invoke. For example:

``` pre
/*valid method invocation*/ 
SELECT DISTINCT * FROM /exampleRegion p WHERE p.positions.size >= 2 - maps to positions.size()
```

## <a id="reference_D5CE64F5FD6F4A808AEFB748C867189E__section_9221C29BC1FD49D7BBD26BB34D5BDEB8" class="no-quick-link"></a>Can I invoke a static method on an object in a query?

No, you cannot invoke a static method on an object. For example, the following query is invalid.

``` pre
/*invalid method invocation*/
SELECT DISTINCT * FROM /exampleRegion WHERE aDay = Day.Wednesday
```

To work around this limitation, write a reusable query that uses a query bind parameter to invoke the static method. Then at query run time, set the parameter to the static method invocation (`Day.Wednesday`). For example:

``` pre
SELECT DISTINCT * FROM /exampleRegion WHERE aDay = $1
```

## <a id="reference_D5CE64F5FD6F4A808AEFB748C867189E__section_907DBBBA1AEC4570A15B3491B0A7DF0E" class="no-quick-link"></a>How do I write a reusable query?

Using query APIs, you can set query bind parameters that are passed values at query run time. For example:

``` pre
// specify the  query string
 String queryString = "SELECT DISTINCT * FROM /exampleRegion p WHERE p.status = $1";

QueryService queryService = cache.getQueryService();
Query query = queryService.newQuery(queryString);

// set a query bind parameter
Object[] params = new Object[1];
params[0] = "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();
```

If you use a query bind parameter in place of a region path in your path expression, the parameter value must reference a collection (and not a String such as the name of the region path.)

See [Using Query Bind Parameters](../developing/query_additional/using_query_bind_parameters.html#concept_173E775FE46B47DF9D7D1E40680D34DF) for more details.

## <a id="reference_D5CE64F5FD6F4A808AEFB748C867189E__section_3A9528E8E43140BAA0D5A1457CCAB2D2" class="no-quick-link"></a>When should I create indexes to use in my queries?

Determine whether your query’s performance will benefit from an index. For example, in the following query, an index on pkid can speed up the query.

``` pre
SELECT DISTINCT * FROM /exampleRegion portfolio WHERE portfolio.pkid = '123'
```

## <a id="reference_D5CE64F5FD6F4A808AEFB748C867189E__section_76CDCCFBDB134A339DBE556C28D48F11" class="no-quick-link"></a>How do I create an index?

An index can be created programmatically using APIs or by using xml. Here are two examples:

**Sample Code**

``` pre
QueryService qs = cache.getQueryService();
 qs.createIndex("myIndex", "status", "/exampleRegion");
 qs.createKeyIndex("myKeyIndex", "id", "exampleRegion");
```

For more information on using this API, see the [JavaDocs](/releases/latest/javadoc/index.html).

**Sample XML**

``` pre
<region name="portfolios">
 <region-attributes . . . >
 </region-attributes>
 <index name="myIndex">
 <functional from-clause="/exampleRegion" 
     expression="status"/>
 </index>
 <index name="myKeyIndex">
 <primary-key field="id"/>
 </index>
 <entry>
```

For more details on indexes, see [Working with Indexes](../developing/query_index/query_index.html).

## <a id="reference_D5CE64F5FD6F4A808AEFB748C867189E__section_30D8C9A5472E4768AB7A0D598C9A496E" class="no-quick-link"></a>Can I create indexes on overflow regions?

You can create indexes on overflow regions, but you are subject to some limitations. For example, the data contained in the index itself cannot be overflowed to disk. See [Using Indexes with Overflow Regions](../developing/query_index/indexes_with_overflow_regions.html#concept_87BE7DB32C714EB0BF7532AF93569328) for more information.

## <a id="reference_D5CE64F5FD6F4A808AEFB748C867189E__section_EDD17817450C4FC0B510CD87DB2FCD16" class="no-quick-link"></a>Can I query a partitioned region? Can I perform a join query on a partitioned region?

You can query partitioned regions, but there are some limitations. You cannot perform join queries on partitioned regions, however you can perform equi-join queries on colocated partitioned regions by executing a function on a local data set.

For a full list of restrictions, see [Partitioned Region Query Restrictions](../developing/query_additional/partitioned_region_query_restrictions.html#concept_5353476380D44CC1A7F586E5AE1CE7E8).

## <a id="reference_D5CE64F5FD6F4A808AEFB748C867189E__section_5FF905E0D10D4CDF9E6F49A70848AF69" class="no-quick-link"></a>How can I improve the performance of a partitioned region query?

If you know the data you need to query, you can target particular nodes in your queries (thus reducing the number of servers the query needs to access) by executing the query with the FunctionService. See [Querying a Partitioned Region on a Single Node](../developing/query_additional/query_on_a_single_node.html#concept_30B18A6507534993BD55C2C9E0544A97) for details. If you are querying data that has been partitioned by a key or specific field, you should first create a key index and then execute the query using the FunctionService with the key or field as a filter. See [Optimizing Queries on Data Partitioned by a Key or Field Value](../developing/query_additional/partitioned_region_key_or_field_value.html#concept_3010014DFBC9479783B2B45982014454).

## <a id="reference_D5CE64F5FD6F4A808AEFB748C867189E__section_FBC59A5420FD40D6907A302A1D50DF7E" class="no-quick-link"></a>Which query language elements are supported in <%=vars.product_name%>?

| Supported elements ||          |
|----------|----------|----------|
| AND      | LIMIT    | TO_DATE  |
| AS       | LIKE     | TYPE     |
| COUNT    | NOT      | WHERE    |
| DISTINCT | NVL      | |
| ELEMENT  | OR       | |
| FROM     | ORDER BY | |
| \<HINT\> | SELECT   | |
| IMPORT   | SET      | |
| IN       | \<TRACE\>| |
| IS_DEFINED| TRUE    | |
| IS_UNDEFINED        | |

For more information and examples on using each supported keyword, see [Supported Keywords](../developing/query_additional/supported_keywords.html#reference_07214B0F8DC94659B0F2D68B67195BD8).

## <a id="reference_D5CE64F5FD6F4A808AEFB748C867189E__section_314B88A55B514B88A12DC36227A2D4EF" class="no-quick-link"></a>How do I debug queries?

You can debug a specific query at the query level by adding the &lt;trace&gt; keyword before the query string that you want to debug. Here is an example:

``` pre
<trace> SELECT * FROM /exampleRegion
```

You can also write:

``` pre
<TRACE> SELECT * FROM /exampleRegion
```

When the query is executed, <%=vars.product_name%> will log a message in `$GEMFIRE_DIR/system.log` with the following information:

``` pre
[info 2011/08/29 11:24:35.472 PDT CqServer <main> tid=0x1] Query Executed in 9.619656 ms; rowCount = 99; 
indexesUsed(0) "select *  from /exampleRegion" 
```

If you want to enable debugging for all queries, you can enable query execution logging by setting a System property on the command line during start-up:

``` pre
gfsh>start server --name=server_name -–J=-Dgemfire.Query.VERBOSE=true
```

Or you can set the property programmatically:

``` pre
System.setProperty("gemfire.Query.VERBOSE","true");
```

## <a id="reference_D5CE64F5FD6F4A808AEFB748C867189E__implicit_attributes" class="no-quick-link"></a>Can I use implicit attributes or methods in my query?

If an implicit attribute or method name can only be associated with one untyped iterator, the <%=vars.product_name%> query processor will assume that it is associated with that iterator. However, if more than one untyped iterator is in scope, then the query will fail with a `TypeMismatchException`. The following query fails because the query processor does not fully type expressions:

``` pre
select distinct value.secId from /pos , getPositions(23)
```

The following query, however, succeeds because the iterator is either explicitly named with a variable or it is typed:

``` pre
select distinct e.value.secId from /pos , getPositions(23) e
            
```

## Can I instruct the query engine to use specific indexes with my queries?

Using HINT *indexname* allows you to instruct the query engine to prefer and filter results from the specified indexes. If you provide multiple index names, the query engine will use all available indexes but prefer the specified indexes.

``` pre
<HINT 'IDIndex'> SELECT * FROM /Portfolios p WHERE p.ID > 10 AND p.owner = 'XYZ'
```

``` pre
<HINT 'IDIndex', 'OwnerIndex'> SELECT * FROM /Portfolios p WHERE p.ID > 10 AND p.owner = 'XYZ' AND p.value < 100
            
```

## <a id="reference_D5CE64F5FD6F4A808AEFB748C867189E__section_ayq_hqw_1r" class="no-quick-link"></a>How do I perform a case-insensitive search on a field in OQL?

You can use the Java String class methods `toUpperCase` and `toLowerCase` to transform fields where you want to perform a case-insensitive search. For example:

``` pre
SELECT entry.value FROM /exampleRegion.entries entry WHERE entry.value.toUpperCase LIKE '%BAR%'
```

or

``` pre
SELECT * FROM /exampleRegion WHERE foo.toLowerCase LIKE '%bar%'
```