oracle sql plsql expert part 1

এর দ্বারা পোস্ট করা assad
Tuesday, May 15, 2012


ROLLUP

In addition to the regular aggregation results we expect from the GROUP BY clause, the ROLLUP extension produces group subtotals from right to left and a grand total. If "n" is the number of columns listed in the ROLLUP, there will be n+1 levels of subtotals.
SELECT fact_1_id,
       fact_2_id,
       SUM(sales_value) AS sales_value
FROM   dimension_tab
GROUP BY ROLLUP (fact_1_id, fact_2_id)
ORDER BY fact_1_id, fact_2_id;

 FACT_1_ID  FACT_2_ID SALES_VALUE
---------- ---------- -----------
         1          1     4363.55
         1          2     4794.76
         1          3     4718.25
         1          4     5387.45
         1          5     5027.34
         1               24291.35
         2          1     5652.84
         2          2     4583.02
         2          3     5555.77
         2          4     5936.67
         2          5     4508.74
         2               26237.04
                         50528.39

13 rows selected.

SQL>

CUBE

In addition to the subtotals generated by the ROLLUP extension, the CUBE extension 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.
SELECT fact_1_id,
       fact_2_id,
       SUM(sales_value) AS sales_value
FROM   dimension_tab
GROUP BY CUBE (fact_1_id, fact_2_id)
ORDER BY fact_1_id, fact_2_id;

 FACT_1_ID  FACT_2_ID SALES_VALUE
---------- ---------- -----------
         1          1     4363.55
         1          2     4794.76
         1          3     4718.25
         1          4     5387.45
         1          5     5027.34
         1               24291.35
         2          1     5652.84
         2          2     4583.02
         2          3     5555.77
         2          4     5936.67
         2          5     4508.74
         2               26237.04
                    1    10016.39
                    2     9377.78
                    3    10274.02
                    4    11324.12
                    5     9536.08
                         50528.39

18 rows selected.

GROUPING Functions

GROUPING

It can be quite easy to visually identify subtotals generated by rollups and cubes, but to do it programatically you really need something more accurate than the presence of null values in the grouping columns. This is where the GROUPING function comes in. It accepts a single column as a parameter and returns "1" if the column contains a null value generated as part of a subtotal by a ROLLUP or CUBE operation or "0" for any other value, including stored null values.
The following query is a repeat of a previous cube, but the GROUPING function has been added for each of the dimensions in the cube.
SELECT fact_1_id,
       fact_2_id,
       SUM(sales_value) AS sales_value,
       GROUPING(fact_1_id) AS f1g, 
       GROUPING(fact_2_id) AS f2g
FROM   dimension_tab
GROUP BY CUBE (fact_1_id, fact_2_id)
ORDER BY fact_1_id, fact_2_id;

 FACT_1_ID  FACT_2_ID SALES_VALUE        F1G        F2G
---------- ---------- ----------- ---------- ----------
         1          1     4363.55          0          0
         1          2     4794.76          0          0
         1          3     4718.25          0          0
         1          4     5387.45          0          0
         1          5     5027.34          0          0
         1               24291.35          0          1
         2          1     5652.84          0          0
         2          2     4583.02          0          0
         2          3     5555.77          0          0
         2          4     5936.67          0          0
         2          5     4508.74          0          0
         2               26237.04          0          1
                    1    10016.39          1          0
                    2     9377.78          1          0
                    3    10274.02          1          0
                    4    11324.12          1          0
                    5     9536.08          1          0
                         50528.39          1          1

18 rows selected.

SQL>
From this we can see:
  • F1G=0,F2G=0 : Represents a row containing regular subtotal we would expect from a GROUP BY operation.
  • F1G=0,F2G=1 : Represents a row containing a subtotal for a distinct value of the FACT_1_ID column, as generated by ROLLUP and CUBE operations.
  • F1G=1,F2G=0 : Represents a row containing a subtotal for a distinct value of the FACT_2_ID column, which we would only see in a CUBE operation.
  • F1G=1,F2G=1 : Represents a row containing a grand total for the query, as generated by ROLLUP and CUBE operations.
It would now be easy to write a program to accurately process the data.
The GROUPING columns can used for ordering or filtering results.
SELECT fact_1_id,
       fact_2_id,
       SUM(sales_value) AS sales_value,
       GROUPING(fact_1_id) AS f1g, 
       GROUPING(fact_2_id) AS f2g
FROM   dimension_tab
GROUP BY CUBE (fact_1_id, fact_2_id)
HAVING GROUPING(fact_1_id) = 1 OR GROUPING(fact_2_id) = 1
ORDER BY GROUPING(fact_1_id), GROUPING(fact_2_id);

 FACT_1_ID  FACT_2_ID SALES_VALUE        F1G        F2G
---------- ---------- ----------- ---------- ----------
         1               24291.35          0          1
         2               26237.04          0          1
                    4    11324.12          1          0
                    3    10274.02          1          0
                    2     9377.78          1          0
                    1    10016.39          1          0
                    5     9536.08          1          0
                         50528.39          1          1

8 rows selected.

SQL>

GROUPING_ID

The GROUPING_ID function provides an alternate and more compact way to identify subtotal rows. Passing the dimension columns as arguments, it returns a number indicating the GROUP BY level.
SELECT fact_1_id,
       fact_2_id,
       SUM(sales_value) AS sales_value,
       GROUPING_ID(fact_1_id, fact_2_id) AS grouping_id
FROM   dimension_tab
GROUP BY CUBE (fact_1_id, fact_2_id)
ORDER BY fact_1_id, fact_2_id;

 FACT_1_ID  FACT_2_ID SALES_VALUE GROUPING_ID
---------- ---------- ----------- -----------
         1          1     4363.55           0
         1          2     4794.76           0
         1          3     4718.25           0
         1          4     5387.45           0
         1          5     5027.34           0
         1               24291.35           1
         2          1     5652.84           0
         2          2     4583.02           0
         2          3     5555.77           0
         2          4     5936.67           0
         2          5     4508.74           0
         2               26237.04           1
                    1    10016.39           2
                    2     9377.78           2
                    3    10274.02           2
                    4    11324.12           2
                    5     9536.08           2
                         50528.39           3

18 rows selected.

SQL>

GROUP_ID

It's possible to write queries that return the duplicate subtotals, which can be a little confusing. The GROUP_ID function assigns the value "0" to the first set, and all subsequent sets get assigned a higher number. The following query forces duplicates to show the GROUP_ID function in action.
SELECT fact_1_id,
       fact_2_id,
       SUM(sales_value) AS sales_value,
       GROUPING_ID(fact_1_id, fact_2_id) AS grouping_id,
       GROUP_ID() AS group_id
FROM   dimension_tab
GROUP BY GROUPING SETS(fact_1_id, CUBE (fact_1_id, fact_2_id))
ORDER BY fact_1_id, fact_2_id;

 FACT_1_ID  FACT_2_ID SALES_VALUE GROUPING_ID   GROUP_ID
---------- ---------- ----------- ----------- ----------
         1          1     4363.55           0          0
         1          2     4794.76           0          0
         1          3     4718.25           0          0
         1          4     5387.45           0          0
         1          5     5027.34           0          0
         1               24291.35           1          1
         1               24291.35           1          0
         2          1     5652.84           0          0
         2          2     4583.02           0          0
         2          3     5555.77           0          0
         2          4     5936.67           0          0
         2          5     4508.74           0          0
         2               26237.04           1          1
         2               26237.04           1          0
                    1    10016.39           2          0
                    2     9377.78           2          0
                    3    10274.02           2          0
                    4    11324.12           2          0
                    5     9536.08           2          0
                         50528.39           3          0

20 rows selected.

SQL>
If necessary, you could then filter the results using the group.
SELECT fact_1_id,
       fact_2_id,
       SUM(sales_value) AS sales_value,
       GROUPING_ID(fact_1_id, fact_2_id) AS grouping_id,
       GROUP_ID() AS group_id
FROM   dimension_tab
GROUP BY GROUPING SETS(fact_1_id, CUBE (fact_1_id, fact_2_id))
HAVING GROUP_ID() = 0
ORDER BY fact_1_id, fact_2_id;

 FACT_1_ID  FACT_2_ID SALES_VALUE GROUPING_ID   GROUP_ID
---------- ---------- ----------- ----------- ----------
         1          1     4363.55           0          0
         1          2     4794.76           0          0
         1          3     4718.25           0          0
         1          4     5387.45           0          0
         1          5     5027.34           0          0
         1               24291.35           1          0
         2          1     5652.84           0          0
         2          2     4583.02           0          0
         2          3     5555.77           0          0
         2          4     5936.67           0          0
         2          5     4508.74           0          0
         2               26237.04           1          0
                    1    10016.39           2          0
                    2     9377.78           2          0
                    3    10274.02           2          0
                    4    11324.12           2          0
                    5     9536.08           2          0
                         50528.39           3          0

18 rows selected.

SQL>

GROUPING SETS

Calculating all possible subtotals in a cube, especially those with many dimensions, can be quite an intensive process. If you don't need all the subtotals, this can represent a considerable amount of wasted effort. The following cube with three dimensions gives 8 levels of subtotals (GROUPING_ID: 0-7), shown here.
SELECT fact_1_id,
       fact_2_id,
       fact_3_id,
       SUM(sales_value) AS sales_value,
       GROUPING_ID(fact_1_id, fact_2_id, fact_3_id) AS grouping_id
FROM   dimension_tab
GROUP BY CUBE(fact_1_id, fact_2_id, fact_3_id)
ORDER BY fact_1_id, fact_2_id, fact_3_id;
If we only need a few of these levels of subtotaling we can use the GROUPING SETS expression and specify exactly which ones we need, saving us having to calculate the whole cube. In the following query we are only interested in subtotals for the "FACT_1_ID, FACT_2_ID" and "FACT_1_ID, FACT_3_ID" groups.
SELECT fact_1_id,
       fact_2_id,
       fact_3_id,
       SUM(sales_value) AS sales_value,
       GROUPING_ID(fact_1_id, fact_2_id, fact_3_id) AS grouping_id
FROM   dimension_tab
GROUP BY GROUPING SETS((fact_1_id, fact_2_id), (fact_1_id, fact_3_id))
ORDER BY fact_1_id, fact_2_id, fact_3_id;

 FACT_1_ID  FACT_2_ID  FACT_3_ID SALES_VALUE GROUPING_ID
---------- ---------- ---------- ----------- -----------
         1          1                4363.55           1
         1          2                4794.76           1
         1          3                4718.25           1
         1          4                5387.45           1
         1          5                5027.34           1
         1                     1      2737.4           2
         1                     2     1854.29           2
         1                     3     2090.96           2
         1                     4     2605.17           2
         1                     5     2590.93           2
         1                     6      2506.9           2
         1                     7     1839.85           2
         1                     8     2953.04           2
         1                     9     2778.75           2
         1                    10     2334.06           2
         2          1                5652.84           1
         2          2                4583.02           1
         2          3                5555.77           1
         2          4                5936.67           1
         2          5                4508.74           1
         2                     1     3512.69           2
         2                     2     2847.94           2
         2                     3      2972.5           2
         2                     4     2534.06           2
         2                     5     3115.99           2
         2                     6     2775.85           2
         2                     7     2208.19           2
         2                     8     2358.55           2
         2                     9     1884.11           2
         2                    10     2027.16           2

30 rows selected.

SQL>
Notice how we have gone from returning 198 rows with 8 subtotal levels in the cube, to just 30 rows with 2 subtotal levels.

Composite Columns

ROLLUP and CUBE consider each column independently when deciding which subtotals must be calculated. For ROLLUP this means stepping back through the list to determine the groupings.
ROLLUP (a, b, c)
(a, b, c)
(a, b)
(a)
()
CUBE creates a grouping for every possible combination of columns.
CUBE (a, b, c)
(a, b, c)
(a, b)
(a, c)
(a)
(b, c)
(b)
(c)
()
Composite columns allow columns to be grouped together with braces so they are treated as a single unit when determining the necessary groupings. In the following ROLLUP columns "a" and "b" have been turned into a composite column by the additional braces. As a result the group of "a" is not longer calculated as the column "a" is only present as part of the composite column in the statement.
ROLLUP ((a, b), c)
(a, b, c)
(a, b)
()

Not considered:
(a)
In a similar way, the possible combinations of the following CUBE are reduced because references to "a" or "b" individually are not considered as they are treated as a single column when the groupings are determined.
CUBE ((a, b), c)
(a, b, c)
(a, b)
(c)
()

Not considered:
(a, c)
(a)
(b, c)
(b)
The impact of this is shown clearly in the follow two statements, whose output is shown here and here. The regular cube returns 198 rows and 8 groups (0-7), while the cube with the composite column returns only 121 rows with 4 groups (0, 1, 6, 7)
-- Regular Cube.
SELECT fact_1_id,
       fact_2_id,
       fact_3_id,
       SUM(sales_value) AS sales_value,
       GROUPING_ID(fact_1_id, fact_2_id, fact_3_id) AS grouping_id
FROM   dimension_tab
GROUP BY CUBE(fact_1_id, fact_2_id, fact_3_id)
ORDER BY fact_1_id, fact_2_id, fact_3_id;

-- Cube with composite column.
SELECT fact_1_id,
       fact_2_id,
       fact_3_id,
       SUM(sales_value) AS sales_value,
       GROUPING_ID(fact_1_id, fact_2_id, fact_3_id) AS grouping_id
FROM   dimension_tab
GROUP BY CUBE((fact_1_id, fact_2_id), fact_3_id)
ORDER BY fact_1_id, fact_2_id, fact_3_id;

Concatenated Groupings

Concatenated groupings are defined by putting together multiple GROUPING SETS, CUBEs or ROLLUPs separated by commas. The resulting groupings are the cross-product of all the groups produced by the individual grouping sets. It might be a little easier to understand what this means by looking at an example. The following GROUPING SET results in 2 groups of subtotals, one for the fact_1_id column and one for the fact_id_2 column.
SELECT fact_1_id,
       fact_2_id,
       SUM(sales_value) AS sales_value,
       GROUPING_ID(fact_1_id, fact_2_id) AS grouping_id
FROM   dimension_tab
GROUP BY GROUPING SETS(fact_1_id, fact_2_id)
ORDER BY fact_1_id, fact_2_id;

 FACT_1_ID  FACT_2_ID SALES_VALUE GROUPING_ID
---------- ---------- ----------- -----------
         1               24291.35           1
         2               26237.04           1
                    1    10016.39           2
                    2     9377.78           2
                    3    10274.02           2
                    4    11324.12           2
                    5     9536.08           2

7 rows selected.

SQL>
The next GROUPING SET results in another 2 groups of subtotals, one for the fact_3_id column and one for the fact_4_id column.
SELECT fact_3_id,
       fact_4_id,
       SUM(sales_value) AS sales_value,
       GROUPING_ID(fact_3_id, fact_4_id) AS grouping_id
FROM   dimension_tab
GROUP BY GROUPING SETS(fact_3_id, fact_4_id)
ORDER BY fact_3_id, fact_4_id;

 FACT_3_ID  FACT_4_ID SALES_VALUE GROUPING_ID
---------- ---------- ----------- -----------
         1                6250.09           1
         2                4702.23           1
         3                5063.46           1
         4                5139.23           1
         5                5706.92           1
         6                5282.75           1
         7                4048.04           1
         8                5311.59           1
         9                4662.86           1
        10                4361.22           1
                    1     4718.55           2
                    2      5439.1           2
                    3      4643.4           2
                    4      4515.3           2
                    5     5110.27           2
                    6     5910.78           2
                    7     4987.22           2
                    8     4846.25           2
                    9     5458.82           2
                   10      4898.7           2

20 rows selected.

SQL>
If we combine them together into a concatenated grouping we get 4 groups of subtotals. The output of the following query is shown here. 
SELECT fact_1_id,
       fact_2_id,
       fact_3_id,
       fact_4_id,
       SUM(sales_value) AS sales_value,
       GROUPING_ID(fact_1_id, fact_2_id, fact_3_id, fact_4_id) AS grouping_id
FROM   dimension_tab
GROUP BY GROUPING SETS(fact_1_id, fact_2_id), GROUPING SETS(fact_3_id, fact_4_id)
ORDER BY fact_1_id, fact_2_id, fact_3_id, fact_4_id;
The output from the previous three queries produce the following groupings.
GROUPING SETS(fact_1_id, fact_2_id) 
(fact_1_id)
(fact_2_id)

GROUPING SETS(fact_3_id, fact_4_id) 
(fact_3_id)
(fact_4_id)

GROUPING SETS(fact_1_id, fact_2_id), GROUPING SETS(fact_3_id, fact_4_id) 
(fact_1_id, fact_3_id)
(fact_1_id, fact_4_id)
(fact_2_id, fact_3_id)
(fact_2_id, fact_4_id)
So we can see the final cross-product of the two GROUPING SETS that make up the concatenated grouping. A generic summary would be as follows.
GROUPING SETS(a, b), GROUPING SETS(c, d) 
(a, c)
(a, d)
(b, c)
(b, d)



Oracle Database 10g: New Features For Administrators OCP Exam Articles




Automatic Database Diagnostic Monitor (ADDM) in Oracle Database 10g - This article describes several methods for producing reports from the Automatic Database Diagnostic Monitor (ADDM) in Oracle 10g.
Automatic SQL Tuning in Oracle Database 10g - This article the discusses the new features which automate the tuning of SQL statements in Oracle 10g.
Automatic Storage Management (ASM) in Oracle Database 10g - Automatic Storage Management (ASM) is a new feature introduced in Oracle 10g to simplify the storage and administration of Oracle files.
Automatic Workload Repository (AWR) in Oracle Database 10g - The automatic workload repository (AWR) is the latest evolution of statspack which provides the central information store for all Oracle 10g self-tuning functionality.
Database Security Enhancements in Oracle Database 10g - Oracle 10g contains many improvements to Virtual Private Database (VPD), Fine Grained Auditing (FGA) and data encryption.
DBMS_FILE_TRANSFER Package in Oracle Database 10g - Use this new API to make binary copies of files on the local server or to transfer files between the local server and remote servers.
Flashback New Features and Enhancements in Oracle Database 10g - Use the new and enhanced features of flashback technology to view old data, track data changes and recover from accidental data loss without reverting to LogMiner or point in time recoveries.
Improved VLDB Support in Oracle Database 10g - Take advantage of the improvements in Very Large Database (VLDB) support in Oracle 10g.
Installation Enhancements in Oracle Database 10g - This article provides a brief description of the installation enhancements which are relevant to the OCP upgrade exam for Oracle 10g.
MERGE Statement Enhancements in Oracle Database 10g - Learn about the enhancements to the merge statement in Oracle Database 10g.
Oracle Data Pump in Oracle Database 10g - Test drive the Oracle 10g replacement for the EXP and IMP utilities.
Performance Tuning Enhancements in Oracle Database 10g - Oracle 10g includes many performance tuning enhancements which take some of the mystery out of being a DBA.
PL/SQL Enhancements in Oracle Database 10g - Get to grips with the Oracle 10g enhancements to PL/SQL using simple cut & paste examples.
Resource Manager Enhancements in Oracle Database 10g - In Oracle 10g resource manager is more flexible and even easier to configure than before. Check out the enhancements here.
RMAN Enhancements in Oracle Database 10g - Check out the large number of enhancements in the Oracle 10g version of RMAN. If you are not currently using this tool you're running out of excusses.
Services in Oracle Database 10g - Run applications on a subset of Real Application Cluser (RAC) nodes using services.
Server Configuration in Oracle Database 10g - Oracle 10g includes some changes relating to basic server configuration which are relevant to the OCP upgrade exam.
Scheduler in Oracle Database 10g - Oracle Database 10g includes a flexible scheduler to replace the DBMS_JOB package.
Space, Object, and Transaction Management in Oracle Database 10g - A description of the new features relating to space, object and transaction management in Oracle 10g.
SQL Access Advisor in Oracle Database 10g - Use this advisor to get suggestions for indexes and materialized views that might help system performance.
SQL*Plus Enhancements in Oracle Database 10g - Check out enhancements to SQL*Plus in Oracle Database 10g.
Tablespace Management Enhancements in Oracle Database 10g - Oracle 10g includes several small but neat enhancements related to tablespace management.
Upgrading to Oracle Database 10g - This article provides a brief overview of the areas involved in upgrading existing databases to Oracle 10g.




How To Pass a Parameter to a Cursor?
When you define a cursor, you can set a formal parameter in the cursor. The formal parameter will be replaced by an actual parameter in the OPEN cursor statement. Here is a good example of a cursor with two parameters:
CREATE OR REPLACE PROCEDURE FYI_CENTER AS
  CURSOR emp_cur(low NUMBER, high NUMBER)
    IS SELECT * FROM employees WHERE salary >= low
      AND salary <= high;
BEGIN
  FOR row IN emp_cur(12000,15000) LOOP
    DBMS_OUTPUT.PUT_LINE(row.first_name || ' '
      || row.last_name 
      || ': ' || row.salary); 
  END LOOP; 
END;
/
How To Use Attributes of the Implicit Cursor?
Right after executing a DML statement, you retrieve any attribute of the implicit cursor by using SQL%attribute_name, as shown in the following tutorial exercise:
CREATE TABLE student (id NUMBER(5) PRIMARY KEY,
  first_name VARCHAR(80) NOT NULL,
  last_name VARCHAR(80) NOT NULL);
Table created.

DECLARE
  id NUMBER;
  first_name CHAR(10);
BEGIN
  id := 29;
  first_name := 'Bob';
  INSERT INTO student VALUES(id, first_name, 'Henry');
  first_name := 'Joe';
  INSERT INTO student VALUES(id+1, first_name, 'Bush');
  DBMS_OUTPUT.PUT_LINE('# of rows inserted: '
    || SQL%ROWCOUNT);
 
  first_name := 'Fyi';
  UPDATE student SET first_name = first_name WHERE id = 29;
  IF SQL%FOUND THEN
      DBMS_OUTPUT.PUT_LINE('# of rows updated: '
        || SQL%ROWCOUNT);
  END IF;

  UPDATE student SET first_name = first_name
    WHERE id = id+1;
  IF SQL%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('No records updated.');
  END IF;
    
  DELETE FROM student WHERE id = id;                    
  DBMS_OUTPUT.PUT_LINE('# of rows deleted: '
    || SQL%ROWCOUNT);
END;
How To Define an Explicit Cursor?
An explicit cursor must be defined in the declaration part of a procedure or function with the CURSOR ... IS statement as shown in the following sample script:
DECLARE
  CURSOR c_list IS SELECT * FROM countries;
  CURSOR t_list IS SELECT * FROM employees
    WHERE employee_id = 100;
BEGIN
  NULL;
END;
/
Cursor Variable?
A cursor variable is a variable of a specific REF CURSOR data type, which is a pointer to a data structure resource connects to query statement result, similar to the CURSOR data type.. The advantage of using cursor variables is that cursor variables can be used as variables to pass between procedures and functions.
Define a Cursor Variable?
To define cursor variable, you must decide which REF CURSOR data type to use. There are 3 ways to select a REF CURSOR data type:
  • Define your own specific REF CURSOR types using the TYPE ... RETURN statement.
  • Define your own generic REF CURSOR type using the TYPE ... statement.
  • Use the system defined REF CURSOR type: SYS_REFCURSOR.
The follwoing tutorial exercise defines 3 cursor variables in 3 different ways:
CREATE OR REPLACE PROCEDURE FYI_CENTER AS
  TYPE emp_ref IS REF CURSOR RETURN employees%ROWTYPE;
  TYPE any_ref IS REF CURSOR;
  emp_cur emp_ref;
  any_cur any_ref;
  sys_cur SYS_REFCURSOR;
BEGIN
  NULL;
END;
/
Open a Cursor Variable?
A cursor variable must be opened with a specific query statement before you can fetch data fields from its data rows. To open a cursor variable, you can use the OPEN ... FOR statement as shown in the following tutorial exercise:
CREATE OR REPLACE PROCEDURE FYI_CENTER AS
  TYPE emp_ref IS REF CURSOR RETURN employees%ROWTYPE;
  TYPE any_ref IS REF CURSOR;
  emp_cur emp_ref;
  any_cur any_ref;
  sys_cur SYS_REFCURSOR;
BEGIN
  OPEN emp_cur FOR SELECT * FROM employees;
  OPEN any_cur FOR SELECT * FROM employees;
  OPEN sys_cur FOR SELECT * FROM employees;
  CLOSE sys_cur;
  CLOSE any_cur;
  CLOSE emp_cur;
END;
/
Cursor Variables Are Easier to Use than Cursors?
Cursor variables are easier to use than cursors because:
  • Cursor variables are easier to define. No need to give a specific query statement.
  • Cursor variables are easier to open. You can specify the query dynamically at the time of open.
  • Cursor variables can be passed into procedures or functions.

Oracle Database 11g New Features

It hardly seems five minutes since Oracle 10g was released and we were discussing the new features of that, but now we have Oracle 11g Database. Below are the main new features of Oracle Database 11g.

Oracle Real Application Testing

This feature makes it easier to test systems before and after database upgrades and hardware and operating system changes so you can catch and fix problems more quickly.

Enhanced Partitioning

These capabilities include partitioning by parent/child references; partitioning by virtual columns, more composite partitioning choices; and interval partitioning, which automatically generates new partitions based on intervals, such as every day or every month.

Advanced Compression

Oracle Database 11g supports data compression for update, insert and delete operations commonly used in online transaction processing (OLTP) applications.  Oracle Database 11g table compression improves database performance with better use of memory for caching data and reduced I/O for table scans.

Flashback Data Archive

Part of the Oracle Total Recall option, this feature lets you query data in selected tables “as of” earlier times, which adds a time dimension to your data for change tracking, information lifecycle management, auditing and compliance.  Flashback data archive stores change data automatically to enable fast query access to old versions of the data.

Oracle SecureFiles

This feature lets you store large objects such as images, large text objects, or advanced datatypes including XML, medical imaging, and geospatial raster objects inside the database.  The performance is apparently comparable to file systems, and Oracle SecureFiles provides advanced functionality including intelligent compression, transparent encryption and transparent deduplication.

PL/SQL Function Result Cache

This new PL/SQL function allows you cache frequently retrieved data, and get the data from the block buffer cache, instead of checking to see if the query has already been parsed, finding the data in the buffer and then returning it.