ADD A COLUMN CALLED QUANTITY WITH DATATYPE NUMBER TO THE ORDER_ITEMS TABLE

12. Add a column called QUANTITY with datatype NUMBER to the ORDER_ITEMS table:

alter table order_items add (quantity number);

Create and Use Temporary Tables

PART IIA temporary table has a definition that is visible to all sessions, but the rows within it are private to the session that inserted them. Programmers can use them as a private storage area for manipulating large amounts of data. The syntax is

CREATE GLOBAL TEMPORARY TABLE temp_tab_name

(column datatype [,column datatype] )

[ON COMMIT {DELETE | PRESERVE} ROWS] ;

The column definition is the same as a regular table and can indeed be supplied from a subquery. The optional clause at the end determines the lifetime of any rows inserted. The default is to remove the rows the moment the transaction that inserted them completes, but this behavior can be changed to preserve them until the session that inserted them ends. Whichever option is chosen, the data will be private to each session: different users can insert their own rows into their own copy of the table, and they will never see each other’s rows.In many ways, a temporary table is similar to a permanent table. You can execute any DML or SELECT command against it. It can have indexes, constraints, and triggers defined. It can be referenced in views and synonyms, or joined to other tables. The difference is that the data is transient and private to the session, and that all SQL commands against it will be far faster than commands against permanent tables.The first reason for the speed is that temporary tables are not segments in permanent tablespaces. Ideally, they exist only in the PGAs of the sessions that are using them, so there is no disk activity or even database buffer cache activity involved. If the PGA cannot grow sufficiently to store the temporary table (which will be the case if millions of rows are being inserted—not unusual in complex report generation), then the table gets written out to a temporary segment in the user’s temporary tablespace. I/O on temporary tablespaces is much faster than I/O on permanent tablespaces, because it does not go via the database buffer cache; it is all performed directly on disk by the session’s server process.A second reason for speed is that DML against temporary tables does not generate redo. Since the data only persists for the duration of a session (perhaps only for the duration of a transaction), there is no purpose in generating redo. This gives the dual benefit of fast DML for the session working on the table, and taking the strain off the redo generation system, which can be a bad point of contention on busy multiuser databases.Figure 7-2 shows the creation and use of a temporary table with SQL*Plus. The Database Control Table Creation Wizard can also create temporary tables.

Figure 7-2

Creation and use of a temporary table