Strictly, yes, the FROM
clause of a SELECT
statement is not optional. The syntax for SQL-99 details the basic SELECT
statment, and the FROM
clause doesn't have any square brackets around it. That indicates the standard considers it non-optional:
SELECT [ DISTINCT | ALL ]
{Column expression [ AS name ]} [ ,... ] | *
FROM <Table reference> [ {,<Table reference>} ... ]
[ WHERE search condition ]
[ GROUP BY Columns [ HAVING condition ] ]
[ORDER BY {col_name | expr | position} [ASC | DESC],...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name' export_options |
INTO DUMPFILE 'file_name' |
INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]
In actual use, programmers and DBAs often find it useful to do things other than manipulate data in tables or manipulate tables and data structures. This type of thing is largely beyond the scope of the SQL standard, which is concerned with the data features more than the nuts and bolts of specific implementations. Whether we want to run SELECT getdate()
or SELECT 1
or SELECT DB_NAME()
(or whatever your dialect prefers), we don't actually want data from a table.
Oracle chooses to solve the standard and implementation discrepancy using a dummy table with the following effective definition:
CREATE TABLE DUAL (
DUMMY CHAR(1)
)
INSERT INTO DUAL (DUMMY) VALUES ('X')
Other RDBMSes essentially assume that a dummy table is used if no FROM
is specified.
The history of the DUAL table is on Wikipedia:
The DUAL table was created by Charles Weiss of Oracle corporation to
provide a table for joining in internal views:
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.
The original DUAL table had two rows in it (hence its name), but
subsequently it only had one row.
- Create the new bufferpool.
- Create a new tablespace that is associated with that bufferpool.
- Create a copy of the table you want and place it in the new tablespace.
- Copy the data from the original table to the new table.
- Drop the old table.
While you can associate tablespaces with different bufferpools using the ALTER TABLESPACE
statement, tables can only be associated with one tablespace. You can't issue ALTER TABLE
and switch the tablespace. So you unfortunately have to create a new table and move the data from one to the other.
EDIT: Also note that any indexes you have associated with the old table will also get dropped and have to be re-created.
EDIT: Code Example, note for this I am assuming a 4K tablespace. Please substitute your correct size if need be (ie, 4, 8, 16, or 32 K).
db2 create bufferpool TICKET_BP immediate size 250 automatic pagesize 4k;
--note I list automatic storage here. We prefer to use that. Plus, IBM is pushing to move
-- in that direction. This TS is for the table.
db2 create large tablespace TICKETTS pagesize 4k managed by automatic storage bufferpool TICKET_BP;
--this TS is for indexes.
db2 create large tablespace TICKET_IND_TS pagesize 4k managed by automatic storage bufferpool TICKET_BP;
db2 create large tablespace TICKET_LOB_TS pagesize 4k managed by automatic storage bufferpool TICKET_BP;
-- now you can run the stored proc to move the data. It essentially does the same thing as what I specified above, except that it can move the data during normal work loads, so it should have less impact on locking and such, but it may take longer I'm guessing.
db2 "call syscproc.admin_move_table('MAX','TICKET','TICKETTS','TICKET_IND_TS','TICKET_LOB_TS','','','','','','MOVE');
And yes, most definitely take backups before and after. Also note that if you allow any transactions during the data movement period you may lose those transactions if you have to rollback.
Best Answer
Singleton Table. In relational terms it is a relation with primary key ∅ (the empty set).