Problem: database table/index sizing for Sum Length Columns greater than Available Data Space 
Author Message
 Problem: database table/index sizing for Sum Length Columns greater than Available Data Space

Hi there,

    I am trying to size Oracle 8.x tables and am having difficulty due to
the fact that the formulas Oracle gives are only appropriate if your Space
Used Per Row (i.e. Sum Length Columns + Overhead) is less than your
Available Data Space (i.e. Block_Size - Header Space - Pct_Free_Space).

    What if Space Used Per Row >= Available Data Space?  That is, what if
you have big columns which are much bigger than your block size and you
don't want to increase the block size?

    If you could point me to where there are formulas for this particular



Fri, 11 Jan 2002 03:00:00 GMT
 Problem: database table/index sizing for Sum Length Columns greater than Available Data Space

Sorry, but this is just the way Oracle works.
If your records are by design bigger than the available dataspace, you will
get chaining. If this is a structural problem in your database the only way
to resolve it is to increase the block size. There might be formulas, but
you definitely don't want the chaining, as you will be getting more than one
1 I/O to retrieve a record.

Hth,

Sybrand Bakker, Oracle DBA


Quote:
> Hi there,

>     I am trying to size Oracle 8.x tables and am having difficulty due to
> the fact that the formulas Oracle gives are only appropriate if your Space
> Used Per Row (i.e. Sum Length Columns + Overhead) is less than your
> Available Data Space (i.e. Block_Size - Header Space - Pct_Free_Space).

>     What if Space Used Per Row >= Available Data Space?  That is, what if
> you have big columns which are much bigger than your block size and you
> don't want to increase the block size?

>     If you could point me to where there are formulas for this particular




Fri, 11 Jan 2002 03:00:00 GMT
 Problem: database table/index sizing for Sum Length Columns greater than Available Data Space
Thanks for your quick response.  However, increasing block size is for the
entire database, which may not be such a good idea for small tables, right?
Whats the correlation with NT block size?

Regards,

Brendan



Fri, 11 Jan 2002 03:00:00 GMT
 Problem: database table/index sizing for Sum Length Columns greater than Available Data Space
Hi Brendan,

    I am almost certain (oops, place foot firmly in mouth) that
the default block size for NT for Oracle 7.3.x and 8.0.x is 2048.

    If you check your init<sid>.ora file you should find a
parameter:
"db_file_multiblock_read_count".  Multiply that value by your
block size and the result is the number of bytes Oracle is going
to read -- regardless of the size of your tables.

    Based on your large row problem you should set the blocksize
to the maximum that Oracle will support on NT and don't worry
about how much extra space your small tables will need.

    I understand your concern about wasting disk space but
consider;  if you change your database block size from 2K to 16K
you won't lose that much disk space.  Assume 1000 tables that
each fit into a 2K block.  After you change to 16K your 1000
tables will require 16MB of disk space as compared to 2MB.

regards
Jerry Gitomer

Quote:

>Thanks for your quick response.  However, increasing block size
is for the
>entire database, which may not be such a good idea for small
tables, right?
>Whats the correlation with NT block size?

>Regards,

>Brendan



Sat, 12 Jan 2002 03:00:00 GMT
 Problem: database table/index sizing for Sum Length Columns greater than Available Data Space

Quote:

> Hi there,

>     I am trying to size Oracle 8.x tables and am having difficulty due to
> the fact that the formulas Oracle gives are only appropriate if your Space
> Used Per Row (i.e. Sum Length Columns + Overhead) is less than your
> Available Data Space (i.e. Block_Size - Header Space - Pct_Free_Space).

The max total size of indexed columns is "40%*db_block_size - some overhead".
In some version, the manual says "half of db_block_size - some overhead".

Quote:
>     What if Space Used Per Row >= Available Data Space?  That is, what if
> you have big columns which are much bigger than your block size and you
> don't want to increase the block size?

You will get an error message 'ORA-01450: maximum key length n exceeded'
when creating the index.
In general, such an index is impractical and poor-designed.
You should re-examine your database design.
Quote:
>     If you could point me to where there are formulas for this particular




Sat, 12 Jan 2002 03:00:00 GMT
 Problem: database table/index sizing for Sum Length Columns greater than Available Data Space
Thanks for your help - but I need help for tables, not indexes.

Quote:




Quote:
>> Hi there,

>>     I am trying to size Oracle 8.x tables and am having difficulty due to
>> the fact that the formulas Oracle gives are only appropriate if your
Space
>> Used Per Row (i.e. Sum Length Columns + Overhead) is less than your
>> Available Data Space (i.e. Block_Size - Header Space - Pct_Free_Space).

>The max total size of indexed columns is "40%*db_block_size - some
overhead".
>In some version, the manual says "half of db_block_size - some overhead".

>>     What if Space Used Per Row >= Available Data Space?  That is, what if
>> you have big columns which are much bigger than your block size and you
>> don't want to increase the block size?

>You will get an error message 'ORA-01450: maximum key length n exceeded'
>when creating the index.
>In general, such an index is impractical and poor-designed.
>You should re-examine your database design.

>>     If you could point me to where there are formulas for this particular




Sat, 12 Jan 2002 03:00:00 GMT
 Problem: database table/index sizing for Sum Length Columns greater than Available Data Space
You have.  I run all my Oracle databases with 8k blocks (including NT).  I
don't have the problem of the original writer of this thead:  most of my
tables have rows less than 100 bytes.  I keep my
db_file_multiblock_read_count at 8.  There was a bug in Oracle 7.3.3 on HPUX
that would give ORA-00600 errors occasionally if
db_file_multiblock_read_count times your block size was greater than 64k.
With big, cheap disks and plenty of RAM, I haven't found a significant
reason to change my block size to something other than 8k.

-R. Nightingale.


Quote:
> Hi Brendan,

>     I am almost certain (oops, place foot firmly in mouth) that
> the default block size for NT for Oracle 7.3.x and 8.0.x is 2048.

>     If you check your init<sid>.ora file you should find a
> parameter:
> "db_file_multiblock_read_count".  Multiply that value by your
> block size and the result is the number of bytes Oracle is going
> to read -- regardless of the size of your tables.



Sat, 12 Jan 2002 03:00:00 GMT
 
 [ 7 post ] 

 Relevant Pages 

1. Adding columns to a table and adding length to columns

2. 10000 Length Sized Char Column

3. SQL SERVER Table size and Index size query

4. Table size estimates and column sizes

5. columns names,datatypes,length and indexes !

6. database/table size w/index backup

7. Query to return database, table and index size

8. database size broken down by tables and indices

9. alternate column names or column size through sql-create-table

10. Dropping column silently kills multi-coumn index (was [ODBC] Error when accessing tables with deleted columns)


 
Powered by phpBB® Forum Software