NUMBER

(no explicit precision)

INTEGER, NUMERIC or FLOAT

In Oracle, the NUMBER data type with no explicit precision stores each number N as an integer M, together with a scale S. The scale can range from -84 to 127, while the precision of M is limited to 38 digits. So N = M * 10^S.

When precision is specified, precision/scale applies to all entries in the column. If omitted, the scale defaults to 0.

For the common case where Oracle’s NUMBER with no explicit precision data type is used to store only integer values, INTEGER is the best suited and the fastest Vertica data type. However, INTEGER (the same as BIGINT) is limited to a little less than 19 digits, with a scale of 0; if the Oracle column contains integer values outside of the range [-9223372036854775807, +9223372036854775807], use the Vertica data type NUMERIC(p,0) where p is the maximum number of digits required to represent the values of N.

Even though no explicit scale is specified for an Oracle NUMBER column, Oracle allows non-integer values, each with its own scale. If the data stored in the column is approximate, Vertica recommends using the Vertica data type FLOAT, which is standard IEEE floating point, like ORACLE BINARY_DOUBLE. If the data is exact with fractional places, for example dollar amounts, Vertica recommends NUMERIC(p,s) where p is the precision (total number of digits) and s is the maximum scale (number of decimal places).

Vertica conforms to standard SQL, which requires that p >= s and s >= 0. Vertica's NUMERIC data type is most effective for p=18, and increasingly expensive for p=37, 58, 67, etc., where p <= 1024.

Vertica recommends against using the data type NUMERIC(38,s) as a default "failsafe" mapping to guarantee no loss of precision. NUMERIC(18,s) is better, and INTEGER or FLOAT are better yet, if one of these data types will do the job.

NUMBER (P,0),

P <= 18

INTEGER

In Oracle, when precision is specified the precision/scale applies to all entries in the column. If omitted the scale defaults to 0. For the Oracle NUMBER data type with 0 scale, and a precision less than or equal to 18, use INTEGER in Vertica.

NUMBER (P,0),

P > 18

NUMERIC (p,0)

An Oracle column precision greater than 18 is often more than an application really needs.

If all values in the Oracle column are within the INT range

[-9223372036854775807,+9223372036854775807], use INTEGER for best performance. Otherwise, use the Vertica data type NUMERIC(p, 0), where p = P.

NUMBER (P,S)

all cases other than previous 3 rows

NUMERIC (p,s)

or FLOAT

When P >= S and S >= 0, use p = P and s = S, unless the data allows reducing P or using FLOAT as discussed above.

If S > P, use p = S, s = S. If S < 0, use p = P – S, s = 0.

NUMERIC (P,S)

See notes -->

Rarely used in Oracle. See notes for the NUMBER type.

DECIMAL (P,S)

See notes -->

DECIMAL is a synonym for NUMERIC. See notes for the NUMBER type.

BINARY_FLOAT

FLOAT

Same as FLOAT(53) or DOUBLE PRECISION.

BINARY_DOUBLE

FLOAT

Same as FLOAT(53) or DOUBLE PRECISION.

RAW

VARBINARY(RAW)

The maximum size of RAW in Oracle is 2,000 bytes.

The maximum size of CHAR/BINARY in Vertica is 65000 bytes.

In Vertica, RAW is a synonym for VARBINARY.

LONG RAW

VARBINARY(RAW)

The maximum size of Oracle’s LONG RAW is 2GB.

The maximum size of Vertica’s VARBINARY is 65000 bytes. Vertica users should exercise caution to avoid truncation during data migration from Oracle.

CHAR(n)

CHAR(n)

The maximum size of CHAR in Oracle is 2,000 bytes.

The maximum size of CHAR in Vertica is 65000 bytes.

NCHAR(n)

CHAR(n*3)

Vertica supports national characters with CHAR(n) as variable-length UTF8-encoded UNICODE character string. UTF-8 represents ASCII in 1 byte, most European characters in 2 bytes, and most oriental and Middle Eastern characters in 3 bytes.

VARCHAR2(n)

VARCHAR(n)

The maximum size of VARCHAR2 in Oracle is 4,000 bytes.

The maximum size of VARCHAR in Vertica is 65000.

Note: The behavior of Oracle’s VARCHAR2 and Vertica’s VARCHAR is semantically different. Vertica’s VARCHAR exhibits standard SQL behavior, whereas Oracle’s VARCHAR2 is not completely consistent with standard behavior – it treats an empty string as NULL value and uses non-padded comparison if one operand is VARCHAR2.

NVARCHAR2 (n)

VARCHAR(n*3)

See notes for NCHAR().

DATE

TIMESTAMP or

possibly DATE

Oracle’s DATE is different from the SQL standard DATE data type implemented by Vertica. Oracle’s DATE includes the time (no fractional seconds), while Vertica DATE type includes only date per SQL specification.

TIMESTAMP

TIMESTAMP

TIMESTAMP defaults to six places, that is, to microseconds

TIMESTAMP WITH

TIME ZONE

TIMESTAMP WITH

TIME ZONE

TIME ZONE defaults to the currently SET or system time zone.

INTERVAL YEAR

TO MONTH

INTERVAL YEAR

TO MONTH

Per the SQL standard, INTERVAL can be qualified with YEAR TO MONTH sub-type in Vertica.

INTERVAL DAY

TO SECOND

INTERVAL DAY

TO SECOND

In Vertica, DAY TO SECOND is the default sub-type for INTERVAL.

CLOB, BLOB

LONG VARCHAR, LONG VARBINARY

You can store a CLOB (character large object) or BLOB (binary large object) value in a table or in an external location. The maximum size of a CLOB or BLOB is 128 TB.

You can store Vertica LONG data types only in LONG VARCHAR and LONG VARBINARY columns. The maximum size of the LONG data types is 32,000,000 bytes.

LONG, LONG RAW

LONG VARCHAR, LONG VARBINARY

Oracle recommends using CLOB and BLOB data types instead of LONG and LONG RAW data types.

In Oracle, a table can contain only one LONG column, The maximum size of a LONG or LONG RAW data type is 2 GB.