index hint not being used

managed BEAN

Hello everyone,

I am providing a hint on a query:

select CONTENT6 /*+ index(PARAMETERS PARAMS_IDX) */

from PARAMETERS

where PROGNAME='Global'

and PROCNAME='Other'

and VARNAME='Generic_cfg_local'

and (WORKSTATIONKY='140' or WORKSTATIONKY is NULL)

and 1=1;

But for some reason it´s not working on another schema (the one on the rigth) , and i do not knnow why, can you please help me out:

I realize optimizer is choosing another index rather than the one i sugested (but is used on left schema), but i want to use it (in that case i could compare explain plan for the same schema, also).

Thanks in advance

Hub Tijhuis

I don't know of a "nice" solution, but maybe temporary drop PARAMETERS_N1 index?

Hub Tijhuis

Are the statistics up to date?

ANALYZE TABLE PARAMETERS COMPUTE STATISTICS FOR ALL INDEXES;

And keep in mind: if the data in both schema's are different, statistics are different and using an other index can be more efficient.

managed BEAN

Hello @Hub Tijhuis ,

Thanks for reply.

Yes the statistics are up to date👍️

I am aware that with diferent data the efficiency can be diferent with same index, but how do i proof that, if i don´t have execution plans of both indexes?

Regards,

Hub Tijhuis

I don't know of a "nice" solution, but maybe temporary drop PARAMETERS_N1 index?

managed BEAN

ok, :(

Thanks very much anyway.

Regards

padders

Perhaps put the hint before the select_list as is documented?

SELECT [ hint ] [ { { DISTINCT | UNIQUE } | ALL } ] select_list

 FROM ...