Re: : RE: bytes vs chars
- From: Dave Morgan <oracle@xxxxxxxxxxx>
- To: Neil Chandler <neil_chandler@xxxxxxxxxxx>, Oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 15 Mar 2016 12:13:28 -0600
On 03/15/2016 10:57 AM, Neil Chandler wrote:
Your case seems to be "People aren't very good at data modelling so lets just
over-allocate everything and not worry about it."
I don't agree with that.
Your assertion "This means there is no negative operational impact due to overly
large VARCHAR2 columns." is false. There are very real performance impacts in
relation to being unable to do set-based
operations efficiently.
Your argument seems to be based on the claim multiple blocks are used because
of the table definition. This is not the case.
Space for VARCHAR2s is not allocated until the data is inserted.
SQL> create table testsize (
2 col1 varchar2(4000),
3 col2 varchar2(4000),
4 col3 varchar2(4000)
5 );
Table created.
SQL> select blocks from user_extents where segment_name='TESTSIZE';
BLOCKS
----------
8
SQL> create table testsize1 (
2 col1 varchar2(40),
3 col2 varchar2(40),
4 col3 varchar2(40)
5 );
Table created.
SQL> select blocks from user_extents where segment_name='TESTSIZE1';
BLOCKS
----------
8
SQL> insert into testsize values('a', 'b','c');
1 row created.
SQL> insert into testsize1 values('a', 'b','c');
1 row created.
SQL> commit;
Commit complete.
SQL> select blocks from user_extents where segment_name='TESTSIZE';
BLOCKS
----------
8
SQL> select blocks from user_extents where segment_name='TESTSIZE1';
BLOCKS
----------
8
SQL>
If the data is large enough that 1 row requires multiple blocks then it will
use multiple blocks.
But until that time there is NO negative impact. And if the data is that large
then you have no
choice but to swallow it. My wants and desires have nothing to do with it.
Dave
--
Dave Morgan
Senior Consultant, 1001111 Alberta Limited
dave.morgan@xxxxxxxxxxx
403 399 2442
--
Dave Morgan
Senior Consultant, 1001111 Alberta Limited
dave.morgan@xxxxxxxxxxx
403 399 2442
--
//www.freelists.org/webpage/oracle-l
Other related posts:
