SQL & PL/SQL

Hi all,


My database version is 11.1.

We have to rebuild/re-create index in our batch system every day. And we always append 'parallel 2' to reuild/re-create statement.
As a result, the degree of indices is 2 in our database.

And I have found that this may cause CBO to do wrong estimation for some SQLs and in turn to choose wrong join method.

For example, one bitmap index has degree 2. With this setting, CBO uses star transformation(bitmap conversion operation) strategy. But if I alter this index noparallel, CBO will uses hash join and the execution time is just 10s rather than 2 hrs for original plan.

So I plan to suggest we alter indices noparallel after we rebuild them.

But I am not sure what implications it may have if I do so. Could someone give me a piece of advice on advantage and disadvantage of parallel index, please?

Best regards,
Leon

Edited by: user12064076 on Mar 20, 2011 10:41 PM