Toad World® Forums

does global index could produce composite index?


#1

Hi All,

i am testing SQL Optimizer for Oracle 7.3.

In my Test scenario, global index just suggest single coulmn index.

So, i wonder global index could produce composite index.

Thanks,
woong


#2

Yes, the index recommendation will suggest composite index, but the criteria is that index will cause changes to explain plan. With these indexes with same effect for explain plan, we will prefer to take the index with less columns.

Thanks,

Tony Ng


#3

Thanks for your prompt reply.

Could you explain more detail how global index suggest an index set for a group of sql statments?

does global index check existing indexes?

i am afraid that global index just suggest to add indexes for the statements
which’s execution plan were full table scan, sort without index.

thanks,
woong


#4

The virtual index generator will consider any columns combination as an index, and set of indexes as an index set. First of all, it finds the columns, which are possible to build index and apply to these SQL. And than, we analyses the selectivity to decide how to build the index (which columns should include and what order should be). Finally, we examine it will take effect to existing execution plan.

For the column contains in existing indexes, the generator will ignore it as it should be. There is not point to recommend any index which user has already created. However, it should be a good option to disable existing index, unfortunately Oracle doesn’t provide this function in session level. So, it could be a very later enhancement.

As the above mentioned, the virtual index generator will consider index in column and SQL level, not the execution plan. Even there is an index applied, the generator will also recommend another one which can take effect to the execution plan.

Thanks,

Tony Ng


#5

Hello Tony Ng,

Thanks for your kindly reply.

i am appreciate for your help.

Thanks,

woong