Bug when comparing Indexes that have Parallel enabled

Compares and synchronizes the schemas of Oracle databases

Moderators: eddie davis, richardjm, Michael Christofides, neil.anderson

Bug when comparing Indexes that have Parallel enabled

Postby gilcrest » Mon Dec 02, 2013 12:17 am

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!
gilcrest
 
Posts: 4
Joined: Sun Dec 01, 2013 5:54 pm
Location: Boston

Postby Chris Auckland » Tue Dec 03, 2013 2:59 pm

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
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Postby gilcrest » Tue Dec 03, 2013 8:09 pm

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?
gilcrest
 
Posts: 4
Joined: Sun Dec 01, 2013 5:54 pm
Location: Boston

Postby Chris Auckland » Wed Dec 04, 2013 10:55 am

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
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Re:

Postby gilcrest » Wed Dec 04, 2013 11:14 am

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...
gilcrest
 
Posts: 4
Joined: Sun Dec 01, 2013 5:54 pm
Location: Boston


Return to Schema Compare for Oracle

Who is online

Users browsing this forum: No registered users and 0 guests