varchar2 always inline? [message #268425] |
Tue, 18 September 2007 09:52 |
smartin
Messages: 1803 Registered: March 2005 Location: Jacksonville, Florida
|
Senior Member |
|
|
In Oracle, is a varchar2 column always stored inline (inside the same row/block as the rest of the table data) regardless of how large the varchar column is?
I've been trying to find Oracle Documentation "proof" of this one way or another, and can't seem to find it. I've tried concepts, sql reference, and administrator; am I just overlooking it? Are varchar2's always inline, which is why I can't find any info?
I seem to remember that if you declared a varchar to be of large enough length, that it (or part of it) would be stored out of line. But I'm not sure if I'm confusing Oracle with another database vendor or not.
Of course, CLOBS can be stored either way, based on your definition and based on the data size stored in them.
|
|
|
|
|
Re: varchar2 always inline? [message #268575 is a reply to message #268425] |
Wed, 19 September 2007 02:15 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I think you'll have to accept proof by exception - all the things that we know can be stored out of line (LOBS and nested tables) have that fact mentioned in the documentation. Anything else I guess must be assumed to be inline.
|
|
|
|
|
|
|
Re: varchar2 always inline? [message #268732 is a reply to message #268425] |
Wed, 19 September 2007 08:52 |
smartin
Messages: 1803 Registered: March 2005 Location: Jacksonville, Florida
|
Senior Member |
|
|
Thanks guys, interesting responses. I must have been thinking about another database vendor (or else imagining things).
Also, if it wouldn't be stored inline, it raises the question: where in fact would it actually be stored? Which adds more weight to the assumption that it is stored inline, because all of the out of line methods, IOT's etc, let you specify where.
In following the link to J Lewis, and the 2k blocksize suggested by JRowbottom, you can indeed successfully run:
MYDBA@orcl > create table big_col(a varchar2(4000)) tablespace test_2k;
Table created.
MYDBA@orcl > insert into big_col values (rpad('x',4000));
1 row created.
MYDBA@orcl > commit;
Commit complete.
MYDBA@orcl > analyze table big_col compute statistics;
Table analyzed.
MYDBA@orcl > select chain_cnt from user_tables where table_name = 'BIG_COL';
CHAIN_CNT
----------
1
1 row selected.
I haven't gotten into block dumping yet to get specifics...
Don't forget when trying this at home (I did; but it is clearly documented) that before you create a 2k blocksize tablespace, you need a 2k buffer cache, and even with sga_target set, indicating "automatic", the non-standard blocksizes are not covered and must be set manually.
Interesting in the J Lewis note is the length byte, per column, going as high as 3, which is getting up there as far as overhead is concerned. Another reason to not declare one way larger than needed. Next to research is when it goes past 1.
Also interesting is the chaining WITHIN a block. It would be interesting to see if this still happens. Because of course I've alwas heard and read of chaining happening BETWEEN blocks.
|
|
|