We help IT Professionals succeed at work.

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

158 Views
Last Modified: 2019-07-06
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

PortletPaulEE Topic Advisor
CERTIFIED EXPERT
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
CERTIFIED EXPERT
Top Expert 2009

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

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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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
CERTIFIED EXPERT

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:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION