Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

March 31, 2007

Oracle Native Types Part 2

Filed under: Oracle How To — H.Tonguç Yılmaz @ 8:30 pm

The decision of choosing which datatype you will use for your needs will result with more or less flexibility and performance of your database application. This series discusses the native data types of Oracle.

In Part 1 we mentioned;

1- ANYTYPE, ANYDATA and ANYDATASET
2- Binary_double vs. Number in compute intensive processes
3- XMLType and XDB

and on how to give data types to columns with create table as select statement.

4- String data types with Oracle

There are two dominant string native data types, CHAR and VARCHAR2 in Oracle. The CHAR datatype stores fixed-length character strings, and Oracle compares CHAR values using blank-padded comparison semantics. Where as the VARCHAR2 datatype stores variable-length character strings, and Oracle compares VARCHAR2 values using nonpadded comparison semantics.

This variable-length behavior of VARCHAR2 datatype has a pitfall when you want to enlarge lets say a VARCHAR2(80) columns value from 40 characters to 70, and there is no available space left on the Oracle block that row is stored. This concept is called Row Chaining and Migrating. When a row is chained or migrated, since Oracle must scan more than one data block to retrieve the information for the row I/O increases and responce time increases. You can detect migrated or chained rows by checking the number of table fetch continued row statistic in V$SYSSTAT and CHAIN_COUNT columns value on user_tables view. A small number of chained rows(for example less than 1%) is unlikely to impact system performance. However, a large percentage of chained rows can affect read performance since you have to do much more I/O than you should.

There is a second performance problem for VARCHAR2 values, since the datatype has flexibilities to store variable-length values for example lets say “why not storing a “human name” :) on a varchar2(4000) column, what can be the impact since varchar2(40) is already enough for this need?” First of all there some limitations like;


drop table t purge ;
create table t ( a varchar2(4000), b varchar2(4000) );
create index t_idx_ab on t(a, b);

create index t_idx_ab on t(a, b)
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded

And here is an example of programmatic pitfalls caused by some data processing methods you may use;

Code listing 73 : Memory cost analysis of varchar(4000) column versus varchar(40)

Also be carefull when comparing or joining on the columns having these datatypes;


set serveroutput on
DECLARE
last_name1 VARCHAR2(10) := 'TONGUC';
last_name2     CHAR(10) := 'TONGUC';
BEGIN
IF last_name1 = last_name2 THEN
DBMS_OUTPUT.PUT_LINE ( '-' || last_name1 || '- is equal to -' || last_name2 || '-');
ELSE
DBMS_OUTPUT.PUT_LINE ( '-' || last_name1 || '- is NOT equal to -' || last_name2 || '-');
END IF;
END;
/
-TONGUC- is NOT equal to -TONGUC    -

PL/SQL procedure successfully completed.

DECLARE
last_name1 CHAR(6)  := 'TONGUC';
last_name2 CHAR(10) := 'TONGUC';
BEGIN
IF last_name1 = last_name2 THEN
DBMS_OUTPUT.PUT_LINE ( '-' || last_name1 || '- is equal to -' || last_name2 || '-');
ELSE
DBMS_OUTPUT.PUT_LINE ( '-' || last_name1 || '- is NOT equal to -' || last_name2 || '-');
END IF;
END;
/
-TONGUC- is equal to -TONGUC    -

PL/SQL procedure successfully completed.

Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release 10.2.0.1.0

Refences Used :
Related Asktom thread
How to install and use runstats package
show_space procedure on Asktom

2 Comments »


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: