Link to home
Start Free TrialLog in
Avatar of diteps06
diteps06Flag for United States of America

asked on

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

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.
Avatar of PortletPaul
PortletPaul
Flag of Australia image

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.
it might be a bug
have you created an sr about the query ?
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.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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)
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!
Avatar of diteps06

ASKER

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
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
ASKER CERTIFIED SOLUTION
Avatar of diteps06
diteps06
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial