When to use reverse index

In Oracle, there is an option to create index entries as reversed, which is called reverse key indexes. Oracle stores the index entries as their bytes reversed, except rowids.
Here I have explained the cases where we can use reverse key index which will improve performance.

When we have to use reverse key index?

1. Your database must be in RAC environment
2. You have a column populated by an increasing sequence
3. You delete some old rows from the table frequently
4. Do not do range scan on the reverse key indexed column.
5. You have contention issues on index blocks.

If you have column populate by increasing sequence, the new entries come to the same block when you are inserting. This will lead to contention for the same index blocks between nodes when concurrent sessions trying to insert rows from different RAC instances. But when you use reverse key index, the new index entries will go to different blocks so that contention will be reduced.

If you are deleting some old rows, the blocks from a normal index on that column will have some used and some empty space in them, but they will not be put on the freelist because they are not completely free. That empty space will not be used because the sequence values are always increasing and they will not go to those old blocks because of that. You will be able to use that space for different values with reverse key indexes.

If your application is running on high buffer busy waits for the index segment, lets say more than 100 waits for a 5 minute period. This is you can find out when you run statspack report.
To change an existing index as a reverse key index you can use the alter index statement.

alter index indexname rebuild reverse;

After this change you can notice a huge change in index size also in the program run time.

Oracle 11g R1 - Partitioning based on virtual column.

Coming soon....

Virtual columns in Oracle 11g R1

Virtual columns are normal columns in a table but their values are derived rather than being stored on disc. The syntax is given below.
column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]
datatype is optional, it will automatically determine based on the result of the expression. GENERATED ALWAYS and VIRTUAL keywords are for clarity only.
Below is an example for creating a simple virtual column.
CREATE
TABLE T
( a NUMBER,
b NUMBER,
c AS (a+b),
d NUMBER GENERATED ALWAYS AS (a*b) VIRTUAL
);
INSERT INTO T(a,b) VALUES(30,20);
INSERT INTO T(a,b) VALUES(10,10);
COMMIT;
SELECT * FROM t;
a b c d
-------------------
30 20 50 600
10 10 20 100
2 rows selected.

To see the virual columns in a table, use the below query.
SELECT * FROM user_tab_cols WHERE table_name='T' AND virtual_column='YES';

Notes about virtual columns:
1. Can be indexed but equivalent to function based index.
2. Cannot perform any DML operations on virtual column.
3. Cannot be based on other virtual columns. It should be based on columns defined on the same table.
4. Can be constrained.
5. Eligible for result caching.
6. Virtual columns can be used in the partition key. That means virtual column can be partitioned.
7. Virtual columns are not supported under external, object, cluster, temporary, or index organized tables.
8. Functions in expressions must be deterministic at the time of table creation, but can subsequently be recompiled and made non-deterministic without invalidating the virtual column. In such cases the following steps must be taken after the function is recompiled:
-Constraint on the virtual column must be disabled and re-enabled.
-Indexes on the virtual column must be rebuilt.
-Materialized views that access the virtual column must be fully refreshed.
-The result cache must be flushed if cached queries have accessed the virtual column.
-Table statistics must be regathered.
See my notes about virtual column partitioning.

Oracle PL/SQL Package initialization

The initialization part of PL/SQL package runs only once for a session when you first reference the package. Below i have explained about basics of package initialization with example so that you can understand how & where you can apply this in your application
Here is a small example.

CREATE OR REPLACE PACKAGE init_pack
AS
FUNCTION fun1 RETURN number;
PROCEDURE proc1;
END init_pack;
/

CREATE OR REPLACE PACKAGE BODY init_pack
AS
abc int;
FUNCTION fun1 RETURN number
AS
BEGIN
abc := abc+1;
DBMS_OUTPUT.PUT_LINE('In Function1-abc value is 'abc);
RETURN abc;
END fun1;
PROCEDURE proc1
AS
BEGIN
abc := abc + 1;
DBMS_OUTPUT.PUT_LINE('In proc1- abc values is 'abc);
END proc1;
BEGIN
INSERT INTO test VALUES(1);
COMMIT;
abc :=0 ;
END init_pack;
/
session 1
SQL> exec init_pack.proc1;
In proc1- abc values is 1
PL/SQL procedure successfully completed.
SQL> exec init_pack.proc1;
In proc1- abc values is 2
PL/SQL procedure successfully completed.
SQL> exec init_pack.proc1;
In proc1- abc values is 3
PL/SQL procedure successfully completed.
SQL> select init_pack.fun1 from dual;
FUN1
----------
4
In Function1-abc value is 4
SQL>
-------------------------------------------------------------------------------------------
Here you can notice that the initialization part of a package is run just once, the first time you reference the package. So, in the last example, only one row is inserted into the database table test. The variable abc is initialized only once and every time the procedure proc1 is called, the variable abc is updated. However, the count kept by abc is session specific.

-- The important factors which need to understand --

In the above example, if we reference package by calling the function at the first time in a session, we will get an error. Lets have a look at below.
open a new session- session2
run in the same way below.
SQL> select init_pack.fun1 from dual;
ERROR at line 1:ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "INIT_PACK", line 24
SQL> select init_pack.fun1 from dual;
FUN1
---------

In Function1-abc value is
SQL> exec init_pack.proc1;
In proc1- abc values is

you might have noticed the above result. If the initilization part of a package contains any DML statement and if we are using the function in a SQL query first time in a session, it will end up with an error. But when you call next time, it will not raise any error but the initialization part won't work even if we call procedure second time.
Think how to resolve this if such kind of situation comes. :)

ORDER BY dbms_random.value()
This method orders the data by a random column number.
Example: SQL> SELECT * FROM (SELECT ename FROM emp ORDER BY dbms_random.value()) WHERE rownum <= 3;
ENAME
----------
WARD
MILLER
TURNER
The ORA_HASH() function
The following example retrieves a subset of the data in the emp table by specifying 3 buckets (0 to 2) and then returning the data from bucket
SELECT * FROM emp WHERE ORA_HASH(empno, 2) = 1;
select a random collection of rows from a table
The following methods can be used to select a random collection of rows from a table:
The SAMPLE Clause
The easiest way to randomly select rows from a table is to use the SAMPLE clause with a SELECT statement.
Examples: SELECT * FROM emp SAMPLE(10);
In the above example, Oracle is instructed to randomly return 10% of the rows in the table.
SELECT * FROM emp SAMPLE(5) BLOCKS;
This example will sample 5% of all formatted database blocks instead of rows.
This clause only works for single table queries on local tables. If you include the SAMPLE clause within a multi-table or remote query, you will get a parse error or "ORA-30561: SAMPLE option not allowed in statement with multiple table references". One way around this is to create an inline view on the driving table of the query with the SAMPLE clause.
Example: SELECT t1.dept, t2.emp FROM (SELECT * FROM dept SAMPLE(5)) t1,emp t2 WHERE t1.dep_id = t2.dep_id;

DatabaseLink between oracle 10g and 11g fails.ORA-01017. Invalid username/password

When i tried to create a database link between oracle 10g and 11g, i got an error ORA-01017. Invalid username/password. I found two solutions for this.

1. Oracle 11g R1 passwords are case sensitive by default.

2. Enclose username and password section of dblink in double quotes. Username is not mandatory to be in double quotes.

Eg: CREATE DATABASE LINK dblink10g11g CONNECT TO "username" IDENTIFIED BY "password" USING '11g db name';

DBMS_ALERT

The dbms_alert package provides an alerting mechanism based on events. An event can be defined as an occurrence, or an action. Example events include the payroll table being updated, a specific user logging on to the database, or available free space less than 20 MB. Anything that is detectable can be used to trigger an event.
The dbms_alert package provides a mechanism for the database to notify a client (anything listening) of an event asynchronously, which means that the application does not need to periodically check for the occurrence of events. Instead, when an event occurs a notification will be sent. In the past, developers created a polling process that checked the status of something on the database, like a completed job, by checking for a table value that the process had just updated. dbms_alert renders such techniques obsolete and is one of the best Oracle supplied packages.
The dbms_alert package is created by executing the catproc.sql file and is owned by SYS. Once granted the execute privilege to dbms_alert, it can be executed by any software component that can call a stored procedure.
DBAs can use it for database monitoring and application developers can use it to signal business, or application events.
How It Works
The dbms_alert package begins with a client process registering interest in an alert. Once it registers for an alert, it waits for that specific alert (waitone ) or any other alert (waitany ) to occur. Once the alert occurs, the client is notified and a string is passed containing whatever the signal process selected to send to the client
Once the call to waitone or waitany is made, Oracle will check the dbms_alert_info table to see if any new rows have been inserted. If a new row exists, the data in the column message is returned from the procedure call.

The Java program below (Alert.java) can be executed from the DOS or UNIX prompt. This program prompts for database connection information, connects to the database via JDBC, registers for an alert, and waits for the LOGONALERT alert to occur. It can easily be customized to not only display an event but to prompt for the event of interest.

import java.sql.*;
import java.io.*;
import java.util.*;
import oracle.jdbc.driver.*;

public class Alert
{
static String host, dbname, port, user, password, option = null;
public static void main(String args[])
{
InputStreamReader isr = new InputStreamReader ( System.in );
BufferedReader br = new BufferedReader ( isr );
System.out.print(" ************************************************\n");
System.out.print(" * This is a simple utility to test *\n");
System.out.print(" * the alert notification between the database *\n");
System.out.print(" * and any client application *\n");
System.out.print(" ************************************************\n\n");

try {
System.out.print(" Enter Host Name: ");
host = br.readLine();
System.out.print("\n Enter Database Name: ");
dbname = br.readLine();
System.out.print("\n Enter TNS Listener Port#: ");
port = br.readLine();
System.out.print("\n Enter Database User Name: ");
user = br.readLine();
System.out.print("\n Enter Database User Password: ");
password = br.readLine();

System.out.print("\n Press at any time to exit \n\n");

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection databaseConnection =
DriverManager.getConnection("jdbc:oracle:thin:@" +
host + ":" +
port + ":" +
dbname, user, password);

System.out.print("\n\nSuccessully connected to " + dbname + " as " + user);
System.out.print("\n");

String sql = null;
String message = null;
String status = null;

sql = "{call dbms_alert.register('LOGONALERT')}";
CallableStatement cs1 = databaseConnection.prepareCall(sql) ;
cs1.execute();
sql = "{call dbms_alert.waitone('LOGONALERT', ?, ?)}";

CallableStatement cs2 = databaseConnection.prepareCall(sql) ;
cs2.registerOutParameter(1, Types.VARCHAR);
cs2.registerOutParameter(2, Types.VARCHAR);

int x = 0;

while (x==0) {
cs2.execute();
String Result = cs2.getString(1);
System.out.print(Result + "\n");
}

databaseConnection.close();

} // try

catch (SQLException sqle) {
System.out.print("Unable to connect, error: " + sqle );
}
catch (IOException ioe) {
System.out.print("IO Exception (bad data): " + ioe );
}
}
}

Next, a trigger will be created that will signal this event. The signal code is the key to the trigger below. Notice that it contains the same alert name that the client registered, LOGONALERT.

dbms_alert.signal('LOGONALERT', , );
commit;

The following PL/SQL (Logontrigger.sql) is an AFTER LOGON TRIGGER that sends information to those registered for LOGONALERT. As part of the signal call, it returns the user, machine, and time that the user logged on. The commit statement after the signal call is required to send the signal.
CREATE or replace TRIGGER on_connect
AFTER LOGON ON DATABASE
DECLARE
pragma autonomous_transaction; -- needed for trigger commit
vsess varchar2(30);
vuser varchar2(30);
vmachine varchar2(30);
vosuser varchar2(30);
dbname varchar2(40);
BEGIN
select userenv('SESSIONID') into vsess from dual;
select username, machine, osuser
into vuser,vmachine,vosuser
from v$session where audsid = vsess;
select value
into dbname
from v$parameter
where name = 'db_name';
dbms_alert.signal('LOGONALERT', 'User: ' vuser
' logged on from ' vmachine
' at ' to_char(sysdate,'mm-dd-yyyy HH24:MI:SS'));
commit; -- required for the signal to work
END;
/
Once everything is configured, the trigger will fire when a user connects to the database. From the operating system, the program will be started and the required information entered.

C:\oracle9i\bin>java Alert

************************************************
* This is a simple utility to test *
* the alert notification between the database *
* and any client application *
************************************************

Enter Host Name: MoorePC

Enter Database Name: ORCL92

Enter TNS Listener Port#: 1521

Enter Database User Name: scott

Enter Database User Password: tiger


Press at any time to exit


Successully connected to ORCL92 as scott

User: SYS logged on from WORKGROUP\MOOREPC at 01-02-2003 21:18:37

SCOTT was the user that connected to the database to wait for the event. Within one second of user SCOTT logging in to the database, the LOGINALERT was signaled and sent to the Java client. Based on the message above, the SYS user logged in to the database.

Duplicating this Java code functionality in SQL*Plus would be simple, although not as functional, since SQL*Plus is not automatically notified of the event. The print statement below is used to check the status of the logon alert message.

SQL> var out1 varchar2(800)
SQL> var out2 varchar2(800)
SQL> exec dbms_alert.waitone('LOGONALERT', :out1, :out2);
PL/SQL procedure successfully completed.

SQL> print out1

OUT1
-------------------------------------------------------------------
User: SYS logged on from WORKGROUP\MOOREPC at 01-02-2003 21:24:55
The dbms_alert_info Table
Any alert that has been registered via dbms_alert.register is inserted into the dbms_alert_info table. This table is owned by the SYS user and contains the data for registered alerts. A DBA can query this table to discover who is waiting for certain events. This can be important since the DBA may want to inspect the types of alerts that are being generated. In addition, in the event that the database needs restarting, the DBA can see the sessions waiting on events that will be impacted.

The columns of the dbms_alert_info table include:

· Name - The name of the alert specified by the user.
· SID – Session ID of the session that is waiting for this alert.
· Changed – Y/N Indicator that the alert has been signaled. Y = alert signaled, N = no alert signaled via (dbms_alert.SIGNAL)
· Message – An 800-byte character string that can be passed to the client waiting for the alert. An 800-byte character is large enough to send a meaningful message to any registered client. This message is optional, since the occurrence of a specific alert may be enough information alone.
dbms_alert Methods
The following options are available when utilizing dbms_alert. The register and signal procedures are required, the rest are optional.

· register – The register procedure adds a session and specified alert to a registration list that appears in the dbms_alert_info table. There is no limit to the number of alerts a session can register. When the session is no longer interested in receiving alerts, the remove procedure (or remove_all) should be called to deactivate the notifications. Register always performs a commit on exit.
· remove – Procedure that removes the specified alert from the registration list for a particular session. This should always be called when the session is no longer interested in receiving particular alerts.
· remove_all – Procedure that removes all of the registered events for that session.
· set_defaults – This procedure sets the default polling interval, in seconds.
· signal - The signal procedure sends an alert to any session that has registered for it. The signal includes the name of the alert along with an optional 800-character string. This string negates the need for client sessions to ping the database for information. The SIGNAL only works after a commit is issued.
· waitany – In the event that a session has registered interest in many alerts, the waitany procedure is used for the client to be notified if any of them occur. Waitany avoids the need to implement multiple waitone calls, thereby simplifying the code. When waitany is called, the timeout period in seconds can be specified.
· waitone – This procedure waits for one alert to occur as specified in the call to waitone.
Practical Uses for dbms_alert
There are many times that users would prefer instant notification. For a DBA, the following events may prove worthy of real-time alerts:

· Unauthorized users logging on to database
· Unauthorized table access
· Performance measurements, including long wait events and locks
· Free space deficit
· Queries that have exceeded n seconds in CPU usage
The dbms_alert package is a powerful tool that allows asynchronous notification of events. Any event the DBA chooses can be monitored and communicated to any client application that registers an interest in the event. The dbms_alert package is a great tool that enhances the monitoring skills of the DBA.
-----------------------------------------------------------------------------------------------
Other Examples.

/* alert.sql */set serveroutput on;
declare
message varchar2(200);
status integer;
begin
dbms_alert.register('my_alert');
dbms_alert.waitone('my_alert',message,status,60);
dbms_output.put_line('status = 'status);
dbms_output.put_line('message = 'message);
dbms_alert.remove('my_alert');
end;
/
exit;
/* alert2.sql */
exec dbms_alert.signal('my_alert','a message from another process');
commit;
exit;
REM - alert.cmd (Windows NT/2000)
start cmd /k sqlplus system/manager @alertstart system/manager @alert2
#!/bin/sh# (for UNIX)
sqlplus system/manager @alert &
sqlplus system/manager @alert2