Ask the Expert

Why are there and what is the effect of having blank rows in an Oracle table?

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?


    Requires Free Membership to View

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


This was first published in May 2001

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: