Tuning Your System for CA Dataquery › Tuning Queries › Examples

Examples

The following examples illustrate how typical queries are processed.

Example 1

Query text:

FIND TABLE1

Results:

Compound Boolean Selection Facility accesses the Native Key index sequentially.

Every row ID is returned to CA Dataquery.

In this case, since no predicates at all are supplied, Compound Boolean Selection Facility simply reads the Index for TABLE1 and returns the row IDs to CA Dataquery. Data rows are not accessed.

Example 2

Query text:

FIND TABLE1 WITH P1

Results:

Compound Boolean Selection Facility reads each data row in Native Key sequence to evaluate P1.

Compound Boolean Selection Facility returns the IDs of qualifying rows to CA Dataquery.

In this case, a nonindexed predicate is passed to Compound Boolean Selection, so it must read the rows in the CA Datacom/DB region to determine which qualify. No data rows are passed to the CA Dataquery region. This requires a full table search in the Compound Boolean Selection Facility region.

Example 3

Query text:

FIND TABLE1 WITH P2

Results:

Compound Boolean Selection Facility uses the Index to locate entries that satisfy P2.

Compound Boolean Selection Facility returns the IDs of qualifying rows to CA Dataquery.

In this case, an indexed predicate is passed to Compound Boolean Selection, so it can evaluate P2 without accessing data rows.

Example 4

Query text:

FIND TABLE1 WITH P5

Results:

Compound Boolean Selection Facility reads each data row to evaluate P5.

Compound Boolean Selection Facility returns the IDs of qualifying rows to CA Dataquery.

In this case, an indexed predicate is passed to Compound Boolean Selection, but the indexed item is compared to another column in the row, rather than to a literal value. Data rows must be read in the Compound Boolean Selection Facility region to evaluate P5. Data rows are not passed to the CA Dataquery region. This requires a full table search in the Compound Boolean Selection Facility region.

Example 5

Query text:

FIND TABLE1 WITH P3 AND P4

Results:

Compound Boolean Selection Facility uses the Index to locate entries satisfying P3.

Compound Boolean Selection Facility passes the data rows for these entries to CA Dataquery.

CA Dataquery evaluates P4 using the data rows.

In this case, an indexed predicate is passed to Compound Boolean Selection, and another indexed but nonqualifying predicate ANDed with the first causes CA Dataquery to have to examine the data rows that satisfied P3 in order to evaluate P4.

Example 6

Query text:

FIND TABLE1 WITH P2 OR P3

Results:

Compound Boolean Selection Facility uses the Index to locate entries satisfying P2.

Compound Boolean Selection Facility uses the Index to locate entries satisfying P3.

Compound Boolean Selection Facility builds a temporary index to eliminate duplicate values.

Compound Boolean Selection Facility passes the qualifying IDs to CA Dataquery.

In this case, two ORed indexed predicates are passed to the Compound Boolean Selection Facility. Compound Boolean Selection Facility uses the Index to locate entries that qualify for each predicate, and a special temporary index to eliminate the duplicates. No data rows are accessed by the Compound Boolean Selection Facility or CA Dataquery.

Example 7

Query text:

FIND TABLE1 WITH P3 OR P4

Results:

Compound Boolean Selection Facility passes all data rows to the CA Dataquery region.

CA Dataquery evaluates P3 or P4.

In this case, two ORed indexed predicates are used, but one is nonqualifying. Since they are ORed, neither can be passed to the Compound Boolean Selection Facility and a full table search is done in the CA Dataquery region.

Example 8

Query text:

FIND TABLE1 WITH P2 RELATED BY KEY1 TO TABLE2

Results:

Compound Boolean Selection Facility uses the index to locate entries in TABLE1 satisfying P2.

Compound Boolean Selection Facility passes the row IDs and data for these to CA Dataquery.

Compound Boolean Selection Facility uses the index to locate entries in TABLE2 satisfying the link column predicate.

Compound Boolean Selection Facility passes those row IDs to CA Dataquery.

In this case, two tables, one with a qualifying indexed predicate, are joined. Compound Boolean Selection Facility can evaluate P2 in the index, but must read and pass to CA Dataquery the data rows for the entries that satisfy P2 so that CA Dataquery can extract the value needed to build the key-value predicate for KEY1 on TABLE2. Compound Boolean Selection Facility can then locate the qualifying rows in TABLE2 without accessing its data rows.