Why are there rows with no records in an Oracle table, and what is the effect on the integrity of the databases if there are blank rows? Is it necessary to maintain three or four tables for just making changes in the database?
The only way that I've seen rows in a table that seemed to have no records is when someone inserted all NULL values into the table. For instance, look at the following example:
SQL> create table test ( 2 id number, 3 name varchar2(20)); Table created. SQL> insert into test values (NULL,NULL); 1 row created. SQL> commit; Commit complete. SQL> select * from test; ID NAME ---------- -------------------- SQL> select count(*) from test; COUNT(*) ---------- 1
As you can see, it appears that I have one record in this table which is completely empty.
The effect of the integrity on the database is really determined by your business rules. The database has no problems holding these "blank" rows. But is this good for your business and the application that runs against the database? For instance, let's suppose that I have a table of invoices. And this table holds 100 "real" invoices and 10 of these blank rows. If my application needs to know how many invoices are out there and I do a SELECT COUNT(*) FROM INVOICES;, it will return 110 rows. When in fact, I really have only 100 invoices. So my application could return erroneous information because of my business rules.
Is it necessary to maintain three or four tables for just making changes in the database? No. You should just need the tables that will hold your data. For the most part, you may not need any tables just for the sole pupose of holding your changes. But again, it is all determined by your business rules.
For More Information
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our Oracle guru is waiting to answer your toughest questions.
This was first published in May 2001