Internals Of Index Online Building

Background:

Whenever a session seems stuck or doesn’t comes out many DBA’s /Developers have a habit to cancel their session (CTRL C) or kill the session. My bad, I did the same when I was testing rebuilding of an index.

Sql_Sesssion1> Select object_name,object_id,object_type From user_objects;

OBJECT_NAME                                OBJECT_ID                           OBJECT_TYPE

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

IDX                                                        61837                                         INDEX

ALL_OBJ                                              61836                                        TABLE

In session 2:

Sql_Sesssion2> Alter index idx rebuild online;

CTRL+C <------

And I got this

ORA-01013 user requested cancel of current operation.

But when I checked my 1st session again with same query I got more number of objects present in my schema (Journal table and IOT index). 

Sql_Sesssion1> Select object_name, object_id, object_type From user_objects;

OBJECT_NAME                                                      OBJECT_ID                           OBJECT_TYPE

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

IDX                                                                               61837                                                INDEX

ALL_OBJ                                                                     61836                                              TABLE

SYS_IOT_TOP_61851                                               61852                                               INDEX

SYS_JOURNAL_61837                                             61851                                              TABLE

Wondering why these objects get created???

So this post is of mine is intended to describe online index rebuilding internals and a brief of journal table.

The way online index build (OIB) works is by creating an IOT journal table to keep track of changes while the rebuild is in progress and merge all the changes from journal table to complete index build operation.

Journal table is the index organized table and their name will essentially be SYS_JOURNAL_<index object_id>. It gets dropped automatically when the job completes successfully or cleans or cancelled successfully.

Refer above query, IDX object_id is 61837 and Journal table named as SYS_JOURNAL_61837

The structure of Journal table looks like

Sql_Sesssion1> DESC  SYS_JOURNAL_61837

Name                                  Null?                  Type

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

 C0                                    NOT NULL         NUMBER

 C1                                    NOT NULL         NUMBER

 OPCODE                                                     CHAR(1)

 PARTNO                                                     NUMBER

 RID                          NOT NULL         ROWID

Where

"OPCODE" column represents the type of operation like "I" for Insert and "D" for Delete. Any update operation of index key columns would be converted to "DELETE" and "INSERT" in the journal table.

"PARTNO" column represents partition number of the underlying table.

Internal Processing:

OIB will get in the DML queue to lock the table exclusively while preventing the new DML's to go through, once all the active transactions (ones which were initiated before the OIB) are completed, OIB will create the journal IOT table and release the exclusive table lock (it'll still keep the share lock on the table to prevent any other DDL's) for DML's to continue.

As part of journal table creation, Oracle would create an internal trigger on the primary table to record all the changes to the journal table. Along with using all the index columns, journal table will add "ROWID" to that list to make it as primary key.

Among all the changes to a given row for any of the index key columns, only the most recent change for that record is visible in the journal table. 

While rest of the user sessions populate journal table with the ongoing changes for index key columns, OIB session would be reading the table in consistent mode (as of the time journal table is created) to create the index followed by the final merge operation.

During the merge process, Oracle will read the journal table (IOT) leaf blocks from left to right to merge those changes with the index being built. As the journal table leaf block changes are applied, once a leaf block is fully consumed, its reference will be deleted from the branch block.

This process will continue until all leaf blocks are consumed and when it comes to the last leaf block, Oracle would stop all the DML's again to do the final merge and drop the journal table before releasing the mode 6 exclusive table lock.

As each leaf block is consumed, Oracle would mark each entry as deleted. If more DML's happen while Oracle is doing the merge, it'll do one more pass of walking through the leaf blocks, this process continues until the merge process is all done.

Please Note: In 11g, major changes were introduced to handle online index rebuilding. The above post is meant for previous version to 11g.

If you find this article interesting then please provide your feedback through comments.

IN V/S EXISTS

IN v/s Exists:  Which one is Appropriate/Faster?

Background:

IN: It’s a multiple-row operator used in a query.

Select * From table_name Where column_name IN( value1,Value2)

EXISTS: Operator is frequently used with correlated sub-queries to test whether a value retrieved by the outer query exists in the results set of the values retrieved by the inner query.

Select column1, column2, FROM   table1 outer

 Where EXISTS (Select ‘X’ FROM    table2 Where   column1=outer. column2);

Note that the inner SELECT query does not need to return a specific value, so a constant can be selected. From a performance standpoint, it is faster to select a constant than a column.

Correlated Sub Queries: Correlated sub-queries are used for row-by-row processing. Each sub-query is executed once for every row of the outer query.

Before proceeding further with the topic I would like to show you “How operators (IN/EXISTS) are processed internally”.

IN Operator

Select * from t1 Where x IN (Select y from t2 )

is typically processed as:

Select * From t1, (Select distinct y From T2 ) t2

 Where t1.x = t2.y;

The sub-query is evaluated, distinct'ed, indexed (or hashed or sorted) and then joined to the original table -- typically.

Exists Operator :

Select * from t1 Where exists ( Select null from t2 Where y = x )

That is processed more like:

   for x in ( Select * From t1 )

   loop

      if ( EXISTS ( Select null From t2 Where y = x.x )

      then

      output the records

      end if

   end loop

Note here: Whenever Exists is used, always Full Scan of outer table is performed but the first query can make use of an index.

So which is more appropriate/faster and when?

To deal with this I created one test plan as below( One table is big and other is relatively small)

Create Table test As Select * From all_objects;

Create Index test_idx On test(object_id);

Create Table test1 As Select * From all_objects Where Rownum < 1000;

Create Index test1_idx On test2(object_id);

Analyze Table test Compute Statistics For Table For All Indexes For All Indexed Columns;

Analyze Table test1 Compute Statistics For Table For All Indexes For All indexed Columns;

SQL> Select Count(*) From test;

              COUNT(*)

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

              1306432

 SQL> Select Count(*) From test1;

                COUNT(*)

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

                    999

So, I executed my test plan.

SQL> Set Timing On

SQL>Select Count (subobject_name) From test Where EXISTS (Select null From test1 where test1.object_id = test.object_id);

COUNT (SUBOBJECT_NAME)

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

                    0

Elapsed: 00:00:02.94

SQL>Select Count (subobject_name) From test Where object_id IN (Select object_id From test)

COUNT(SUBOBJECT_NAME)

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

                    0

Elapsed: 00:00:02.34

Clearly a 60 micro second’s difference between the queries.

In 1^st statement: (Outer query is using test table (which is bigger than test1 ) Here each sub-query is executed once for every row of the outer query. So a full table scan is probing a table scan in the inner query and it clearly shows it is inefficient to use Exist operator here

Whereas,

In 2^nd query the inner query is accessing small table test1 and executing it without bothering about the outer query.

 So, above result clearly shows if the outer query is "big" and the inner query is "small, IN operator is generally more efficient then EXISTS.

SQL>Select Count (subobject_name) From test1 Where EXISTS (Select null From test Where test1.object_id = test.object_id)

COUNT(SUBOBJECT_NAME)

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

                    0

Elapsed: 00:00:02.34

SQL>Select Count (subobject_name) From test1 Where object_id IN (Select object_id From test)

COUNT(SUBOBJECT_NAME)

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

                    0

Elapsed: 00:00:02.84

Difference of 50 micro seconds.

In 1^st statement: (Outer query is using test1 table (which is smaller than test table) Here each sub-query is executed once for every row of the outer query. the table test1 is relatively small and executing (Select null From test Where test.object_id = test1.object_id) is fast (nice index on test(object_id)). Then the EXISTS will be faster as the time to full scan test and do the index probe into test could be less than the time to simply full scan test to build the sub-query we need to distinct on.

Whereas,

In 2^nd query the inner query is accessing bigger table test and executing it without bothering about the outer query.Clearly we can see IN is inappropriate in this condition.

So, above result clearly shows if the outer query is "small" and the inner query is "big”, EXISTS operator is generally more efficient then IN.

A very important point to note here:

Where EXISTS will find the first row faster in general then the IN.

The IN will get the last row (all rows) faster than the where exists.  If your optimizer mode is set to FIRST_ROW then EXISTS might totally out cast IN.

If you find this article interesting then please provide feedback through your comments. 

Oracle Security Issues: "Prevent User from Altering Password of Sys/System and Own"

In today’s article I will discuss two securities issues:

A) How to prevent user with ALTER USER privileges from changing password of sys and system.

B) How to prevent the user to alter his/her password.

I'll start with discussing “How to prevent user with alter user privileges from changing password of sys and system”

If suppose one of the users in Db has “ALTER USER” privilege and he is misusing it by changing the passwords of another USER then, is it possible to stop a user from changing SYS/SYTEM password. This is an issue as the system privilege ALTER USER allows password changes of any user.

One way is to revoke “ALTER USER” grant. But it's always better to take preventive measures.

For this we have a native way. Here our PLSQL code will come in handy.

I came up with the idea of a DDL trigger that fires on the use of an ALTER USER command and then blocks the user.

First I need to create my test user.

Login as SYS user:

SQL> create user test identified by test;

User created.

SQL> grant connect to test;

Grant succeeded.

SQL> grant alter user to test; 

Grant succeeded.

SQL> alter user system identified by manager; 

User altered.

SQL> show user

USER is "TEST"

Now I will create a trigger “STOP_ALTER_PASS” in SYS schema on test user (TEST schema has “ALTER USER” privilege)

Login as SYS user:

SQL> CREATE or REPLACE TRIGGER STOP_ALTER_PASS

BEFORE ALTER on test.schema

BEGIN

IF ora_sysevent='ALTER' and ora_dict_obj_type ='USER' and

(ora_dict_obj_name = 'SYSTEM' or ora_dict_obj_name = 'SYS')

THEN

RAISE_APPLICATION_ERROR(-20003,

'You Are Not Allowed To Alter SYSTEM/SYS User');

END IF;

END;

/

Note: “ora_sysevent” is the way you can handle the “SYSTEM EVENT”. When a trigger is fired, you can retrieve certain attributes about the event that fired the trigger. Each attribute is retrieved by a function call. To make these attributes available, you must first run the CATPROC.SQL script.

Let’s check the robustness of above trigger.

SQL> CONN TEST/TEST

Connected.

SQL> alter user system identified by manager

                                *

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-20003: You are not allowed to alter SYSTEM/SYS user.

ORA-06512: at line 5

SQL> show user

USER is "TEST"

So now it is possible to stop a user from changing SYS/SYTEM password using our native code.

 But,

B) What about if someone asks you to prevent user to alter his/her own password?

For this activity I will use Oracle’s Profile and PASSWORD_VERIFY_FUNCTION.

PASSWORD_VERIFY_FUNCTION: There is a default password verify function under $ORACLE_HOME/rdbms/admin with filename utlpwdmg.sql. This script creates a password verify function named "verify_function"

PASSWORD_VERIFY_FUNCTION verify_function; (Usage of Function)  

I will create my own function and refer it to PASSWORD_VERIFY_FUNCTION profile as a parameter.

So going ahead with above info, first I will revoke the alter user privilege from TEST schema.

SQL> revoke alter user from test;

Revoke succeeded.

Then I create password verify funtion

CREATE OR REPLACE FUNCTION verify_passwd (

username VARCHAR2

, password VARCHAR2

, old_password VARCHAR2) RETURN boolean IS

BEGIN

raise_application_error(-20009, 'ERROR: Password cannot be changed');

END;

/

SQL> CREATE PROFILE testpwd LIMIT PASSWORD_VERIFY_FUNCTION verify_passwd; -----àHere I created a profile and passed my function to PASSWORD_VERIFY_FUNCTION as a parameter.



SQL> alter user test profile testpwd;

SQL> conn test/test

I will try to change my own password as a test user

SQL> alter user test identified by test;

alter user test identified by test

*

ERROR at line 1:

ORA-28221: REPLACE not specified

SQL> alter user test identified by test replace test; ----------------- If you don’t have alter user privilege then you have to use 'replace' keyword to change your password(In case password verify function is on).

SQL> alter user test identified by test replace test;  

alter user test identified by test replace test
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20009: ERROR: Password cannot be changed

SQL> show user

USER is "TEST"

This brings us to end of the topic .Hope you enjoy it. What I have written is purely my understanding so if you find any mistakes please correct me through your comments.