14 Replies Latest reply on Mar 15, 2017 10:41 AM by 3013837

    Select list column's size different between Oracle 11 vs 12

    3412273

      Hi,

       

      I have two tables :

      table T1 has one column C1  CHAR (30)

      table T2 has one column D1  CHAR (50)

       

      One joint select where 2 columns have different size :

      select  DISTINCT T1.C1 from T1, T2 where T1.C1 = T2.D1;

       

      In my C language application, the buffer to bind the selected columns is

      char myBuffer [31];          /*  The sizeof C1  add 1 for null terminated string */

       

      With Oracle 11, the application is OK

      With Oracle 12, the application is failed due to data overflow.

       

      If myBuffer size is increased to

      char myBuffer [51]       /* the sizeof D1 add 1 */

      => With Oracle 12, the application is also OK.

       

      My question:

      Is there an Oracle parameter or environment variable,  to set the selected column size to the smaller one of the joint (30)

      instead of the bigger (50), to avoid the source modification which is increasing the buffer size ?

       

      Thank you in advance

        • 1. Re: Select list column's size different between Oracle 11 vs 12
          John Thorton

          use of CHAR datatype should be avoided for this exact reason.

          • 2. Re: Select list column's size different between Oracle 11 vs 12
            John Stegeman

            Is the character set different between 11 and 12?

            • 3. Re: Select list column's size different between Oracle 11 vs 12
              Jonathan Lewis

              Check the execution paths. It's possible that 12c has managed to use table elimination to remove your table T1 from the query forcing it to use T2.D1 as a substitute for T1.C1 in the select list. This could only be the case if T1.C1 was the primary key (or a NOT NULL unique key) for T1 and D1 was the corresponding foreign key on T2.

               

              If that is the case then you'd have to ask why the PK and FK have non-matching data type.

               

              If this is the problem then you can block join elimination by parameter (_optimizer_join_elimination_enabled = false) or by hint ( no_eliminate_join (t1))

               

              Regards

              Jonathan Lewis

               

              UDPATE: Is this 12.2 or 12.1 ? If the former then it's worth nothing that 12.2 can do join elimination on a multi-column primary key, so the scenario I describe above could appear in 12.2 where it couldn't appear in 12.1 or 11.2.

              • 4. Re: Select list column's size different between Oracle 11 vs 12
                ddf_dba

                3412273 wrote:

                 

                Hi,

                 

                I have two tables :

                table T1 has one column C1 CHAR (30)

                table T2 has one column D1 CHAR (50)

                 

                One joint select where 2 columns have different size :

                select T1.C1 from T1, T2 where T1.C1 = T2.D1;

                 

                In my C language application, the buffer to bind the selected columns is

                char myBuffer [31]; /* The sizeof C1 add 1 for null terminated string */

                 

                With Oracle 11, the application is OK

                With Oracle 12, the application is failed due to data overflow.

                 

                If myBuffer size is increased to

                char myBuffer [51] /* the sizeof D1 add 1 */

                => With Oracle 12, the application is also OK.

                 

                My question:

                Is there an Oracle parameter or environment variable, to set the selected column size to the smaller one of the joint (30)

                instead of the bigger (50), to avoid the source modification which is increasing the buffer size ?

                 

                Thank you in advance

                You really don't want to do that especially with CHAR columns as there could be data past the 30 character limit in the 50 character field.  Even with varchar2(50) columns the entire column could be populated with printable characters and you would then be trimming data out of that column, data your application or users might need.

                 

                The best design decision to make between these two tables is to change the definition in both T1 to this:

                 

                C1     VARCHAR2(50)

                 

                and the definition in T2 to this:

                 

                D1      VARCHAR2(50)

                 

                Doing so will actually conserve space in your tables (the columns won't be 'padded' to length with blanks).  That being said you're also better off designing this application to use the largest of the available column definitions since 30 characters will fit in a 50-character space and, as you've discovered on your own, 50 characters won't fit in the 30-character space.

                 

                 

                David Fitzjarrell

                • 5. Re: Select list column's size different between Oracle 11 vs 12
                  rp0428

                  My question:

                  Is there an Oracle parameter or environment variable, to set the selected column size to the smaller one of the joint (30)

                  instead of the bigger (50), to avoid the source modification which is increasing the buffer size ?

                   

                  There is likely a difference in the two databases. Maybe one is using BYTE length semantics and the other using CHAR.

                   

                  Testing that just on 12c v1 shows proper behaviour

                  create table t3 as

                  select T1.C1 from T1, T2 where T1.C1 = T2.D1;

                   

                  desc t3

                  Table T3 created.

                   

                  Name Null Type    

                  ---- ---- --------

                  C1        CHAR(30)

                   

                  create table t4 as

                  select T2.D1 from T1, T2 where T1.C1 = T2.D1;

                   

                  desc t4

                   

                  Table T4 created.

                   

                  Name Null Type    

                  ---- ---- --------

                  D1        CHAR(50)

                   

                  • 6. Re: Select list column's size different between Oracle 11 vs 12
                    3412273

                    Thank you very much for yours replies.

                     

                    I would like to add one precision : The application query is  "select DISTINCT  T1.C1 from T1, T2 where T1.C1 = T2.D1;"

                    yesterday I forgot the word "DISTINCT".

                     

                    Today I test without "DISTINCT" in the request, there is not problem with Oracle 12.1

                     

                    Additionnal informations :

                    The version is 12.1.0.2.0

                    There is not primary key in T1, nor foreign key in T2

                    NLS_LENGTH_SEMANTICS           :  BYTE

                    NLS_CHARACTERSET                   :  WE8ISO8859P15   (it was WE8MSWIN1252 on Oracle 11.2.0.3.0)

                    NLS_NCHAR_CHARACTERSET     :  AL16UTF16           (The same as on Oracle 11.2.0.3.0)

                     

                    Tests :

                    create table t3 as (select DISTINCT T1.C1 from T1, T2 where T1.C1 = T2.D1);

                    =>  ORA-12899: value too large for column ??? (actual : 50, maximum : 30)

                     

                    without DISTINCT

                    create table t4 as (select  T1.C1 from T1, T2 where T1.C1 = T2.D1);

                    => OK      desc t4 => type CHAR (30)

                     

                    create table t5 as (select DISTINCT T2.D1 from T1, T2 where T1.C1 = T2.D1);

                    => OK     desc t5 => type CHAR (50)

                     

                    create table t6 as (select T2.D1 from T1, T2 where T1.C1 = T2.D1);

                    => OK     desc t6 => type CHAR (50)

                     

                    Some people suggests a short term fix with optimizer_features_enable='11.2.0.4'

                    but I think like ddf_dba, we must change the two columns to VARCHAR (50)

                    We discuss with the customer next week.

                     

                    Thank you.

                     

                     

                    • 7. Re: Select list column's size different between Oracle 11 vs 12
                      Jonathan Lewis

                      The anomaly prompted me to write a brief note yesterday about an obvious case where something like this could happen (the problem I mentioned above), though it may have nothing to do with your particular example: https://jonathanlewis.wordpress.com/2017/03/09/join-elimination/

                       

                       

                      Regards

                      Jonathan Lewis

                      • 8. Re: Select list column's size different between Oracle 11 vs 12
                        Rob the Real Relic

                        Do you have the execution plans for each of the 'create table...' statements you listed?  It looks as though the distinct may be forcing the optimizer to choose an index access on T2.D1, while using the column definition for T1.C1

                        • 9. Re: Select list column's size different between Oracle 11 vs 12
                          3412273

                          Hi,

                           

                          Execution plan with "DISTINCT"

                           

                          SQL> EXPLAIN PLAN FOR

                            2  select DISTINCT T1.C1  FROM T1, T2 WHERE T1.C1=T2.D1;

                           

                          SQL> select plan_table_output

                            2  from table(dbms_xplan.display('plan_table',null,'basic'));

                           

                          --------------------------------------------------------

                          | Id  | Operation                             | Name   |

                          --------------------------------------------------------

                          |   0 | SELECT STATEMENT          |             |

                          |   1 |   HASH UNIQUE                   |             |

                          |   2 |     NESTED LOOPS SEMI    |              |

                          |   3 |        INDEX FULL SCAN       | T2_PK  |

                          |   4 |        INDEX RANGE SCAN   | T1_PK  |

                          -------------------------------------------------------

                           

                          Execution plan without "Distinct"

                           

                          There is not "HASH UNIQUE" operation .

                           

                          SQL> EXPLAIN PLAN FOR

                            2  select T1.C1  FROM T1, T2 WHERE T1.C1= T2.D1;

                           

                          SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'basic'));

                           

                          -----------------------------------------------------

                          | Id  | Operation                          | Name  |

                          ----------------------------------------------------

                          |   0 | SELECT STATEMENT      |             |

                          |   1 |   NESTED LOOPS            |             |

                          |   2 |     INDEX FULL SCAN      | T2_PK  |

                          |   3 |     INDEX RANGE SCAN  | T1_PK  |

                          ----------------------------------------------------

                           

                          Select request output :

                          SQL> select DISTINCT T1.C1 FROM T1, T2 WHERE T1.C1 = T2.D1 ;

                           

                          C1

                          --------------------------------------------------

                          51304830103

                           

                          SQL> select T1.C1 FROM T1, T2 WHERE T1.C1 = T2.D1 ;

                           

                          C1

                          ------------------------------

                          51304830103

                           

                          SQL>  select DISTINCT LENGTH(T1.C1) FROM T1, T2 WHERE T1.C1 = T2.D1 ;

                           

                          LENGTH(T1.C1)

                          ---------------------

                                                50

                           

                           

                          SQL>  select  LENGTH(S.IDEXT_CLIENTS) FROM T1, T2 WHERE T1.C1 = T2.D1 ;

                           

                          LENGTH(T1.C1)

                          ---------------------

                                                30

                           

                          Thank you.

                          • 10. Re: Select list column's size different between Oracle 11 vs 12
                            3013837

                            Hi all,

                             

                            I work with the OP and we have both been tackling this. Thanks a lot to all people who have answered.

                             

                            So the "culprit" is actually a partial join. Adding the hint  /*+ no_eliminate_join(t1) */ as suggested by Jonathan did not work but gave us the idea. Adding the hint  /*+ no_partial_join(t1) */ solves this specific problem in a unit test by reverting to Oracle 11 behavior.

                             

                            This being said and done, we still do not know how we are going to solve this, since it happens in a very generic database abstraction layer that can accept any SQL request, and finding all the (dynamically built) request that could be impacted by the new optimizer parameters is just impossible. It was not a join elimination in this case, but a join elimination could also happen.

                             

                            I do not feel confident forcing the optimizer_features_enable parameter. Any ideas welcome.

                             

                            Sincerely,

                            JGA

                            • 11. Re: Select list column's size different between Oracle 11 vs 12
                              John Stegeman

                              There's a hidden parameter Oracle Support could tell you about - you can read about it here: https://blog.dbi-services.com/partial-join-evaluation-in-oracle-12c/

                               

                              But, don't use hidden parameters without Support's blessing.

                              • 12. Re: Select list column's size different between Oracle 11 vs 12
                                Jonathan Lewis

                                JGA,

                                 

                                Thanks for giving us the feedback.

                                As John Stegeman says, as is often the case there's a hidden parameter to disable the feature pending a code fix by Oracle and Franck Pachot's article names it.

                                 

                                Regards

                                Jonathan Lewis

                                • 13. Re: Select list column's size different between Oracle 11 vs 12
                                  3013837

                                  Thanks John and Jonathan.

                                   

                                  My problem with disabling _optimizer_whatever (even with Support's blessings) is that I just can not possibly know all the different ones I need to enable/disable.

                                   

                                  So far I have identified at least two (for partial joins and for table elimination) but I can also see other (like 'enhanced' join elimination) added in Oracle 12, and from what you wrote, actually table join elimination could have already happened in Oracle 11. We could currently have this unwanted behavior and just not know it because the resulting memory shenanigans did not provoke any error that we detected.

                                   

                                  I am a tad surprised that if I ask explicitely for SELECT mytable.mycolumn WHERE (whatever) I can get another size than mytable.mycolumn just because the optimizer decided to use something else I never asked for.

                                   

                                  Once we have sorted this out I shall post the solution/workaround we chose, but if you see other options I shall be checking this thread regularly.

                                  Sincerely,

                                  JGA

                                  • 14. Re: Select list column's size different between Oracle 11 vs 12
                                    3013837

                                    Just for the record : changing the join columns from CHAR to VARCHAR2 yields the exact same results. We still have 50 stored in the SQLDAWORKP->L Pro*C structure.

                                    Which is logical, because the buffer size should always be the maximum allowed in the column, whether CHAR or VARCHAR2, not the max size actually stored in the zillions of rows not even yet read.