That is, in case of VARCHAR2(4000) and VARCHAR2(16) columns, if we store less then 16 bytes data in these two columns then same amount of space will be allocated, and performance should be the same. But, have you ever tested it? I got a funny example http://hrivera99.blogspot.com/2008/05/why-is-varchar2-oversizing-bad.html here. There it is said performance problem but in reality there is not. In the example it is shown problem in physical reads but I don't agree with the example. In fact in the first example it is cached data and hence physical reads is reduced.
In the following section I simulate same example and see no performance differences. However there may rise, http://arjudba.blogspot.com/2008/09/ora-01450-maximum-key-length-3215.html while creating index in case of bigger VARCHAR2 length.
The most misleading example can be created by omitting
"
ALTER TABLESPACE EXAMPLE OFFLINE;
ALTER TABLESPACE EXAMPLE ONLINE;"
If you omit this step you may get different result as data become cached. And you need to take tablespace offline in order to get most accurate result as offlining a tablespace uncache of corresponding tablespace data.
Step 1)Create varchar2_length_test table with VARCHAR2(4000) and insert data into it.
SQL> create table varchar2_length_test( 2 ID NUMBER, 3 COL2 VARCHAR2(4000), 4 COL3 VARCHAR2(4000), 5 COL4 VARCHAR2(4000), 6 COL5 VARCHAR2(4000), 7 COL6 VARCHAR2(4000), 8 COL7 VARCHAR2(4000), 9 COL8 VARCHAR2(4000), 10 COL9 VARCHAR2(4000), 11 COL10 VARCHAR2(4000), 12 COL11 VARCHAR2(4000), 13 COL12 VARCHAR2(4000), 14 COL13 VARCHAR2(4000)) TABLESPACE EXAMPLE; Table created. SQL> SQL> begin 2 for i in 1 .. 100000 3 LOOP 4 INSERT into varchar2_length_test VALUES( 5 i, i||'Col2',i||'Col3',i||'Col4',i||'Col5',i||'Col6',i||'Col7',i||'Col8',i||'Col9',i||'Col10',i||'Col11',i||'Col12', 6 i||'Col13'); 7 END LOOP; 8 END; 9 / PL/SQL procedure successfully completed.
Step 2)Create varchar2_length_test_short table with VARCHAR2(16) and insert data into it.
SQL> create table varchar2_length_test_short( 2 ID NUMBER, 3 COL2 VARCHAR2(16), 4 COL3 VARCHAR2(16), 5 COL4 VARCHAR2(16), 6 COL5 VARCHAR2(16), 7 COL6 VARCHAR2(16), 8 COL7 VARCHAR2(16), 9 COL8 VARCHAR2(16), 10 COL9 VARCHAR2(16), 11 COL10 VARCHAR2(16), 12 COL11 VARCHAR2(16), 13 COL12 VARCHAR2(16), 14 COL13 VARCHAR2(16)) TABLESPACE EXAMPLE; Table created. SQL> begin 2 for i in 1 .. 100000 3 LOOP 4 INSERT into varchar2_length_test_short VALUES( 5 i, i||'Col2',i||'Col3',i||'Col4',i||'Col5',i||'Col6',i||'Col7',i||'Col8',i||'Col9',i||'Col10',i||'Col11',i||'Col12', 6 i||'Col13'); 7 END LOOP; 8 END; 9 / PL/SQL procedure successfully completed.
Step 3)Clear caching in the tablespace.
SQL> ALTER TABLESPACE EXAMPLE OFFLINE; Tablespace altered. SQL> ALTER TABLESPACE EXAMPLE ONLINE; Tablespace altered.
Step 4)Enable tracing and look at statistics
SQL> SET AUTOT TRACE SQL> select count(*) from varchar2_length_test; 1 row selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1500664439 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 418 (2)| 00:00:06 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| VARCHAR2_LENGTH_TEST | 88364 | 418 (2)| 00:00:06 | ----------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 29 recursive calls 1 db block gets 1980 consistent gets 1912 physical reads 176 redo size 411 bytes sent via SQL*Net to client 396 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> ALTER TABLESPACE EXAMPLE OFFLINE; Tablespace altered. SQL> SQL> ALTER TABLESPACE EXAMPLE ONLINE; Tablespace altered. SQL> select count(*) from varchar2_length_test_short; 1 row selected. Execution Plan ---------------------------------------------------------- Plan hash value: 161270611 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 418 (2)| 00:00:06 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| VARCHAR2_LENGTH_TEST_SHORT | 109K| 418 (2)| 00:00:06 | ----------------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 29 recursive calls 1 db block gets 1993 consistent gets 1912 physical reads 176 redo size 411 bytes sent via SQL*Net to client 396 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
So we see in both VARCHAR2(4000) and VARCHAR2(16) almost same consistent gets and physical reads. So oversize of varchar2 does not cause performance problem issue but lead to other problems.
Related Documents
