From Wikipedia:
The DUAL table is a special one-row table present by default in all Oracle database installations. It is suitable for use in selecting a pseudocolumn such as SYSDATE or USER. The table has a single VARCHAR2(1) column called DUMMY that has a value of 'X'.
Thus, the dual table is a way to perform operations against what amounts to be an empty but not null table. This is useful when one doesn't care about the table, but needs to perform operations through a select statement. If the table had more than one row or column, multiple results would be returned (due to operating over the entire set of tuples when performing the operation.)
It shouldn't be used in production, unless you specifically need to invoke certain procedures through SQL.
4*5 is a mathematical operation, just as 'Foo' is a string. Thus, just as one can select 4*5 from any table, just as one can select 'Foo' from any table, DUAL is a way of selecting it from a known-good table that will never have multiple results.
From the documentation (CONCEPTS):
DUAL is a small table in the data dictionary that Oracle Database and user-written programs can reference to guarantee a known result. The dual table is useful when a value must be returned only once, for example, the current date and time. All database users have access to DUAL.
The DUAL table has one column called DUMMY and one row containing the value X.
And the SQL Reference:
DUAL is a table automatically created by Oracle Database along with the data dictionary. DUAL is in the schema of the user SYS but is accessible by the name DUAL to all users. It has one column, DUMMY, defined to be VARCHAR2(1), and contains one row with a value X. Selecting from the DUAL table is useful for computing a constant expression with the SELECT statement. Because DUAL has only one row, the constant is returned only once. Alternatively, you can select a constant, pseudocolumn, or expression from any table, but the value will be returned as many times as there are rows in the table. Refer to "About SQL Functions" for many examples of selecting a constant value from DUAL.
Beginning with Oracle Database 10g Release 1, logical I/O is not performed on the DUAL table when computing an expression that does not include the DUMMY column. This optimization is listed as FAST DUAL in the execution plan. If you SELECT the DUMMY column from DUAL, then this optimization does not take place and logical I/O occurs.
Firstly, I will say that this workload is by no means heavy by Oracle standards; thousands of commits/sec are possible, easily. Secondly, however, what matters here is not your database and it's not your server: it's your storage. There are many options here; you won't go too wrong with something like NetApp (I don't work for them, just a satisfied user) and the question is, what size? Here ORION is your friend. Whatever storage array you choose, this will take care of your first level of resilience, if your first node fails you simply mount the disks on another and start back up again, and Oracle will perform crash recovery so no data will be lost.
My advice is, get some numbers at a lower level - MB/s, IOPs - by performing a representative benchmark and take those to your nearest storage vendor and ask them what they've got. With NetApp at least, you can start fairly small and grow, adding another head for more resilience/better performance, adding shelves for more capacity, etc etc. Then test the crap out of it with Orion!
Best Answer
What do you expect this query to do?
regexp_likeis a regular expression version of theLIKEstatement so it makes sense to use it in the same sorts of places that you would use aLIKE. You wouldn't try to directlySELECTthe result of aLIKEstatement. You could, however, embed theregexp_likein acasestatement. For exampleregexp_likeis a function that returns aboolean. Oracle SQL, however, does not support thebooleandata type so you cannot directlySELECTthe result of the function just like you couldn'tSELECTa function you write that returns aboolean.