Oracle 11g Semantic Hints

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.

Here is a quick summary I did for a client regarding new Semantic Hints on 11g..

11g New Semantic Hints

In Database version 11.2.0.1, Oracle introduced three new hints: IGNORE_ROW_ON_DUPKEY_INDEX, CHANGE_DUPKEY_ERROR_INDEX and RETRY_ON_ROW_CHANGE. They have different working ends but they all tries to avoid ORA-0001 in some specific situations. An important note is that, unlike almost all other hints, this hint has a semantic effect: it changes the actual behavior – not just the optimization – of the SQL. Oracle Docs says about this Hint:

“The CHANGE_DUPKEY_ERROR_INDEX, IGNORE_ROW_ON_DUPKEY_INDEX, and RETRY_ON_ROW_CHANGE hints are unlike other hints in that they have a semantic effect. The general philosophy explained in “Hints” does not apply for these three hints.”

Some specifics about each one:

  • IGNORE_ROW_ON_DUPKEY_INDEX: “When a unique key violation is encountered, a row-level rollback occurs and execution resumes with the next input row.”
  • CHANGE_DUPKEY_ERROR_INDEX: “The CHANGE_DUPKEY_ERROR_INDEX hint provides a mechanism to unambiguously identify a unique key violation for a specified set of columns or for a specified index. When a unique key violation occurs for the specified index, an ORA-38911 error is reported instead of an ORA-001.”
  • RETRY_ON_ROW_CHANGE: “When you specify this hint, the operation is retried when the ORA_ROWSCN for one or more rows in the set has changed from the time the set of rows to be modified is determined to the time the block is actually modified.”

The use of those hints, however, is only recommended for pretty specific ends and should be avoided if possible. Not only for having some related bugs, but to add some complexity to known operations like bulk inserts and similar.

Other important hint is the Result Cache Hint, mentioned some months ago, under Result Cache Post.

Cheers!

Oracle Read Only Tables: Do you use it?

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:

Read Only Tables

In previous Oracle releases, tables could be made to appear read-only to other users by only granting the SELECT object privilege to them, but the tables remained read-write for the owner. Oracle 11g allows tables to be marked as read-only using the ALTER TABLE command. Examples as per below:

ALTER TABLE table_name READ ONLY;
ALTER TABLE table_name READ WRITE;

In case of any other DML or DDL operation try to be ran, the following error will be prompted:

ORA-12081: update operation not allowed on table "OWNER"."TABLE_NAME"

This can be a very useful feature for specific situations where we need to guarantee that no changes be performed on table by applications, like a forced revoke on this specific table, even system grants be in place like “update any table”.

Cheers!

Regular Expressions with SUBEXPR

Hi All!

Did you know since 11g the REGEXP_INSTR and REGEXP_SUBSTR functions include a new SUBEXPR parameter that limits the pattern match to a specific subexpression in the search pattern?

Also, a new function, REGEXP_COUNT, returns the number of times the search pattern appears in source string.

Below a simple example of each one:

SQL> SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 3) FROM dual;

REGEXP_INSTR('1234567890','(123)(4(56)(78))',1,1,0,'I',3)
---------------------------------------------------------
                                                        5

SQL> SELECT REGEXP_SUBSTR('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 3) FROM dual;

RE
--
56

SQL> SELECT REGEXP_COUNT('123 123 123 123', '123', 1, 'i') FROM dual;

REGEXP_COUNT('123123123123','123',1,'I')
----------------------------------------
                                       4

Cheers!

Oracle: Create Disabled Triggers

Hey,
I believe you may know about this already, but in case its something new to you: Yes, this is possible since 11g!

Prior to 11g, it was only possible to create triggers in the enabled state, and after creating to disable them. Since 11g they can be explicitly enabled or disabled at creation time using clause ENABLE or DISABLE. As per example:

CREATE OR REPLACE TRIGGER disabled_trigger
BEFORE INSERT ON table_example
FOR EACH ROW
DISABLE
BEGIN NULL; END;
/

If didn’t know it yet. Np! You know it now! 😀

Cheers!

Oracle Invisible indexes

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 here is an 11g feature that is actually when known and I really like to use  when considering to remove indexes on clients or even “testing” a creation in production. How does that work?

Invisible indexes

Oracle 11g allows indexes to be marked as invisible. Invisible indexes are maintained like any other index, but they are ignored by the optimizer unless the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE at the instance or session level. Indexes can be created as invisible by using the INVISIBLE keyword, and their visibility can be toggled using the ALTER INDEX command. Examples of those commands are listed below:

CREATE INDEX index_name ON table_name(column_name) INVISIBLE;
ALTER INDEX index_name INVISIBLE;
ALTER INDEX index_name VISIBLE;

Cheers!

Oracle SQL Join Factorization Transformation (JFT)

Hi All!
Following the line of last week post, let’s talk today about the JFT…

Oracle SQL Join Factorization Transformation (JFT)

The UNION operator is great for merging the results of multiple queries that return similar rowsets, essentially executing each query separately and merging the results together into a single result set. Oracle 11g release 2 come with improvements for SQL optimizer on how Oracle handles UNION ALL performance with the new join factorization transformation (JFT). The Join Factorization Transformation applies only to UNION ALL queries.

The following example show how the optimizer improves the performance of UNION ALL by dynamically re-writing a UNION ALL query into a more efficient form using an in-line view:

Original Query:

select t1.c1, t2.c2     
    from t1, t2, t3     
    where t1.c1 = t2.c1 and t1.c1 > 1 and t2.c2 = 2 and t2.c2 = t3.c2    
union all     
    select t1.c1, t2.c2     
    from t1, t2, t4     
    where t1.c1 = t2.c1 and t1.c1 > 1 and t2.c3 = t4.c3;

Re-Written Query:

select t1.c1, VW_JF_1.item_2
     from t1, (select t2.c1 item_1, t2.c2 item_2
                    from t2, t3
                    where t2.c2 = t3.c2 and t2.c2 = 2
               union all
                    select t2.c1 item_1, t2.c2 item_2
                    from t2, t4
                    where t2.c3 = t4.c3) VW_JF_1
     where t1.c1 = VW_JF_1.item_1 and t1.c1 > 1;

Nice, right?
Cheers!

11g SQL transformations (CBQT, CNT, JE, JPPD)

Hi All!
Let’s save some time to review those?
They usually very important when analysing query performance, specially on 10046 traces. I separated those were included on 11g, to have it as subset. Here it goes:

New 11g SQL transformations (CBQT, CNT, JE, JPPD)

The Oracle cost-based optimizer CBO is constantly being improved in its ability to re-write and transform sub-optimal SQL queries. In general way, CBO work by replacing operations by equivalent optimal when possible, changing clauses or re-grouping to take advantage on in indexes, eliminating subqueries by replacing with joins and not null tests and similar. In 11g new SQL transformations were implemented looking to continue improving this process. They are:

  • Cost-Based Query Transformation (CBQT): This is one of the core mechanisms in CBO, that tries different transformed and untransformed variants of the query and runs them through the costing algorithm. Improvements were done in 11g, specially related to clauses NOT IN into NOT EXISTS.
  • Count Transformation (CNT): If all rows have a value on this column (not null), Oracle CBO can simply count the number of rows. There is no need to actually retrieve the column value. This transformation changes count(col) to count(*).
  • Join Elimination (JE): Join elimination is a query transformation of the Oracle optimizer to remove joins that are not required in a query. Released on 10g, Join Elimination got several improvements and new mechanisms in 11g and even more in 12c.
  • Join Predicate PushDown (JPPD): Allows a view to be joined with index-based nested-loop join method, which may provide a more optimal alternative. The following types of views supports predicate pushdown: UNION ALL/UNION view, Outer-joined view, Anti-joined view, Semi-joined view, DISTINCT view and GROUP-BY view.

Nice?

Hope you enjoy it!

PL/Scope – Did you know that?

Hi All!
I decided to make a serie of posts with really quick notes about some really awesome features we simply don’t use. Today’s one is about PL/Scope.
You can see all posts in this serie in my page of posts and some others more.

Ready? Here it goes:

PL/Scope

PL/Scope is a tool that gathers information about user defined identifiers at compile time. Collection of PL/Scope data is controlled by the PLSCOPE_SETTINGS parameter, which has a default setting of “IDENTIFIERS:NONE”. To enable collection, switch value to “IDENTIFIERS:ALL”. The data is stored in the SYSAUX tablespace, this space needs to be well planned and managed..

The PL/Scope data is available from the %_IDENTIFIERS views. The following query displays data gathered during the compilation of a test procedure:

COLUMN name FORMAT A30
SELECT LPAD(' ', level*2, ' ') || name AS name, type, usage, usage_id, line, col
FROM   user_identifiers
START WITH usage_context_id = 0
CONNECT BY PRIOR usage_id = usage_context_id;

NAME                           TYPE               USAGE         USAGE_ID       LINE        COL
------------------------------ ------------------ ----------- ---------- ---------- ----------
  TEST_PLSCOPE                 PROCEDURE          DECLARATION          1          1         11
    TEST_PLSCOPE               PROCEDURE          DEFINITION           2          1         11
      P_IN                     FORMAL IN          DECLARATION          3          1         25
      L_VAR                    VARIABLE           DECLARATION          4          2          3
      L_VAR                    VARIABLE           ASSIGNMENT           5          4          3
        P_IN                   FORMAL IN          REFERENCE            6          4         12
      L_VAR                    VARIABLE           ASSIGNMENT           7          6          3
        L_VAR                  VARIABLE           REFERENCE            8          6         12

8 rows selected.

(this script was taken from here)

Some additional information about it: The documentation states that some identifiers will not listed unless the STANDARD package is recompiled after the PLSCOPE_SETTINGS parameter is set properly. However there is some opening community discussions about invalidation of basic procedures not being revalidated again, even on use of urlrp procedure. This way, we don’t recommend to use it directly on Production environment without validating before in an equivalent environment and taking all caution measures as possible.

Hope you enjoy it. Cheers!

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!