Bug when comparing Indexes that have Parallel enabled

gilcrestgilcrest Posts: 6
edited December 4, 2013 5:14AM in Schema Compare for Oracle
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!

Comments

  • 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.
    Chris
  • 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?
  • 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.
    Chris
  • 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...
Sign In or Register to comment.