Example with Directives

Enterprise Edition Home | Express Edition Home | Previous Page | Next Page Optimizer Directives > Types of Directives That Are Included in SQL Statements >

Example with Directives

The following example shows how directives can alter the query plan.

Suppose you have the following query:

SELECT * FROM emp,job,dept
WHERE emp.location = 10
   AND emp.jobno = job.jobno
   AND emp.deptno = dept.deptno
   AND dept.location = "DENVER";

Assume that the following indexes exist:

ix1: emp(empno,jobno,deptno,location)
ix2: job(jobno)
ix3: dept(location)

You run the query with SET EXPLAIN ON to display the query path that the optimizer uses.

QUERY:
------
SELECT * FROM emp,job,dept
WHERE emp.location = "DENVER"
   AND emp.jobno = job.jobno
   AND emp.deptno = dept.deptno
   AND dept.location = "DENVER"

Estimated Cost: 5
Estimated # of Rows Returned: 1

1) informix.emp: INDEX PATH

    Filters: informix.emp.location = 'DENVER' 

    (1) Index Keys: empno jobno deptno location   (Key-Only)

2) informix.dept: INDEX PATH

    Filters: informix.dept.deptno = informix.emp.deptno 

    (1) Index Keys: location 
        Lower Index Filter: informix.dept.location = 'DENVER' 
NESTED LOOP JOIN

3) informix.job: INDEX PATH

    (1) Index Keys: jobno   (Key-Only)
        Lower Index Filter: informix.job.jobno = informix.emp.jobno 
NESTED LOOP JOIN

The diagram in Figure 62 shows a possible query plan for this query.

Figure 62. Possible Query Plan Without Directives

begin figure description -The query plan shows at the top level a nested-loop join, at the mid-level an index scan using ix2 on the job table and a nested-loop join, and at the bottom level an index scan using ix1 on the emp table and an index scan using ix3 on the dept table - end figure description

Perhaps you are concerned that using a nested-loop join might not be the fastest method to execute this query. You also think that the join order is not optimal. You can force the optimizer to choose a hash join and order the tables in the query plan according to their order in the query, so the optimizer uses the query plan that Figure 63 shows.

Figure 63. Possible Query Plan with Directives

begin figure description -The query plan shows at the top level a hash join (build is on dept), at the mid-level an index scan using ix2 on the dept table and a hash join (build is on job), and at the bottom level an index scan using ix1 on the emp table and a full table scan on the job table - end figure description

To force the optimizer to choose the query plan that uses hash joins and the order of tables shown in the query, use the directives that the following SET EXPLAIN output shows:

QUERY:
------
SELECT {+ORDERED,
   INDEX(emp ix1),
   FULL(job),
   USE_HASH(job /BUILD),
   USE_HASH(dept /BUILD),
   INDEX(dept ix3)}
   * FROM emp,job,dept
   WHERE emp.location = 1
   AND emp.jobno = job.jobno
   AND emp.deptno = dept.deptno
   AND dept.location = "DENVER"

DIRECTIVES FOLLOWED: 
ORDERED 
INDEX ( emp ix1 )
FULL ( job )
USE_HASH ( job/BUILD )
USE_HASH ( dept/BUILD )
INDEX ( dept ix3 )
DIRECTIVES NOT FOLLOWED: 

Estimated Cost: 7
Estimated # of Rows Returned: 1

1) informix.emp: INDEX PATH

    Filters: informix.emp.location = 'DENVER' 

    (1) Index Keys: empno jobno deptno location   (Key-Only)

2) informix.job: SEQUENTIAL SCAN


DYNAMIC HASH JOIN 
    Dynamic Hash Filters: informix.emp.jobno = informix.job.jobno 

3) informix.dept: INDEX PATH

    (1) Index Keys: location 
        Lower Index Filter: informix.dept.location = 'DENVER' 

DYNAMIC HASH JOIN 
    Dynamic Hash Filters: informix.emp.deptno = informix.dept.deptno