Avoiding Outer Joins

Solution 1:

Oracle “Normal” joins are used to retrieve records from both the tables which satisfy join conditions. But sometimes we need records from the table which satisfy the join condition and also those records which are not present in another table. In this situation we go for Outer Joins.

If the tables being joined are having millions of records then using outer joins on such tables takes lots of time for execution. Even though the query is returning 10 or 100 records, the execution time taken by a query will be huge.

Please have a look at a simple query mentioned below. The query provides department names for, employees with ID between 1 and 100 and also those employees which are not allocated to any department.

select e.ID,e.NAME, d.NAME

from dept d, emp e

where d.id (+) = e.DEPT_ID

and e.id between 1 AND 100

In the above example we need only one column, department name, from DEPT table. But for this we need to perform a costly outer join on DEPT table. In such situations it is possible to avoid outer joins. Let’s have a look at the example given below,

select e.ID,e.NAME,(select NAME from dept d where d.id = e.DEPT_ID)

from emp e

where e.id between 1 AND 100

In this solution we are removing the DEPT table from the FROM clause of main query. The DEPT table is used as a sub query to retrieve values for department name column. This sub query will be executed only 100 times. If the DEPT (ID) column is indexed then the query on DEPT table will be faster.

Above solution gives positive results when,

1. Only one column is being selected from the huge INNER table
2. The query is joining tables with millions of records
3. Query return very few records as an output

Solution 2:

In case of normalized database we need to join multiple tables and some times we need to use outer joins. These queries take lots of time for retrieving 10 or 100 records.

Please see the example mentioned below. The query is trying to retrieve all the transaction between dates 01-Jan-2009 and 10-Jan-2009. For each demat transaction the query provides details for Beneficiary account, Depository participant account and settlement account involved in the transaction. The demat transaction details are stored in the DEMAT_TRANSACTION table and demat account details are stored in DEMAT_ACCOUNT table. DEMAT_ACCOUNT represents multiple accounts involved in a transaction and stores one record per account.

SELECT demat_trn.transaction_id,

benacct.party_name "Beneficiary Account",

dpacct.party_name "Depository Participant Party",

settleacct.part_name "Settlement Account"

FROM demat_transaction demat_trn,

demat_account benacct,

demat_account dpacct,

demat_account settleacct

WHERE demat_trn.transaction_id = benacct.transaction_id(+)

AND demat_trn.transaction_id = dpacct.transaction_id(+)

AND demat_trn.transaction_id = settleacct.transaction_id(+)

AND demat_transaction.transaction_date BETWEEN

TO_DATE ('01012009', 'DDMMYYYY')

AND TO_DATE ('10012009', 'DDMMYYYY')

In this example we can avoid outer joins by denormalizing the DEMAT_TRANSACTION and DEMAT_ACCOUNT tables. If there are maximum 3 accounts involved in the demat transaction, then the DEMAT_TRANSACTION table should be altered to accommodate details for all the three accounts for each transaction. After denormalization the final query will look like as mentioned below,

SELECT demat_trn.transaction_id,

benacct.party_name "Beneficiary Account",

dpacct.party_name "Depository Participant Party",

settleacct.part_name "Settlement Account"

FROM demat_transaction demat_trn

WHERE demat_trn.transaction_date BETWEEN

TO_DATE ('01012009', 'DDMMYYYY')

AND TO_DATE ('10012009', 'DDMMYYYY')

Since the denormalized query is fetching records from only one table, hence this query will definitely be executed faster than the normalized query.

Conclusion:

Outer joins are very costly when the tables being joined are very big and having millions of records. Depending on queries output it is possible to easily avoid the outer joins using Sub queries or denormalizing the tables involved in the Join.

15 Tips for New Employees

1. Don’t be a know-it-all
   By jumping into things, older and more experienced colleagues will perceive you as arrogant and you could lose your respect.
2. Appreciate others
   Saying “thank you” to your boss and other colleagues for their help and guidance will have long-term positive effects.
3. Give in something extra
   Volunteer for extra tasks and assignments to show your commitment, loyalty and diligence.
4. Listen before you speak
   Try to understand and grasp every piece of information and make sure you understand everything before you give in your contributions.
5. Understand the culture of the organization
   Observe how people interact with others and how things are done. Find out who is most admired and/or most influential and why.
6. Learn more about yourself
   Your first job gives you a chance to figure out what you are best at and what kind of work you enjoy the most.
7. Take time out for developing new skills
   Look out for opportunities of career development within and outside the organization and acquire new skills for your professional growth.
8. Dress professionally
   Observe how people are generally dressed in the organization and follow that dress code.
9. Don’t feel shy to ask questions
   The embarrassment will be much more if you end up messing up your work because you did not take clear instructions. So ask questions and take down notes.
10. Be punctual
    Never be late for work and keep a good attendance. Try to come in before the official work time and stay a little late.
11. Stay away from office gossip
    Don’t get involved in the office politics and gossip. This is especially important in the first few months.
12. Get involved in the informal events
    Joining the sports club or other social events organized by the firm will give you a chance to develop cordial relationships with your co-workers.
13. Keep your boss informed
    Give your boss updates of all your activities at work. This will increase your reliability and will show how responsible you are.
14. Remember people’s names
    As soon as possible try to remember names of people you come into contact with.
15. Don’t complain or criticize
    You might not be happy with the entire management system but wait before you start to give suggestions

Installation of Peoplesoft Components

Following should be the sequence of Installing/Configuring Peoplesoft Applications

1.    Install Tuxedo 9.1
2.    Install BEA weblogic or IBM Websphere or oracle app server
3.    Install Peopletools 8.49 on Server
4.    Install Peopletools 8.49 on Windows client
5.    Install Peoplesoft HRMS Application on Server
6.    Install Peoplesoft HRMS Application on Windows client
7.    Install Database software (Oracle Database 10g Release 2) 
        7.1    Peoplesoft Database creation part I : 
        7.2    Peoplesoft Database creation part II: 
        7.3    Peoplesoft Database creation part III:
8.    Configure Application Server
9.    Configure Web Server 
        9.1    Encrypting the Password (AIX Only) 
        9.2    Starting and Stopping WebSphere Application Servers 
        9.3    Accessing the PeopleSoft Signon
10.    Configure Process Scheduler
11.    Install configure Cobol to compile Cobol programs
12.    Install Crystal Reports on Report Server or client

1 Installing Tuxedo 9.1

Downloaded from http://edelivery.oracle.com
Choose "Peoplesoft Entreprise", plateform "IBM AIX 64-bit", then under "PeopleSoft Enterprise - PeopleTools 8.49 Media Pack", choose the disk :
"BEA Tuxedo 9.1 for AIX, Solaris, and Linux"
As root, create the groups and the Oracle user :
[root@psoft ~]# groupadd dba
[root@psoft ~]# groupadd oinstall
[root@psoft:/root] ()$ useradd -g oinstall -G dba ora102
[root@psoft:/root] ()$ passwd ora102
Changing password for user ora102.
New UNIX password:
BAD PASSWORD: it is based on a dictionary word
Retype new UNIX password:
passwd: all authentication tokens updated successfully.

[root@psoft:/root]$ cd /apps
[root@psoft:/apps]$ mkdir -p bea/tuxedo/9.1
[root@psoft:/apps/psoft/bea]$
2. Install software as oracle user.
[tuxedo@psoft:/software/BEA_Tuxedo_9.1]$ sh ./install.sh
01) ibm/aix53 02) linux/sles9 03) sun/sol9
Please select a platform? [1-3, q to quit, l for list]: 1
Installing for ibm/aix53
Is this correct? [y,n,q]: y
If you wish to quit the installation at any point
press the interrupt key or .
BEA Home being defaulted to /bea (y/n)
n
Please supply alternative BEA Home:
/apps/bea
Tuxedo 9.1 will be installed to /apps/bea/tuxedo91 (y/n):
n
Please supply alternative Tuxedo location:
/apps/bea/tuxedo/9.1
Accept default TListen password 'password' (y/n):
y
Continue with installation of Tuxedo 9.1 (y/n):
y
Installing...
Tuxedo 9.1 base installation complete
Installing patch at /apps/bea/tuxedo/9.1
using /bin/gunzip to uncompress archive
Installing server and client files...
Enter owner for patch files:
oracle
Enter group for patch files:
oinstall
The patch installation finished successfully.
[tuxedo@psoft:/software /BEA_Tuxedo_9.1]$
3. Add the two last line (in bold) in tuxedo's .profile
[tuxedo@psoft:/home/tuxedo]$ more .profile
# .profile
# Get the aliases and functions
if [ -f ~/.kshrc ]; then
. ~/.kshrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
unset USERNAME
export BEAHOME=/apps/bea
export TUXDIR=/apps/bea/tuxedo/9.1
[tuxedo@psoft:/home/tuxedo]$
4. After reconnection, check the installation by existence of two files :
[tuxedo@psoft:/home/tuxedo]$ cd $TUXDIR
[tuxedo@psoft:/apps/bea/tuxedo/9.1]$ ls
bin cobinclude help include lib locale samples tux.env udataobj uninstaller
[tuxedo@psoft:/apps/bea/tuxedo/9.1]$ find . -name lic.txt
./udataobj/lic.txt
[tuxedo@psoft:/apps/bea/tuxedo/9.1]$ find . -name tlisten.pw
./udataobj/tlisten.pw
[tuxedo@psoft:/apps/bea/tuxedo/9.1]$
[tuxedo@psoft:/apps/bea/tuxedo/9.1]$ ln -s $TUXDIR/locale/C $TUXDIR/locale/Ja_JP
[tuxedo@psoft:/apps/bea/tuxedo/9.1]$ ls -lrt locale
total 60
-rw-rw-r-- 1 oracle oinstall 24 Jan 5 15:00 CATNAMES
lrwxrwxrwx 1 oracle oinstall 1 Jan 5 15:01 en_US.UTF-8 -> C
lrwxrwxrwx 1 oracle oinstall 11 Jan 5 15:01 japanese.euc -> ja_JP.eucJP
lrwxrwxrwx 1 oracle oinstall 11 Jan 5 15:01 japanese -> ja_JP.eucJP
lrwxrwxrwx 1 oracle oinstall 11 Jan 5 15:01 ja_JP.ujis -> ja_JP.eucJP
lrwxrwxrwx 1 oracle oinstall 11 Jan 5 15:01 ja_JP -> ja_JP.eucJP
lrwxrwxrwx 1 oracle oinstall 13 Jan 5 15:05 ja_JP.eucjp -> ./ja_JP.eucJP
drwxrwxr-x 2 oracle oinstall 4096 Jan 5 15:05 ja_JP.SJIS
drwxrwxr-x 2 oracle oinstall 4096 Jan 5 15:05 ja_JP.eucJP
drwxrwxr-x 2 oracle oinstall 4096 Jan 5 15:05 C
lrwxrwxrwx 1 oracle oinstall 29 Jan 5 15:09 Ja_JP -> /apps/bea/tuxedo/9.1/locale/C
[tuxedo@psoft:/apps/bea/tuxedo/9.1]$

2 Install BEA weblogic or IBM Websphere or oracle app server

[root@psoft:/root]$ cd /apps/IBM
[root@psoft:/apps/IBM]$ ls
tuxedo
[root@psoft:/apps/IBM]$ mkdir -p Websphere/9.2
[root@psoft:/apps/IBM]$
2. Install software as Websphere user.
[Websphere@psoft:/home/Websphere]$ more .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
unset USERNAME
export BEA_HOME=/apps/IBM/Websphere/9.2
[Websphere@psoft:/home/Websphere]$

Open a command prompt and execute the command - “installWAS. [bat/sh]”, which will start the installation of WebSphere ND6.1.

This will invoke the default GUI install window. If a window does not appear on a UNIX system, check that the DISPLAY environment variable is set properly. On this window, accept the Software license agreement and Click Next.

The next window will ask you for Install Location, followed by profile creation & server environments. Select ‘NONE’ as the WebSphere Application server environment option. PeopleSoft Internet Application install will create a customized cell profile for you.

Note. On Windows platform, selecting anything other than NONE as the WebSphere Application Server environment Profile may cause the install to not complete successfully. The other selections, besides NONE, create a Windows-OS lock on the profile, restricting PeopleSoft related interim fixes to not install properly.

A warning message will appear to check whether you want to proceed without creating a profile, Click ‘Yes’ to continue.

Warning Message

Click Next, to proceed with the WebSphere ND 6.1 installation.

After the installation has been completed, an ‘Installation Results’ window will appear. On this window, Uncheck the Create a new WebSphere Server profile option, as shown below, and then Click Finish.

You have completed the installation of the WebSphere ND v6.1.0.3 using the GUI mode.

Apply Interim Fixes

Refer customer connection link ftp://ftp.peoplesoft.com/outgoing/PTools/websphere/6103 to check whether any interim fixes are needed. If they are present, you must install them using the readme file.

3 Install Peopletools 8.49 on Server

Downloaded from http://edelivery.oracle.com
Choose "Peoplesoft Entreprise", plateform "AIX 64-bit", then under "PeopleSoft Enterprise - PeopleTools 8.49 Media Pack", then disk named as
"PeopleSoft Enterprise PeopleTools 8.49 PeopleTools 8.49 Mobile Agent (use requires specific license) Disk 1 of 7"
"PeopleSoft Enterprise PeopleTools 8.49 PeopleTools 8.49 Mobile Agent (use requires specific license) Disk 2 of 7"
"PeopleSoft Enterprise PeopleTools 8.49 PeopleTools 8.49 Mobile Agent (use requires specific license) Disk 3 of 7"
"PeopleSoft Enterprise PeopleTools 8.49 PeopleTools 8.49 Mobile Agent (use requires specific license) Disk 4 of 7"
"PeopleSoft Enterprise PeopleTools 8.49 PeopleTools 8.49 Mobile Agent (use requires specific license) Disk 5 of 7"
"PeopleSoft Enterprise PeopleTools 8.49 PeopleTools 8.49 Mobile Agent (use requires specific license) Disk 6 of 7"
"PeopleSoft Enterprise PeopleTools 8.49 PeopleTools 8.49 Mobile Agent (use requires specific license) Disk 7 of 7"
Here below, I'll show the Peopletools installation for the HRMS application. It's better to install one Peopletools software by application directory.
1. As root, the group oinstall has already been created previously.
[root@psoft:/apps/bea/weblogic/9.2]$
2. Start the installation in GUI mode.
At this step you'll need the license code from (depending of the database vendor) http://licensecodes.oracle.com/ent_keys_by_prod.html#PeopleTools
The license code are same for production usage.
In XClient window :
[oracle@psoft:/software/PeopleTools8.49/Disk1]$ ./setup.aix

Insert or change the directory of CD on demand (all the 7 cd will be required) :

4. Install the patch 8.49.08
Downloaded from ftp://ftp.peoplesoft.com/outgoing/ptools/84919
Password for unzipping the compress file from http://www.metalink3.com (a valid customer ID required)
Tab Update+Fixes, then Apply to Release
Choose Product Line => Peopletools
Product => Peopletools
Release => 8.49
Check the Bunldes checkbox, then search
Open the user doc html files to get the password.
Lastly, at this step you'll need the license code (depending of the database vendor) from http://licensecodes.oracle.com/ent_keys_by_prod.html#PeopleTools
In XClient window :
[oracle@psoft:/software/PeopleTools8.49.08/cd84908/disk1]$ ls
media.inf pt_responsefile.txt readme.txt setup setup.aix setup.exe setup.hp setup.hp-ia64 setup.jar setup.linux setup.solaris [oracle@psoft:/software/PeopleTools8.49.08/cd84908/disk1]$ ./setup.aix

5. Modify the .bash_profile
[oracle@psoft:/home/hrms9]$ more .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
unset USERNAME
export ORACLE_HOME=/apps/oracle/ora102
export PS_HOME=/apps/psoft/hrms9
. $PS_HOME/psconfig.sh
6. Run the psadmin to check your install :

4 Install Peopletools 8.49 on Windows client

Downloaded from http://edelivery.oracle.com
Choose "Peoplesoft Enterprise", plateform "Linux x86", then under "PeopleSoft Enterprise - PeopleTools 8.49 Media Pack", then disk named as
"PeopleSoft Enterprise PeopleTools 8.49 PeopleTools 8.49 Mobile Agent (use requires specific license) Disk 1 of 7"
"PeopleSoft Enterprise PeopleTools 8.49 PeopleTools 8.49 Mobile Agent (use requires specific license) Disk 2 of 7"
"PeopleSoft Enterprise PeopleTools 8.49 PeopleTools 8.49 Mobile Agent (use requires specific license) Disk 3 of 7"
"PeopleSoft Enterprise PeopleTools 8.49 PeopleTools 8.49 Mobile Agent (use requires specific license) Disk 4 of 7"
"PeopleSoft Enterprise PeopleTools 8.49 PeopleTools 8.49 Mobile Agent (use requires specific license) Disk 5 of 7"
"PeopleSoft Enterprise PeopleTools 8.49 PeopleTools 8.49 Mobile Agent (use requires specific license) Disk 6 of 7"
"PeopleSoft Enterprise PeopleTools 8.49 PeopleTools 8.49 Mobile Agent (use requires specific license) Disk 7 of 7"

At this step you'll need the license code from (depending of the database vendor) http://licensecodes.oracle.com/ent_keys_by_prod.html#PeopleTools
The license code are same for production usage.
Just run the setup.exe located into the installation directory, and follow the instruction.
You have to indicate the ORACLE_HOME/bin directory (which is not the case on Unix/Linux).
Note, the Disk 5 will be skipped (not required).

Choose Unicode database if you want to load global application with different languages, choose Non-Unicode if not. Take care , a Unicode database requires much more space (between 2 and 3 times more than a Non-Unicode database).

Choose the module you want to install, in most of case, check all the checkboxes :

If you want later install some ML translation application,keep the Language pack :

Change the folder for each installation CD :

Then, you have to install the patch 8.49.08, the screenshots are almost same as here above.
Lastly, some programs have been added in your programs list, run the Application Designer to check the Peopletools version as well :

5 Install Peoplesoft HRMS Application on Server

Downloaded from http://edelivery.oracle.com
Choose "Peoplesoft Entreprise", plateform "IBM AIX 64-bit", then under "PeopleSoft Enterprise - Human Resources Management System and Campus Solutions 9.0 Translations Media Pack", then disk named as
"PeopleSoft Enterprise Human Resources Management System and Campus Solutions 9.0 Multi-Language"
"PeopleSoft Enterprise Human Resources Management System and Campus Solutions 9.0 (CD)"
1. Create a new folder, temporary for the installation task, as root
[root@psoft:/]$ mkdir tmphrms9
2. Start the installation in console mode:
At this step you'll need the license code from (depending of the database vendor) http://licensecodes.oracle.com/ent_keys_by_prod.html#HCM
The license code are same for production usage.
The temporary parameter (tempdir) might be skip if you have enough space in your default /tmp directory.
[oracle@psoft:/hrms9]$ ./setup.aix

Choose Unicode or Non-Unicode respecting what you choosed for the Peopletools installation where you'll install that Application :

For the Windows Server, on which you can run Process Scheduler, even if the database is on a Unix/Linux machine, for example n/Vision reports, that can help, check what you need to configure :

Give the path directory of the Peopletools you previously installed

Then uncheck the module you don't want to install :

Now, same for the ML, take care, the license code is the one for Tranlsation, regardless of the Application and Tools you install :

[...]
The screenshots are almost the same, eventhough, two difference :
1. You have not to choose which module you want to install
2. You have to choose your language :

The InstallShield Wizard has successfully installed PeopleSoft Enterprise HRMS and Campus Solutions 9.0 ML. Choose Finish to exit the wizard.