Common-Sense Developer, Oracle Expert, Performance Enthusiast, Technology Geek and Creator of #csv2db.
"Write code to solve problems, not to create them!"
Thanks for your great article on NewSQL databases!
I could not help but notice some wording in your article that could be misunderstood and hence would like to add some clarifications.
In the article there is a section that says:
For example, if I am trying to write to a table that has a billion rows of data, and it has foreign key to a table with a billion rows of data, the RDBMS has to inspect both tables prior to committing the write.
It makes it sound like that if I were to, e.g. insert a new row in a table A with a billion rows in it, which in turn has a foreign key to another table B with a billion rows in it, that the RDBMS has to go and inspect every single row of both tables before or while a COMMIT occurs. In the event that readers came to such a belief, I wanted to clarify that adding a row in such a big table is actually still a very lightweight operation for the RDMBS. The target table (table A) would not have to be inspected at all, all the RDBMS may have to check is probably a unique key index for the primary key, in case that a primary key has been specified. Given that such indexes in RDBMSs are commonly B-Tree data structures, i.e self-balancing, sorted data structures, such a lookup would, in fact, be very fast and only require a couple of I/Os. As to table B, the very same principle applies. In order to create a foreign key on table A, a primary key on table B has to be present to which the foreign key points to, equally with its own B-Tree unique key index. All that the RDBMS has to do in order to check whether the parent record exists is to check the primary key index of table B but never table B itself.
I have taken the liberty to verify my claim by creating two such 1 billion rows tables with a simple foreign key between them in an Oracle Database running inside a Docker container with 4 CPUs and 10GB of memory:
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show parameter cpu
NAME TYPE VALUE
------------------------------- ------- -----
cpu_count integer 4
cpu_min_count string 4
parallel_threads_per_cpu integer 1
resource_manager_cpu_allocation integer 4
SQL> show parameter sga_target
NAME TYPE VALUE
---------- ----------- -----
sga_target big integer 10G
I called my target table A actually readings and my table B is called meters. Additionally, I have also added a couple of NOT NULL constraints which will have to be validated as well, just for fun really.
Neither INSERT statement took longer than a couple of milliseconds to execute
Just a few physical read total IO requests have occurred, 14 and 3 respectively, confirming that not both the entire tables had to be scanned
The second time the physical IO requests were significantly fewer than the first time and so was the execution time - I will come back to that in a bit
The meters table is nowhere to be seen in the execution plan
The database correctly identifies a 1-row insert operation of 17 bytes in total
The COMMIT also didn't take much longer than a couple of milliseconds, it was actually the fastest operation of all three of them
So while it may seem intuitive at first that the database has to scan all 2 billion rows in order to insert this new record, in fact, the database has to do very little to accomplish the operation.
I would also like to clarify that if readers came to the belief that the referential integrity is verified during/prior to the COMMIT that this is not the case, with Oracle Database anyway. It is, in fact, done during the INSERT operation itself and can quickly be validated by inserting a record with a non-existing parent record:
Also in this case it can be observed that the foreign-key validation took only a couple of milliseconds.
Coming back to insight number 3 above and
People have tried to solve this by making RDBMS systems in-memory, ...
RDBMSs are actually "in-memory" for a long, long time. All common RDBMSs allocate memory (I have shown before that the memory of my Oracle Database, i.e. SGA_TARGET is set to 10GB) to cache data and many other things in memory. This explains why there was a drop in physical I/Os when executing a second INSERT statement. At that point in time, the database had already some of the B-Tree index blocks cached and could just look them up in memory rather than from disk. Caching also explains why the overall execution time dropped for the second INSERT operation.
Last I would like to add to this section:
As a one-off this is not a big deal, but if I am doing this with a 1,000 threads writing a 1,000 rows per second it begins to become problematic.
So far I have only performed 2 insert operations and indeed they have been quite fast but those two statements were just one-offs and done from a command-line prompt. However, given that the database actually doesn't have to scan 2 billion rows every time I insert a row, I took the liberty to run one last test. Now, I don't have a big server at my disposal to run 1000 threads on, only a compute instance in the cloud with 8 CPU cores where 4 of them are already given to the database. Instead, I just wrote an endless loop in a little Java program that just keeps inserting data into the readings table, just to see how the database performs with a continuous load. The Java program keeps count of how many INSERT operations per second it has performed:
The results are actually quite impressive. The Java program has, for 21 consecutive seconds, inserted more than 3000 rows per second! That means that the database did more than 3 inserts per millisecond with just 4 CPUs and 10GB of RAM on this 1 billion+ rows table!
I can confirm that by querying the 1 billion+ table as well (note the two 1-row inserts from earlier on):
Of course, at this stage I could go on and start a second instance of that Java program and test the upper limits of that particular database, however, that's probably a topic for a post on its own. :)
I hope that I have given readers a better understanding of how an RDBMS actually performs referential integrity checks well, at least Oracle Database, and of how fast these are done even on bigger tables.
Thanks once again!
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
Hi Stephen,
Thanks for your great article on NewSQL databases!
I could not help but notice some wording in your article that could be misunderstood and hence would like to add some clarifications.
In the article there is a section that says:
It makes it sound like that if I were to, e.g. insert a new row in a table
Awith a billion rows in it, which in turn has a foreign key to another tableBwith a billion rows in it, that the RDBMS has to go and inspect every single row of both tables before or while aCOMMIToccurs. In the event that readers came to such a belief, I wanted to clarify that adding a row in such a big table is actually still a very lightweight operation for the RDMBS. The target table (tableA) would not have to be inspected at all, all the RDBMS may have to check is probably a unique key index for the primary key, in case that a primary key has been specified. Given that such indexes in RDBMSs are commonly B-Tree data structures, i.e self-balancing, sorted data structures, such a lookup would, in fact, be very fast and only require a couple of I/Os. As to tableB, the very same principle applies. In order to create a foreign key on tableA, a primary key on tableBhas to be present to which the foreign key points to, equally with its own B-Tree unique key index. All that the RDBMS has to do in order to check whether the parent record exists is to check the primary key index of tableBbut never tableBitself.I have taken the liberty to verify my claim by creating two such 1 billion rows tables with a simple foreign key between them in an Oracle Database running inside a Docker container with 4 CPUs and 10GB of memory:
I called my target table
Aactuallyreadingsand my tableBis calledmeters. Additionally, I have also added a couple ofNOT NULLconstraints which will have to be validated as well, just for fun really.As said, both of the tables contain 1 billion rows each:
And when inserting rows into
readingsthe database performs that task in an instant:There are a couple of interesting insights here:
INSERTstatement took longer than a couple of milliseconds to executephysical read total IO requestshave occurred, 14 and 3 respectively, confirming that not both the entire tables had to be scannedmeterstable is nowhere to be seen in the execution planCOMMITalso didn't take much longer than a couple of milliseconds, it was actually the fastest operation of all three of themSo while it may seem intuitive at first that the database has to scan all 2 billion rows in order to insert this new record, in fact, the database has to do very little to accomplish the operation.
I would also like to clarify that if readers came to the belief that the referential integrity is verified during/prior to the
COMMITthat this is not the case, with Oracle Database anyway. It is, in fact, done during theINSERToperation itself and can quickly be validated by inserting a record with a non-existing parent record:Also in this case it can be observed that the foreign-key validation took only a couple of milliseconds.
Coming back to insight number 3 above and
RDBMSs are actually "in-memory" for a long, long time. All common RDBMSs allocate memory (I have shown before that the memory of my Oracle Database, i.e. SGA_TARGET is set to 10GB) to cache data and many other things in memory. This explains why there was a drop in physical I/Os when executing a second
INSERTstatement. At that point in time, the database had already some of the B-Tree index blocks cached and could just look them up in memory rather than from disk. Caching also explains why the overall execution time dropped for the secondINSERToperation.Last I would like to add to this section:
So far I have only performed 2 insert operations and indeed they have been quite fast but those two statements were just one-offs and done from a command-line prompt. However, given that the database actually doesn't have to scan 2 billion rows every time I insert a row, I took the liberty to run one last test. Now, I don't have a big server at my disposal to run 1000 threads on, only a compute instance in the cloud with 8 CPU cores where 4 of them are already given to the database. Instead, I just wrote an endless loop in a little Java program that just keeps inserting data into the
readingstable, just to see how the database performs with a continuous load. The Java program keeps count of how manyINSERToperations per second it has performed:The results are actually quite impressive. The Java program has, for 21 consecutive seconds, inserted more than 3000 rows per second! That means that the database did more than 3 inserts per millisecond with just 4 CPUs and 10GB of RAM on this 1 billion+ rows table!
I can confirm that by querying the 1 billion+ table as well (note the two 1-row inserts from earlier on):
Of course, at this stage I could go on and start a second instance of that Java program and test the upper limits of that particular database, however, that's probably a topic for a post on its own. :)
I hope that I have given readers a better understanding of how an RDBMS actually performs referential integrity checks well, at least Oracle Database, and of how fast these are done even on bigger tables.
Thanks once again!