Home » SQL & PL/SQL » SQL & PL/SQL » does varchar2 actually take space
does varchar2 actually take space [message #18484] Fri, 01 February 2002 10:14 Go to next message
novice
Messages: 5
Registered: February 2001
Junior Member
If I have a field varchar2(200) in a table, but the actual data length is less than 200. does ORACLE actually reserve 200 bytes for this field for each row? or Oracle will allocate space according its actual data length?

Thanks!
Re: does varchar2 actually take space [message #18485 is a reply to message #18484] Fri, 01 February 2002 10:21 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
no, it only uses as much as it needs for the contents of that field. One additional byte per additional character.
Re: does varchar2 actually take space [message #18488 is a reply to message #18484] Fri, 01 February 2002 10:31 Go to previous messageGo to next message
Sanjay Bakshi OCP
Messages: 1
Registered: February 2002
Junior Member
Well, Var-Char stands for Variable Character so it should be clear that the space taken up varies depending upon the data. For Char datatype its fixed.
Re: does varchar2 actually take space [message #18491 is a reply to message #18484] Fri, 01 February 2002 10:37 Go to previous messageGo to next message
novice
Messages: 5
Registered: February 2001
Junior Member
Thanks very much!
But how Oracle implement this.using a pointer? if this is true, am I supposed to get worse performance than CHAR, if I create a index base on this varchar2 field?

The original reason I concern about this is: I have a big table(50M rows), I need to add a new varchar2(100) field into this table, but 99% of the rows are empty for this field. So If Oracle doesn't preallocate space for this field, I don't have to worry about the storage, otherwise,it's wasteful.

Another question is, how can I calucate the actual used size of a table?
thanks again!
Re: does varchar2 actually take space [message #18493 is a reply to message #18484] Fri, 01 February 2002 11:21 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
no, the index is a separate data structure and contanis 2 parts - the exact data indexed and the rowid where the corresponding data resides in the table. varchar2 does not impact the index performance in any way. It could be date, number or any other valid indexable datatype without impacting performance.

If your existing table alteady has data in it, then adding the new column and putting data in it could give you a performance problem. Usually when you create a table or index, Oracle reserves 10% (configurable) of the space in the data blocks to allow the records in that block to grow in length when you update them. Once that 10% is used, the row gets migrated to a new block. You index still points to it in the original block, but when Oracle gets there to retrieve it, it finds it's moved. That causes lots of extra i/o (which is slow). It's referred to as row chaining. To find out if row chaining has taken place, the avg length of each row (based on the data in it) and the number of rows, run the following:
analyze table ABC estimate statistics;
Instead of estimate, you van use compute too for a more accurate stats (but it'll take longer).
To rectify the chained rows, use the move command to "rebuild" the table. Assuming your table ABC is in USERS tablespace to start with:
alter table ABC move tablespace USERS;
You can also change the initial, next, pctfree etc if you want. Because your table has been re-orged now, you need to rebuild the indexes on it as they will be invalid.
Alter index ABC_ind1 rebuild tablespace MY_TS;

select table_name, num_rows, avg_row_len, chain_cnt, last_analyzed
from user_tables where table_name = 'ABC';
Previous Topic: please help: pass string into sp
Next Topic: group by purgatory
Goto Forum:
  


Current Time: Thu Jul 02 11:00:40 CDT 2020