We've recently migrated a server from Oracle 11 to Oracle 12c and ran into a few annoying issues. One of them is that a few a reports give the following error:
ORA-01792: maximum number of columns in a table or view is 1000
OK, fine, I understand there have to be some limits. The problem is that the query worked before and that I see no reason for the DB to load all columns. None of tables in the query have more than 1000 columns. Combined they could have. So if I did a select * from everything; I understand that the output runs into limits because a result set is virtualy the same thing as a view/table (is it? I'm just guessing here?)
The "problem query" joins a bunch of big tables and a few smaller ones multiple times, so yes, the total column count could be more than 1000.
However, the query only selects 30 or so columns. Add the fields used for joining and maybe even some additional fields used by indices in the background it might add up to 50~70 columns that are needed to preform the query. All of that information is well defined within the query and table information. So why on earth would the DB even care about all the additional columns?
The solutions I've read so far are to change the query or to disable this warning/error. Both of which are non-solutions to me. The only reason I can think of for disabling the warning is that Oracle decided to inform users earlier and that this "problem" existed in the background in both versions.
Any ideas about this? Or my understanding of how 'big' queries are handled?