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

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

Is the character set different between 11 and 12?

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.

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

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)

 

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

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

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

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.

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

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

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.