Home » SQL & PL/SQL » SQL & PL/SQL » varchar2 always inline?
varchar2 always inline? [message #268425] Tue, 18 September 2007 09:52 Go to next message
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 #268427 is a reply to message #268425] Tue, 18 September 2007 09:54 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Well I have never heard of them being stored out of line, but obviously that's a long way from being proof Smile
Re: varchar2 always inline? [message #268431 is a reply to message #268425] Tue, 18 September 2007 10:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is always stored in the same block as the rest of the row as long as the row fits in only one block.
Otherwise it is chained as for any other column (datatype).

Regards
Michel
Re: varchar2 always inline? [message #268575 is a reply to message #268425] Wed, 19 September 2007 02:15 Go to previous messageGo to next message
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 #268596 is a reply to message #268575] Wed, 19 September 2007 02:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
But db_block_size can be set to 2048 (source)
Would that mean we are not able to create a varchar2(4000) column in a table in that tablespace?
Re: varchar2 always inline? [message #268603 is a reply to message #268596] Wed, 19 September 2007 02:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
No, that would just mean that it would be chained across multiple blocks.

After all, LONGS were always stored inline, and they could be up to 2Gb (if memory serves)

{fix typo}

[Updated on: Wed, 19 September 2007 02:50]

Report message to a moderator

Re: varchar2 always inline? [message #268615 is a reply to message #268603] Wed, 19 September 2007 03:02 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Aha, never knew that chaining could occur in the middle of columns.
Re: varchar2 always inline? [message #268617 is a reply to message #268615] Wed, 19 September 2007 03:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, to be honest, neither did I, but if you've got a 4000 byte fiels, and a 2k block, I guess it's got to.

Jonathon Lewis has a side note about this behaviour in 8i
Re: varchar2 always inline? [message #268732 is a reply to message #268425] Wed, 19 September 2007 08:52 Go to previous message
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.
Previous Topic: my procedure generating different files bt want it to generate single file
Next Topic: PL/SQL Records question
Goto Forum:
  


Current Time: Fri Dec 09 17:17:39 CST 2016

Total time taken to generate the page: 0.24778 seconds