Thanks for this, both because it demonstrates things very nicely and because
it reminds us that it does not matter if a wrong argument against something
is debunked if other reasons against that something are valid. (I hope that
is not too hard to parse.)
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Jonathan Lewis
Sent: Tuesday, March 15, 2016 2:40 PM
To: Oracle-l
Subject: RE: : RE: bytes vs chars
No, Neil's comment is based on the fact that if the definition of the table
ALLOWS a row to be larger than a single block then the run-time engines
plays safe and assumes it will have to process a row that IS larger than a
single block and therefore uses single row processing to handle what would
otherwise be array processing methods.
Try this:
====
drop table t1 purge;
create table t1 (
v1 varchar2(4000),
v2 varchar2(4000)
);
insert into t1 values('x','x');
commit;
execute dbms_stats.gather_table_stats(user,'t1');
execute snap_redo.start_snap
insert into t1
select object_type, object_name
from all_objects
where rownum <= 1000
;
commit;
execute snap_redo.end_snap
====
(The snap_redo package is something that let's me take snapshots of my
session stats restricted to redo - you may have to use Tanel Poder's
Snapper, or some other method to find the redo generated).
Repeat the experiment with varchar2(40).
My figures for redo entries and redo size:
varchar2(4000)
--------------
redo entries 924
redo size 268,412
varchar2(40)
------------
redo entries 1
redo size 36,304
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on
behalf of Dave Morgan [oracle@xxxxxxxxxxx]
Sent: 15 March 2016 18:13
To: Neil Chandler; Oracle-l
Subject: Re: : RE: bytes vs chars
On 03/15/2016 10:57 AM, Neil Chandler wrote:
Your case seems to be "People aren't very good at data modelling so letsjust over-allocate everything and not worry about it."
performance impacts in relation to being unable to do set-based operations
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