Oracle PeopleSoft

Subtracting Dates

If you want to find the difference in dates between now and some date in the past/future then you can use the following query using peoplesoft meta-SQL:

The %DateDiff function returns an integer. In Oracle you can use the subtraction operator:

Use ROUND() to round to the nearest day or TRUNC() to round down to the number of whole days.

Continue reading

Oracle SQL Performance Tuning

Composite / Concatenated SQL Indexes

When a single index has multiple columns that are indexed, it is called a composite or concatenated index. Prior to the introduction of skip-scan functionality, queries could only use the index if the leading column of the index was used in the WHERE clause. Consider the example in the following listing where the EMP table has a concatenated index on Empno, Ename, and Deptno. Note that Empno is the first part, Ename is the second…

Continue reading