CODEWITHSUNDEEP
sqloracle-databaseoracle12c
Matthew Zackschewski
Matthew Zackschewski

Reputation: 486

SQLDeveloper Tuning Advisor with Compiler Hints

I am trying to use the Oracle SQL Developer program to analyze a few queries that are taking an exponentially greater time to complete as the data pool gets large. In order to do so, I'm using the SQL Tuning Advisor to analyze the query/table structure to potentially create better indices and/or better queries.

Everything was going great until I analyzed my insert query. It has a /*+ no_merge(<>) */ hint in it since the sub-select used in the insert has aggregations that will cause a compiler "ORA-00979: not a GROUP BY expression" error. It is a well known bug already and I'm aware of that.

My problem is that the Tuning Advisor apparently ignores the hints and returns with the same error as you see above. I can still obtain an execution plan and can still run the query but I would like to use the advisor to get a better analysis.

A solution to my problem could either be a suggestion on how to alter my query to not use the no_merge hint or a tip on how to use the advisor with the hint.

I've already seen this post here which shows one of the users commenting about the Oracle bug but it doesn't help my cause.

For discretionary purposes I can't just post the query on here. A description of the query is as follows:

Insert into tableX (
   cols...
)
select /*+ no_merge(<<alias>>) */
   <<alias>>.*
   otherCols...
from (
   select 
      cols...
      min(col1) as newName1,
      max(col1) as newName2
   from (
      select *
      from tableX
      where not exists (
         select 'x'
         from tableX
         where conditions...
      )
   ) group by
      cols...
) <<alias>>;

For @BobC in the comments... Version Number of the Oracle DB

Upvotes: 0

Views: 383

Answers (1)

mustaccio
mustaccio

Reputation: 18980

Seeing that 1) the bug appears to only affect INSERT statements and the SELECT in it should run just fine with the hint and 2) the advisor is unlikely to offer any help tuning the INSERT part specifically, you can simply run the adviser against the SELECT statement alone, with or without the hint.

Upvotes: 2

Related Questions