Bug when comparing Indexes that have Parallel enabled

gilcrest

When Schema Compare for Oracle finds two indexes that don't match due to differences in Parallel, if you want to choose the index without parallel enabled, this is the DDL the script provides:

ALTER INDEX fake_index_name ;

It should be:

ALTER INDEX fake_index_name NOPARALLEL;

The NOPARALLEL clause is missing...

Thanks!

Chris Auckland

Thanks for your post.

I could be wrong, but I don't think there would be any need to script the NOPARALLEL query hint when creating the index. As far as I can see the NOPARALLEL query hint is used to override the existing PARALLEL settings. If the index is created with NOPARALLEL then it is effectively like creating an index without the PARALLEL setting.

If I create an index in SQL Developer using the NOPARALLEL hint, then when I script out the DDL using SQL Developer, it doesn't mention the NOPARALLEL setting. I have a feeling Oracle doesn't even store the fact that the index was created with NOPARALLEL and treats it the same as an index that doesn't specify PARALLEL.

gilcrest

Thanks for the reply, Chris. So, the issue is actually when I want to remove parallel from a given instance. For example, I compare schema A to schema B - schema A has a Parallel of 4 for index named fake_index_name, schema B has no parallel setup for the same index. I want to make fake_index_name in schema A look exactly like schema B, meaning I need to drop the parallel from the index. In this case, the tool provides "ALTER INDEX fake_index_name ;" when it should provide "ALTER INDEX fake_index_name NOPARALLEL;"

Hope this makes sense?

Chris Auckland

I think I follow. If you don't specify NOPARALLEL then the index after the alter keeps the original PARALLEL setting.

I'll test that out and report a bug if that's the case.

Thanks for the clarification.

gilcrest

Chris Auckland wrote:

I think I follow. If you don't specify NOPARALLEL then the index after the alter keeps the original PARALLEL setting.

I'll test that out and report a bug if that's the case.

Thanks for the clarification.

Actually - the alter statement given by the tool "ALTER INDEX fake_index_name ;" is simply not a valid DDL statement... you need the NOPARALLEL in there...