Oracle Global Optimizer Hints

Table hints refer to the tables in the statement block in which the hint is specified. If you have a view in your statement block and you try to hint the base tables using a table hint, it won’t work.

Here we have two options:

1. Embed the hint in the view definition

2. Use GLOBAL hints

Oracle recommends using the Global Hints as preferred method to hint base tables inside the views. Any oracle specified table hint can be transformed into a global hint by using a bit of extra syntax for table name.

Consider the below example:

CREATE OR REPLACE VIEW V1 AS  SELECT *  FROM students  WHERE student_no < 200;

CREATE OR REPLACE VIEW V2 AS SELECT * v1.student_no ,v1.student_name ,lecturers.lecturer_name ,lecturers.subject ,lecturers.subject_ID  FROM V1 ,lecturers  WHERE v1.subject = lecturers.subject; 

 WHERE subject_ID = 02;

The above SELECT query has a global hint which specifies an INDEX Scan for table student which is referenced in view V1, which in turn is referenced in view v2. If I had used

/*+ INDEX(students students_n01)*/,

the optimizer would have ignored the hint as the STUDENTS table is not present in the FROM clause of the statement block.


1. In case of a UNION or UNION ALL in the view referenced by a GLOBAL HINT, the hint is applied to the first statement branch that contains the hinted table.

2. The global hint syntax also applies to un-mergeable views.

Change user_name in FND_USER from back-end

PROBLEM: Identify a method to update the existing usernames in FND_USER to a value matching the Active Directory username. This is required for implementing stuff like Single Sign On (SSO). The process works fine when done from ERP Application front end, however a simple update of the FND_USER table from back-end does not work.

CAUSE: The reason why a simple update of FND_USER doesn’t work is that Oracle uses Workflows (WF) to maintain this information and when we update the FND_USER the foreign key references in the Workflow are not updated automatically.

SOLUTION:We can use the oracle provided procedure APPS.FND_USER_PKG.CHANGE_USER_NAME () to change the username as well as update the related WF tables.

I tested this on my username SARATHJ and changed it to SARATHJ1. All the responsibilities that existed for the user SARATHJ still exists for the changes user SARATHJ1. I validated this from both back-end and front-end. I could also login to the Application with my old password and work. A script can be written using this API and we can use SQL Loader to import data from a spreadsheet (.xls file) into a table in the Database. Then the API can be called in a loop to update the records. A pseudo code for the same is given below:

1. Place the usernames.xls in the interface directory

2. Run SQL Loader and import data into a loading table in the Database.

3.  SQL Script

a. For each row in loading table, call the API.
b. COMMIT every 500 rows