Lesson 33 : Global Temporary Tables (GTT)

Global Temporary Tables (GTT)

Global temporary tables can be used to store data temporarily, privately, persistently for a session or transaction. The data flushes out at defined instant automatically. The data in temporary tables are session specific but the table is available to across all the sessions. The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session.

Syntax :

CREATE GLOBAL TEMPORARY TABLE <Table-name> (Column-1 datatype, col2, datatype ...) ON COMMIT [DELETE | PRESERVE] ROW

ON COMMIT DELETE ROWS option is to have the data for a single TRANSACTION. The data is automatically flushed away after each COMMIT/ROLLBACK is executed. These are Transaction-specific Temporary tables.

ON COMMIT PRESERVE ROWS option is to have data for a SESSION. Data is preserved in the table for a session only. These are Session-specific Temporary tables.

Default is (if neither of above options specified) - ON COMMIT DELETE ROWS

Here are the few points to remember with GTT

• Data in temporary tables is stored in temp tablespace.
• Indexes can be created on temporary tables.
• When we TRUNCATE a temporary table, only the session specific data is truncated and no impact on the data of other sessions.
• Data in temporary tables is automatically deleted at the end of the database session, even if it ends abnormally. 
• Views can be created against temporary tables and combinations of temporary and permanent tables.
• Temporary tables can have triggers associated with them. 
• Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.

Example:

SQL> create global temporary table session_report(login_id varchar(10),start_time date,end_time date) ON COMMIT PRESERVE ROWS;

Table created.

Lesson 32 - Virtual Columns

Virtual Column

We can create virtual columns in tables as an expression. These kind of columns do not consume  any spaces in disk as it is computed at the run time.

Syntax  :

column_name datatype  GENERATED ALWAYS AS expression  VIRTUAL

datatype, GENERATED ALWAYS and VIRTUAL are optional key words.

If  we omitt datatype,  it is determined based on the result of the expression given.

The expression  should not be another virtual column  and it should refer only columns defined in same table.

And output of the expression must be a scalar value. It  should not be Oracle supplied datatype, a user-defined type, or LOB or LONG RAW.

Example :

SQL> create table salary_master(emp_id number(5),salary number(6),Tax NUMBER GENERATED ALWAYS AS  (salary*30/100) VIRTUAL);

Table created.

SQL> insert into salary_master(emp_id,salary) values(1,45000);

1 row created.

SQL>  insert into salary_master(emp_id,salary) values(2,75000);

1 row created.

SQL> insert into salary_master(emp_id,salary) values(3,85000);

1 row created.

SQL> insert into salary_master(emp_id,salary) values(4,25000);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from salary_master;

    EMP_ID     SALARY        TAX

---------- ---------- ----------
         1      45000      13500
         2      75000      22500
         3      85000      25500
         4      25000       7500

Points to remember with Virtual columns

1)  Constraints can be created on virtual columns.

2) DML operations are not allowed

3) function-based indexes can be created on these columns.

Lesson 31 : CUBE in SELECT

CUBE in SELECT Queries 

 CUBE enables  SELECT statement  to set of grouping columns and creates subtotals for all of their possible combinations. In terms of multidimensional analysis, CUBE generates all the subtotals that could be calculated for a data cube with the specified dimensions.

If  we  specify CUBE in SELECT, the result set will include all the values that would be included in an equivalent ROLLUP statement plus additional combinations.

In short, the CUBE is extension of ROLLUP and it will generate subtotals for all combinations of the dimensions specified. If "n" is the number of columns listed in the CUBE, there will be 2n subtotal combinations

Syntax :

SELECT col1, group function(col2), .. FROM <tablename> GROUP  BY  CUBE(column list)

Examples :

SQL> create table employee(name varchar(10),dept_id number(3),location varchar(10),salary number(5));

Table created.

SQL> insert into employee values('John',10,'Dallas',50000);

1 row created.

SQL> insert into employee values('Devy',10,'Dallas',75000);

1 row created.

SQL> insert into employee values('Paul',20,'Texas',85000);

1 row created.

SQL> insert into employee values('Raj',20,'Texas',65000);

1 row created.

SQL> insert into employee values('Chetan',30,'Boston',95000);

1 row created.

SQL> insert into employee values('Jayne',30,'Boston',55000);

1 row created.

SQL> insert into employee values('Senthil',30,'Chennai',45000);

1 row created.

SQL> insert into employee values('Jeya',30,'Chennai',25000);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from employee;

NAME          DEPT_ID LOCATION       SALARY

---------- ---------- ---------- ----------
John             10 Dallas          50000
Devy            10 Dallas          75000
Paul             20 Texas           85000
Raj               20 Texas           65000
Chetan       30 Boston         95000
Jayne          30 Boston         55000
Senthil       30 Chennai       45000
Jeya           30 Chennai       25000

8 rows selected.

SQL> select location,dept_id,count(*),sum(salary) from employee group by (location,dept_id);

 LOCATION      DEPT_ID   COUNT(*) SUM(SALARY)
---------- ---------- ---------- -----------
Texas              20          2      150000
Boston           30          2      150000
Dallas             10          2      125000
Chennai         30          2       70000

SQL> SQL> select location,dept_id,count(*),sum(salary) from employee group by cube(location,dept_id);

LOCATION      DEPT_ID   COUNT(*) SUM(SALARY)

---------- ---------- ---------- -----------
                               8      495000
                   10       2      125000
                   20       2      150000
                   30       4      220000
Texas                    2      150000
Texas        20       2      150000
Boston                 2      150000
Boston     30       2      150000
Dallas                  2      125000
Dallas       10      2      125000
Chennai             2       70000
Chennai   30     2       70000

12 rows selected.

Lesson : 30 - Pseudocolumn - ROWID

Pseudocolumn  column in Oracle

A pseudocolumn  is like a table column, but is not actually stored in the table. We can select from pseudocolumns, but we cannot insert, update, or delete their values.

 ROWID

 ROWID pseudocolumn returns thee address of the row which contains the data object number, the data block of the row, the row position and data file.

 Using the ROWID  is  the fastest way to search for a given row in the database.

The ROWID hexadecimal values consists for following information

•The data object number of the object (1-4 digits)

•The data block in the datafile in which the row resides (4 to 8th )

•The position of the row in the data block (first row is 0)  - 8th to 12th of rowid

•The datafile in which the row resides (first file is 1). The file number is relative to the tablespace. (12th to 16th)

  Example :

SQL> select rowid from emp;

ROWID

------------------

AAAsR4AAFAAABgzAAA

AAAsR4AAFAAABgzAAB

AAAsR4AAFAAABgzAAC

Note : ROWID values are not necessarily unique within a database. It is  possible for two rows of two different tables stored in the same cluster and have the same ROWID.  But it is unique within table.

 ROWID  values may change if the row is physically moved on disk, in the following  scenories  :

When we do  export or import of the table

 

·         ALTER TABLE  with MOVE option

 

·         When we  SHRINK SPACE with Alter table 

·         When do table FLASHBACK  

·         When we do  splitting a partition 

·         When combining two partitions

Lesson 29 : ROLLUP option in SELECT

ROLLUP in SELECT Queries  

ROLLUP enables a SELECT statement to compute multiple levels of subtotals across a specified group of dimensions. It also calculates a grand total. ROLLUP is a simple extension to the GROUP BY clause.

It creates subtotals that roll up from the most detailed level to a grand total, following a grouping list specified in the ROLLUP clause. ROLLUP takes as its argument an ordered list of grouping columns. First, it calculates the standard aggregate values specified in the GROUP BY clause. Then, it creates progressively higher-level subtotals, moving from right to left through the list of grouping columns. Finally, it creates a grand total.

ROLLUP creates subtotals at n+1 levels, where n is the number of grouping columns.  For example, if a query specifies ROLLUP on grouping columns of dept,location, and  salar (n=3), the result set will include rows at four aggregation levels.

Syntax :

SELECT col1, group function(col2), .. FROM <tablename> GROUP  BY  ROLLUP(column list)

Examples :

SQL> create table employee(name varchar(10),dept_id number(3),location varchar(10),salary number(5));

Table created.

SQL> insert into employee values('John',10,'Dallas',50000);

1 row created.

SQL> insert into employee values('Devy',10,'Dallas',75000);

1 row created.

SQL> insert into employee values('Paul',20,'Texas',85000);

 
1 row created.

SQL> insert into employee values('Raj',20,'Texas',65000);

1 row created.

SQL> insert into employee values('Chetan',30,'Boston',95000);

1 row created.

SQL> insert into employee values('Jayne',30,'Boston',55000);

1 row created.

SQL> insert into employee values('Senthil',30,'Chennai',45000);

1 row created.

SQL> insert into employee values('Jeya',30,'Chennai',25000);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from employee;

NAME          DEPT_ID LOCATION       SALARY

---------- ---------- ---------- ----------
John               10 Dallas          50000
Devy               10 Dallas          75000
Paul               20 Texas           85000
Raj                20 Texas           65000
Chetan             30 Boston          95000
Jayne              30 Boston          55000
Senthil            30 Chennai         45000
Jeya               30 Chennai         25000

8 rows selected.

SQL> select location,dept_id,count(*),sum(salary) from employee group by (location,dept_id);

LOCATION      DEPT_ID   COUNT(*) SUM(SALARY)
---------- ---------- ---------- -----------
Texas              20          2      150000
Boston           30          2      150000
Dallas             10          2      125000
Chennai         30          2       70000

SQL> select location,dept_id,count(*),sum(salary) from employee group by rollup(location,dept_id);

 LOCATION      DEPT_ID   COUNT(*) SUM(SALARY)

---------- ---------- ---------- -----------
Texas              20          2      150000
Texas                            2      150000
Boston           30          2      150000
Boston                         2      150000
Dallas             10          2      125000
Dallas                          2      125000
Chennai         30          2       70000
Chennai                        2       70000
                                     8      495000

 9 rows selected.

 
SQL> select location,dept_id,sum(salary) from employee group by rollup(location,dept_id);

LOCATION      DEPT_ID SUM(SALARY)

---------- ---------- -----------
Texas              20      150000
Texas                      150000
Boston             30      150000
Boston                     150000
Dallas             10      125000
Dallas                     125000
Chennai            30       70000
Chennai                     70000
                                495000
9 rows selected.

SQL>

Lesson 28 : Index

Index

An index is a schema object that contains an entry for each value that appears in the indexed column(s) of the table.  And it provides direct, fast access to rows.  Actually, it is used for faster retrieval of records from tables.

Here are different types of indexes,

• Normal indexes. (By default, Oracle Database creates B-tree indexes.)

• Bitmap indexes, which store rowids associated with a key value as a bitmap.

• Partitioned indexes, which consist of partitions containing an entry for each value that appears in the indexed column(s) of the table.

• Function-based indexes, which are based on expressions. They enable you to construct queries that evaluate the value returned by an expression, which in turn may include built-in or user-defined functions.

• Domain indexes, which are instances of an application-specific index of type indextype.

CREATE INDEX :

Syntax :

CREATE  [UNIQUE] I NDEX <index_name>  ON table_name (column1, column2, ... column_n)   [ COMPUTE STATISTICS ];

UNIQUE  denotes that the combination of values in the indexed columns must be unique.

COMPUTE STATISTICS  is to  collect statistics during the creation of the index.  The statistics are then used by the optimizer to choose the plan.

Examples :

SQL> create index  empnameidx on emp(empname);

Dropping index

Syntax :

Drop index <indexname>

Example :

SQL> drop index empnameidx;

Rename index :

Syntax :

ALTER INDEX <existing indexname>   RENAME TO <new name>

Example :

SQL> alter index empnameidx  rename to empname_idx;

Lesson : 27 - ROLLBACK

ROLLBACK

ROLLBACK statement is used to undo work done in the current transaction. If we specify savepoint, it rollbacks(undo) all changes after the specified savepoint.

Syntax -1  :

ROLLBACK   or ROLLBACK WORK

Syntax - 2

ROLLBACK  TO  <savapoint name>;  or  ROLLBACK  TO  SAVEPOINT <savapoint name>;

Note : WORK  and SAVEPOINT are optional key workds.

Examples :

SQL> insert into emp(empname) values (‘Mr Testing’);

SQL> savepoint step1;

SQL> insert into emp(empname) values (‘Bill’);

SQL> insert into emp(empname) values (‘Robert’);

SQL> insert into emp(empname) values (‘Ramesh’);

SQL> Rollback to savepoint step1;

It  undo last three changes. (Records of Bill,Robert,Rames).

Note :

If we use  ROLLBACK without the TO SAVEPOINT clause, it  performs the following operations,

·         Ends the current transaction

·         Undoes all changes in the current transaction

·         Erases all savepoints in the transaction

·         Releases any transaction locks

If we use  ROLLBACK with the TO SAVEPOINT clause, it  performs the following operations,

·         Rolls back just the portion of the transaction after the savepoint. It does not end the transaction.

·         Erases all savepoints created after that savepoint. The named savepoint is retained, so you can roll back to the same savepoint multiple times. Prior savepoints are also retained.

·         Releases all table and row locks acquired since the savepoint. Other transactions that have requested access to rows locked after the savepoint must continue to wait until the transaction is committed or rolled back. Other transactions that have not already requested the rows can request and access the rows immediately.

Note : Transaction is set of DML  changes between two commits.