Use of indices in Impromptu

[timida]

Hi
I have a impromptu-report where the execution time is way to long. So I analysed the SQL-Statement. After adding a particular index to it - the execution time is very short:

for example (simplified statement):

Select table_a.nr, table_a.name, table_a.date,...
from table_a, table_b
where table_a.nr = table_b.a_nr
and ...

with use of a particular index:

Select /*+ index(table_a INDEXNAME) */ table_a.nr, table_a.name, table_a.date,...
from table_a, table_b
where table_a.nr = table_b.a_nr
and ...

Is there a possiblity in Impromptu (I'm using version 7.0) to use a particular index (like in the sql-statement above)?

Thanks in advance!

 

[griffindm]

timida,

To my knowledge, Impromptu does not use index hints. From experience looking at execution plans, there is a correlation between the ordering of filter elements and the use of indexes. If you have a filter that uses database-only functions in the filter, and order the filter elements to correspond to the components of the unique or other known index keys present, the database will use the index in execution.

Regards,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[doddy]

I just came searching for information on indexing SQL in an IQD, and found your post. Don't know if it's of any help (because your original post was so long ago), but IQD's can use indexing if you use the alternate syntax, that is instead of ...

select /*+ index(T1) */

you use the other comment method ...

select --+ index(T1)

I thought maybe it might work for your IMR or IWR SQL windows as well, if you use the direct entry of SQL method. Of course, you'll lose other functionality that the Impromptu wizard provides.

regards,
doddy