Toad World® Forums

Cannot generate virtual indexes


#1

Hi,

I’m using Quest SQL optimizer 7.4.1 and oracle 10.2.

I would to optimize a query and generate virtual indexes.
I copy the SQL query and click on the generate virtual indexes button. and an Information dialog box opens (see the attached image) and tells me that
“The Index Expert did not find any index or set with a unique execution plan using the Intellignece Level setting to 5…”

I tried the different settings whit allways the same information dialog box.

How can I take benefit of Generate Virtual indexes?

Please help

Seb

snap.jpeg


#2

It is hard to tell whether your SQL can be tuned by virtual index, there are some situations that no virutual index can be provided by SQL Optimizer:

  1. Table size too small, then oracle always consider full scan instead of index search.
  2. There are no available index candidate in your SQL, which means that all potential candidates are indexed or potential candidates cannot make Oracle to consider new plan generation.
  3. The lowerst cost plan is already generated by your existing index configuration, any other new virtual indexes cannot change Oracle optimizer decision.

If you want, give us your SQL, related table size, existing index structure/configuration and current query plan for us to further investage.

Richard