Oracle Pivot and Unpivot Operators

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.

Ready? Here it goes:

Pivot and Unpivot Operators

Pivoting tables are now possible in 11g through PIVOT clause. The PIVOT operator takes data in separate rows, aggregates it and converts it into columns. Prior to 11g new functions it was possible to accomplish by using DECODE combined with some aggregate function like SUM.

Also, Adding the XML keyword to the PIVOT operator allows us to convert the generated pivot results to XML format. It also makes the PIVOT a little more flexible, making possible to replace the hard coded IN clause with a subquery, or the ANY commands.

In same way, the UNPIVOT operator converts column-based data into separate rows. Some important considerations about feature:

  • Column names are required. These can be set to any name not currently in the driving table.
  • The columns to be unpivoted must be named in the IN clause.
  • By default the EXCLUDE NULLS clause is used. To override the default behavior use the INCLUDE NULLS clause.
  • The following query shows the inclusion of the INCLUDE NULLS clause.

For more information and examples: https://oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1

Cheers!

Oracle No Segment Indexes

Did you know we have this? Yes, and this is since 11g.

As you know, the process of tuning SQL statements often requires the testing of alternate indexing strategies to see the effect on execution plans.

Adding extra indexes to large tables can take a considerable amount of time and disk space besides possibly changing known execution plans and possibly affecting all the application behavior.

This can be problematic when you are trying to identify problems on a production system. Some of those problems can be solved by using invisible indexes, but they still would require creation and segment allocation, but also being updated by the application itself.

To solve that, virtual indexes have no associated segment, so the creation time and associated disk space are irrelevant. In addition, it is not seen by other sessions, so it doesn’t affect the normal running of your system, similar to invisible indexes, but also don’t not even to be updated, as invisible indexes need to be.

To create the virtual index consists in simply add the NOSEGMENT clause to the CREATE INDEX statement, for example:

CREATE INDEX index_name ON table(column_name) NOSEGMENT;

However, to make the virtual index available we must set the _use_nosegment_indexes parameter. Another important mention is that the virtual indexes don’t appear in the USER_INDEXES view, but can be found in the USER_OBJECTS view.

Cheers!

Oracle Compound Triggers

Did you know that?

Since 11g, Oracle has something called “compound trigger”, that allows a single trigger to be used for combining actions for different timing points for a specific object.

The individual timing points share a single global declaration section, whose state is maintained for the lifetime of the statement. Once a statement ends, due to successful completion or an error, the trigger state is cleaned up. In previous releases this type of functionality was only possible by defining multiple triggers whose code and global variables were defined in a separate package.

The triggering actions are defined in the same way as any other DML trigger, with the addition of the COMPOUND TRIGGER clause. The main body of the trigger is made up of an optional global declaration section and one or more timing point sections, each of which may contain a local declaration section whose state is not maintained. For example:

CREATE OR REPLACE TRIGGER trigger_example
    FOR action ON table_name COMPOUND TRIGGER
    global_variable VARCHAR2(30);
    BEFORE STATEMENT IS BEGIN NULL; END BEFORE STATEMENT;
    BEFORE EACH ROW IS BEGIN NULL; END BEFORE EACH ROW;
    AFTER EACH ROW IS BEGIN NULL; END AFTER EACH ROW;
    AFTER STATEMENT IS BEGIN NULL; END AFTER STATEMENT;
END trigger_example;
/

A good summary with detailed information and examples can be found (as always) here: https://oracle-base.com/articles/11g/trigger-enhancements-11gr1

Cheers!

Segment Creation on Demand or Deferred Segment Creation

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.

Ready? Here it goes:

Segment Creation on Demand or Deferred Segment Creation

I also talked about it in post EXP Missing Tables on 11.2.

Segment creation on demand, or deferred segment creation as it is also known, is a space saving feature of Oracle Database 11g Release 2. When non-partitioned tables are created, none of the associated segments (table, implicit index and LOB segments) are created until rows are inserted into the table. For systems with lots of empty tables, this can represent a large space saving. This functionality can be controlled by the DEFERRED_SEGMENT_CREATION initialization parameter, which is set to TRUE by default.

Continue reading

11g Improvements: New Grants

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So I decided to make a serie of posts with really quick notes about some of them.
You can see all posts in this serie in my page of posts and some others more.

Ready? Here it goes, today actually 2 small things:

Execute Grant on Directories

In 10g was possible to grant READ and/or WRITE, but this also allowed executing the ORACLE_LOADER access driver. In 11g, only a user that has been given EXECUTE access to the directory object is allowed to run programs in it. This should be granted using:

grant EXECUTE on DIR_EXAMPLE;

DATABASE_ROLE constant for SYS_CONTEXT

In 11g the context procedure can also give DATABASE_ROLE, as it might be used as check for certain procedures to run only on standbys or to avoid it, for example.

Between values are: PRIMARY, PHYSICAL STANDBY, LOGICAL STANDBY and SNAPSHOT STANDBY. This can be executed this way:

SELECT sys_context('USERENV', 'DATABASE_ROLE') FROM dual;

You probably know about that, right?
Anyway, always good to remember!

Cheers!

PL/SQL Generalized Invocation

Did you know we have that since 11g? The generalized invocation allows a subtype to invoke a method of a parent type (supertype) using the following syntax:

(SELF AS supertype_name).method_name

Check the example below to understand it. First, creating original type:

CREATE OR REPLACE TYPE type_test AS OBJECT (MEMBER FUNCTION return_text RETURN VARCHAR2) NOT FINAL;
/

CREATE OR REPLACE TYPE BODY type_test AS
MEMBER FUNCTION return_text RETURN VARCHAR2 IS
BEGIN RETURN 'This is the original text.'; END;
END;
/

And now creating a subtype of this object, which adds a new attribute and method as well as overriding the member’s function.

CREATE OR REPLACE TYPE subtype_test UNDER type_test (OVERRIDING MEMBER FUNCTION return_text RETURN VARCHAR2);
/

CREATE OR REPLACE TYPE BODY subtype_test AS
OVERRIDING MEMBER FUNCTION return_text RETURN VARCHAR2 IS
BEGIN RETURN (self AS type_test).return_text || ' This is an additional subtype text.'; END;
END;
/

And when calling:

SET SERVEROUTPUT ON
DECLARE
my_subtype subtype_test;
BEGIN
DBMS_OUTPUT.put_line(my_subtype.show_attributes);
END;
/
This is the original text. This is an additional subtype text.

A type can invoke the member functions of any parent type in this way, regardless of the depth of the inheritance.

Pretty nice, right?
Cheers!

PLSQL: Working with NULLs and Function NHT_VALUE

Did you know this function?

NTH_VALUE returns the result of expression on value of the nth row in the window defined by the analytic clause. The returned value has the data type of the expression. RESPECT NULLS or IGNORE clauses can be used to determine whether null values of expression are included in or eliminated from the calculation. The default is RESPECT NULLS. Also clauses FROM FIRST or FROM LAST determines whether the calculation begins at the first or last row of the window. The default is FROM FIRST.

The function returns NULL if the data source window has fewer than n rows. If n is null, then the function returns an error.

Example:

SELECT prod_id, channel_id, MIN(amount_sold),
    NTH_VALUE(MIN(amount_sold), 2) OVER (PARTITION BY prod_id ORDER BY channel_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) nv
  FROM sales
  WHERE prod_id BETWEEN 13 and 16
  GROUP BY prod_id, channel_id;

   PROD_ID CHANNEL_ID MIN(AMOUNT_SOLD)         NV
---------- ---------- ---------------- ----------
        13          2           907.34      906.2
        13          3            906.2      906.2
        13          4           842.21      906.2
        14          2          1015.94    1036.72
        14          3          1036.72    1036.72
        14          4           935.79    1036.72
        15          2           871.19     871.19
        15          3           871.19     871.19
        15          4           871.19     871.19
        16          2           266.84     266.84
        16          3           266.84     266.84
        16          4           266.84     266.84
        16          9            11.99     266.84

 

For more information and examples, check: https://docs.oracle.com/cloud/latest/db112/SQLRF/functions114.htm#SQLRF30031

Cheers!

WHEN OTHERS -> NULL: Hidding your PLSQL Errors?

Are you using WHEN OTHERS -> NULL to hide your PLSQL errors?
Don’t be so sure…

WHEN OTHERS exception handlers that do nothing and don’t raise errors using RAISE or RAISE_APPLICATION_ERROR can often hide code failures that result in hard to identify bugs.

To avoid this, a new PL/SQL compiler warning was added in 11g to identify those kind of situations. Check example below:

SQL> ALTER SESSION SET plsql_warnings = 'enable:all';

Session altered.

SQL> CREATE OR REPLACE PROCEDURE warning_test AS
  2  BEGIN
  3    RAISE_APPLICATION_ERROR(-20000, 'This is an Exception!');
  4  EXCEPTION
  5    WHEN OTHERS THEN
  6      NULL;
  7  END;
  8  /

SP2-0804: Procedure created with compilation warnings

SQL> SHOW ERRORS
Errors for PROCEDURE OTHERS_TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/8      PLW-06009: procedure "WARNING_TEST" OTHERS handler does not end in
         RAISE or RAISE_APPLICATION_ERROR

Nice, right?!

There are also some other warnings improvements like:

  • New NO_DATA_NEEDED Predefined Exception: ORA-06548: For parallel access and pipelined table functions the caller of a pipelined function does not need more rows to be produced by the pipelined function.

Warnings:

  • Severe
    – 5018 – omitted optional AUTHID clause
    – 5018 – omitted optional AUTHID clause
    – 5019 – deprecated language element
    – 5020 – parameter name must be identified
  • Informative
    – 6016 – native code generation turned off (size/time)
    – 6017 – operation will raise an exception
    – 6018 – an infinity or NaN value computed or used

Cheers!

Cross-Session PL/SQL Function Result Cache

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.

Ready? Here it goes:

Cross-Session PL/SQL Function Result Cache

Since 11gR1 we have simple way to boost the performance of PL/SQL functions by saving the results of function calls for specific combinations of input parameters in the SGA:The cross-session PL/SQL function result cache.

The results can be reused by any session calling the same function with the same parameters. This can result in a significant performance boost when functions are called for each row in a SQL query, or within a loop in PL/SQL.

Ok, but how to do this? It’s as simple as adding the RESULT_CACHE clause:

CREATE OR REPLACE FUNCTION procedure_example (p_in IN NUMBER)
  RETURN NUMBER
  RESULT_CACHE

The RELIES_ON may be set in 11gR1 but is unnecessary in 11.2 as it automatically tracks dependencies and invalidates the cached results when necessary.

Nice, right?
Cheers!

Oracle SecureFiles | In 11.2 extended to Oracle Database File System – DBFS

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.

Ready? Here it goes:

Oracle SecureFiles | In 11.2 extended to Oracle Database File System – DBFS

In version 11g Oracle introduced SecureFiles, a new LOB storage architecture as replacement for BASICFILES LOBs’storage, being faster than Unix files to read/write. Lots of potential benefits for OLAP analytic workspaces are expected, as the LOBs used to hold AWs have historically been very slow to write. In addition, this object type is compliant to other mechanisms like deduplication, compression and encryption. Besides that, lock and concurrency model has been improved to manage those kind of objects. Other improvements like space management, reduced fragmentation, intelligent pre-fetching, new network layer, no LOB index contention, no high water mark contention and being easier to manage are important to mention.

Continue reading