Re: : RE: bytes vs chars

From: Dave Morgan <oracle_at_1001111.com>
Date: Tue, 15 Mar 2016 12:13:28 -0600
Message-ID: <56E850C8.90708_at_1001111.com>

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_at_1001111.com
403 399 2442



-- 
Dave Morgan
Senior Consultant, 1001111 Alberta Limited
dave.morgan_at_1001111.com
403 399 2442
--
http://www.freelists.org/webpage/oracle-l

Received on Tue Mar 15 2016 - 19:13:28 CET