| does varchar2 actually take space [message #18484] |
Fri, 01 February 2002 10:14  |
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 #18491 is a reply to message #18484] |
Fri, 01 February 2002 10:37  |
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 |
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';
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
