ON 7.3 HP10.20


How can I force a query to use a Index?

OPTCOMPIND=1
OPTGOAL=-1
Stats update on a weekly basis

When I run my query for YEAR > 2002 and use the index I want and
runs for a few seconds. It as a order by on date.

When I run my query for YEAR > 2000 it's not using the index and takes hours to run. I get this in the explain plan for > 2000 "Temporary Files Required For: Order By"

Hi,

You may use Optimizer Directives which forces the optimizer to take a alternate path. Example:

1.
SELECT --+ INDEX ( orders year_index )
*
FROM orders
WHERE year > 2000
ORDER BY year

2.
SELECT --+ AVIOD_FULL(b), INDEX ( b year_index )
*
FROM customer a, orders b
WHERE a.custno=b.custno and year > 2000
ORDER BY year

Please post snapshots of below for further support:
info indexes for <tabname> ;
sqexplain.out

Regards,
Shriyan

Thanks Shriyan.

I did like you mention, the explain plan shows
that it did not use a a temporary file.

But it also indicated "Directive not followed"
But it did use the index instead and the query
took about 2 second to show up instead of hours.

So now the question is..."Drum roll"...Why?
Why is the optimizer not picking this up?
What should I change to be able to run my query
without having to put --+...

Alex:

As we know, in normal circumstances the optimizer considers existing indices in the least cost method of path selection. In your case, by not selecting one, it may be behaving abnormally. But this can be confirmed only after scanning thoroghly the SQL that been fired without directive been passed. In very rare cases, the optimizer inteprets certain "good" paths as inefficient and costly and resorts to otherwise a costly or to a poor path.

It would be very helpful if you could post the sqexplain output along with info indexes for tables involved in the query.

Regards,
Shriyan

Here it is.....

Header which is the main table is 1.5 million rows and result query is
half a million.

QUERY(BAD):
------
select *
from header, address, outer email_gen
WHERE region_code = '1'
AND email_gen.sma_em = 'P'
AND header.head_number = email_gen.sma_number
AND address.supplier_number = header.head_vendor
AND address.address_number = header.head_vendor_addr
AND 1=1
and head_date > '2000/01/01'
ORDER BY head_date

Estimated Cost: 355796
Estimated # of Rows Returned: 1
Temporary Files Required For: Order By

1) sumdba.sdf_address: SEQUENTIAL SCAN

Filters: 1 = 1

2) sumdba.poheader: INDEX PATH

Filters: (sumx.header.region_code = '1' AND sumx.header.head_date > 2000/01/01 )

(1) Index Keys: head_vendor head_vendor_addr (Serial, fragments: ALL)
Lower Index Filter: (sumx.header.head_vendor_addr = sumx.sdf_address.address_number AND sumx.po
header.head_vendor = sumx.address.supplier_number )
NESTED LOOP JOIN

3) sumy.email_gen: INDEX PATH

(1) Index Keys: sma_number sma_em
Lower Index Filter: (sumy.email_gen.sma_em = 'P' AND sumy.email_gen.sma_number = sumx
.header.head_number )
NESTED LOOP JOIN
=============================================

QUERY(GOOD):
------
select --+ INDEX (header header_i02)
*
from header, address, outer email_gen
WHERE region_code = '1'
AND email_gen.po_sma_em = 'P'
AND header.head_number = email_gen.sma_number
AND address.supplier_number = header.head_vendor
AND address.address_number = header.head_vendor_addr
AND 1=1
and head_date > '2000/01/01'
ORDER BY head_date


DIRECTIVES FOLLOWED:
INDEX ( header header_i02 )
DIRECTIVES NOT FOLLOWED:

Estimated Cost: 578199
Estimated # of Rows Returned: 1

1) sumx.header: INDEX PATH

Filters: (1 = 1 AND sumx.header.region_code = '1' )

(1) Index Keys: head_date (Serial, fragments: ALL)
Lower Index Filter: sumx.header.head_date > 2000/01/01

2) sumx.address: INDEX PATH

(1) Index Keys: supplier_number address_number
Lower Index Filter: (sumx.address.address_number = sumx.poheader.head_vendor_addr AND sumx.address.supplier_number = sumx.header.head_vendor )
NESTED LOOP JOIN

3) sumy.email_gen: INDEX PATH

(1) Index Keys: sma_number sma_em
Lower Index Filter: (sumy.email_gen.sma_em = 'P' AND sumy.email_gen.sma_number = sumx
.header.head_number )
NESTED LOOP JOIN
============================================
--Index for Header--
Index name Owner Type Cluster Columns
header_i02 informix dupls No head_date

Alex:

Query seems to be alright, except the join clause:

AND 1=1

I'm bit wary of condition, as it is always true, and I don't see any reason for specifying it. Explain suggests table header being scanned squentially, may be due to 1=1 specification. Please remove this and fire the SQL without the index directive been passed.

Regards,
Shriyan

Yep, if I took out the 1=1 and it run fine.
Why would someone put 1=1 in a query?