ODBC and JDBC Guide

36 FileMaker ODBC and JDBC Guide
DROP INDEX statement
Use the DROP INDEX statement to remove an index from a database file. The format of the DROP INDEX
statement is:
DROP INDEX [ON] table_name.column_name
Remove an index when your database file is too large, or you don’t often use a field in queries.
If your queries are experiencing poor performance, and you’re working with an extremely large FileMaker
database file with many indexed text fields, consider dropping the indexes from some fields. Also consider
dropping the indexes from fields that you rarely use in SELECT statements.
Dropping an index for any column automatically selects the Storage Option of None and clears
Automatically create indexes as needed in Indexing for the corresponding field in the FileMaker database
file.
The PREVENT INDEX CREATION attribute is not supported.
Example
DROP INDEX ON Salespeople.Salesperson_ID
SQL aggregate functions
Aggregate functions return a single value from a set of records. You can use an aggregate function as part
of a SELECT statement, with a field name (for example, AVG(SALARY)), or in combination with a column
expression (for example, AVG(SALARY * 1.07)).
You can precede the column expression with the DISTINCT operator to eliminate duplicate values. For
example:
COUNT (DISTINCT last_name)
In this example, only unique last name values are counted.
Important Use uppercase letters for SQL function names (some are case sensitive).
Aggregate function Returns
SUM The total of the values in a numeric field expression. For example, SUM(SALARY) returns the sum
of all salary field values.
AVG The average of the values in a numeric field expression. For example, AVG(SALARY) returns the
average of all salary field values.
COUNT The number of values in any field expression. For example, COUNT(NAME) returns the number of
name values. When using COUNT with a field name, COUNT returns the number of non-null field
values. A special example is COUNT(*), which returns the number of records in the set, including
records with null values.
MAX The maximum value in any field expression. For example, MAX(SALARY) returns the maximum
salary field value.
MIN The minimum value in any field expression. For example, MIN(SALARY) returns the minimum
salary field value.