Printable View
Is still valid to say they should be at start of table definition? My developer has scripts with NOT NULLS at start and end of large tables and columns of 1000 and 500 chars in between.
What are the storage implications?
Also - precision with NUMBER columns. What's the implications of using NUMBER over NUMBER(8)?
Quote
---------
Also - precision with NUMBER columns. What's the implications of using NUMBER over NUMBER(8)?
----------
there is no difference between number and number(8) as far as storage is concerned .
by mentioning number(8), u are just restricting the the maximum number of digits for this column. adding a constraint .
oracle will shrink the storage usage based on the value itself .
u can check the number of bytes used using the following command .
SQL> select vsize ( 9999) from dual ;
VSIZE(9999)
-----------
3
SQL> select vsize ( -99 ) from dual ;
VSIZE(-99)
----------
3
SQL> select vsize ( 9.9 ) from dual ;
VSIZE(9.9)
----------
3
SQL> select vsize ( 99 ) from dual ;
VSIZE(99)
----------
2
for every two digits it takes 1 byte .
and extra byte for sign and extra byte for precision .
pk first
fk second
not null third
null, large (..) last
F.
quote from Mnemonical
pk first
fk second
not null third
null, large (..) last
shouldn't it be
pk first
fk second
not null third
large (..)
null last << since null takes 1 byte if precedes non-null column
Hm, what does position of FK have to do with space saving? What if FK column is NULLable? Would you still position it immediately after the PK and in front of other NOT NULL columns?Quote:
Originally posted by Mnemonical
pk first
fk second
not null third
null, large (..) last
So -I assume it's good practice to save nullable columns til the end of the table definitions, but WHY - explicitly? In words that a non-Oracle person might understand (the project manager, for instance).
It's quite simple. If row contains null value in a particular column, this column consumes 0 or 1 byte of space on disk. If a null-value column is the last one in a table or if all columns that follow it contain null values, then it consumes no space at all (0 bytes). But if any following columns contain non-null value, then oracle uses 1 byte to store that null value.
So -I assume it's good practice to save nullable columns til the end of the table definitions, but WHY - explicitly? In words that a non-Oracle person might understand (the project manager, for instance).
It's quite simple. If row contains null value in a particular column, this column consumes 0 or 1 byte of space on disk. If a null-value column is the last one in a table or if all columns that follow it contain null values, then it consumes no space at all (0 bytes). But if any following columns contain non-null value, then oracle uses 1 byte to store that null value.
To follow on to jmodic:
If you follow the NOT_NULL->NULLABLE pattern (PK's are not nullable by definition, a unique can be null but a PK can't and a foreign key only has to be unique in the parent table), then a given row in Oracle will take less space because Oracle doesn't store anything for null fields at the end of a row. This is because Oracle stores a 'map' of how many columns are stored for each row in a block. If a null is followed by a field with data, then the map is updated to show the columns.
This means less disk usage. It can also mean better performance because more rows are stored in a given block, meaning more data is returned in a given read (or written in a given write) and given rows can be found faster in memory than on disk.
As per my knowledge, the position of the columns should be based on reducing the application coding .
of course primary key can be set in the beigining of the table .
null , not null and foreign key positioning should be mainly determined by business requirements . there is no point in decreasing the front end processing at the cost of storage .
these days storage is not a major usage . GB and TBytes of space are mostly present .
any other views .
In few situations to use a null fk is not a poor strategy, then I positioned fk in second place. But of course, it might be in last if null, depending on the business requeriments.
F.
A couple of points:
This strategy, while well-known, is not of *major* concern, in that I wouldn't rank it in the top 20 items to be worried about. In 99.9% of the cases, the space savings are minimal, and the resulting time savings are far, far dwarfed by such things as proper design and optimal SQL.
Having said that, however, things like proper design do include space-saving practices, and saving space IS ALWAYS IMPORTANT!!! I cringe every time I hear someone make the remark that prakashs43 made. The point of saving space in a database is not because of the footprint of the database itself!!! Honestly, we all understand how cheap disk space is. The point is minimizing I/O. I/O is what causes all the speed problems in the database. And the database is what causes all the speed problems in the application. You must manage your row size so that more rows can be read for each I/O, so that less I/O is actually necessary. You manage your row size so that more rows can fit in the cache, thereby reducing your I/O still further. Managing row size is very important, in that a reduction in size of a row to 1/3rd the size can actually reduce the I/O necessary to 1/9th. 3 times as much fits in cache, meaning you are 1/3 as likely to need to read from disk. When you do, you can read 3 times as much with the same I/O which means you will likely need 1/3rd the I/O to read the same data. So, you will need to do I/O 1/3rd as many times because of the cache, and will only need to do 1/3rd as much I/O to read the data, meaning possibly 1/9th the I/O (check my math ;) )
So, why do I say that the whole NULL-able column position is not important? Because you are only saving a couple bytes per row, and only if all those columns are actually null for each row. If you have 10 NULL-able fields in a table, and 5 are only NULL 50% of the time and the other 5 are NULL maybe 75% of the time, the following math applies:
Chance to save that one byte by column (starting from back)
When added together, you get ~2.Code:Colx .75 =0.75
Colx-1 .75 * .75 =0.5625
Colx-2 .75 * .75 * .75 =0.2373046875
Colx-3 .75 * .75 * .75 * .75 =0.177978515625
Colx-4 .75 * .75 * .75 * .75 * .75 =0.13348388671875
Colx-5 .75 * .75 * .75 * .75 * .75 * .5 =0.066741943359375
Colx-6 .75 * .75 * .75 * .75 * .75 * .5 * .5 =0.0333709716796875
Colx-7 .75 * .75 * .75 * .75 * .75 * .5 * .5 * .5 =0.01668548583984375
Colx-8 .75 * .75 * .75 * .75 * .75 * .5 * .5 * .5 * .5 =0.008342742919921875
Colx-9 .75 * .75 * .75 * .75 * .75 * .5 * .5 * .5 * .5 * .5 =0.0041713714599609375
So, after all of that, you save an average of 2 bytes per row.
A far better way to save space is to create unique, numeric IDs for every table. There are many, many good reasons to do this - saving space is just one of them. But let's take a look at an example quick.
Let's say we have a firm with 1000 employees in an employee table. Knowing that, in the US, a person's SSN is unique, we might decide to use that field as the PK for the table. The problem is that this is a string value of length 9 (without the dashes). Now, that means that all the IDs for all the employees take up 9000 bytes. If we assume 5 child tables (with Emp_ID as FKs), with an average of 3 records per employee in each table, we now have 144,000 bytes. Of course, the emp table will have an index on the PK and each child will likely have an index on their corresponding FK, so we actually have 288,000 bytes used to hold the emp_ID in various tables. If, instead, we had created a new, numeric, sequence-populated field for the emp_id, it would only take 2 bytes to store up to the number 1000. So, our storage would drop to 2/9th the amount, or 64000 bytes total. We would have saved 224,000 bytes just by changing that single ID. Actually, since we added a field, we need to add back in the additional 2000 bytes for the new field, and the 9000 bytes for the ssn index (since it will still most likely be indexed). We therefore have a net savings of 213,000 bytes for a key that only had 1000 rows - that's 213 bytes per row - for changing a single column!
So, if one had a database full of large string keys and changed them all to numeric, sequence-filled keys, one could easily envision a database cut to 1/3 its original size. And, as explained before, this could make the database up to 9 times faster - just using this one, proven design practice - there are more.
Once you have a database that follows all the best design practices, then the positioning of NULL-able columns might buy you another 1-2%, if you're really lucky.
So again I say - yes, the theory is correct and, at times, helpful, but it's nowhere near the top of the list of things to worry about.
- Chris
[Edited by chrisrlong on 09-13-2002 at 04:38 PM]
A further consideration is that reading columns on the end of a wide table's rows takes more CPU usage than reading columns at the beginning. The difference can get very noticeable when you get up to 100+ columns. By "moving" a commonly used column to the start of the column list on a DW fact table with 130 columns I dropped the time required for a full table scan that summed on that col from 40 seconds to 16 seconds.
Just to add to chris' well-made points, if you squeeze in the rows by putting all your nulls at the end of the row, and do not save space for them using PCTFREE, then an update to a non null value is going to give you a row migration, isn't it? So you have to consider not only the chance of the columns being null, but also the chance of the null columns later changing to non-null. If such a situation is pervasive in a table then you will want to reserve the space for an update anyway, and won't save space by positioning nulls at the end.
chris calculations might be correct .
point to be noted is changing the validations at screen level ( cursor movement etc... ) may be still a costly issue ( particularly in web applications ) than saving small bytes at database level .
yeah i accept this saving mechanism for datawarehousing activities.
[Edited by prakashs43 on 09-18-2002 at 10:03 AM]
I absolutely agree. As far as physical column order is concerned, that is the defining aspect. I advocate putting the PK first, then the FKs, then numerics, then strings, in general. This is because the PK is the primary way of getting to a table and is probably used most often. The FKs are used to join out to other tables and are also used all the time. Note that I advocate that all IDs (except very special cases like State Codes) be numeric. This means that the table is still very thin at this point. Then the rest of the numeric fields, as numerics are more likely used in aggregate functions, and the table is still relatively thin. Then the string fields in decreasing usefulness. All this is irrespective of NULL-ability. There are, of course, exceptions to every rule, but this is the starting point I advocate. One wild card is usually the AK (Alternate Key). This field is usually used quite often, so should be towards the front of the list. However, if it is a big string, you should probably move it right after the numerics anyway.Quote:
Originally posted by slimdave
A further consideration is that reading columns on the end of a wide table's rows takes more CPU usage than reading columns at the beginning...
Excellent point - I had missed this completely in my arguments. I hope you don't mind if I appropriate this point for my own future uses :D.Quote:
Originally posted by slimdave
Just to add to chris' well-made points, if you squeeze in the rows by putting all your nulls at the end of the row, and do not save space for them using PCTFREE, then an update to a non null value is going to give you a row migration, isn't it? So you have to consider not only the chance of the columns being null, but also the chance of the null columns later changing to non-null. If such a situation is pervasive in a table then you will want to reserve the space for an update anyway, and won't save space by positioning nulls at the end.
I'm afraid I must disagree with this point. As I alluded to in my original post, there are many reasons for assigning abstracted / surrogate / single-field-numeric-sequence-filled keys to every table. Saving space is just one of them. And, as I tried to show, it is not simply a few bytes that are saved - it is a whole lot of space that is saved. And the validation argument doesn't hold up, I'm afraid. Well, not with 3rd generation languages, at any rate - I have no idea if it might be an issue with 4GL languages like Oracle Forms (in Powerbuilder, it would not have been a problem <- Only 4GL I've coded in ;) ). Most validation is a matter of picking from a list of possible values. These lists can just as easily hold 2 values - the display value and the ID. Even if you used string codes, this would be the case, since showing the user your internal codes is a very bad idea in any case. So, this only leaves us with free-form fields, such as an on-screen SSN box. Note that SSN is still in the table and still has a unique index. So validating the SSN and retrieving the PK has only had the smallest of impacts - one more logical read. Actually, another trick for AKs is to make the index (AK,PK), which would allow you to do lookups and translations within the index alone - without having to hit the table. ( not a general-use practice, but a handy tool when needed ).Quote:
Originally posted by prakashs43
...point to be noted is changing the validations at screen level ( cursor movement etc... ) may be still a costly issue ( particularly in web applications ) than saving small bytes at database level .
yeah i accept this saving mechanism for datawarehousing activities.
So anyway, I've rambled quite a bit here, but basically, I must humbly state that I don't buy that argument.
- Chris
Here's another consideration.
If you use data segment compression in 9iR2, you should put columns with few distinct values at the beginning of the list to maximize the benefits, and if possible you should implement physical row ordering on those columns. This improves the efficiency of the compression -- I tested this on a large fact table and the compression improved from 50% to 40%, giving me a welcome improvement (ie. increase) in rows-per-block and improved (ie. reduced) I/O.
It seems that there are so many other condiderations that the placing on nullable columns at the end might be way down the benefit-to-hassle list of potential improvements, except for clear cases of very long varchar2's with low probability of update.
i accept with what chris and slimdave is quoting . but why do u see design strategy just from oracle database perspective.
there is every possiblility that the performance of the application in 9iAS gets affected with the slightest increase of code. ( yes i am talking about 4GL's Forms , Reports )
bottom line is overall performance should increase whether u tune database or reduce application code.
I'll take that question ;)
Well, I'm not sure of the impacts in Forms for which you speak - From all the bad things I've heard about it, I guess it could be anything.
However, from a general perspective...
The database is the most important part of the application. (again, every rule has its exceptions, but this is a pretty solid assumption)
The current application I am working on, for example, is an internet application written in VB with Oracle on the back-end. We have tens of thousands of users. We have 14 hefty NT application servers. We have a 160 Gig database on an E 10000 Sun Server with 28 procs and an EMC storage array. This is some hefty stuff. I can pretty much guarantee you that if you took all of the database calls out of the code, and figured out a way to call every method of every object in the entire application, it would be done running in about 5 minutes. Compare that to some reports that run for hours. Code runs very, very, very fast, at least in most languages ;). Our application has ~140 Meg of compiled application code, compared to 160 Gig of database. If you added all the database calls back into the application and performed the same test, it would likely take several hours to run all the code.
As I said before, I/O is the fundamental cause of database performance problems and the database itself is the cause of application performance problems. 90+% of the speed for any application comes from the database, plain and simple. If not, then you either have a very small and/or seldom-used database or some terribly inefficient application code.
Therefore, if you want to make the application run as quickly as possible, you need to make the database run as quickly as possible. It's as simple as that.
- Chris
As a caveat...
It occurs to me that many people consider 'application code' to include SQL. I do not. That could be causing the confusion here. I most adamantly advocate that all SQL exist within procs in the database - the database abstraction layer. Therefore, when I say 'tuning the database', I am referring to the SQL as well. I further assume that all bulk operations are performed within this abstration layer. If, however, the application code does things like:
- asks for a large set of data directly through poorly-coded SQL
- then loops through the resultset
- and calls 10 other individual, poorly-coded SQL statements for each record
... then yes, tuning both the application code and SQL will result in massive performance improvements.
The optimal solution would be to move the entire operation within a stored procedure and collapse all 11 statements into 1, if possible. Once inside proc, it becomes part of the database, IMHO.
So anyway, my apologies if this is what you were referring to :),
- Chris