Outer joins on non-preserved columns

In this blog I will try to explain the necessity of adding the outer join on already preserved rows.

There are 3 tables, MYUSER, TEST1, TEST2. MYUSER is related to TEST1 table and TEST1 table is related to TEST2 table. The requirement is to fetch all the users from MYUSER table and if present fetch the related data from the TEST1 table and related data from TEST2 table.

The query would be

SELECT USERV.USERID ,USERV.NAME,
TEST1.ID, TEST1.TEST1NAME,
TEST2ID , TEST2.TEST2NAME
FROM MYUSER USERV,
TEST1 TEST1,
TEST2 TEST2
WHERE USERV.USERID = TEST1.ID(+)
AND TEST1.ID = TEST2.TEST2ID(+)
order by 1

The point to note is that “AND TEST1.ID = TEST2.TEST2ID(+)“ condition should be with + sign as shown. This is required because the first join will return all the users even if there is no corresponding record in Test1 table. For those preserved rows, the value will be null for TEST1.ID column. For this row to be preserved when joined with TEST2 table, the + on TEST2 should be maintained.

This can be seen by the following example

MYUSER Table

USERID	NAME
1	Bill
2	Purva
3	James
5	Meij

TEST1 Table

ID	TEST1NAME
1	test1bill
2	test1purva

TEST2 Table

TEST2ID	TEST2NAME
2	test2purva
3	test2james

The query that meets the requirement is

SELECT USERV.USERID ,USERV.NAME,
TEST1.ID, TEST1.TEST1NAME,
TEST2ID , TEST2.TEST2NAME
FROM MYUSER USERV,
TEST1 TEST1,
TEST2 TEST2
WHERE USERV.USERID = TEST1.ID(+)
AND TEST1.ID = TEST2.TEST2ID(+)
order by 1

ANSI Syntax

SELECT USERV.USERID ,USERV.NAME,
TEST1.ID, TEST1.TEST1NAME,
TEST2ID , TEST2.TEST2NAME
FROM MYUSER USERV
LEFT OUTER JOIN TEST1 TEST1 ON (USERV.USERID = TEST1.ID)
LEFT OUTER JOIN TEST2 TEST2 ON (TEST1.ID = TEST2.TEST2ID)
ORDER BY 1

USERID	NAME	ID	TEST1NAME	TEST2ID	TEST2NAME
1	Bill	1	test1bill
2	Purva	2	test1purva	2	test2purva
3	James
5	Meij

The query2 without outer join on the second clause is

SELECT USERV.USERID ,USERV.NAME,
TEST1.ID, TEST1.TEST1NAME,
TEST2ID , TEST2.TEST2NAME
FROM MYUSER USERV,
TEST1 TEST1,
TEST2 TEST2
WHERE USERV.USERID = TEST1.ID(+)
AND TEST1.ID = TEST2.TEST2ID
order by 1

USERID	NAME	ID	TEST1NAME	TEST2ID	TEST2NAME
2	Purva	2	test1purva	2	test2purva

To be precise, if a table is part of the outer join and the data from the table is outer joined to any other table, that condition also should be outer joined.

Consistent Read of the Data in Oracle Queries

In some cases, you might want different queries to return same set of data as far as the where criteria defined on the query are the same. For e.g.

Select price from vehicle_price where status=’A’

Select sum(price) from vehicle_price where status=’A’

In the same transaction, if I try to do these queries, there is a possibility that the second query might give a sum that will not be equal to the sum added up by the price returned in the first query. This is possible if new records get added to the vehicle_price table with status as ‘A’ or any of the existing record status changes from ‘A’ to something else.

To ensure that you always get the same data, there are multiple ways to get this done

1. Lock the table and perform queries on it. This ensures that no one else can modify the data while your transaction is in progress. I wont be surprised if you get shouted by other folks who uses this table in some other part of the application.

2. Make the data time sensitive. For every record that gets into the table, there should be a effective start time and effective end time. The query should always query on these columns. So the row doesn’t get modified but a new row gets added with new status. As long as the query uses the effective start date and effective end date in the query, the data returned in subsequent queries will be same. This is a better approach as other users are not impacted. But there could be size and performance implications.

3. Use Serializable isolation level for the transaction which queries the tables. This ensures that all queries are made to wait serially. So till the read transaction doesn’t get over, the new transaction cannot insert data. This also impacts the table usage.

4. Use Transaction read only feature. There are few limitations in using this feature of transaction. This level can be set only for transactions that have only read statements. Oracle maintains a snapshot of the data and all queries are worked upon the snapshot effective the transaction start time. This should be used if its viable to be. A commit or rollback on this transaction will cause the flag to be turned off. Set Transaction Read only ; is the command.

These are few of the ways by which we can ensure that queries return the same data within a same transaction.

Use Decode and Sign function together

Decode statements are useful in queries to a large extent. Sometimes people write the PLSQL code to achieve somethings that are possible through a normal decode statement.

For e.g. the requirement is to add two columns retrieved from the table and if its less than zero, make it zero and if its greater than zero, provide the sum of the two values as the output. This can be done using the decode statement

select column1 , coulmn2, column1+column2 ,
decode(SIGN(column1+column2), -1, 0, (column1+column2))
from tableA

The above query means that if the sum of column1 and column2 value is less than zero, then output the value as zero and if its greater than zero, output the sum as the value. We had to use SIGN function as decode does not support checks like less than or greater than.

JDK Logging

Log4j is widely accepted as a logging component. There is yet another one, the logging component inbuilt in java. This article will discuss about the logging provided by JDK.

Ideal scenario, the entire configuration can be done through the logging.properties file. The logging.properties file should be present in the classpath of the application. In case you want to provide a different properties file, the same can be set using the system property. The command is as shown below

java –Djava.util.logging.config.file=/home/users/mypath/mylogging.properties

The logging.properties file sample is present in the jre/lib folder. This file can be edited to get the necessary logging. The structure of the logging properties file is shown below

#Defines the handlers which can be used by the logger. It can be comma separated. For
# each handler, definition should be provided in the properties file
handlers= java.util.logging.FileHandler, java.util.logging.ConsoleHandler
#Default level of logging in case specific logging level as shown in the last line is not
# mentioned.
.level= INFO
#Details about the filehandler.
java.util.logging.FileHandler.pattern = %h/java%u.log
java.util.logging.FileHandler.limit = 50000
java.util.logging.FileHandler.count = 1
java.util.logging.FileHandler.formatter = java.util.logging.XMLFormatter
#custom logging level
com.xyz.foo.level = SEVERE

By default the logging happens at the package level of the class when you are using the property file for configuring the logger.

The restrictions here are that we can specify only one log file per logging properties file. So in case you need the log to go to multiple files, may be based on the application, it’s not possible through configuration in the properties file. However, you can perform the above requirement by writing a specific logger class.

private void initialize(String name)
{
Handler fileHandler;
try {
fileHandler = new FileHandler(filePath, true);
fileHandler.setFormatter(new MyFormatter());
logger=Logger.getLogger(name);
logger.addHandler(fileHandler);
}
public static MyLogger getInstance(String someName)
{
//add the necessary synchronization blocks
mylogger = new MyLogger();
mylogger.initializer(someName);
return mylogger;
}
//this is sample method. Like this you need to provide implementation for
//all other methods
public void debug(String sourceClass, String sourceMethod, String msg)
{
logger.logp(Level.FINEST,sourceClass,sourceMethod,msg);
}
//in your application code, use it this way
MyLogger logger = MyLogger.getInstance(“namethatidentifiestheloglevel”);
logger.debug(“classname”,”methodname”,”msg”);

In the above code snippet, the log level is not mentioned. The log level can be mentioned in the properties file that is passed as the java.util.logging.config.file property. In this case, the value will be set as

namethatidentifiestheloglevel=SEVERE

There are various levels available, the important ones being SEVERE, INFO, FINE, FINEST, DEBUG etc … Please refer to the sun documentation site as the priority of the log level does matter in the content that gets logged.