MINUS query presents Oracle12c - ORA-01792: maximum number of columns in a table or view is 1000 error

diteps06
diteps06 used Ask the Experts™
on
We are experiencing the Oracle12c - ORA-01792: maximum number of columns in a table or view is 1000 after an upgrade from 11g to 12c which involves a MINUS query. The individual queries execute without the error.
In other words lets say the query is SELECT A, B FROM HN1 MINUS SELECT A, B FROM HN2
The statement SELECT A, B FROM HN1 executes without error. Likewise SELECT A, B FROM HN2.
However when they are combine the above error shows. There are about 18 columns in the query and when Oracle does internal query processing creating inline temporary tables it may strike the 1000 limit. At the moment I will not reduce the amount of columns in the query. Is there an Oracle hint I can use to make it function or any other strategy.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
A limit is a limit, to my knowledge there is no "hint" that will raise internal limits.

However I have had clients with 11g views that exceeded 1,000 columns - but had not needed to use it via "minus"

It is really rather impossible to answer this fully without detail:

SELECT A, B FROM HN1 << is this a view?
MINUS
SELECT A, B FROM HN2 << is this a view?


"There are about 18 columns in the query"
Why "about"?
Can we see the real query?

I think you are going to have to delve into "HN1" and "HN2" to resolve this problem. You may need to revise those views to reduce the number of columns.
Geert GOracle dba
Top Expert 2009

Commented:
it might be a bug
have you created an sr about the query ?
johnsoneSenior Oracle DBA

Commented:
I would think the full query with the execution plan would be most helpful here.  I am going to assume that at least one of those 2 "tables" are views as well.  The view definitions would be helpful as well.  Sounds like Oracle is trying to materialize something in the middle that ends up with more than 1,000 columns.

The query plan for the same query in 11g would be extremely helpful as well.  My guess is the plan changed and that is causing the issue.  If you have the actual plan from 11g, you could probably force the plan in 12c.  Your best option is most likely going to be a stored outline to force a specific plan.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
You didn't provide your full 12c version so that can make diagnosing things difficult.

I agree with the SR comment.  Oracle Support can tell you exactly what bug you are hitting.

I would also search Oracle Support for possible known bugs and workarounds.

Specifically:
Select Statement Throws ORA-01792 Error in 12c (Doc ID 1951689.1)
jtriftsMI and Automation

Commented:
First of all, a limit is NOT ALWAYS a limit. There are a host of undocumented "underscore" parameters that override default behaviour.
That is not an invitation to make changes willy-nilly! Especially since such changes, if not agreed though oracle, can potentially invalidate your support agreement.
So raise an SR (service request) with Oracle in the first instance.
Next, follow Netminder:s advice in searching MOS... Especially the docid he gave you as one of the options is to change an undocumented parameter...or apply a certain patch.

If you want the E-E experts to help you as much as possible, you need to provide some concrete code, and the exact version of Oracle, ideally clarifying what patch sets have been applied. This allows for an accurate test harness and replication of the error. If you are able to provide any table creation scripts, all the better.

QQ for you: Have you applied patch 19509982 to your DB (either directly or as part of a PSU)?

The mos note indicated by netminder also indicates a back ported bug which raises this error condition which is fixed by patch 19509982.

Best of luck!

Author

Commented:
I finally resolved it by substituting 2 tables with inline views which withdrew just the required columns used in the joins and Select statement.
This make Oracle optimize use a different execution plan which avoids the issue.
Thanks guys

Author

Commented:
I finally resolved it by substituting 2 tables with inline views which withdrew just the required columns used in the joins and Select statement.
This make Oracle optimize use a different execution plan which avoids the issue.
Thanks guys
Commented:
I finally resolved it by substituting 2 tables with inline views which withdrew just the required columns used in the joins and Select statement.
This make Oracle optimize use a different execution plan which avoids the issue.
Thanks guys

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial