byrdfarmer
Vendor
I would like to be able to run a query and ignore an index that is in place on the table being used...is there some trick with SQL or a way to do this? I am just doing some time tests, with indexs versus without.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
how do I run a query and ignore an index?
- Thread starter byrdfarmer
- Start date
- Status
Hi,
An ability to influence the behaviour and functioning of the optimizer component with the SQL is called Optimizer Directives or Hintings.
To ignore an existing index in a table, Informix provides syntax as below:
{ +AVOID_INDEX( tabellenname, indexname ) }
or
--+AVOID_INDEX( tabellenname, indexname )
Example:
select --+ AVOID_INDEX(customer, cust_id_u_idx)
* from customer where cust_id='ACZ00453' ;
To see what it did with this directive:
select --+ AVOID_INDEX(customer, cust_id_u_idx) explain
* from customer where cust_id='ACZ00453' ;
If your intension is to avoid index only, you can do it by nagation of the condition like:
select * from customer where cust_id !='0' ;
This will force the optimizer to resort into sequential scan of the object.
Slightly off-subject:
In very rare cases, the optimizer refuse to use an existing index. Hence you can use:
{ +USE_INDEX( tabellenname, indexname ) }
However, it is to be noted that Optimizer Directive or Hintings will work only if your $INFORMIXDIR/etc/$ONCONFIG parameter file contains the object enabled. It is:
DIRECTIVES 1
Regards,
Shriyan
An ability to influence the behaviour and functioning of the optimizer component with the SQL is called Optimizer Directives or Hintings.
To ignore an existing index in a table, Informix provides syntax as below:
{ +AVOID_INDEX( tabellenname, indexname ) }
or
--+AVOID_INDEX( tabellenname, indexname )
Example:
select --+ AVOID_INDEX(customer, cust_id_u_idx)
* from customer where cust_id='ACZ00453' ;
To see what it did with this directive:
select --+ AVOID_INDEX(customer, cust_id_u_idx) explain
* from customer where cust_id='ACZ00453' ;
If your intension is to avoid index only, you can do it by nagation of the condition like:
select * from customer where cust_id !='0' ;
This will force the optimizer to resort into sequential scan of the object.
Slightly off-subject:
In very rare cases, the optimizer refuse to use an existing index. Hence you can use:
{ +USE_INDEX( tabellenname, indexname ) }
However, it is to be noted that Optimizer Directive or Hintings will work only if your $INFORMIXDIR/etc/$ONCONFIG parameter file contains the object enabled. It is:
DIRECTIVES 1
Regards,
Shriyan
- Status