Here’s a quirky little bug that appeared on the OTN database forum in the last 24 hours which (in 12.1.0.2, at least [update: fixed in 12.2.0.0]) produces an issue which I can best demonstrate with the following cut-n-paste:
rem rem Script: 12c_col_prob.sql rem Author: Jonathan Lewis rem Dated: March 2015 rem SQL> desc purple Name Null? Type ----------------------------------- -------- ------------------------ G_COLUMN_001 NOT NULL NUMBER(9) P_COLUMN_002 VARCHAR2(2) SQL> select p.* 2 from GREEN g 3 join RED r on g.G_COLUMN_001 = r.G_COLUMN_001 4 join PURPLE p on g.G_COLUMN_001 = p.G_COLUMN_001; join PURPLE p on g.G_COLUMN_001 = p.G_COLUMN_001 * ERROR at line 4: ORA-01792: maximum number of columns in a table or view is 1000 SQL> select p.g_column_001, p.p_column_002 2 from GREEN g 3 join RED r on g.G_COLUMN_001 = r.G_COLUMN_001 4 join PURPLE p on g.G_COLUMN_001 = p.G_COLUMN_001; no rows selected
The query that requires “star-expansion” fails with ORA-01792 but if you explicitly expand the ‘p.*’ to list the columns the star represents the optimizer is happy. (The posting also showed the same difference in behaviour when changing “select constant from {table join}” to “select (select constant from dual) from {table join}”)
The person who highlighted the problem supplied code to generate the tables so you can repeat the tests very easily; one of the quick checks I did was to modify the code to produce tables with a much smaller number of columns and then expanded the SQL to see what Oracle would have done with the ANSI. So, with only 3 columns each in table RED and GREEN, this is what I did:
set serveroutput on set long 20000 variable m_sql_out clob declare m_sql_in clob := ' select p.* from GREEN g join RED r on g.G_COLUMN_001 = r.G_COLUMN_001 join PURPLE p on g.G_COLUMN_001 = p.G_COLUMN_001 '; begin dbms_utility.expand_sql_text( m_sql_in, :m_sql_out ); end; / column m_sql_out wrap word print m_sql_out
The dbms_utility.expand_sql_text() function is new to 12c, and you’ll need the execute privilege on the dbms_utility package to use it; but if you want to take advantage of it in 11g you can also find it (undocumented) in a package called dbms_sql2.
Here’s the result of the expansion (you can see why I reduced the column count to 3):
M_SQL_OUT -------------------------------------------------------------------------------- SELECT "A1"."G_COLUMN_001_6" "G_COLUMN_001","A1"."P_COLUMN_002_7" "P_COLUMN_002" FROM (SELECT "A3"."G_COLUMN_001_0" "G_COLUMN_001","A3"."G_COLUMN_002_1" "G_COLUMN_002","A3"."G_COLUMN_003_2" "G_COLUMN_003","A3"."G_COLUMN_001_3" "G_COLUMN_001","A3"."R_COLUMN__002_4" "R_COLUMN__002","A3"."R_COLUMN__003_5" "R_COLUMN__003","A2"."G_COLUMN_001" "G_COLUMN_001_6","A2"."P_COLUMN_002" "P_COLUMN_002_7" FROM (SELECT "A5"."G_COLUMN_001" "G_COLUMN_001_0","A5"."G_COLUMN_002" "G_COLUMN_002_1","A5"."G_COLUMN_003" "G_COLUMN_003_2","A4"."G_COLUMN_001" "G_COLUMN_001_3","A4"."R_COLUMN__002" "R_COLUMN__002_4","A4"."R_COLUMN__003" "R_COLUMN__003_5" FROM "TEST_USER"."GREEN" "A5","TEST_USER"."RED" "A4" WHERE "A5"."G_COLUMN_001"="A4"."G_COLUMN_001") "A3","TEST_USER"."PURPLE" "A2" WHERE "A3"."G_COLUMN_001_0"="A2"."G_COLUMN_001") "A1"
Tidying this up:
SELECT A1.G_COLUMN_001_6 G_COLUMN_001, A1.P_COLUMN_002_7 P_COLUMN_002 FROM ( SELECT A3.G_COLUMN_001_0 G_COLUMN_001, A3.G_COLUMN_002_1 G_COLUMN_002, A3.G_COLUMN_003_2 G_COLUMN_003, A3.G_COLUMN_001_3 G_COLUMN_001, A3.R_COLUMN__002_4 R_COLUMN__002, A3.R_COLUMN__003_5 R_COLUMN__003, A2.G_COLUMN_001 G_COLUMN_001_6, A2.P_COLUMN_002 P_COLUMN_002_7 FROM ( SELECT A5.G_COLUMN_001 G_COLUMN_001_0, A5.G_COLUMN_002 G_COLUMN_002_1, A5.G_COLUMN_003 G_COLUMN_003_2, A4.G_COLUMN_001 G_COLUMN_001_3, A4.R_COLUMN__002 R_COLUMN__002_4, A4.R_COLUMN__003 R_COLUMN__003_5 FROM TEST_USER.GREEN A5, TEST_USER.RED A4 WHERE A5.G_COLUMN_001=A4.G_COLUMN_001 ) A3, TEST_USER.PURPLE A2 WHERE A3.G_COLUMN_001_0=A2.G_COLUMN_001 ) A1
As you can now see, the A1 alias lists all the columns in GREEN, plus all the columns in RED, plus all the columns in PURPLE – totaling 3 + 3 + 2 = 8. (There is a little pattern of aliasing and re-aliasing that turns the join column RED.g_column_001 into G_COLUMN_001_3, making it look at first glance as if it has come from the GREEN table).
You can run a few more checks, increasing the number of columns in the RED and GREEN tables, but essentially when the total number of columns in those two tables goes over 998 then adding the two extra columns from PURPLE makes that intermediate inline view break the 1,000 column rule.
Here’s the equivalent expanded SQL if you identify the columns explicitly in the select list (even with several hundred columns in the RED and GREEN tables):
SELECT A1.G_COLUMN_001_2 G_COLUMN_001, A1.P_COLUMN_002_3 P_COLUMN_002 FROM ( SELECT A3.G_COLUMN_001_0 G_COLUMN_001, A3.G_COLUMN_001_1 G_COLUMN_001, A2.G_COLUMN_001 G_COLUMN_001_2, A2.P_COLUMN_002 P_COLUMN_002_3 FROM ( SELECT A5.G_COLUMN_001 G_COLUMN_001_0, A4.G_COLUMN_001 G_COLUMN_001_1 FROM TEST_USER.GREEN A5, TEST_USER.RED A4 WHERE A5.G_COLUMN_001=A4.G_COLUMN_001 ) A3, TEST_USER.PURPLE A2 WHERE A3.G_COLUMN_001_0=A2.G_COLUMN_001 ) A1
As you can see, the critical inline view now holds only the original join columns and the columns required for the select list.
If you’re wondering whether this difference in expansion could affect execution plans, it doesn’t seem to; the 10053 trace file includes the following (cosmetically altered) output:
Final query after transformations:******* UNPARSED QUERY IS ******* SELECT P.G_COLUMN_001 G_COLUMN_001, P.P_COLUMN_002 P_COLUMN_002 FROM TEST_USER.GREEN G, TEST_USER.RED R, TEST_USER.PURPLE P WHERE G.G_COLUMN_001=P.G_COLUMN_001 AND G.G_COLUMN_001=R.G_COLUMN_001
So it looks as if the routine to transform the syntax puts in a lot of redundant text then the optimizer takes it all out again.
The problem doesn’t exist with traditional Oracle syntax, by the way, it’s an artifact of Oracle’s expansion of the ANSI syntax and 11.2.0.4 is quite happy to handle the text generated by the ANSI transformation when there are well over 1,000 columns in the inline view.
Hi Jonathan,
I would like to mention the relevant fix control.
The ORA-01792 error can be avoided by disabling the fix for bug 17376322:
Select Statement Throws ORA-01792 Error (Doc ID 1951689.1)
Pros:
– no code modification is needed.
For example we could use SQL Patch to inject an opt_param hint: opt_param(‘_fix_control’ ‘17376322:0’)
Cons:
– however, there are some side effects.
For example Bug 19468326 : LONGER PARSE TIME DUE TO DYNAMIC SAMPLING
Slightly offtopic:
When I see “select *” in the application code,
I suggest the developer to read:
1. Oracle Core Chapter 7 Parsing and Optimising: https://jonathanlewis.wordpress.com/oracle-core/oc-7-parsing-and-optimising/
2. Alexander Anokhin blog post about column projection: https://alexanderanokhin.wordpress.com/2012/07/18/dont-forget-about-column-projection/
Best regards,
Mikhail.
Comment by Velikikh Mikhail — March 30, 2015 @ 2:28 pm BST Mar 30,2015 |
Mikhail,
Funnily enough there was someone on the OTN database forum a couple of weeks ago arguing the case for using “select *” in all cases. (Though there was an element in the thread that “always” meant – “provided you do everything else perfectly, and don’t do it in the cases where it’s going to go wrong’ ;)
Comment by Jonathan Lewis — April 1, 2015 @ 8:14 pm BST Apr 1,2015 |
Hi Mikhail, how exactly do I inject the opt_param? I mean, what is the command line for doing that? Thank you!
Comment by Cássio Bastos — August 19, 2015 @ 9:57 pm BST Aug 19,2015 |
Cássio
There are various strategies that people have used in the past, but possibly the best one – given that this is a very small hint – is to create an “sql patch” using the dbms_sqldiag_internal package. Although it’s an internal package it’s use has been described on the general Oracle blog at this URL: https://blogs.oracle.com/optimizer/post/using-sql-patch-to-add-hints-to-a-packaged-application
Updated comment: this is now official in the dbms_sqldiag package; see: https://jonathanlewis.wordpress.com/2017/06/12/dbms_sqldiag/
Comment by Jonathan Lewis — August 22, 2015 @ 9:53 am BST Aug 22,2015 |
[…] where it had the full projection of all the columns in the two tables. (This reminded me of an old bug in ANSI select expansion that was fixed in 12cR2. It made me wonder if this was a code path where the same bug had been […]
Pingback by Join View delete | Oracle Scratchpad — May 31, 2022 @ 5:39 pm BST May 31,2022 |