Database we use is being migrated from Greenplum to Oracle | Performance Difference

[avinashginjupal]

One of the database which we use is migrated from Greenplum to Oracle  

So our team is doing some performance test by pulling same data from Oracle and Green Plum (1000 Records Table) to SAS where table coming from oracle DB is getting created by occupying 600 - 800 MB of size where as table coming from Green Plum is getting created by occupying 300MB of space under SAS work area.

And also date coloumn is getting created along with timestamp in table that was created from Oracle Database (tnsnames.ora) and same issue is not there on Green Plum (ODBC Connection).

Is there any setting that need to be done to avoid this or is it expected?

[LinusH]

If the table structures in the source are "identical", it probably comes down to how SAS/ACCESS maps data types by default. Check the documentation for each SA/ACCESS engine. One way of handling is using the DBSASTYPE option. 

You don't have any different encoding? (DBCS issues)

For the date/timestamp part, can you describe the issue in more detail, with examples?

[avinashginjupal · Accepted solution]

I can think of 2 possibilities here.

• It’s possible that character fields have different length specifications in the 2 databases, causing one SAS dataset to occupy more space than the other.
  1. Note that Oracle has some data types that are not supported in Greenplum. The ones I think could affect the resulting SAS dataset size are Oracle LONG, CLOB, LONG RAW, and BLOB RAW.  If the Oracle DB has any columns specified with these types, they could result in SAS Character fields up to 32K long.
• It’s possible that one of the SAS datasets is being compressed using SAS compression but the other is not.

Root Cause :

The issue is with CLOB datatype, apparently in Greenplum the CLOB size is lesser compared to Oracle, the difference in file size is seen only for tables that have this CLOB datatype and all other are same,