afiedt.buf, why Oracle had the world's first commercial RDBMS even though it was based on IBM's System-R, why nobody ever used MTS. One thing it doesn't cover is why the DUAL table is so named. Now the reason for this appeared in an Oracle Magazine column a few years back. I clicked on my bookmark so I could forward the URL only to get a 404 error. Turns out there's been a recent purge of the Oracle Magazine online archive. I presume this is because of a desire to retire stale and potentially misleading information rather than because OTN is running out of disk space.
Fortunately the Wayback Machine still has a copy of the web page in question. But it is the Internet Archive only works if we know what and when we know what we are looking for. So, s a public service and to keep this information in a Google-isable domain, I reproduce the text here.
The History of Dual
In the November/December issue of Oracle Magazine you recount the history of the company, so I'm wondering if you can tell me how the DUAL table got its name. People I've asked, including seasoned Oracle gurus, have not been able to answer this.
Sean O'Neill
I created the DUAL table as an underlying object in the Oracle Data Dictionary. It was never meant to be seen itself, but instead used inside a view that was expected to be queried. The idea was that you could do a JOIN to the DUAL table and create two rows in the result for every one row in your table. Then, by using GROUP BY, the resulting join could be summarized to show the amount of storage for the DATA extent and for the INDEX extent(s). The name, DUAL, seemed apt for the process of creating a pair of rows from just one.
Chuck Weiss, Oracle
Source: Oracle Magazine "Sendmail column" January 2002
Update
In the Comments Marco Gralike linked to a thread on AskTom. That URL was broken by the migration of AskTom to a new version of ApEx. Here is the new URL for the thread.
3 comments:
I've always felt that explanation raised more questions than it answered.
1. For a start I don't really follow the data/index extent duplication example.
2. What did they use before DUAL? Did they just query USER_TABLES or whatever WHERE ROWNUM = 1 whenever they wanted SYSDATE?
3. How long did they manage without a single-row table? It seems odd that they would go to the trouble of inventing a special two-row table just for row duplication, which apparently they later found they didn't even need, when the everyday requirement for a one-row table still had no reliable solution.
4. Did they ever consider making the FROM clause optional?
5. Whose idea was it to start using the row-doubler as a single-row table, and why? Presumably that must have happened some time after they stopped needing it for the extent duplication view. Why didn't they just drop it and create another one called MONO? Somebody at some point must have thought "Querying random tables WHERE ROWNUM = 1 every time I need to see SYSDATE is becoming a bit of a pain. Hey, I don't think anyone's using Chuck's old row doubler table any more, why don't I just delete a row out of that, and not rename it or anything? Oracle version 2's gonna rock."
I've got a few more links on the topic here (although it appears as if I have to update the Oracle magazine one).
http://thinkoracle.blogspot.com/2005/11/dual-table.html
Asktom has a very nice item about DUAL (and among others the "why dual" part).
See here: http://asktom.oracle.com/pls/ask/f?p=4950:8:2063070446917787945::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1562813956388
Post a Comment