What is an Index?

An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.

 

Create an Index, The syntax for creating a index is:

CREATE [UNIQUE] INDEX index_name
  ON table_name (column1, column2, . column_n)
  [ COMPUTE STATISTICS ];

UNIQUE indicates that the combination of values in the indexed columns must be unique.

COMPUTE STATISTICS tells Oracle to collect statistics during the creation of the index. The statistics are then used by the optimizer to choose a "plan of execution" when SQL statements are executed.

Example:

CREATE INDEX supplier_idx
   ON supplier (supplier_name);

In this example, we've created an index on the supplier table called supplier_idx. It consists of only one field - the supplier_name field.

We could also create an index with more than one field as in the example below:

CREATE INDEX supplier_idx
   ON supplier (supplier_name, city);

We could also choose to collect statistics upon creation of the index as follows:

CREATE INDEX supplier_idx
  ON supplier (supplier_name, city)
  COMPUTE STATISTICS;

Create a Function-Based Index

In Oracle, you are not restricted to creating indexes on only columns. You can create function-based indexes.
The syntax for creating a function-based index is:

CREATE [UNIQUE] INDEX index_name
  ON table_name (function1, function2, . function_n)
  [ COMPUTE STATISTICS ];

Example:

CREATE INDEX supplier_idx
   ON supplier (UPPER(supplier_name));

In this example, we've created an index based on the uppercase evaluation of the supplier_name field.

However, to be sure that the Oracle optimizer uses this index when executing your SQL statements, be sure that UPPER(supplier_name) does not evaluate to a NULL value. To ensure this, add UPPER(supplier_name) IS NOT NULL to your WHERE clause as follows:

SELECT supplier_id, supplier_name, UPPER(supplier_name)
FROM supplier
WHERE UPPER(supplier_name) IS NOT NULL
ORDER BY UPPER(supplier_name);

Rename an Index

The syntax for renaming an index is:

ALTER INDEX index_name
  RENAME TO new_index_name;

Example:

ALTER INDEX supplier_idx
  RENAME TO supplier_index_name;

In this example, we're renaming the index called supplier_idx to supplier_index_name.

Collect Statistics on an Index

If you forgot to collect statistics on the index when you first created it or you want to update the statistics, you can always use the ALTER INDEX command to collect statistics at a later date.

The syntax for collecting statistics on an index is:

ALTER INDEX index_name
  REBUILD COMPUTE STATISTICS;

Example:

ALTER INDEX supplier_idx
  REBUILD COMPUTE STATISTICS;

 

In this example, we're collecting statistics for the index called supplier_idx. 

Drop an Index

The syntax for dropping an index is:

DROP INDEX index_name;
 
Example:

DROP INDEX supplier_idx;

What is Conversion and Conversion Process

Conversion is a pre implementation load which will transfer the data from the legacy system to the oracle apps system. After the completion of load we will drop all the objects which we have used for the conversion.

Conversion Process:

1.     We will get the flat file to convert, based on the flat file we will generate the temporary file and by using SQLLDR we transfer data from flat file to temporary table.

2.     Create staging table, which is replica of interface table provided by oracle.

3.     Develop a package which have different program units for validation and moving to interface tables

4.     Run the validation procedure to validate the data which has been loaded into staging table. Use extra cols in a staging table ERRFLAG and MESSAGETEXT that are used to identify valid and invalid records with respective error messages.

5.     We transfer the successful records into interface table and change the ERRFLAG status from V(Validated) to M(Moved)

·         ERRFLAG _ E _ ERROR

·         ERRFLAG _ V _ VALIDATED

·         ERRFLAG _ M _ MOVED

6.     As we are working with open interface we will run the import program provided by oracle used to pull the data from interface table and load into base table.

7.     If any records got failed while importing these will exist under oracle defined errors tables.

Conversion Process (Brief):

1.     Based on the flat file provided write the control file to transfer the data into staging tables.

2.     Validate the data that has been loaded into staging tables.

3.     Insert the validated records into interface tables.

4.     This is an open interface we run the import program to import the data into base tables.

What is Interface and Interface process

Interface is program used to transfer the data from flat file to database table or from data base table to flat file. Two types of interfaces:

·      Outbound Interface

·      Inbound Interface

Outbound Interface:

Outbound interface is used to transfer the data from oracle apps base table to legacy system flat file. We will develop a concurrent program, which is plsql procedure. We will use UTL_FILE package to generate the flat file.

Inbound Interface:

Inbound interface is used to upload the data from legacy system flat file to oracle apps base table. For this we will use SQL LOADER program and plsql program.

Interface is post implementation loads which will be helpful to import the data after go live.

The object that we have developed for this interface will be registered in the oracle applications.

Types of Interfaces: There are two types of interfaces.

1.     Open Interface.
2.     Custom Interface.

Open Interface: Open interface is nothing but it provides interface tables and import programs to import data into base tables.

Customer Interface: Whenever oracle has not provided any import programs or API to import data into base tables we call it as a custom interface.

Interface Process:

1.     Based on the flat file we will develop the control file and insert the data into staging table.

Note: Place the control file in the bin directory of your custom application directory.

2.     Register that in oracle 11i applications by taking execution method as SQL * LOADER we develop the packages which will be having procedures like validate the staging table, moving the validated data to the interface tables, run import program after that register that package in oracle 11i applications

Note: While transferring the data into base tables we pass three extra cols those are given below.

Ø  Process_flag (Number)

Ø  Transaction_type (Varchar2)

Ø  Set_process_id

Process_flag: To check the status of the row which we fetched into the table. We pass different values that indicate the status of the row those are given below.

Ø  1 à To be process

Ø  2 à Import Success

Ø  3 à Error

Ø  4 à Validation Under Process

Ø  7 à Validation Successful.

Transaction_type: This is used to identify which transaction was done those are CREATE, UDATE.

Set_process_id: It is used to split the no. of records to use memory correctly without getting any SGA MEMORY error.

How we can view the report output and logfile for another user?

By using “Concurrent:Report Access Level” profile option.

What is ORG ID and Organization ID

ORG ID:

You have Inventory Stores in two different countries like India and USA. You have installed Oracle Apps single instance and entered all the suppliers information, customers data, tax rules etc. And your business requirement is like this, all the rules and the data corresponding to Indian store should not be applicable/available to American store and vice versa.

So to meet the above requirements you will define two operating units one for India and one for USA. Now each operating unit will have an ID known as ORG ID and we use ORG ID to secure our business data.

Now to do transactions for Indian operations you need to define one responsibility and attach Indian ORG ID to this responsibility. Indian Employees will not see any data/rules applicable to USA.

Similarly you will define another responsibility for USA and attach USA s ORG ID to this responsibility so that Americans will not see any data/rules corresponding to India.

 

Organization ID:

Within India you have got various branches in different parts of the country in different states like AP,UP,MP etc.and the business requirement is like this in AP you will maintain stock of Rice, UP for Wheat and MP for Maize.

To meet this business requirement you will define 3 Inventory Organizations under Indian Operating Unit to maintain the respective stocks of the states.

Each Inventory Organization will have an Organization ID.

 

This is the outline of the differences between ORG ID and Organization ID. 

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

SELECT * FROM HR_OPERATING_UNITS ORDER BY 1;

SELECT organization_id,operating_unit,organization_name FROM ORG_ORGANIZATION_DEFINITIONS ORDER BY 2;