Output Employee Attendance Calendar to Web with VBS

16 12 2009

December 16, 2009

This post is adapted from a small part of a presentation I gave a couple months ago.  The original code sample integrated into an ERP system to display an employee’s running attendance record for the last 6 or 12 months in graphical form, output to an Internet Explorer window.

First, we need sample “attendance” data in a table:

CREATE TABLE EMPLOYEE_RECORD_TEST AS
SELECT
  DECODE(TRUNC(DBMS_RANDOM.VALUE(0,5)),
          0,'MIKE',
          1,'ROB',
          2,'SAM',
          3,'JOE',
          4,'ERIC') EMPLOYEE_ID,
  TRUNC(SYSDATE)-ROUND(DBMS_RANDOM.VALUE(0,1000)) SHIFT_DATE,
  DECODE(TRUNC(DBMS_RANDOM.VALUE(0,10)),
          0,'VAC',
          1,'HOL',
          2,'BEREAVE',
          3,'JURY',
          4,'ABS',
          5,'EXCUSE',
          6,'MIL',
          'OTHER') INDIRECT_ID
FROM
  DUAL
CONNECT BY
  LEVEL<=1000;

The above created a table with 1,000 rows that picked one of five employees at random for each row, specifying a random date between today and 999 days ago, with one of eight random identifiers for the date.  The data in the table will look something like this:

SELECT
  *
FROM
  EMPLOYEE_RECORD_TEST
WHERE
  ROWNUM<=10;

EMPLOYEE_ID SHIFT_DAT INDIRECT_ID
----------- --------- -----------
MIKE        03-SEP-08 OTHER
MIKE        26-JUL-09 HOL
MIKE        27-MAY-09 EXCUSE
ERIC        27-JUL-08 OTHER
ERIC        02-NOV-07 VAC
ROB         02-OCT-07 OTHER
JOE         26-MAY-08 HOL
ERIC        29-JUL-08 ABS
ERIC        23-JUL-09 MIL
ERIC        14-JUN-07 HOL

Now that we have sample data, let’s see what we are trying to achieve (reduced in size):

As the color-coded output shows, Eric took a vacation day on January 4 and March 8.  Eric also was on bereavement leave on February 23 and 24, as well as March 22 and 25.  So, how was this output created?  A VBS script connected to the Oracle database (using a custom DLL to hide the username and password, and to simplify the process of submitting the SQL statement with bind variables), submitted a query, and then built the web page on the fly.

Dim varDateStart
Dim varDateEnd
Dim varDateMonthStart
Dim varDateMonthEnd
Dim intWeekdayStart
Dim intShiftDay
Dim i
Dim intRow
Dim intCol

'The color constants
Dim lngVacationBackColor
Dim lngHolidayBackColor
Dim lngBereavementBackColor
Dim lngJuryDutyBackColor
Dim lngAbsentBackColor
Dim lngExcusedBackColor
Dim lngMilitaryBackColor
Dim lngOtherMonthBackColor

Dim lngDateBackColor(31)
Dim lngDateForeColor(31)
Dim strSQL
Dim snpData
Dim OracleSQL
Dim objIE
Dim objShell

Dim intLastMonth
Dim intOutputMonth

Dim strHTML
Dim strEmployeeID
Dim dteTransactionDate

strEmployeeID = "MIKE"
'strEmployeeID = "ROB"
'strEmployeeID = "SAM"
'strEmployeeID = "JOE"
'strEmployeeID = "ERIC"

'dteTransactionDate = CDate("January 1, 2009") 'Can specify a specific date
dteTransactionDate = Date 'Can specify the current date

'Define the colors to be used to indicate the indirect in the date
'Note that the RGB components must be specified as BGR to be compatible with HTML
lngVacationBackColor = RGB(255, 0, 0)
lngHolidayBackColor = RGB(0, 255, 0)
lngBereavementBackColor = RGB(255, 175, 0)
lngJuryDutyBackColor = RGB(33, 153, 255)
lngAbsentBackColor = RGB(0, 0, 255)
lngExcusedBackColor = RGB(0, 255, 255)
lngMilitaryBackColor = RGB(255, 0, 150)
lngOtherMonthBackColor = RGB(75, 75, 100)

Set OracleSQL = CreateObject("VMDBOracle.SQLProcessor")
Set snpData = CreateObject("ADODB.Recordset")

'Specify the start of the month based on the current transaction date - set it back to the first day of the month
varDateStart = DateAdd("m", -11, DateSerial(DatePart("yyyy", dteTransactionDate), DatePart("m", dteTransactionDate), 1))

'Finding the end of the month is a little more difficult - we add 1 month to the transaction date, find the start of that month, and subtract one day
varDateEnd = DateAdd("d", -1, DateSerial(DatePart("yyyy", DateAdd("m", 1, dteTransactionDate)), DatePart("m", DateAdd("m", 1, dteTransactionDate)), 1))

'Set the starting colors
For i = 1 To 31
    lngDateBackColor(i) = RGB(230, 230, 230) 'Off White
    lngDateForeColor(i) = RGB(0, 0, 0) 'Black
Next

strSQL = "SELECT" & vbCrLf
strSQL = strSQL & "  SHIFT_DATE," & vbCrLf
strSQL = strSQL & "  EMPLOYEE_ID," & vbCrLf
strSQL = strSQL & "  INDIRECT_ID" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & "  EMPLOYEE_RECORD_TEST" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & "  SHIFT_DATE BETWEEN ? AND ?" & vbCrLf
strSQL = strSQL & "  AND INDIRECT_ID IS NOT NULL" & vbCrLf

'Specify the start of the month based on the current transaction date - set it back to the first day of the month
OracleSQL.SetParameter varDateStart, "DATE"

'Finding the end of the month is a little more difficult - we add 1 month to the transaction date, find the start of that month, and subtract one day
OracleSQL.SetParameter varDateEnd, "DATE"

If strEmployeeID <> "" Then
    strSQL = strSQL & "  AND EMPLOYEE_ID= ?" & vbCrLf
    OracleSQL.SetParameter strEmployeeID, "VARCHAR"
End If

strSQL = strSQL & "ORDER BY" & vbCrLf
strSQL = strSQL & "  SHIFT_DATE," & vbCrLf
strSQL = strSQL & "  EMPLOYEE_ID," & vbCrLf
strSQL = strSQL & "  INDIRECT_ID DESC"

OracleSQL.Sql = strSQL
Set snpData = OracleSQL.Execute

intOutputMonth = False
strHTML = ""
intRow = 0

'Shadow
strHTML = strHTML & "<div style=""position: absolute; width: 200px; height: 20px;"
strHTML = strHTML & "top: 7px;left: 7;"
strHTML = strHTML & "font-family: Arial; font-size: 18pt; color: #000000;"
strHTML = strHTML & "background-color: #FFFFFF;"">"
strHTML = strHTML & "<b>" & strEmployeeID & "</b></div>" & vbCrLf

strHTML = strHTML & "<div style=""position: absolute; width: 200px; height: 20px;"
strHTML = strHTML & "top: 5px;left: 5;"
strHTML = strHTML & "font-family: Arial; font-size: 18pt; color: #FFFF00;"">"
strHTML = strHTML & "<b>" & strEmployeeID & "</b></div>" & vbCrLf

strHTML = strHTML & "<div style=""position: absolute; width: 200px; height: 20px;"
strHTML = strHTML & "top: 6px;left: 6;"
strHTML = strHTML & "font-family: Arial; font-size: 18pt; color: #0000FF;"">"
strHTML = strHTML & "<b>" & strEmployeeID & "</b></div>" & vbCrLf

If Not (snpData Is Nothing) Then
    Do While Not (snpData.EOF)
        intShiftDay = DatePart("d", CDate(snpData("shift_date")))
        Select Case CStr(snpData("indirect_id"))
            Case "VAC", "VACB", "VACC", "VACF", "VACM"
                lngDateBackColor(intShiftDay) = lngVacationBackColor
            Case "HOL", "HOLC", "HOLF", "HOLB", "HOLM"
                lngDateBackColor(intShiftDay) = lngHolidayBackColor
            Case "BEREAVE", "BEREAVEC", "BEREAVEF", "BEREAVEB", "BEREAVEM"
                lngDateBackColor(intShiftDay) = lngBereavementBackColor
            Case "JURY", "JURYC", "JURYF", "JURYB", "JURYM"
                lngDateBackColor(intShiftDay) = lngJuryDutyBackColor
            Case "ABS", "ABSC", "ABSF", "ABSB", "ABSM"
                lngDateBackColor(intShiftDay) = lngAbsentBackColor
            Case "EXCUSE", "EXCUSEC", "EXCUSEF", "EXCUSEB", "EXCUSEM"
                lngDateBackColor(intShiftDay) = lngExcusedBackColor
            Case "MIL", "MILC", "MILF", "MILB", "MILM"
                lngDateBackColor(intShiftDay) = lngMilitaryBackColor
        End Select

        'See if the month will change
        intLastMonth = DatePart("m", CDate(snpData("shift_date")))
        varDateMonthStart = DateSerial(DatePart("yyyy", CDate(snpData("shift_date"))), DatePart("m", CDate(snpData("shift_date"))), 1)
        varDateMonthEnd = DateAdd("d", -1, DateAdd("m", 1, DateSerial(DatePart("yyyy", CDate(snpData("shift_date"))), DatePart("m", CDate(snpData("shift_date"))), 1)))

        snpData.MoveNext

        intOutputMonth = False

        If snpData.EOF Then
            intOutputMonth = True
        Else
            If DatePart("m", CDate(snpData("shift_date"))) <> intLastMonth Then
                intOutputMonth = True
            End If
        End If

        If intOutputMonth = True Then
            intWeekdayStart = Weekday(varDateMonthStart)

            intRow = intRow + 1
            strHTML = strHTML & "<div style=""position: absolute; width: 200px; height: 20px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25 + 10) & "px;left: " & CStr(1 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 14pt; color: #110011;"
            strHTML = strHTML & "background-color: #FFFFFF;"">"
            strHTML = strHTML & "<b>" & MonthName(DatePart("m", varDateMonthStart)) & " " & CStr(DatePart("yyyy", varDateMonthStart)) & "</b></div>" & vbCrLf

            intRow = intRow + 1
            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25 + 10) & "px;left: " & CStr(1 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #4400FF;"
            strHTML = strHTML & "background-color: #FFFFFF;"">"
            strHTML = strHTML & "Sun</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25 + 10) & "px;left: " & CStr(2 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #4400FF;"
            strHTML = strHTML & "background-color: #FFFFFF;"">"
            strHTML = strHTML & "Mon</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25 + 10) & "px;left: " & CStr(3 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #4400FF;"
            strHTML = strHTML & "background-color: #FFFFFF;"">"
            strHTML = strHTML & "Tue</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25 + 10) & "px;left: " & CStr(4 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #4400FF;"
            strHTML = strHTML & "background-color: #FFFFFF;"">"
            strHTML = strHTML & "Wed</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25 + 10) & "px;left: " & CStr(5 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #4400FF;"
            strHTML = strHTML & "background-color: #FFFFFF;"">"
            strHTML = strHTML & "Thu</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25 + 10) & "px;left: " & CStr(6 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #4400FF;"
            strHTML = strHTML & "background-color: #FFFFFF;"">"
            strHTML = strHTML & "Fri</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25 + 10) & "px;left: " & CStr(7 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #4400FF;"
            strHTML = strHTML & "background-color: #FFFFFF;"">"
            strHTML = strHTML & "Sat</div>" & vbCrLf

            intRow = intRow + 1
            'Fill in the days from the previous month
            For i = 1 To intWeekdayStart - 1
                strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
                strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(i * 25) & ";"
                strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #FFFFFF;"
                'Pad with leading 0s
                strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngOtherMonthBackColor)), "0") & Hex(lngOtherMonthBackColor) & ";"">"
                strHTML = strHTML & DatePart("d", DateAdd("d", -(intWeekdayStart - i), varDateMonthStart)) & "</div>" & vbCrLf
            Next

            For i = 1 To DatePart("d", varDateMonthEnd)
                'See if we need to jump to the next row
                If i > 1 Then
                    'See if the week day is less than the previous week day - if so, jump to the next row in the calendar since the week changed
                    If Weekday(DateAdd("d", i - 1, varDateMonthStart)) < Weekday(DateAdd("d", i - 2, varDateMonthStart)) Then
                        intRow = intRow + 1
                    End If
                End If
                intCol = Weekday(DateAdd("d", i - 1, varDateMonthStart))

                strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
                strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(Weekday(DateAdd("d", i - 1, varDateMonthStart)) * 25) & ";"
                strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #" & String(6 - Len(Hex(lngDateForeColor(i))), "0") & Hex(lngDateForeColor(i)) & ";"
                strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngDateBackColor(i))), "0") & Hex(lngDateBackColor(i)) & ";"">"
                strHTML = strHTML & CStr(i) & "</div>" & vbCrLf
            Next

            'Finish out the final week
            For i = Weekday(varDateMonthEnd) + 1 To 7
                strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
                strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(i * 25) & ";"
                strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #FFFFFF;"
                strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngOtherMonthBackColor)), "0") & Hex(lngOtherMonthBackColor) & ";"">"
                strHTML = strHTML & CStr(i - Weekday(varDateMonthEnd)) & "</div>" & vbCrLf
            Next

            intRow = intRow + 1
            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 12px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(1 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 7pt; color: #000000;"
            strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngVacationBackColor)), "0") & Hex(lngVacationBackColor) & ";"">"
            strHTML = strHTML & "VAC</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 12px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(2 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 7pt; color: #000000;"
            strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngHolidayBackColor)), "0") & Hex(lngHolidayBackColor) & ";"">"
            strHTML = strHTML & "HOL</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 12px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(3 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 7pt; color: #000000;"
            strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngBereavementBackColor)), "0") & Hex(lngBereavementBackColor) & ";"">"
            strHTML = strHTML & "BER</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 12px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(4 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 7pt; color: #000000;"
            strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngJuryDutyBackColor)), "0") & Hex(lngJuryDutyBackColor) & ";"">"
            strHTML = strHTML & "JUR</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 12px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(5 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 7pt; color: #000000;"
            strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngAbsentBackColor)), "0") & Hex(lngAbsentBackColor) & ";"">"
            strHTML = strHTML & "ABS</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 12px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(6 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 7pt; color: #000000;"
            strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngExcusedBackColor)), "0") & Hex(lngExcusedBackColor) & ";"">"
            strHTML = strHTML & "EXC</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 12px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(7 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 7pt; color: #000000;"
            strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngMilitaryBackColor)), "0") & Hex(lngMilitaryBackColor) & ";"">"
            strHTML = strHTML & "MIL</div>" & vbCrLf

            'Reset the starting colors
            For i = 1 To 31
                lngDateBackColor(i) = RGB(230, 230, 230) 'Off White
                lngDateForeColor(i) = RGB(0, 0, 0) 'Black
            Next
        End If
    Loop

    snpData.Close
End If

'Fire up Internet Explorer
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Left = 0
objIE.Top = 0
objIE.Width = 260
objIE.Height = 700
objIE.StatusBar = False
objIE.MenuBar = False
objIE.Toolbar = False

objIE.Navigate "about:blank"
objIE.Document.Body.InnerHTML = strHTML
objIE.Document.Title = "Attendance " & strEmployeeID
objIE.Visible = True

'with the help of custom program, set a 1 second delay, then force the Attendance web page to the top
Set objShell = CreateObject("WScript.Shell")
objShell.Run("C:\BringToTop.exe " & Chr(34) & "Attendance " & strEmployeeID & Chr(34) & " 1")

Set objShell = Nothing
Set snpData = Nothing
Set objIE = Nothing
Set OracleSQL = Nothing

While the above uses a custom DLL for the database connection, a standard ADO connection will work just as well.  The script also uses a custom program that I created called BringToTop that simply addresses the “pop under” behavior on Vista and Windows 7.

So, which employee has the best attendance record?





Network Monitoring Experimentations 2

16 12 2009

December 16, 2009

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

The first post in this series introduced Wireshark, demonstrated some of the problems that may be detected with the tool, demonstrated what happens with various fetch array sizes in the presence of configuration problems, and compared a low latency 100Mb connection with a higher latency wireless 802.11G (54Mb) connection from the same client computer during a transfer of database table data.

The connection attempt shown in the screen capture from the previous post was of the computer connecting through the wireless connection to a server during the launch of an ERP package. That connection attempt was slowed a bit by the network latency. That screen shot is shown below.

Why introduce the same screen shot again?  Let’s look at what happens sometimes when application versions are upgraded and in the process (sometimes undocumented) features are added to applications.  Take, for instance, this ERP program’s connection attempt that required at least 10 seconds to complete on a gigabit connection, when it is able to complete in less than a second on the much slower wireless connection.  What changed?  Taking a look at a couple of the screen shots of the slow connection attempt will help (the client computer is .185, the database server is .45, and the file server is .40):

In the above we see the client computer searching the file server for a file named DNSAPI.dll that did not exist.  Then we see a DNS lookup attempt for a device named TNS.k-mm.com (for those following along, this was caused by the TNS: keyword on the remotedbname= entry in the SQL.INI file).  Notice that the timestamp of the request is at 17:25:14.683144.  When the client is told by the DNS server for the second time that the name does not exist, the timestamp has advanced to 17:25:14.686069.  Next, the client tried to resolve the name TNS first by a direct WINS (NBNS) lookup and then by a WINS broadcast.  The timestamp of the next client activity after the WINS lookup is 17:25:16.967553, so roughly 2.3 seconds were wasted.  But, that is not the most significant lost time interval.

The above shows the actual connection starting at 17:25:16.988701 and completing at 17:25:17.001425, which is very quick, but are we done yet?

The above screen shot shows the client looking for a file named nethasp.ini on the file server, but is not able to find that file.  Important?

The above shows that the client finally gave up looking for the nethasp.ini file at 17:25:17:209969, and at 17:25:17:232671 started sending a series of four full broadcast packets, spaced roughly one second apart, to a destination port number that Wireshark has aliased as tcpnethaspsrv.  The client finally started communicating again with the database server at 17:25:21.344038.  So, between 17:25:14.686069 and 17:25:21.344038 the client mostly just sat idle waiting for a response that never came.  Yes, Wireshark ultimately identified the cause of the problem and helped find a course of action that was fully in place at 05:30:00 the next morning, with the new version of the ERP program starting in less than a second just like the old version.

Wireshark exposes a lot of detail from the packets that it captures.  Here are two screen shots of a network packet containing a SQL statement that was transmitted in clear text:

Notice that the raw packet data appears at the bottom and is expanded into human readable text in the upper pane.  Clicking a line in the upper pane (for instance one that shows the destination MAC address) shows that information in the raw packet data at the bottom of the window.





Network Monitoring Experimentations 1

15 12 2009

December 15, 2009 

(Forward to the Next Post in the Series)

Wireshark, formerly known as Ethereal, is a free network packet capture program for several operating system platforms.  Wireshark is able to reveal a wide range of network communication problems:
http://www.wireshark.org/download.html

The problems might include self-inflicted problems that result from implementing changes to improve one performance problem, as suggested here:
  http://www.dslreports.com/faq/tweaks 
or here:
  http://download.microsoft.com/download/2/8/0/2800a518-7ac6-4aac-bd85-74d2c52e1ec6/tuning.doc

The problems might include client computers with 100Mb network cards connected into gigabit switches, which then results in unexpected packet retransmits until the client computer is moved to a 100Mb switch that then connects to the gigabit switch (this is a rare problem, somewhere I have a Wireshark capture that shows this behavior).

The problems might include failing network equipment or bad network wiring or excessive EMF in the environment that distorts traffic on CAT 5e, CAT 6, and wireless connections.

The problems might include a client application that unexpectedly takes 10 to 20 seconds to “log in” when it should take 1 second or less.

The problems might include issues with inappropriate fetch array sizes, poor choices for SDU size, forcing jumbo TCP/IP frames through intermediate network hardware that does not support frame sizes larger than roughly 1500 bytes.

The problems might include a high latency network or WAN connections.

First, let’s look at a Wireshark capture of a successful connection attempt from a client computer connected to the network by an 802.11G (54Mb) wireless connection:

There is nothing terribly out of the ordinary with the above.  The client computer in packet 1 sent an ARP broadcast packet to the network asking for the MAC address of the network card on the network that is associated with IP address 192.185.10.52, and the response should be returned to the client computer at IP address 192.185.10.51.  Roughly 0.002 seconds later the client computer attempted to connect to the database server using the TNS protocol.  Roughly 0.1 seconds later the connection completed.  Roughly 0.06 seconds after the connection attempt finished, the client computer started sending queries to the database server.  There were a couple of delays between the submission of the SQL statement and the response from the server, such as the 0.12 second delay between packet 26 and 27, but nothing significant.

It is quite possible that network problems will occur, as in the following:

In the above, notice that the server (IP address 192.185.10.52) is resending packets that it assumed were lost in transit due to the long delays between packets without receiving an ACK from the client computer (the ACK packet may have been lost).  Notice also the long delays between packets that might either be a symptom of network problems, or CPU/Wait Event that could be captured in a 10046 extended SQL trace.

Next, let’s take a look at the effects of adjusting the fetch array size (number of rows retrieved in each fetch call – ARRAYSIZE setting in SQL*Plus) when executing a SQL statement in SQL*Plus that selects from a table having an average row length of 245 bytes, with the client on a wired 100Mb connection, and with the standard Oracle SDU size.  The server is still at IP address 192.185.10.52, the client computer (same as used above) is now at IP address 192.185.10.53.

Fetch Array Size 1:

Fetch Array Size 15:
 

Fetch Array Size 100:

Fetch Array Size 1000:

In the above, you might notice that after every two packets that are sent by the server, the client computer sends back an ACK packet – this is typical behavior.  So, what happens when someone “optimizes” the network card parameters?

Fetch Array Size 15 with “Optimized” ACK frequency (Same Data):

Fetch Array Size 100 with “Optimized” ACK frequency (Same Data):

In the above, notice the number of packets transmitted before the client sends an ACK packet, and typically just before the client sends the ACK, there is a delay of roughly 0.2 seconds.  OK, a little slower.  So, what happens when we switch from the table with the average row length of 245 bytes to a table containing roughly 1MB to 2MB JPEG pictures?  Compare how long it takes to reach the 35th packet in the following two screenshots:

Fetch Array Size 100:

Fetch Array Size 100 with “Optimized” ACK frequency (Same Data):

(Late Additions to the Post)

Fetch Array Size 100 – Table with Pictures (802.11G):

Fetch Array Size 100 – Table with Pictures, Optimized ACK (802.11G):

SQL*Plus SELECT from the table with the average row length of 245 bytes using the 802.11G (54Mb) connection:

Fetch Array Size 1 with “Optimized” ACK frequency:

Fetch Array Size 15 with “Optimized” ACK frequency (Same Data):

Fetch Array Size 100 with “Optimized” ACK frequency (Same Data):

Fetch Array Size 1000 with “Optimized” ACK frequency (Same Data):





Statspack/AWR Report Resources

14 12 2009

December 14, 2009

On July 15, 2009, the following request appeared on the OTN forums:
http://forums.oracle.com/forums/thread.jspa?threadID=929170

Please provide me with a good link for understanding the AWR report.

One of the responders in the thread provided a link to the following document:
http://mhprofessional.com/downloads/products/0072263059/0072263059_ch14.pdf

- -

I spent about 10 minutes (OK, maybe 30-45 minutes) skim reading the 71 pages in that chapter. I found several items in the chapter that made me ask, is that correct? The dents in my desk from my forehead indicate that the author probably should have had someone else proofread the chapter. As the book was published in 2007, it should have been written to cover Oracle 10g R2 (10.2.0.x) – it did mention Oracle 10g R2. In my skim reading I found the following interesting quotes that I posted in that OTN thread without explanation as to why I selected the quotes (I thought someone would ask):

#1 Page 23:

“Some DBAs (usually those trying to sell you a tuning product) minimize the importance of hit ratios (proactive tuning) and focus completely on waits (reactive tuning), since focusing on waits is a great way to quickly solve the current burning problems. By monitoring the Instance Efficiency section (and using all of STATSPACK and Enterprise Manager), the DBA will combine reactive and proactive tuning and will find some problems before the users scream or wait events hit the top 5 list. Hit ratios are one important piece of the puzzle (so are waits).”

#2 Page 24:

“Hit ratios are a great barometer of the health of your system. A large increase or drop from day to day is an indicator of a major change that needs to be investigated.”

#3 Page 27:

“Try to cache small tables to avoid reading them into memory over and over again. Locate the data on disk systems that have either more disk caching or are buffered by the OS file system cache. DB_FILE_MULTIBLOCK_READ_COUNT can make full scans faster (but it could also influence Oracle to do more of them).”

#4 Page 42:

“Consistent gets: The number of blocks read from the buffer cache for queries without the SELECT FOR UPDATE clause. The value for this statistic plus the value of the “db block gets” statistic constitute what is referred to as logical reads (all reads cached in memory). These are usually the CURRENT version of the block, but it can also be a Consistent Read (CR) version.”

#5 Page 42:

“Hit Ratio = (Logical Reads – Physical Reads) / Logical Reads” “The buffer hit ratio should be above 95 percent. If it is less than 95 percent, you should consider increasing the size of the data cache by increasing the DB_CACHE_SIZE initialization parameter (given that physical memory is available to do this).”

#6 Page 43:

“If chained rows are indicated, the problem needs to be fixed as soon as possible. Chained rows can cause severe degradation of performance if a large number of rows are chained.”

#7 Page 44:

“The parameter that can be set in the init.ora to help improve the read time is the DB_FILE_MULTIBLOCK_READ_COUNT parameter, which controls the number of blocks that can be read in one I/O when a full table scan is being performed. This can reduce the number of I/Os needed to scan a table, thus improving the performance of the full table scan. Unfortunately, the optimizer might do more full table scans as a result of setting DB_FILE_MULTIBLOCK_READ_COUNT (you don’t want this behavior), so you may also need to set the OPTIMIZER_INDEX_COST_ADJ to a number, such as 10, to eliminate this problem and drive the use of indexes.”


I did not really look at the screenshots, nor try to see whether the majority of the information in the chapter was correct. But now looking at the first picture on page 20, I see that the author decided to create an AWR/Statspack report for a 23.5 hour time period – a very bad idea as short-term, intense problems are completely lost in the averages of the long duration report. The author apparently never mentioned that this was a bad idea.

Why did I select the quotes?

#1 Hit ratios are mostly meaningless. If the buffer cache hit ratio is very close to 100% (or any other number, for that matter), is that good, bad, or unknown? I have examples that show a 100% buffer cache hit ratio, yet very, very bad performance was present. See these threads for discussions of the buffer cache hit ratio:
http://forums.oracle.com/forums/thread.jspa?threadID=582929&start=0&tstart=0
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/1a946dbe8dcfa71e
 

#2 If the buffer cache hit ratio gets closer to 100% is that good? If it falls closer to 80% is that bad? If remains the same is that good or bad? Maybe the value predicts the weather?
 

#3 Small tables repeatedly read by full tablescans are more likely to remain in the Oracle buffer cache by default. Moving the tables to different disks promotes “hot” disks and “cold” disks in the system, rather than spreading the read and write access evenly across all disks, as is the principle of the Oracle SAME configuration. As of Oracle 10g R2, the DB_FILE_MULTIBLOCK_READ_COUNT parameter should not be set – instead Oracle will automatically set it to a value which attempts to maximize the read size based on the operating system, typically 1MB, or a value of 128 with an 8KB block size. As of Oracle 10g R1 (10.1.x), CPU costing (NOWORKLOAD) is enabled by default. With WORKLOAD CPU costing, the MBRC value from SYS.AUX_STATS$ determines the calculated cost of a tablescan, not DB_FILE_MULTIBLOCK_READ_COUNT (DB_FILE_MULTIBLOCK_READ_COUNT may still have an effect if the DBA has not yet collected WORKLOAD CPU statistics with a command like the following:

EXEC DBMS_STATS.GATHER_SYSTEM_STATS('interval',interval=>60) 

#4 An absolutely confusing definition of consistent gets, which are different from current mode gets. The consistent get, while coming from memory, may have involved one or more physical disk reads. The “db block gets” statistic is actually the same thing as a current mode get. A consistent get attempts to build a version of a block as of a specific time, or more accurately, as of a specific SCN through the application of zero, one, two, or more undo blocks. The specific SCN to which the block is rolled back is dependent on the current isolation level specified for the session. A current mode get will contain both committed and uncommitted data since it is the version of the block as it exists at that instant. See these blog posts:
http://jonathanlewis.wordpress.com/2009/06/09/quiz-night/
http://jonathanlewis.wordpress.com/2009/06/12/consistent-gets-2/

#5 Even if the buffer cache hit ratio were useful, that is not the correct formula to calculate the value. Per the Oracle 10g R2 Performance Tuning Guide (and the 11g R2 Performance Tuning Guide), the formula is:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/memory.htm

1 - (('physical reads cache') / ('consistent gets from cache' + 'db block gets from cache')

#6 Chained rows happen when a row is too large to fit in a single database block. It would be necessary to recreate the database with a larger block size, or add a new tablespace with a larger block size and make changes in the initialization parameters to set aside memory for the non-default block size and then move the objects into the new tablespace – this is not a recommended approach:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/logical.htm#i15914 
http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/iodesign.htm 

#7 Init.ora files started falling out of favor with Oracle 9i R1 (9.0.1.x), instead replaced with spfiles that allow initialization parameters to be permanently modified with ALTER SYSTEM commands. In addition to controlling the maximum number of blocks read during a full table scan, the DB_FILE_MULTIBLOCK_READ_COUNT parameter also controls the maximum number of blocks read during a fast full index scan. Full tablescans, especially if they complete faster, are not necessarily evil. The need to adjust OPTIMIZER_INDEX_COST_ADJ parameter decreased significantly with the introduction of CPU costing (introduced with Oracle 9i, enabled by default with 10g). Setting OPTIMIZER_INDEX_COST_ADJ to a very low value, such as 10, decreases the calculated costs of index accesses to 10% of their original value – this means that two different index access methods could then have the same rounded calculated costs, yet have very different execution speeds.

After I wrote the above quotes from the link into the thread, I remembered that someone else provided a critical review of this document. Reading this review, I see that I missed a lot of less obvious problems with the book chapter (I admit looking for the low hanging fruit):
http://www.amazon.co.uk/Oracle-Database-Performance-Techniques-Osborne/dp/0072263059

In case you are left wondering where to look for Statspack/AWR information, I suggest starting here when trying to learn how to read AWR/Statspack reports:
http://jonathanlewis.wordpress.com/2006/11/29/analysing-statspack-pt1/ 
http://www.oracle.com/technology/deploy/performance/pdf/statspack_opm4.pdf 
http://hoopercharles.wordpress.com/2009/11/30/11-2-0-1-statspack-report-contents/

Other Resources:
Metalink Doc ID 228913.1 “Systemwide Tuning using STATSPACK Reports”
Metalink Doc ID 94224.1 “FAQ- Statspack Complete Reference”

Other Topics that Might be of Interest:
Metalink Doc ID 390374.1 “Oracle Performance Diagnostic Guide”
Metalink Doc ID 438452.1 “Performance Tools Quick Reference Guide”





Toy Project for Performance Tuning 2

13 12 2009

December 13, 2009

I previously mentioned that I created a “Toy” project for performance tuning, but only provided a couple screen captures and very little descriptive text:
http://hoopercharles.wordpress.com/2009/11/30/toy-project-for-performance-tuning/

The program is perhaps well beyond the “Toy” stage.  My program is not available for purchase, but I thought that I would share a couple of its features as an incentive for you to create something usable for yourself.  Below are a couple more screen captures of the program (with descriptions).

Configure Data Change Logging:

 The Data Change Logging feature is used to build SQL*Plus compatible scripts that create logging tables, triggers to populate the logging tables, and optionally a database schema and tablespace for storing the logged data.

~~~~~~~~~~

Keyword Search:

The Search feature performs a keyword search on statistics, wait events, initialization parameters, hints, and other topic categories.  Such a search may reveal relationships between wait events, initialization parameters, and workarounds/solutions for various problems.

~~~~~~~~~~

SQL Monitor:

The SQL Monitor feature examines the shared pool for potentially high load SQL statements.  Clicking on a row in the grid displays the SQL statement, while double-clicking a row displays the execution plan for all child cursors, the reason why the child cursor was created, and the defined bind variable types.  Placing a check in Log File writes the grid contents to a file on the next refresh, and if SQL Statements is also checked, the SQL statement will also be written to the log file.  CPU Time and Elapsed time are only available on Oracle 9i R2 and above.

~~~~~~~~~~

10046 Trace File Parser:

The Drag & Drop Extended Trace feature performs extensive analysis of 10046 trace files that were captured at levels 4, 8, and 12.  A minimum of four time coordinated analysis files are generated for each trace file that is analyzed.  Several options are available to determine what type of data is retrieved from the trace file.

The wait events output is sent to Microsoft Excel.  The Trace Time column indicates the time offset from the start of the trace file at which the wait event appeared in the trace file.  The Wait Time column indicates the duration of the wait event in 1/1000th of a second.  The Wait Event column indicates the type of wait – a description of the wait event appears at the bottom of the wait events output.  The wait events Raw Details displays the wait event line from the trace without interpretation.  The Oracle Cursor Num column displays an identifier that can be used to relate the wait event back to a specific SQL statement in the other analysis files.

The SQL Parse Order analysis file lists each SQL statement in the order in which the application requested a parse call.  Parse, execute, fetch, bind variables, and row source execution plan for each SQL statement is listed together, along with a summary per SQL statement parsed.  Cursor number is retrieved directly from the 10046 trace file, and may not be directly dependent on application coding.  Ver number indicates the number of cursors that were parsed at the Cursor number through this point in the trace file.  Parse at indicates the time offset in seconds from the start of the trace file until the parse call was issued.  TD Prev indicates the time difference between the current SQL statement and the previous parse call.  EXECs indicates the total number of execution calls from the application for the current SQL statement.  FETCHs indicates the total number of number of fetch requests to retrieve rows from the database for the current SQL statement.  CPU S indicates the number of seconds of database server CPU time required for the SQL statement.  CLOCK S indicates the elapsed time for the SQL statement – the elapsed time will frequently differ from the CPU time, unless the server’s CPU is the only bottleneck in the database.   ROWS indicates the number of rows retrieved or affected by the SQL statement.  PHY RD BLKs indicates the number of blocks that had to be read from disk to satisfy the SQL statement.  CON RD BLKs (Mem) indicates the number of blocks read from memory in consistent read mode – a logical read, which is only roughly 100 times faster than a physical read due to overhead related to consistent reads.  CUR RD BLKs (Mem) indicates the number of blocks read in current mode, which is considerably less expensive that a consistent read.  SHARED POOL MISS indicates the number of times a hard parse was required – if during an execute or fetch call, such parses are expensive and may be partially caused by setting CURSOR_SHARING to SIMILAR.

The SQL Execution order analysis file outputs the SQL statements and bind variable values in the order of execution as the trace file is read.  Minimal diagnostic data is provided in this type of analysis file, although it may help to determine the looping structure of SQL statements that are executed, where the output of one SQL statement is fed in as the input for another SQL statement.

The Grouping Similar SQL statements analysis file attempts to group together SQL statements that are parsed multiple times, rather than only being parsed once and executed many times.  This type of analysis file accumulates the statistics for the similar SQL statements, indicating the percentage of the total execution time each group represents.  While individual executions may total only 0.01 seconds for a SQL statement, if the SQL statement is executed 30,000 times, the SQL statement should probably be analyzed to determine if its execution plan is as efficient as possible.  This analysis file allows one to drill down to the root cause of the problematic SQL statement that represents the greatest percentage of the total run time.  Wait events are summarized at the beginning of the file, as well as with each group of similar SQL statements.

~~~~~~~~~~

Configure Session Tracing:

Session tracing is an important component of database tuning.  Enabling and disabling various types of traces is possible within the Hyper-Extended Performance Monitor.  Activated tracing of sessions is automatically disabled when the Hyper-Extended Performance Monitor program is closed.
10046 Trace: Performance specific to each SQL statement executed.
10053 Trace: Cost Based Optimizer decisions during hard parses.
10032 and 10033 Trace: Sort related trace files.
10104 Trace: Hash join related trace files.

~~~~~~~~~~

Advanced Initialization Parameters:

The Advanced Init Parameter feature retrieves all system level and session level normal as well as hidden parameters in the Oracle database instance.  As a general rule, never modify the value of a hidden parameter (those that begin with _).  A brief description of each parameter is provided, and in many cases a longer description is presented at the bottom of the window.  Session specific parameters are displayed after the system parameters, with the session’s SID displayed in the Type column in the table.  All parameters are simultaneously output to a file in the C:\Oraclelog folder, with a name such as “All Initialization Parameters 200710021321.txt”.

Most of the hidden parameters are only visible to the SYS user.  As such, the Hyper-Extended Oracle Performance Monitor will prompt for the SYS password.  If the SYS password is not provided, only the non-hidden parameters will be presented.  Oracle has several hundred initialization parameters, many of which are hidden (those that begin with _ ) and should not be modified without the guidance of Oracle support.  Following the list of system wide parameters are query optimization parameters which are specific to individual sessions.

~~~~~~~~~~

Smart Logging Configuration:

To decrease the database server’s CPU impact due to logging, logging intervals may be specified to occur less frequently than once every 60 seconds.  Additional options are available to allow the logging capture to start in response to triggering events, such as increased CPU activity, blocking locks, etc.

The program also accepts various command line arguments to control logging and exit the program after logging for a specified number of minutes.

~~~~~~~~~~

Logging Summary While Capturing a Smart Log:

When logging is enabled, a quick overview screen is presented that shows a portion of what was logged during the previous time intervals.  Session Wait Reasons and Session Locks appear in the tables in real-time when they happen.

~~~~~~~~~~ 

System-Wide Overview of Log Data:

The top wait events for the log interval are displayed at the upper left, a graphical display of the history for the selected statistic is displayed at the top center (blue indicates the current period), and a graphical display of the top 30 sessions contributing to the statistic is displayed at the top right.  The bottom left provides a description of the statistic and possible additional tips for addressing problems.  The middle section provides a listing of various performance statistics captured during the logging interval – click one of those statistics to set it as the current statistic.  When a significant event occurs, such as a spike in CPU usage, click the Investigate button to display additional statistics.

~~~~~~~~~~

Session-Level Drill-Down into Logged Data:

Left-clicking a session’s bar in the Review Log Stats window causes the session specific performance statistics to be displayed for the time interval.

~~~~~~~~~~

Investigating the Log Interval:

The Investigate Log Interval window provides additional logging detail for the current log interval, but it is possible to view the data for a range of log intervals.  The top left grid shows system-wide wait events encountered during the logging interval range.  Immediately below the system-wide wait events are the session level wait events.  Below the session level wait events are the session level wait event reasons that were identified during the logging.  These statistics may be used to identify data file/block level contention between sessions, hard parsing that causes excessive wait events, etc – more information is revealed by double-clicking a row.  The bottom left grid shows details of the sessions that were blocked during the specified time intervals – more information is revealed by double-clicking a row.  The top right grid shows data file activity during the specified time intervals – summary information is revealed by double-clicking a row.  Below the file activity is the rollback/undo activity during the specified time intervals.  Below the rollback/undo activity is the data segment changes, which shows expansion and contraction of indexes, tables, and other objects in the database during the logging interval.   Below the data segment changes are the data block wait statistics, which related to buffer busy wait events – more information is revealed by double-clicking a row.  The bottom right shows latch contention that occurred during the logging interval – significant latch contention can cause performance issues – more information is revealed by double-clicking a row.

Double-clicking a couple of the grids produces the following:

I wonder what that Interpret Statistics button does?

~~~~~~~~~~

There are of course more screen captures, but those may wait for some other time.





Use VBS to Search for Oracle Books using Google’s Book Library

13 12 2009

December 12, 2009

Below is a somewhat complicated VBS script that interacts with Internet Explorer to submit a query to books.google.com.  Once Google prepares the web page, the VBS script parses the raw HTML code in the web page to generate a new web page containing the list of matching books supplied by Google.  Selecting a book and then clicking the View button opens that book on the Google books site.

The search keyword is specified on the objIESource.Navigate line at the end of the website address.  A plus sign should appear between each search keyword.  To exclude a particular word from the search, prefix the word with a minus sign, for example to search for the keywords Oracle and SQL, but exclude the word dummy:

objIESource.Navigate "http://books.google.com/books?um=1&q=oracle+sql+-dummy"

Note that there are various ways to extend this example.

Dim objIE            'For the data entry form where we present the harvested book list
Dim objShell         'To add a delay, may throw an error when executed in a Visual macro
Dim strHTML          'Holds what we are displaying on our data entry form
Dim intFlag          'Indicates if the user clicked OK or closed the browser window
Dim i                'For our counter
Dim objIESource      'Holds the data source web page
Dim strHTMLSource    'Holds the HTML contents of the source web page
Dim intStart         'The starting position of title="  in the source web page
Dim intEnd           'The position of the next " after title="  in the source web page
Dim intApproxStart   'The book titles appear after the first entry of coverthumb, so we will start there
Dim strBook(1000)    'Holds the book titles found on this page
Dim intBookCount     'Counter for the number of books found
Dim strFind          'What to find in the HTML code which indicates that the data of interest will follow
Dim intFindLen       'The length of the what to find string
Dim intLinkStart     'The starting position of title="  in the source web page
Dim intLinkEnd       'The position of the next " after title="  in the source web page
Dim strBookLink(1000)'Holds the link to the book found on this page
Dim strLinkFind      'What to find in the HTML code which indicates that the page link will follow
Dim intLinkFindLen   'The length of the what to find link string
Dim strSelectedBook  'The name of the selected book
Dim strBookAddress   'The web address of the book
Dim adsFile          'Used if we want to write the downloaded web page to the hard drive

On Error Resume Next
'Set objShell = CreateObject("WScript.Shell")
Set objIESource = CreateObject("InternetExplorer.Application")
objIESource.Navigate "http://books.google.com/books?um=1&q=oracle+tuning"
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Navigate "about:blank"

For i = 1 to 10000
    'Give it some time to prepare the objIESource
Next
Do While objIESource.Busy <> False
    'objShell.Sleep 500 'Edit: This line was supposed to be replaced with the following line Dec 13, 2009
    Wscript.Sleep 200
Loop

intBookCount = 0
strHTMLSource = cStr(objIESource.Document.Body.InnerHTML)    'Retrieve the raw HTML code from the web page

'Uncomment to save the web page to the hard drive
'Set adsFile = CreateObject("ADODB.Stream")
'adsFile.Type = 2
'adsFile.Charset = "iso-8859-1"
'adsFile.Open
'adsFile.WriteText(strHTMLSource)
'adsFile.SaveToFile "c:\InnerHTML.txt", 2
'adsFile.close
'Set adsFile = Nothing

'In the same HTML page, the first book title starts after the first entry of: 
intApproxStart = InStr(strHTMLSource, "coverthumb") + 1
strFind = "title=" & Chr(34)
intFindLen = Len(strFind)
strLinkFind = "<A href=" & Chr(34)
intLinkFindLen = Len(strLinkFind)

'Find the start of the first book title
'Might be listed like this in the HTML code:  title="Excel 2007 VBA Programming For Dummies"
intStart = InStr(intApproxStart, strHTMLSource, strFind)

Do While intStart > 0
    'Find the end of the book title
    intEnd = InStr(intStart + intFindLen, strHTMLSource, Chr(34))
    If intEnd > 0 Then
        intBookCount = intBookCount + 1
        strBook(intBookCount) = Mid(strHTMLSource, intStart + intFindLen, intEnd - (intStart + intFindLen))

        'Find the link to the book title
        intLinkStart = InStr(intEnd, strHTMLSource, strLinkFind)
        If intLinkStart > 0 Then
            intLinkEnd = InStr(intLinkStart + intLinkFindLen, strHTMLSource, Chr(34))
            If intLinkEnd > 0 Then
                strBookLink(intBookCount) = Mid(strHTMLSource, intLinkStart + intLinkFindLen, intLinkEnd - (intLinkStart + intLinkFindLen))
            Else
                strBookLink(intBookCount) = ""
            End If
        Else
            strBookLink(intBookCount) = ""
        End If

        'Find the start of the next book title
        intStart = InStr(intEnd, strHTMLSource, strFind)
    Else
        Exit Do
    End If
Loop

'Edit: place a single quote in front of the following two lines to prevent the list of books from disappearing, Dec 13, 2009
objIESource.Quit
Set objIESource = Nothing

strHTML = strHTML & "<form name=""BookFind"">" & vbCrLf
strHTML = strHTML & "<input type=hidden id=""txtOK"" value="" "">" & vbCrLf
strHTML = strHTML & "Book:<br /> <select size=""23"" id=""lstBooks"" style=""width:450"">" & vbCrLf

For i = 1 To intBookCount
    strHTML = strHTML & "<option value=" & Chr(34) & strBook(i) & Chr(34) & ">" & strBook(i) & "</option>" & vbCrLf
Next
strHTML = strHTML & "</select>" & vbCrLf
strHTML = strHTML & "<p><center><input type=button value=""View"" id=""cmdOK"" onclick=""document.getElementById('txtOK').value='OK';""></center>" & vbCrLf
strHTML = strHTML & "</form>" & vbCrLf

objIE.Document.Body.InnerHTML = strHTML
objIE.Document.Title = "Select Book Title from Google Books"
objIE.Left = 0
objIE.Top = 0
objIE.Width = 500
objIE.Height = 520
objIE.Statusbar = False
objIE.Menubar = False
objIE.Toolbar = False
objIE.Visible = True

For i = 1 to 10000
    'Give it some time to prepare the objIE
Next
Do While objIE.Busy <> False
    Wscript.Sleep 200
Loop

intFlag = 0
'loop until the button is clicked
Do While intFlag = 0
    If Err <> 0 Then
        intFlag = -1
    End If
    If objIE Is Nothing Then
        'User closed ID
        intFlag = -1
    Else
        If objIE.Document.All.txtOK.Value <> " " Then
            intFlag = 1
        End If
    End If
    Wscript.Sleep 250
Loop

If intFlag = 1 Then
    'Copy in the values from the web page
    strSelectedBook = objIE.Document.Body.All.lstBooks.Value

    'Try to find the associated link to the book
    For i = 1 to intBookCount
        If strBook(i) = strSelectedBook Then
            'Found the book
            strBookAddress = strBookLink(i)
            'Extra credit - display the link associated with the selected book
            'Comment out the following objIE lines and uncomment the objIE.Quit
            '  if the link associated with the book should not be displayed
            objIE.Navigate "about:blank"
            objIE.Width = 800
            objIE.Height = 600
            objIE.Statusbar = True
            objIE.Menubar = True
            objIE.Toolbar = True
            objIE.Navigate strBookLink(i)
            Exit For
        End If
    Next
    'objIE.Quit
End If

Set objIE = Nothing
Set objShell = Nothing

The list of results:

The selected book:





Find the DB Names on a Remote Windows Server using VBS

12 12 2009

December 12, 2009

The following question appeared in one of the OTN forums a couple months ago:
http://forums.oracle.com/forums/thread.jspa?threadID=958849 

I am new in this database field.
I have a small query that how can i find the number of database running on a particular host in windows environment by query?

 There is no query in Oracle which will show you the database instances on a Windows server. That said, here is a portion (adaptation) of a WMI script from the book “Expert Oracle Practices: Oracle Database Administration from the Oak Table” which will do it for you as long as the database instance is started (even on a remote server if you have administrator privileges on the remote server):

Dim objWMIService
Dim strSQL
Dim strComputer
Dim colItems
Dim objItem

strSQL = "SELECT * FROM Win32_Process Where Name like 'Oracle%'"

strComputer = "."  ' the . indicates the local computer
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\CIMV2")

Set colItems = objWMIService.ExecQuery(strSQL,"WQL",48)

For Each objItem in colItems
  Wscript.Echo "Instance: " & objItem.CommandLine & vbCrLf
Next

To run the above script, save it as a text file with the extension .VBS. Open a Windows command prompt, and type cscript followed by the name of the script:

C:\> cscript c:\databases.vbs

Note that you must be an administrator on the remote computer (or a domain administrator) to remotely query the running processes on another Windows computer. 





Print Oracle Data to a Barcode Label with a Zebra Printer using VBS and Excel

12 12 2009

December 12, 2009

Here is the problem: You know how to query the database using a Windows Scripting Host VBS file, but you have no way to print a barcode label using the data to the networked Zebra label printer and there is no printer driver installed for the Zebra printer.  Fortunately, Zebra printers understand ZPL (Zebra Printer Langauge), so we are able to easily work around the issue of not having a driver for the printer installed.  The second problem is how do we send the print job to the printer?  This is where Excel comes to the rescue.  Excel is able to work with the Windows API to start and submit a print job to a printer that is not necessarily installed on the local computer.

First, the Windows Scripting Host VBS file (note that this uses a DLL that I wrote which hides the database name, username, and password, as well as simplifying the process of submitting SQL statements with bind variables to the database):

'PrintLabel.vbs
Dim intParentPartLine
Dim strRawText
Dim strSQL
Dim strWhereUsed
Dim snpData
Dim OracleSQL
Dim objExcel

On Error Resume Next

Set OracleSQL = CreateObject("VMDBOracle.SQLProcessor") 'This is my custom DLL
Set snpData = CreateObject("ADODB.Recordset")
Set objExcel = CreateObject("Excel.Application")

strOut = ""
With objExcel
    'Open the Excel file containing the macro functions
    .Workbooks.Open "C:\ExcelMacroFunction.xls"

    strRawText = "~SD25^XA" 'Set Darkness, Label start
    strRawText = strRawText & "^SZ2" 'Enable ZPL2
    strRawText = strRawText & "^JZ" 'Reprint on error
    strRawText = strRawText & "^PR8,8,8" 'Print speed 8" per second, 8" per sec slew, 8" per sec backfeed
    strRawText = strRawText & "^LH12,30" 'Label home position in pixels
    strRawText = strRawText & "^FO5,0^A0,40,40^FD" & ID & "^FS" 'Proportional font
    strRawText = strRawText & "^FO5,40^A0,20,20^FD" & DESCRIPTION & "^FS" 'Proportional font
    strRawText = strRawText & "^FO5,80^A0,15,15^FD**** WHERE USED ****^FS" ' 'Proportional font

    strSQL = "SELECT DISTINCT" & vbCrLf
    strSQL = strSQL & "  R.WORKORDER_BASE_ID AS TOP_PART_ID," & vbCrLf
    strSQL = strSQL & "  WO.PART_ID AS SUB_PART_ID" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  REQUIREMENT R," & vbCrLf
    strSQL = strSQL & "  WORK_ORDER WO" & vbCrLf
    strSQL = strSQL & "WHERE" & vbCrLf
    strSQL = strSQL & "  WO.TYPE='M'" & vbCrLf
    strSQL = strSQL & "  AND R.WORKORDER_TYPE='M'" & vbCrLf
    strSQL = strSQL & "  AND R.PART_ID= ?" & vbCrLf
    strSQL = strSQL & "  AND R.WORKORDER_TYPE=WO.TYPE" & vbCrLf
    strSQL = strSQL & "  AND R.WORKORDER_BASE_ID=WO.BASE_ID" & vbCrLf
    strSQL = strSQL & "  AND R.WORKORDER_LOT_ID=WO.LOT_ID" & vbCrLf
    strSQL = strSQL & "  AND R.WORKORDER_SPLIT_ID=WO.SPLIT_ID" & vbCrLf
    strSQL = strSQL & "  AND R.WORKORDER_SUB_ID=WO.SUB_ID" & vbCrLf
    strSQL = strSQL & "ORDER BY" & vbCrLf
    strSQL = strSQL & "  R.WORKORDER_BASE_ID," & vbCrLf
    strSQL = strSQL & "  WO.PART_ID"
    OracleSQL.SQL = strSQL
    OracleSQL.SetParameter ID, "VARCHAR"

    Set snpData = OracleSQL.Execute

    If Not (snpData Is Nothing) Then
        If Not (snpData.EOF) Then
            intParentPartLine = 0
            Do While Not (snpData.EOF)
                intParentPartLine = intParentPartLine + 1
                If snpData("top_part_id") <> snpData("sub_part_id") Then
                    strWhereUsed = strWhereUsed & cStr(snpData("top_part_id")) & " (Sub " & cStr(snpData("sub_part_id")) & ")"
                Else
                    strWhereUsed = strWhereUsed & cStr(snpData("top_part_id"))
                End If
                strRawText = strRawText & "^FO10," & cStr(80 + 25 * intParentPartLine) & "^A0,25,25^FD" & strWhereUsed & "^FS" 'Proportional font
                strWhereUsed  = ""

                snpData.MoveNext
            Loop
        End If

        snpData.Close
    End If

    strRawText = strRawText & "^FO20,562^AF^FDMy Company Here^FS"
    strRawText = strRawText & "^XZ"  'End of label indicator

    'Excute a macro located in mdlGlobal that prints a label using API calls
                                 'Subroutine, printer device name, raw ZPL code

    'Print to a shared Zebra printer named ZEBRA on computer named KMMACH98
    'strResult = .Application.Run("ZebraPrintLabel", "\\KMMACH98\ZEBRA", strRawText)

    'Print to a "Local" printer named ZEBRA_M1 with a redirected LTP2 port with NET USE
    strResult = .Application.Run("ZebraPrintLabel", "ZEBRA_M1", strRawText)

    .DisplayAlerts = False
    .ActiveWorkbook.Saved = True
    .Quit
End With

Set objExcel = Nothing 
Set OracleSQL = Nothing
Set snpData = Nothing
'End of PrintLabel.vbs

The above script expects to find an Excel spreadsheet named C:\ExcelMacroFunction.xls with a public subroutine named  ZebraPrintLabel in a regular Excel code module.

The code in the Excel code module looks like this:

'mdlGlobal in C:\ExcelMacroFunction.xls - a global module
Option Explicit

'Type declaration for Zebra label printing
Public Type DOC_INFO_1
    pDocName As String
    pOutputFile As String
    pDatatype As String
End Type

'Zebra Printing API functions
Public Declare Function ClosePrinter Lib "winspool.drv" (ByVal hPrinter As Long) As Long
Public Declare Function EndDocPrinter Lib "winspool.drv" (ByVal hPrinter As Long) As Long
Public Declare Function EndPagePrinter Lib "winspool.drv" (ByVal hPrinter As Long) As Long
Public Declare Function OpenPrinter Lib "winspool.drv" Alias "OpenPrinterA" (ByVal pPrinterName As String, phPrinter As Long, ByVal pDefault As Long) As Long
Public Declare Function StartDocPrinter Lib "winspool.drv" Alias "StartDocPrinterA" (ByVal hPrinter As Long, ByVal Level As Long, pDocInfo As DOC_INFO_1) As Long
Public Declare Function StartPagePrinter Lib "winspool.drv" (ByVal hPrinter As Long) As Long
Public Declare Function WritePrinter Lib "winspool.drv" (ByVal hPrinter As Long, pBuf As Any, ByVal cdBuf As Long, pcWritten As Long) As Long

Public Sub ZebraPrintLabel(strPrinter As Variant, strRawText As Variant)
    'Variables for handling printing
    Dim i As Integer
    Dim lngPrinterHandle As Long
    Dim lngResult As Long
    Dim lngWritten As Long
    Dim lngPrinterDocHandle As Long
    Dim bytRawText() As Byte
    Dim MyDocInfo As DOC_INFO_1

    On Error Resume Next

    'In VB6 to see the list of printer device names, enter the following into the Debug window
    'For i = 0 to Printers.Count - 1:Debug.Print Printers(i).DeviceName:Next

    'Sample label for testing using just Excel
    'strPrinter = "\\KMMACH98\ZEBRA"
    'strRawText = "~SD25^XA" 'Set Darkness, Label start
    'strRawText = strRawText & "^SZ2" 'Enable ZPL2
    'strRawText = strRawText & "^JZ" 'Reprint on error
    'strRawText = strRawText & "^PR8,8,8" 'Print speed 8" per second, 8" per sec slew, 8" per sec backfeed
    'strRawText = strRawText & "^LH10,26" 'Label home position in pixels
    'strRawText = strRawText & "^FO2,14^A0R,20,20^FDMy Company Here^FS" 'rotated text in font A
    'strRawText = strRawText & "^FO2,480^A0R,20,20^FDSomwhere, USA^FS"
    'strRawText = strRawText & "^FO180,135^B3R,,105,N^FD" & "ABC111" & "^FS"  'Font 3 of 9 barcode
    'strRawText = strRawText & "^FO180,0^GB0,760,3^FS"  'Vertical Line 3 pixels wide
    'strRawText = strRawText & "^FO335,0^GB0,1218,3^FS"  'Vertical Line 3 pixels wide
    'strRawText = strRawText & "^FO550,0^GB0,1218,3^FS"  'Vertical Line 3 pixels wide
    'strRawText = strRawText & "^FO260,760^GB0,452,3^FS"  'Vertical Line 3 pixels wide
    'strRawText = strRawText & "^FO0,760^GB335,0,3^FS"  'Horizontal Line 3 pixels wide
    'strRawText = strRawText & "^XZ"  'End of label indicator
    'Convert the variant data types to strings
    strPrinter = CStr(strPrinter)
    strRawText = CStr(strRawText)

    'Terminate the string with a CRLF combination (may not be needed)
    If Right(strRawText, 2) <> vbCrLf Then
        strRawText = strRawText & vbCrLf
    End If

    'Convert the strRawText string to a byte stream
    ReDim bytRawText(1 To Len(strRawText))
    For i = 1 To Len(strRawText)
        bytRawText(i) = Asc(Mid(strRawText, i, 1))
    Next i

    'Create a connection to the printer, returns a handle to the printer
    lngResult = OpenPrinter(strPrinter, lngPrinterHandle, 0)

    If lngPrinterHandle = 0 Then
        MsgBox "Could Not Open Printer"
        Exit Sub
    End If

    'Fill in the document header structure
    MyDocInfo.pDocName = "Zebra Label from Excel"
    MyDocInfo.pOutputFile = vbNullString
    MyDocInfo.pDatatype = "RAW"
    lngPrinterDocHandle = StartDocPrinter(lngPrinterHandle, 1, MyDocInfo)

    If lngPrinterDocHandle = 0 Then
        MsgBox "Could Not Start the Document"
        lngResult = ClosePrinter(lngPrinterHandle)
        Exit Sub
    End If

    'Prepare to start the first page
    Call StartPagePrinter(lngPrinterHandle)

    'Write out the contents of the first page
    lngResult = WritePrinter(lngPrinterHandle, bytRawText(1), Len(strRawText), lngWritten)
    If lngResult = 0 Then
        MsgBox "Could Not Write to the Page"
        lngResult = ClosePrinter(lngPrinterHandle)
        Exit Sub
    End If

    'End the first page
    lngResult = EndPagePrinter(lngPrinterHandle)

    'End the document
    lngResult = EndDocPrinter(lngPrinterHandle)

    'Close the connection to the printet
    lngResult = ClosePrinter(lngPrinterHandle)
End Sub
'End of mdlGlobal

Note that while the above uses a custom DLL to connect to the database and submit SQL statements, the Simple VBS Script to Retrieve Data from Oracle blog entry shows how to do the same without using the custom DLL.  Also, it probably is not necessary to convert the Unicode string that is passes into the procedure into a byte stream for the Windows API call.

The printed label appears below:





Simple VBS Script to Retrieve Data from Oracle

12 12 2009

December 12, 2009

All recent releases of the Windows operating system include the Windows Scripting Host, which allows executing program commands that look much like the macro language used in Excel, Access, and Word, as well as the original line of Microsoft’s Visual Basic (before the introduction of .Net).  Scripts intended to be excuted by the Windows Scripting Host have an extension of .VBS and are executed either with cscript that outputs to a command line window, or the default wscript that outputs to Windows popup messages.

A simple script that connects to an Oracle database (without using ODBC), queries a table, and then performs a comparison on the values retrieved from the table follows:

'Save as ConnectDB.vbs
Dim strSQL
Dim strUsername
Dim strPassword
Dim snpData
Dim dbMyDBConnection
Dim ORDER_ID         'Note that this variable was omitted originally as it was automatically supplied by an ERP package

Set snpData = CreateObject("ADODB.Recordset")
Set dbMyDBConnection = CreateObject("ADODB.Connection")
strUsername = "MyUser"
strPassword = "MySecret"

dbMyDBConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=MyDB;User ID=" & strUsername & ";Password=" & strPassword & ";"
dbMyDBConnection.Open

ORDER_ID = "MYORDERID123"  'Note that this variable value was omitted originally as it was automatically supplied by an ERP package
strSQL = "SELECT C1, C2 FROM MY_TABLE WHERE PURC_ORDER_ID='" & ORDER_ID & "'"

snpData.Open strSQL, dbMyDBConnection

If Not(snpData.EOF) Then
    If cInt(snpData("c1")) < cInt(snpData("c2")) Then
        MsgBox "C1 is Less than C2"
    End If
    If cInt(snpData("c1")) > cInt(snpData("c2")) Then
        MsgBox "C1 is Greater than C2"
    End If
    If cInt(snpData("c1")) = cInt(snpData("c2")) Then
        MsgBox "C1 is Equal to C2"
    End If

    MsgBox cInt(snpData("c1")) - cInt(snpData("c2"))
Else
    MsgBox "Ut oh, No Matching Records"
End If

snpData.Close
dbMyDBConnection.Close

Set snpData = Nothing
Set dbMyDBConnection = Nothing

Once the script is saved, double-clicking the script should automatically execute it using wscript.  Alternatively, open a Windows command prompt and type the following (assuming that the script is named ConnectDB.vbs and is saved in the root of the C:\ drive):

cscript c:\ConnectDB.vbs




Taking a Hint, For What it is Worth

10 12 2009

December 10, 2009

The following query executed in Oracle Database 11.1.0.6 or greater will return a list of all valid Oracle hints, and the Oracle release when the hint was first available:

SELECT
  NAME,
  INVERSE,
  SQL_FEATURE,
  VERSION
FROM
  V$SQL_HINT
ORDER BY
  NAME;

If you were to execute the query on Oracle Database 11.1.0.7 and 11.2.0.1, you would find that the following hints are listed as valid in Oracle Database 11.2.0.1 that are not listed as valid in 11.1.0.7:

NAME                          VERSION
----------------------------  --------
APPEND_VALUES                 11.2.0.1
CHANGE_DUPKEY_ERROR_INDEX     11.1.0.7
COALESCE_SQ                   11.2.0.1
CONNECT_BY_CB_WHR_ONLY        10.2.0.5
CONNECT_BY_ELIM_DUPS          11.2.0.1
DST_UPGRADE_INSERT_CONV       11.2.0.1
EXPAND_TABLE                  11.2.0.1
FACTORIZE_JOIN                11.2.0.1
IGNORE_ROW_ON_DUPKEY_INDEX    11.1.0.7
NO_COALESCE_SQ                11.2.0.1
NO_CONNECT_BY_CB_WHR_ONLY     10.2.0.5
NO_CONNECT_BY_ELIM_DUPS       11.2.0.1
NO_DST_UPGRADE_INSERT_CONV    11.2.0.1
NO_EXPAND_TABLE               11.2.0.1
NO_FACTORIZE_JOIN             11.2.0.1
NO_PLACE_DISTINCT             11.2.0.1
NO_STATEMENT_QUEUING          11.2.0.1
NO_SUBSTRB_PAD                11.2.0.1
NO_TRANSFORM_DISTINCT_AGG     11.2.0.1
PLACE_DISTINCT                11.2.0.1
RETRY_ON_ROW_CHANGE           11.1.0.7
STATEMENT_QUEUING             11.2.0.1
TRANSFORM_DISTINCT_AGG        11.2.0.1
XMLINDEX_SEL_IDX_TBL          11.2.0.1

Note that in the above three of the hints are listed as valid starting with the 11.1.0.7 release and one is listed as valid starting in the 10.2.0.5 release, yet those hints were not included in the 11.1.0.7 output.  Why were these hints introduced?  Were the hints added to correct performance/query transformation problems found in previous releases, or were the hints added to support features added in 11.2.0.1?  What is the purpose of the new hints, or for that matter any of the hints?  Is there a book of hints that describes how and when hints should be used, and in what combination?

A couple of Jonathan Lewis’ blog posts attempt at address those and other questions:
http://jonathanlewis.wordpress.com/category/hints/

The Oracle documentation also makes an attempt at describing some of the hints, but falls a bit short:
http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/hintsref.htm
 “Oracle Database supports more than 60 hints”

http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/sql_elements006.htm

More than 60 hints in Oracle Database 11.2.0.1?  I think that the number is a bit higher than 60.  Is there a reference that covers all of the hints available with 11.2.0.1 or an older release?

Here is the list from 11.1.0.7:

NAME                           INVERSE                        SQL_FEATURE                    VERSION    
------------------------------ ------------------------------ ------------------------------ ---------- 
ALL_ROWS                                                      QKSFM_ALL_ROWS                 8.1.0      
AND_EQUAL                                                     QKSFM_AND_EQUAL                8.1.0      
ANTIJOIN                                                      QKSFM_TRANSFORMATION           9.0.0      
APPEND                         NOAPPEND                       QKSFM_CBO                      8.1.0      
BIND_AWARE                     NO_BIND_AWARE                  QKSFM_CURSOR_SHARING           11.1.0.7   
BITMAP                                                        QKSFM_CBO                      8.1.0      
BITMAP_TREE                                                   QKSFM_BITMAP_TREE              10.2.0.1   
BUFFER                         NO_BUFFER                      QKSFM_CBO                      8.1.5      
BYPASS_RECURSIVE_CHECK                                        QKSFM_ALL                      9.0.0      
BYPASS_UJVC                                                   QKSFM_CBO                      8.1.5      
CACHE                          NOCACHE                        QKSFM_EXECUTION                8.1.0      
CACHE_CB                       NOCACHE                        QKSFM_CBO                      8.1.5      
CACHE_TEMP_TABLE               NOCACHE                        QKSFM_ALL                      8.1.5      
CARDINALITY                                                   QKSFM_STATS                    9.0.0      
CHECK_ACL_REWRITE              NO_CHECK_ACL_REWRITE           QKSFM_CHECK_ACL_REWRITE        11.1.0.6   
CHOOSE                                                        QKSFM_CHOOSE                   8.1.0      
CLUSTER                                                       QKSFM_CBO                      8.0.0      
COLUMN_STATS                                                  QKSFM_STATS                    10.1.0.3   
CONNECT_BY_COMBINE_SW          NO_CONNECT_BY_COMBINE_SW       QKSFM_ALL                      10.2.0.4   
CONNECT_BY_COST_BASED          NO_CONNECT_BY_COST_BASED       QKSFM_TRANSFORMATION           10.2.0.2   
CONNECT_BY_FILTERING           NO_CONNECT_BY_FILTERING        QKSFM_ALL                      10.2.0.2   
COST_XML_QUERY_REWRITE         NO_COST_XML_QUERY_REWRITE      QKSFM_ALL                      11.1.0.6   
CPU_COSTING                    NO_CPU_COSTING                 QKSFM_CPU_COSTING              9.0.0      
CUBE_GB                                                       QKSFM_CBO                      8.1.5      
CURSOR_SHARING_EXACT                                          QKSFM_CBO                      9.0.0      
DBMS_STATS                                                    QKSFM_DBMS_STATS               10.2.0.1   
DB_VERSION                                                    QKSFM_ALL                      11.1.0.6   
DEREF_NO_REWRITE                                              QKSFM_ALL                      8.1.0      
DML_UPDATE                                                    QKSFM_CBO                      9.0.0      
DOMAIN_INDEX_FILTER            NO_DOMAIN_INDEX_FILTER         QKSFM_CBO                      11.1.0.6   
DOMAIN_INDEX_NO_SORT           DOMAIN_INDEX_SORT              QKSFM_CBO                      8.1.5      
DOMAIN_INDEX_SORT              DOMAIN_INDEX_NO_SORT           QKSFM_CBO                      8.1.5      
DRIVING_SITE                                                  QKSFM_ALL                      8.1.0      
DYNAMIC_SAMPLING                                              QKSFM_DYNAMIC_SAMPLING         9.2.0      
DYNAMIC_SAMPLING_EST_CDN                                      QKSFM_DYNAMIC_SAMPLING_EST_CDN 9.2.0      
ELIMINATE_JOIN                 NO_ELIMINATE_JOIN              QKSFM_TABLE_ELIM               10.2.0.1   
ELIMINATE_OBY                  NO_ELIMINATE_OBY               QKSFM_OBYE                     10.2.0.1   
EXPAND_GSET_TO_UNION           NO_EXPAND_GSET_TO_UNION        QKSFM_TRANSFORMATION           9.2.0      
EXPR_CORR_CHECK                                               QKSFM_CBO                      8.0.0      
FACT                           NO_FACT                        QKSFM_STAR_TRANS               8.1.0      
FBTSCAN                                                       QKSFM_CBO                      10.1.0.3   
FIRST_ROWS                                                    QKSFM_FIRST_ROWS               8.1.0      
FORCE_XML_QUERY_REWRITE        NO_XML_QUERY_REWRITE           QKSFM_XML_REWRITE              9.2.0      
FULL                                                          QKSFM_FULL                     8.1.0      
GATHER_PLAN_STATISTICS                                        QKSFM_GATHER_PLAN_STATISTICS   10.1.0.3   
GBY_CONC_ROLLUP                                               QKSFM_TRANSFORMATION           9.0.0      
GBY_PUSHDOWN                   NO_GBY_PUSHDOWN                QKSFM_ALL                      11.1.0.6   
HASH                                                          QKSFM_ALL                      8.1.0      
HASH_AJ                                                       QKSFM_CBO                      8.1.0      
HASH_SJ                                                       QKSFM_CBO                      8.1.0      
HWM_BROKERED                                                  QKSFM_CBO                      9.0.0      
IGNORE_OPTIM_EMBEDDED_HINTS                                   QKSFM_ALL                      10.1.0.3   
IGNORE_WHERE_CLAUSE                                           QKSFM_ALL                      9.2.0      
INCLUDE_VERSION                                               QKSFM_ALL                      10.1.0.3   
INDEX                          NO_INDEX                       QKSFM_INDEX                    8.0.0      
INDEX_ASC                      NO_INDEX                       QKSFM_INDEX_ASC                8.1.0      
INDEX_COMBINE                                                 QKSFM_INDEX_COMBINE            8.1.0      
INDEX_DESC                     NO_INDEX                       QKSFM_INDEX_DESC               8.1.0      
INDEX_FFS                                                     QKSFM_INDEX_FFS                8.1.0      
INDEX_JOIN                                                    QKSFM_INDEX_JOIN               8.1.5      
INDEX_RRS                                                     QKSFM_CBO                      9.0.0      
INDEX_RS_ASC                                                  QKSFM_INDEX_RS_ASC             11.1.0.6   
INDEX_RS_DESC                                                 QKSFM_INDEX_RS_DESC            11.1.0.6   
INDEX_SS                       NO_INDEX_SS                    QKSFM_INDEX_SS                 9.0.0      
INDEX_SS_ASC                   NO_INDEX_SS                    QKSFM_INDEX_SS_ASC             9.0.0      
INDEX_SS_DESC                  NO_INDEX_SS                    QKSFM_INDEX_SS_DESC            9.0.0      
INDEX_STATS                                                   QKSFM_STATS                    10.1.0.3   
INLINE                         MATERIALIZE                    QKSFM_TRANSFORMATION           9.0.0      
INLINE_XMLTYPE_NT                                             QKSFM_ALL                      10.2.0.1   
LEADING                                                       QKSFM_JOIN_ORDER               8.1.6      
LIKE_EXPAND                                                   QKSFM_TRANSFORMATION           8.1.7      
LOCAL_INDEXES                                                 QKSFM_CBO                      9.0.0      
MATERIALIZE                    INLINE                         QKSFM_TRANSFORMATION           9.0.0      
MERGE                          NO_MERGE                       QKSFM_CVM                      8.1.0      
MERGE_AJ                                                      QKSFM_CBO                      8.1.0      
MERGE_CONST_ON                                                QKSFM_CBO                      8.0.0      
MERGE_SJ                                                      QKSFM_CBO                      8.1.0      
MODEL_COMPILE_SUBQUERY                                        QKSFM_TRANSFORMATION           10.2.0.1   
MODEL_DONTVERIFY_UNIQUENESS                                   QKSFM_TRANSFORMATION           10.1.0.3   
MODEL_DYNAMIC_SUBQUERY                                        QKSFM_TRANSFORMATION           10.2.0.1   
MODEL_MIN_ANALYSIS                                            QKSFM_TRANSFORMATION           10.1.0.3   
MODEL_NO_ANALYSIS                                             QKSFM_ALL                      10.1.0.3   
MODEL_PUSH_REF                 NO_MODEL_PUSH_REF              QKSFM_TRANSFORMATION           10.1.0.3   
MONITOR                        NO_MONITOR                     QKSFM_ALL                      11.1.0.6   
MV_MERGE                                                      QKSFM_TRANSFORMATION           9.0.0      
NATIVE_FULL_OUTER_JOIN         NO_NATIVE_FULL_OUTER_JOIN      QKSFM_ALL                      10.2.0.3   
NESTED_TABLE_FAST_INSERT                                      QKSFM_ALL                      10.1.0.3   
NESTED_TABLE_GET_REFS                                         QKSFM_ALL                      8.1.0      
NESTED_TABLE_SET_SETID                                        QKSFM_ALL                      8.1.5      
NLJ_BATCHING                   NO_NLJ_BATCHING                QKSFM_EXECUTION                11.1.0.6   
NLJ_PREFETCH                   NO_NLJ_PREFETCH                QKSFM_EXECUTION                11.1.0.6   
NL_AJ                                                         QKSFM_CBO                      8.0.0      
NL_SJ                                                         QKSFM_CBO                      8.0.0      
NOAPPEND                       APPEND                         QKSFM_CBO                      8.1.0      
NOCACHE                        CACHE                          QKSFM_EXECUTION                8.1.0      
NOPARALLEL                     SHARED                         QKSFM_PARALLEL                 8.1.0      
NO_ACCESS                                                     QKSFM_ALL                      8.1.5      
NO_BASETABLE_MULTIMV_REWRITE   REWRITE                        QKSFM_ALL                      10.1.0.3   
NO_BIND_AWARE                  BIND_AWARE                     QKSFM_CURSOR_SHARING           11.1.0.7   
NO_BUFFER                      BUFFER                         QKSFM_CBO                      8.1.5      
NO_CARTESIAN                                                  QKSFM_ALL                      10.2.0.1   
NO_CHECK_ACL_REWRITE           CHECK_ACL_REWRITE              QKSFM_CHECK_ACL_REWRITE        11.1.0.6   
NO_CONNECT_BY_COMBINE_SW       CONNECT_BY_COMBINE_SW          QKSFM_ALL                      10.2.0.4   
NO_CONNECT_BY_COST_BASED       CONNECT_BY_COST_BASED          QKSFM_TRANSFORMATION           10.2.0.2   
NO_CONNECT_BY_FILTERING        CONNECT_BY_FILTERING           QKSFM_ALL                      10.2.0.2   
NO_COST_XML_QUERY_REWRITE      COST_XML_QUERY_REWRITE         QKSFM_ALL                      11.1.0.6   
NO_CPU_COSTING                 CPU_COSTING                    QKSFM_CPU_COSTING              9.0.0      
NO_DOMAIN_INDEX_FILTER         DOMAIN_INDEX_FILTER            QKSFM_CBO                      11.1.0.6   
NO_ELIMINATE_JOIN              ELIMINATE_JOIN                 QKSFM_TABLE_ELIM               10.2.0.1   
NO_ELIMINATE_OBY               ELIMINATE_OBY                  QKSFM_OBYE                     10.2.0.1   
NO_EXPAND                      USE_CONCAT                     QKSFM_USE_CONCAT               8.1.0      
NO_EXPAND_GSET_TO_UNION        EXPAND_GSET_TO_UNION           QKSFM_TRANSFORMATION           9.2.0      
NO_FACT                        FACT                           QKSFM_STAR_TRANS               8.1.0      
NO_GBY_PUSHDOWN                GBY_PUSHDOWN                   QKSFM_ALL                      11.1.0.6   
NO_INDEX                       INDEX                          QKSFM_INDEX                    8.1.5      
NO_INDEX_FFS                   INDEX_FFS                      QKSFM_INDEX_FFS                10.1.0.3   
NO_INDEX_SS                    INDEX_SS                       QKSFM_INDEX_SS                 10.1.0.3   
NO_LOAD                                                       QKSFM_EXECUTION                11.1.0.6   
NO_MERGE                       MERGE                          QKSFM_CVM                      8.0.0      
NO_MODEL_PUSH_REF              MODEL_PUSH_REF                 QKSFM_ALL                      10.1.0.3   
NO_MONITOR                     MONITOR                        QKSFM_ALL                      11.1.0.6   
NO_MONITORING                                                 QKSFM_ALL                      8.0.0      
NO_MULTIMV_REWRITE             REWRITE                        QKSFM_ALL                      10.1.0.3   
NO_NATIVE_FULL_OUTER_JOIN      NATIVE_FULL_OUTER_JOIN         QKSFM_ALL                      10.2.0.3   
NO_NLJ_BATCHING                NLJ_BATCHING                   QKSFM_EXECUTION                11.1.0.6   
NO_NLJ_PREFETCH                NLJ_PREFETCH                   QKSFM_EXECUTION                11.1.0.6   
NO_ORDER_ROLLUPS                                              QKSFM_TRANSFORMATION           8.0.0      
NO_OUTER_JOIN_TO_INNER         OUTER_JOIN_TO_INNER            QKSFM_OUTER_JOIN_TO_INNER      11.1.0.6   
NO_PARALLEL                    SHARED                         QKSFM_CBO                      10.1.0.3   
NO_PARALLEL_INDEX              PARALLEL_INDEX                 QKSFM_PQ                       8.1.0      
NO_PARTIAL_COMMIT                                             QKSFM_CBO                      10.1.0.3   
NO_PLACE_GROUP_BY              PLACE_GROUP_BY                 QKSFM_PLACE_GROUP_BY           11.1.0.6   
NO_PRUNE_GSETS                                                QKSFM_TRANSFORMATION           9.0.0      
NO_PULL_PRED                   PULL_PRED                      QKSFM_PULL_PRED                10.2.0.1   
NO_PUSH_PRED                   PUSH_PRED                      QKSFM_FILTER_PUSH_PRED         8.1.0      
NO_PUSH_SUBQ                   PUSH_SUBQ                      QKSFM_TRANSFORMATION           9.2.0      
NO_PX_JOIN_FILTER              PX_JOIN_FILTER                 QKSFM_PX_JOIN_FILTER           10.2.0.1   
NO_QKN_BUFF                                                   QKSFM_CBO                      9.2.0      
NO_QUERY_TRANSFORMATION                                       QKSFM_TRANSFORMATION           10.1.0.3   
NO_REF_CASCADE                 REF_CASCADE_CURSOR             QKSFM_CBO                      9.2.0      
NO_RESULT_CACHE                RESULT_CACHE                   QKSFM_EXECUTION                11.1.0.6   
NO_REWRITE                     REWRITE                        QKSFM_TRANSFORMATION           8.1.5      
NO_SEMIJOIN                    SEMIJOIN                       QKSFM_TRANSFORMATION           9.0.0      
NO_SET_TO_JOIN                 SET_TO_JOIN                    QKSFM_SET_TO_JOIN              10.1.0.3   
NO_SQL_TUNE                                                   QKSFM_ALL                      10.2.0.1   
NO_STAR_TRANSFORMATION         STAR_TRANSFORMATION            QKSFM_STAR_TRANS               10.1.0.3   
NO_STATS_GSETS                                                QKSFM_ALL                      8.0.0      
NO_SUBQUERY_PRUNING            SUBQUERY_PRUNING               QKSFM_CBO                      11.1.0.6   
NO_SWAP_JOIN_INPUTS            SWAP_JOIN_INPUTS               QKSFM_CBO                      10.1.0.3   
NO_UNNEST                      UNNEST                         QKSFM_UNNEST                   8.1.6      
NO_USE_HASH                    USE_HASH                       QKSFM_USE_HASH                 10.1.0.3   
NO_USE_HASH_AGGREGATION        USE_HASH_AGGREGATION           QKSFM_ALL                      10.2.0.1   
NO_USE_INVISIBLE_INDEXES       USE_INVISIBLE_INDEXES          QKSFM_INDEX                    11.1.0.6   
NO_USE_MERGE                   USE_MERGE                      QKSFM_USE_MERGE                10.1.0.3   
NO_USE_NL                      USE_NL                         QKSFM_USE_NL                   10.1.0.3   
NO_XMLINDEX_REWRITE            XMLINDEX_REWRITE               QKSFM_XMLINDEX_REWRITE         11.1.0.6   
NO_XMLINDEX_REWRITE_IN_SELECT  XMLINDEX_REWRITE_IN_SELECT     QKSFM_XMLINDEX_REWRITE         11.1.0.6   
NO_XML_DML_REWRITE                                            QKSFM_XML_REWRITE              10.2.0.1   
NO_XML_QUERY_REWRITE           FORCE_XML_QUERY_REWRITE        QKSFM_XML_REWRITE              9.2.0      
NUM_INDEX_KEYS                                                QKSFM_CBO                      10.2.0.3   
OLD_PUSH_PRED                                                 QKSFM_OLD_PUSH_PRED            10.2.0.1   
OPAQUE_TRANSFORM                                              QKSFM_TRANSFORMATION           10.1.0.3   
OPAQUE_XCANONICAL                                             QKSFM_TRANSFORMATION           10.1.0.3   
OPTIMIZER_FEATURES_ENABLE                                     QKSFM_ALL                      10.1.0.3   
OPT_ESTIMATE                                                  QKSFM_OPT_ESTIMATE             10.1.0.3   
OPT_PARAM                                                     QKSFM_ALL                      10.2.0.1   
ORDERED                                                       QKSFM_CBO                      8.1.0      
ORDERED_PREDICATES                                            QKSFM_CBO                      8.0.0      
OR_EXPAND                                                     QKSFM_OR_EXPAND                8.1.7      
OUTER_JOIN_TO_INNER            NO_OUTER_JOIN_TO_INNER         QKSFM_OUTER_JOIN_TO_INNER      11.1.0.6   
OUTLINE                                                       QKSFM_ALL                      10.2.0.1   
OUTLINE_LEAF                                                  QKSFM_ALL                      10.2.0.1   
OVERFLOW_NOMOVE                                               QKSFM_CBO                      9.0.0      
PARALLEL_INDEX                 NO_PARALLEL_INDEX              QKSFM_PQ                       8.1.0      
PIV_GB                                                        QKSFM_ALL                      8.1.0      
PIV_SSF                                                       QKSFM_ALL                      8.1.0      
PLACE_GROUP_BY                 NO_PLACE_GROUP_BY              QKSFM_PLACE_GROUP_BY           11.1.0.6   
PQ_DISTRIBUTE                                                 QKSFM_PQ_DISTRIBUTE            8.1.5      
PQ_MAP                         PQ_NOMAP                       QKSFM_PQ_MAP                   9.0.0      
PQ_NOMAP                       PQ_MAP                         QKSFM_PQ_MAP                   9.0.0      
PRECOMPUTE_SUBQUERY                                           QKSFM_TRANSFORMATION           10.2.0.1   
PRESERVE_OID                                                  QKSFM_ALL                      10.2.0.1   
PULL_PRED                      NO_PULL_PRED                   QKSFM_PULL_PRED                10.2.0.1   
PUSH_PRED                      NO_PUSH_PRED                   QKSFM_FILTER_PUSH_PRED         8.1.0      
PUSH_SUBQ                      NO_PUSH_SUBQ                   QKSFM_TRANSFORMATION           8.1.0      
PX_JOIN_FILTER                 NO_PX_JOIN_FILTER              QKSFM_PX_JOIN_FILTER           10.2.0.1   
QB_NAME                                                       QKSFM_ALL                      10.1.0.3   
QUEUE_CURR                                                    QKSFM_CBO                      8.0.0      
QUEUE_ROWP                                                    QKSFM_CBO                      8.0.0      
RBO_OUTLINE                                                   QKSFM_RBO                      10.2.0.1   
REF_CASCADE_CURSOR             NO_REF_CASCADE                 QKSFM_CBO                      9.2.0      
REMOTE_MAPPED                                                 QKSFM_ALL                      8.1.0      
RESTORE_AS_INTERVALS                                          QKSFM_CBO                      8.1.5      
RESTRICT_ALL_REF_CONS                                         QKSFM_ALL                      10.1.0.3   
RESULT_CACHE                   NO_RESULT_CACHE                QKSFM_EXECUTION                11.1.0.6   
REWRITE                        NO_REWRITE                     QKSFM_TRANSFORMATION           8.1.5      
REWRITE_OR_ERROR                                              QKSFM_TRANSFORMATION           10.1.0.3   
ROWID                                                         QKSFM_CBO                      8.0.0      
RULE                                                          QKSFM_RBO                      8.1.0      
SAVE_AS_INTERVALS                                             QKSFM_CBO                      8.1.5      
SCN_ASCENDING                                                 QKSFM_ALL                      8.1.5      
SEMIJOIN                       NO_SEMIJOIN                    QKSFM_TRANSFORMATION           9.0.0      
SEMIJOIN_DRIVER                                               QKSFM_CBO                      8.1.0      
SET_TO_JOIN                    NO_SET_TO_JOIN                 QKSFM_SET_TO_JOIN              10.1.0.3   
SHARED                         NO_PARALLEL                    QKSFM_PARALLEL                 8.1.0      
SKIP_EXT_OPTIMIZER                                            QKSFM_CBO                      9.0.0      
SKIP_UNQ_UNUSABLE_IDX                                         QKSFM_CBO                      10.1.0.3   
SQLLDR                                                        QKSFM_CBO                      9.0.0      
STAR                                                          QKSFM_STAR_TRANS               8.1.0      
STAR_TRANSFORMATION            NO_STAR_TRANSFORMATION         QKSFM_STAR_TRANS               8.1.0      
STREAMS                                                       QKSFM_CBO                      10.1.0.3   
SUBQUERY_PRUNING               NO_SUBQUERY_PRUNING            QKSFM_CBO                      11.1.0.6   
SWAP_JOIN_INPUTS               NO_SWAP_JOIN_INPUTS            QKSFM_CBO                      8.1.0      
SYS_DL_CURSOR                                                 QKSFM_CBO                      9.2.0      
SYS_PARALLEL_TXN                                              QKSFM_CBO                      8.1.6      
SYS_RID_ORDER                                                 QKSFM_ALL                      9.2.0      
TABLE_STATS                                                   QKSFM_STATS                    10.1.0.3   
TIV_GB                                                        QKSFM_ALL                      8.1.0      
TIV_SSF                                                       QKSFM_ALL                      8.1.0      
TRACING                                                       QKSFM_EXECUTION                10.1.0.3   
UNNEST                         NO_UNNEST                      QKSFM_UNNEST                   8.1.6      
USE_ANTI                                                      QKSFM_CBO                      8.1.0      
USE_CONCAT                     NO_EXPAND                      QKSFM_USE_CONCAT               8.1.0      
USE_HASH                       NO_USE_HASH                    QKSFM_USE_HASH                 8.1.0      
USE_HASH_AGGREGATION           NO_USE_HASH_AGGREGATION        QKSFM_ALL                      10.2.0.1   
USE_INVISIBLE_INDEXES          NO_USE_INVISIBLE_INDEXES       QKSFM_INDEX                    11.1.0.6   
USE_MERGE                      NO_USE_MERGE                   QKSFM_USE_MERGE                8.1.0      
USE_MERGE_CARTESIAN                                           QKSFM_USE_MERGE_CARTESIAN      11.1.0.6   
USE_NL                         NO_USE_NL                      QKSFM_USE_NL                   8.1.0      
USE_NL_WITH_INDEX              NO_USE_NL                      QKSFM_USE_NL_WITH_INDEX        10.1.0.3   
USE_SEMI                                                      QKSFM_CBO                      8.1.0      
USE_TTT_FOR_GSETS                                             QKSFM_TRANSFORMATION           9.0.0      
USE_WEAK_NAME_RESL                                            QKSFM_ALL                      10.1.0.3   
VECTOR_READ                                                   QKSFM_CBO                      10.1.0.3   
VECTOR_READ_TRACE                                             QKSFM_CBO                      10.1.0.3   
XMLINDEX_REWRITE               NO_XMLINDEX_REWRITE            QKSFM_XMLINDEX_REWRITE         11.1.0.6   
XMLINDEX_REWRITE_IN_SELECT     NO_XMLINDEX_REWRITE_IN_SELECT  QKSFM_XMLINDEX_REWRITE         11.1.0.6   
XML_DML_RWT_STMT                                              QKSFM_XML_REWRITE              11.1.0.6   
X_DYN_PRUNE                                                   QKSFM_CBO                      10.1.0.3

239 rows selected.

 

Here is the list from 11.2.0.1:

NAME                           INVERSE                        SQL_FEATURE                    VERSION         
------------------------------ ------------------------------ ------------------------------ ----------      
ALL_ROWS                                                      QKSFM_ALL_ROWS                 8.1.0           
AND_EQUAL                                                     QKSFM_AND_EQUAL                8.1.0           
ANTIJOIN                                                      QKSFM_TRANSFORMATION           9.0.0           
APPEND                         NOAPPEND                       QKSFM_CBO                      8.1.0           
APPEND_VALUES                  NOAPPEND                       QKSFM_CBO                      11.2.0.1        
BIND_AWARE                     NO_BIND_AWARE                  QKSFM_CURSOR_SHARING           11.1.0.7        
BITMAP                                                        QKSFM_CBO                      8.1.0           
BITMAP_TREE                                                   QKSFM_BITMAP_TREE              10.2.0.1        
BUFFER                         NO_BUFFER                      QKSFM_CBO                      8.1.5           
BYPASS_RECURSIVE_CHECK                                        QKSFM_ALL                      9.0.0           
BYPASS_UJVC                                                   QKSFM_CBO                      8.1.5           
CACHE                          NOCACHE                        QKSFM_EXECUTION                8.1.0           
CACHE_CB                       NOCACHE                        QKSFM_CBO                      8.1.5           
CACHE_TEMP_TABLE               NOCACHE                        QKSFM_ALL                      8.1.5           
CARDINALITY                                                   QKSFM_STATS                    9.0.0           
CHANGE_DUPKEY_ERROR_INDEX                                     QKSFM_DML                      11.1.0.7        
CHECK_ACL_REWRITE              NO_CHECK_ACL_REWRITE           QKSFM_CHECK_ACL_REWRITE        11.1.0.6        
CHOOSE                                                        QKSFM_CHOOSE                   8.1.0           
CLUSTER                                                       QKSFM_CBO                      8.0.0           
COALESCE_SQ                    NO_COALESCE_SQ                 QKSFM_COALESCE_SQ              11.2.0.1        
COLUMN_STATS                                                  QKSFM_STATS                    10.1.0.3        
CONNECT_BY_CB_WHR_ONLY         NO_CONNECT_BY_CB_WHR_ONLY      QKSFM_TRANSFORMATION           10.2.0.5        
CONNECT_BY_COMBINE_SW          NO_CONNECT_BY_COMBINE_SW       QKSFM_ALL                      10.2.0.4        
CONNECT_BY_COST_BASED          NO_CONNECT_BY_COST_BASED       QKSFM_TRANSFORMATION           10.2.0.2        
CONNECT_BY_ELIM_DUPS           NO_CONNECT_BY_ELIM_DUPS        QKSFM_ALL                      11.2.0.1        
CONNECT_BY_FILTERING           NO_CONNECT_BY_FILTERING        QKSFM_ALL                      10.2.0.2        
COST_XML_QUERY_REWRITE         NO_COST_XML_QUERY_REWRITE      QKSFM_COST_XML_QUERY_REWRITE   11.1.0.6        
CPU_COSTING                    NO_CPU_COSTING                 QKSFM_CPU_COSTING              9.0.0           
CUBE_GB                                                       QKSFM_CBO                      8.1.5           
CURSOR_SHARING_EXACT                                          QKSFM_CBO                      9.0.0           
DBMS_STATS                                                    QKSFM_DBMS_STATS               10.2.0.1        
DB_VERSION                                                    QKSFM_ALL                      11.1.0.6        
DEREF_NO_REWRITE                                              QKSFM_ALL                      8.1.0           
DML_UPDATE                                                    QKSFM_CBO                      9.0.0           
DOMAIN_INDEX_FILTER            NO_DOMAIN_INDEX_FILTER         QKSFM_CBO                      11.1.0.6        
DOMAIN_INDEX_NO_SORT           DOMAIN_INDEX_SORT              QKSFM_CBO                      8.1.5           
DOMAIN_INDEX_SORT              DOMAIN_INDEX_NO_SORT           QKSFM_CBO                      8.1.5           
DRIVING_SITE                                                  QKSFM_ALL                      8.1.0           
DST_UPGRADE_INSERT_CONV        NO_DST_UPGRADE_INSERT_CONV     QKSFM_ALL                      11.2.0.1        
DYNAMIC_SAMPLING                                              QKSFM_DYNAMIC_SAMPLING         9.2.0           
DYNAMIC_SAMPLING_EST_CDN                                      QKSFM_DYNAMIC_SAMPLING_EST_CDN 9.2.0           
ELIMINATE_JOIN                 NO_ELIMINATE_JOIN              QKSFM_TABLE_ELIM               10.2.0.1        
ELIMINATE_OBY                  NO_ELIMINATE_OBY               QKSFM_OBYE                     10.2.0.1        
EXPAND_GSET_TO_UNION           NO_EXPAND_GSET_TO_UNION        QKSFM_TRANSFORMATION           9.2.0           
EXPAND_TABLE                   NO_EXPAND_TABLE                QKSFM_TABLE_EXPANSION          11.2.0.1        
EXPR_CORR_CHECK                                               QKSFM_CBO                      8.0.0           
FACT                           NO_FACT                        QKSFM_STAR_TRANS               8.1.0           
FACTORIZE_JOIN                 NO_FACTORIZE_JOIN              QKSFM_JOINFAC                  11.2.0.1        
FBTSCAN                                                       QKSFM_CBO                      10.1.0.3        
FIRST_ROWS                                                    QKSFM_FIRST_ROWS               8.1.0           
FORCE_XML_QUERY_REWRITE        NO_XML_QUERY_REWRITE           QKSFM_XML_REWRITE              9.2.0           
FULL                                                          QKSFM_FULL                     8.1.0           
GATHER_PLAN_STATISTICS                                        QKSFM_GATHER_PLAN_STATISTICS   10.1.0.3        
GBY_CONC_ROLLUP                                               QKSFM_TRANSFORMATION           9.0.0           
GBY_PUSHDOWN                   NO_GBY_PUSHDOWN                QKSFM_ALL                      11.1.0.6        
HASH                                                          QKSFM_ALL                      8.1.0           
HASH_AJ                                                       QKSFM_CBO                      8.1.0           
HASH_SJ                                                       QKSFM_CBO                      8.1.0           
HWM_BROKERED                                                  QKSFM_CBO                      9.0.0           
IGNORE_OPTIM_EMBEDDED_HINTS                                   QKSFM_ALL                      10.1.0.3        
IGNORE_ROW_ON_DUPKEY_INDEX                                    QKSFM_DML                      11.1.0.7        
IGNORE_WHERE_CLAUSE                                           QKSFM_ALL                      9.2.0           
INCLUDE_VERSION                                               QKSFM_ALL                      10.1.0.3        
INDEX                          NO_INDEX                       QKSFM_INDEX                    8.0.0           
INDEX_ASC                      NO_INDEX                       QKSFM_INDEX_ASC                8.1.0           
INDEX_COMBINE                                                 QKSFM_INDEX_COMBINE            8.1.0           
INDEX_DESC                     NO_INDEX                       QKSFM_INDEX_DESC               8.1.0           
INDEX_FFS                                                     QKSFM_INDEX_FFS                8.1.0           
INDEX_JOIN                                                    QKSFM_INDEX_JOIN               8.1.5           
INDEX_RRS                                                     QKSFM_CBO                      9.0.0           
INDEX_RS_ASC                                                  QKSFM_INDEX_RS_ASC             11.1.0.6        
INDEX_RS_DESC                                                 QKSFM_INDEX_RS_DESC            11.1.0.6        
INDEX_SS                       NO_INDEX_SS                    QKSFM_INDEX_SS                 9.0.0           
INDEX_SS_ASC                   NO_INDEX_SS                    QKSFM_INDEX_SS_ASC             9.0.0           
INDEX_SS_DESC                  NO_INDEX_SS                    QKSFM_INDEX_SS_DESC            9.0.0           
INDEX_STATS                                                   QKSFM_STATS                    10.1.0.3        
INLINE                         MATERIALIZE                    QKSFM_TRANSFORMATION           9.0.0           
INLINE_XMLTYPE_NT                                             QKSFM_ALL                      10.2.0.1        
LEADING                                                       QKSFM_JOIN_ORDER               8.1.6           
LIKE_EXPAND                                                   QKSFM_TRANSFORMATION           8.1.7           
LOCAL_INDEXES                                                 QKSFM_CBO                      9.0.0           
MATERIALIZE                    INLINE                         QKSFM_TRANSFORMATION           9.0.0           
MERGE                          NO_MERGE                       QKSFM_CVM                      8.1.0           
MERGE_AJ                                                      QKSFM_CBO                      8.1.0           
MERGE_CONST_ON                                                QKSFM_CBO                      8.0.0           
MERGE_SJ                                                      QKSFM_CBO                      8.1.0           
MODEL_COMPILE_SUBQUERY                                        QKSFM_TRANSFORMATION           10.2.0.1        
MODEL_DONTVERIFY_UNIQUENESS                                   QKSFM_TRANSFORMATION           10.1.0.3        
MODEL_DYNAMIC_SUBQUERY                                        QKSFM_TRANSFORMATION           10.2.0.1        
MODEL_MIN_ANALYSIS                                            QKSFM_TRANSFORMATION           10.1.0.3        
MODEL_NO_ANALYSIS                                             QKSFM_ALL                      10.1.0.3        
MODEL_PUSH_REF                 NO_MODEL_PUSH_REF              QKSFM_TRANSFORMATION           10.1.0.3        
MONITOR                        NO_MONITOR                     QKSFM_ALL                      11.1.0.6        
MV_MERGE                                                      QKSFM_TRANSFORMATION           9.0.0           
NATIVE_FULL_OUTER_JOIN         NO_NATIVE_FULL_OUTER_JOIN      QKSFM_ALL                      10.2.0.3        
NESTED_TABLE_FAST_INSERT                                      QKSFM_ALL                      10.1.0.3        
NESTED_TABLE_GET_REFS                                         QKSFM_ALL                      8.1.0           
NESTED_TABLE_SET_SETID                                        QKSFM_ALL                      8.1.5           
NLJ_BATCHING                   NO_NLJ_BATCHING                QKSFM_EXECUTION                11.1.0.6        
NLJ_PREFETCH                   NO_NLJ_PREFETCH                QKSFM_EXECUTION                11.1.0.6        
NL_AJ                                                         QKSFM_CBO                      8.0.0           
NL_SJ                                                         QKSFM_CBO                      8.0.0           
NOAPPEND                       APPEND                         QKSFM_CBO                      8.1.0           
NOCACHE                        CACHE                          QKSFM_EXECUTION                8.1.0           
NOPARALLEL                     SHARED                         QKSFM_PARALLEL                 8.1.0           
NO_ACCESS                                                     QKSFM_ALL                      8.1.5           
NO_BASETABLE_MULTIMV_REWRITE   REWRITE                        QKSFM_ALL                      10.1.0.3        
NO_BIND_AWARE                  BIND_AWARE                     QKSFM_CURSOR_SHARING           11.1.0.7        
NO_BUFFER                      BUFFER                         QKSFM_CBO                      8.1.5           
NO_CARTESIAN                                                  QKSFM_ALL                      10.2.0.1        
NO_CHECK_ACL_REWRITE           CHECK_ACL_REWRITE              QKSFM_CHECK_ACL_REWRITE        11.1.0.6        
NO_COALESCE_SQ                 COALESCE_SQ                    QKSFM_COALESCE_SQ              11.2.0.1        
NO_CONNECT_BY_CB_WHR_ONLY      CONNECT_BY_CB_WHR_ONLY         QKSFM_TRANSFORMATION           10.2.0.5        
NO_CONNECT_BY_COMBINE_SW       CONNECT_BY_COMBINE_SW          QKSFM_ALL                      10.2.0.4        
NO_CONNECT_BY_COST_BASED       CONNECT_BY_COST_BASED          QKSFM_TRANSFORMATION           10.2.0.2        
NO_CONNECT_BY_ELIM_DUPS        CONNECT_BY_ELIM_DUPS           QKSFM_ALL                      11.2.0.1        
NO_CONNECT_BY_FILTERING        CONNECT_BY_FILTERING           QKSFM_ALL                      10.2.0.2        
NO_COST_XML_QUERY_REWRITE      COST_XML_QUERY_REWRITE         QKSFM_COST_XML_QUERY_REWRITE   11.1.0.6        
NO_CPU_COSTING                 CPU_COSTING                    QKSFM_CPU_COSTING              9.0.0           
NO_DOMAIN_INDEX_FILTER         DOMAIN_INDEX_FILTER            QKSFM_CBO                      11.1.0.6        
NO_DST_UPGRADE_INSERT_CONV     DST_UPGRADE_INSERT_CONV        QKSFM_ALL                      11.2.0.1        
NO_ELIMINATE_JOIN              ELIMINATE_JOIN                 QKSFM_TABLE_ELIM               10.2.0.1        
NO_ELIMINATE_OBY               ELIMINATE_OBY                  QKSFM_OBYE                     10.2.0.1        
NO_EXPAND                      USE_CONCAT                     QKSFM_USE_CONCAT               8.1.0           
NO_EXPAND_GSET_TO_UNION        EXPAND_GSET_TO_UNION           QKSFM_TRANSFORMATION           9.2.0           
NO_EXPAND_TABLE                EXPAND_TABLE                   QKSFM_TABLE_EXPANSION          11.2.0.1        
NO_FACT                        FACT                           QKSFM_STAR_TRANS               8.1.0           
NO_FACTORIZE_JOIN              FACTORIZE_JOIN                 QKSFM_JOINFAC                  11.2.0.1        
NO_GBY_PUSHDOWN                GBY_PUSHDOWN                   QKSFM_ALL                      11.1.0.6        
NO_INDEX                       INDEX                          QKSFM_INDEX                    8.1.5           
NO_INDEX_FFS                   INDEX_FFS                      QKSFM_INDEX_FFS                10.1.0.3        
NO_INDEX_SS                    INDEX_SS                       QKSFM_INDEX_SS                 10.1.0.3        
NO_LOAD                                                       QKSFM_EXECUTION                11.1.0.6        
NO_MERGE                       MERGE                          QKSFM_CVM                      8.0.0           
NO_MODEL_PUSH_REF              MODEL_PUSH_REF                 QKSFM_ALL                      10.1.0.3        
NO_MONITOR                     MONITOR                        QKSFM_ALL                      11.1.0.6        
NO_MONITORING                                                 QKSFM_ALL                      8.0.0           
NO_MULTIMV_REWRITE             REWRITE                        QKSFM_ALL                      10.1.0.3        
NO_NATIVE_FULL_OUTER_JOIN      NATIVE_FULL_OUTER_JOIN         QKSFM_ALL                      10.2.0.3        
NO_NLJ_BATCHING                NLJ_BATCHING                   QKSFM_EXECUTION                11.1.0.6        
NO_NLJ_PREFETCH                NLJ_PREFETCH                   QKSFM_EXECUTION                11.1.0.6        
NO_ORDER_ROLLUPS                                              QKSFM_TRANSFORMATION           8.0.0           
NO_OUTER_JOIN_TO_INNER         OUTER_JOIN_TO_INNER            QKSFM_OUTER_JOIN_TO_INNER      11.1.0.6        
NO_PARALLEL                    SHARED                         QKSFM_CBO                      10.1.0.3        
NO_PARALLEL_INDEX              PARALLEL_INDEX                 QKSFM_PQ                       8.1.0           
NO_PARTIAL_COMMIT                                             QKSFM_CBO                      10.1.0.3        
NO_PLACE_DISTINCT              PLACE_DISTINCT                 QKSFM_DIST_PLCMT               11.2.0.1        
NO_PLACE_GROUP_BY              PLACE_GROUP_BY                 QKSFM_PLACE_GROUP_BY           11.1.0.6        
NO_PRUNE_GSETS                                                QKSFM_TRANSFORMATION           9.0.0           
NO_PULL_PRED                   PULL_PRED                      QKSFM_PULL_PRED                10.2.0.1        
NO_PUSH_PRED                   PUSH_PRED                      QKSFM_FILTER_PUSH_PRED         8.1.0           
NO_PUSH_SUBQ                   PUSH_SUBQ                      QKSFM_TRANSFORMATION           9.2.0           
NO_PX_JOIN_FILTER              PX_JOIN_FILTER                 QKSFM_PX_JOIN_FILTER           10.2.0.1        
NO_QKN_BUFF                                                   QKSFM_CBO                      9.2.0           
NO_QUERY_TRANSFORMATION                                       QKSFM_TRANSFORMATION           10.1.0.3        
NO_REF_CASCADE                 REF_CASCADE_CURSOR             QKSFM_CBO                      9.2.0           
NO_RESULT_CACHE                RESULT_CACHE                   QKSFM_EXECUTION                11.1.0.6        
NO_REWRITE                     REWRITE                        QKSFM_TRANSFORMATION           8.1.5           
NO_SEMIJOIN                    SEMIJOIN                       QKSFM_TRANSFORMATION           9.0.0           
NO_SET_TO_JOIN                 SET_TO_JOIN                    QKSFM_SET_TO_JOIN              10.1.0.3        
NO_SQL_TUNE                                                   QKSFM_ALL                      10.2.0.1        
NO_STAR_TRANSFORMATION         STAR_TRANSFORMATION            QKSFM_STAR_TRANS               10.1.0.3        
NO_STATEMENT_QUEUING           STATEMENT_QUEUING              QKSFM_PARALLEL                 11.2.0.1        
NO_STATS_GSETS                                                QKSFM_ALL                      8.0.0           
NO_SUBQUERY_PRUNING            SUBQUERY_PRUNING               QKSFM_CBO                      11.1.0.6        
NO_SUBSTRB_PAD                                                QKSFM_EXECUTION                11.2.0.1        
NO_SWAP_JOIN_INPUTS            SWAP_JOIN_INPUTS               QKSFM_CBO                      10.1.0.3        
NO_TRANSFORM_DISTINCT_AGG      TRANSFORM_DISTINCT_AGG         QKSFM_TRANSFORMATION           11.2.0.1        
NO_UNNEST                      UNNEST                         QKSFM_UNNEST                   8.1.6           
NO_USE_HASH                    USE_HASH                       QKSFM_USE_HASH                 10.1.0.3        
NO_USE_HASH_AGGREGATION        USE_HASH_AGGREGATION           QKSFM_ALL                      10.2.0.1        
NO_USE_INVISIBLE_INDEXES       USE_INVISIBLE_INDEXES          QKSFM_INDEX                    11.1.0.6        
NO_USE_MERGE                   USE_MERGE                      QKSFM_USE_MERGE                10.1.0.3        
NO_USE_NL                      USE_NL                         QKSFM_USE_NL                   10.1.0.3        
NO_XMLINDEX_REWRITE            XMLINDEX_REWRITE               QKSFM_XMLINDEX_REWRITE         11.1.0.6        
NO_XMLINDEX_REWRITE_IN_SELECT  XMLINDEX_REWRITE_IN_SELECT     QKSFM_XMLINDEX_REWRITE         11.1.0.6        
NO_XML_DML_REWRITE                                            QKSFM_XML_REWRITE              10.2.0.1        
NO_XML_QUERY_REWRITE           FORCE_XML_QUERY_REWRITE        QKSFM_XML_REWRITE              9.2.0           
NUM_INDEX_KEYS                                                QKSFM_CBO                      10.2.0.3        
OLD_PUSH_PRED                                                 QKSFM_OLD_PUSH_PRED            10.2.0.1        
OPAQUE_TRANSFORM                                              QKSFM_TRANSFORMATION           10.1.0.3        
OPAQUE_XCANONICAL                                             QKSFM_TRANSFORMATION           10.1.0.3        
OPTIMIZER_FEATURES_ENABLE                                     QKSFM_ALL                      10.1.0.3        
OPT_ESTIMATE                                                  QKSFM_OPT_ESTIMATE             10.1.0.3        
OPT_PARAM                                                     QKSFM_ALL                      10.2.0.1        
ORDERED                                                       QKSFM_CBO                      8.1.0           
ORDERED_PREDICATES                                            QKSFM_CBO                      8.0.0           
OR_EXPAND                                                     QKSFM_OR_EXPAND                8.1.7           
OUTER_JOIN_TO_INNER            NO_OUTER_JOIN_TO_INNER         QKSFM_OUTER_JOIN_TO_INNER      11.1.0.6        
OUTLINE                                                       QKSFM_ALL                      10.2.0.1        
OUTLINE_LEAF                                                  QKSFM_ALL                      10.2.0.1        
OVERFLOW_NOMOVE                                               QKSFM_CBO                      9.0.0           
PARALLEL_INDEX                 NO_PARALLEL_INDEX              QKSFM_PQ                       8.1.0           
PIV_GB                                                        QKSFM_ALL                      8.1.0           
PIV_SSF                                                       QKSFM_ALL                      8.1.0           
PLACE_DISTINCT                 NO_PLACE_DISTINCT              QKSFM_DIST_PLCMT               11.2.0.1        
PLACE_GROUP_BY                 NO_PLACE_GROUP_BY              QKSFM_PLACE_GROUP_BY           11.1.0.6        
PQ_DISTRIBUTE                                                 QKSFM_PQ_DISTRIBUTE            8.1.5           
PQ_MAP                         PQ_NOMAP                       QKSFM_PQ_MAP                   9.0.0           
PQ_NOMAP                       PQ_MAP                         QKSFM_PQ_MAP                   9.0.0           
PRECOMPUTE_SUBQUERY                                           QKSFM_TRANSFORMATION           10.2.0.1        
PRESERVE_OID                                                  QKSFM_ALL                      10.2.0.1        
PULL_PRED                      NO_PULL_PRED                   QKSFM_PULL_PRED                10.2.0.1        
PUSH_PRED                      NO_PUSH_PRED                   QKSFM_FILTER_PUSH_PRED         8.1.0           
PUSH_SUBQ                      NO_PUSH_SUBQ                   QKSFM_TRANSFORMATION           8.1.0           
PX_JOIN_FILTER                 NO_PX_JOIN_FILTER              QKSFM_PX_JOIN_FILTER           10.2.0.1        
QB_NAME                                                       QKSFM_ALL                      10.1.0.3        
QUEUE_CURR                                                    QKSFM_CBO                      8.0.0           
QUEUE_ROWP                                                    QKSFM_CBO                      8.0.0           
RBO_OUTLINE                                                   QKSFM_RBO                      10.2.0.1        
REF_CASCADE_CURSOR             NO_REF_CASCADE                 QKSFM_CBO                      9.2.0           
REMOTE_MAPPED                                                 QKSFM_ALL                      8.1.0           
RESTORE_AS_INTERVALS                                          QKSFM_CBO                      8.1.5           
RESTRICT_ALL_REF_CONS                                         QKSFM_ALL                      10.1.0.3        
RESULT_CACHE                   NO_RESULT_CACHE                QKSFM_EXECUTION                11.1.0.6        
RETRY_ON_ROW_CHANGE                                           QKSFM_DML                      11.1.0.7        
REWRITE                        NO_REWRITE                     QKSFM_TRANSFORMATION           8.1.5           
REWRITE_OR_ERROR                                              QKSFM_TRANSFORMATION           10.1.0.3        
ROWID                                                         QKSFM_CBO                      8.0.0           
RULE                                                          QKSFM_RBO                      8.1.0           
SAVE_AS_INTERVALS                                             QKSFM_CBO                      8.1.5           
SCN_ASCENDING                                                 QKSFM_ALL                      8.1.5           
SEMIJOIN                       NO_SEMIJOIN                    QKSFM_TRANSFORMATION           9.0.0           
SEMIJOIN_DRIVER                                               QKSFM_CBO                      8.1.0           
SET_TO_JOIN                    NO_SET_TO_JOIN                 QKSFM_SET_TO_JOIN              10.1.0.3        
SHARED                         NO_PARALLEL                    QKSFM_PARALLEL                 8.1.0           
SKIP_EXT_OPTIMIZER                                            QKSFM_CBO                      9.0.0           
SKIP_UNQ_UNUSABLE_IDX                                         QKSFM_CBO                      10.1.0.3        
SQLLDR                                                        QKSFM_CBO                      9.0.0           
STAR                                                          QKSFM_STAR_TRANS               8.1.0           
STAR_TRANSFORMATION            NO_STAR_TRANSFORMATION         QKSFM_STAR_TRANS               8.1.0           
STATEMENT_QUEUING              NO_STATEMENT_QUEUING           QKSFM_PARALLEL                 11.2.0.1        
STREAMS                                                       QKSFM_CBO                      10.1.0.3        
SUBQUERY_PRUNING               NO_SUBQUERY_PRUNING            QKSFM_CBO                      11.1.0.6        
SWAP_JOIN_INPUTS               NO_SWAP_JOIN_INPUTS            QKSFM_CBO                      8.1.0           
SYS_DL_CURSOR                                                 QKSFM_CBO                      9.2.0           
SYS_PARALLEL_TXN                                              QKSFM_CBO                      8.1.6           
SYS_RID_ORDER                                                 QKSFM_ALL                      9.2.0           
TABLE_STATS                                                   QKSFM_STATS                    10.1.0.3        
TIV_GB                                                        QKSFM_ALL                      8.1.0           
TIV_SSF                                                       QKSFM_ALL                      8.1.0           
TRACING                                                       QKSFM_EXECUTION                10.1.0.3        
TRANSFORM_DISTINCT_AGG         NO_TRANSFORM_DISTINCT_AGG      QKSFM_TRANSFORMATION           11.2.0.1        
UNNEST                         NO_UNNEST                      QKSFM_UNNEST                   8.1.6           
USE_ANTI                                                      QKSFM_CBO                      8.1.0           
USE_CONCAT                     NO_EXPAND                      QKSFM_USE_CONCAT               8.1.0           
USE_HASH                       NO_USE_HASH                    QKSFM_USE_HASH                 8.1.0           
USE_HASH_AGGREGATION           NO_USE_HASH_AGGREGATION        QKSFM_ALL                      10.2.0.1        
USE_INVISIBLE_INDEXES          NO_USE_INVISIBLE_INDEXES       QKSFM_INDEX                    11.1.0.6        
USE_MERGE                      NO_USE_MERGE                   QKSFM_USE_MERGE                8.1.0           
USE_MERGE_CARTESIAN                                           QKSFM_USE_MERGE_CARTESIAN      11.1.0.6        
USE_NL                         NO_USE_NL                      QKSFM_USE_NL                   8.1.0           
USE_NL_WITH_INDEX              NO_USE_NL                      QKSFM_USE_NL_WITH_INDEX        10.1.0.3        
USE_SEMI                                                      QKSFM_CBO                      8.1.0           
USE_TTT_FOR_GSETS                                             QKSFM_TRANSFORMATION           9.0.0           
USE_WEAK_NAME_RESL                                            QKSFM_ALL                      10.1.0.3        
VECTOR_READ                                                   QKSFM_CBO                      10.1.0.3        
VECTOR_READ_TRACE                                             QKSFM_CBO                      10.1.0.3        
XMLINDEX_REWRITE               NO_XMLINDEX_REWRITE            QKSFM_XMLINDEX_REWRITE         11.1.0.6        
XMLINDEX_REWRITE_IN_SELECT     NO_XMLINDEX_REWRITE_IN_SELECT  QKSFM_XMLINDEX_REWRITE         11.1.0.6        
XMLINDEX_SEL_IDX_TBL                                          QKSFM_ALL                      11.2.0.1        
XML_DML_RWT_STMT                                              QKSFM_XML_REWRITE              11.1.0.6        
X_DYN_PRUNE                                                   QKSFM_CBO                      10.1.0.3        

263 rows selected.

 

My Toy Project for Performance Tuning screenshot:





SQL – Bad Execution Plan caused by ROWNUM, ROW_NUMBER is Possible Fix

9 12 2009

December 9, 2009

A recent discussion in the OTN forums demonstrated a cardinality calculation problem caused by using ROWNUM as a predicate in the WHERE clause (this specific issue was corrected in 11.2.0.1):
http://forums.oracle.com/forums/thread.jspa?threadID=934895&start=0

The person who started the OTN thread obviously knew how to ask a question, provide relevant information, and analyze the problem (essentially a model of a good request for assistance):

I’m having a couple of issues with a query, and I can’t figure out the best way to reach a solution.

Platform Information
Windows Server 2003 R2
Oracle 10.2.0.4

Optimizer Settings

SQL > show parameter optimizer

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      10.2.0.4
optimizer_index_caching              integer     90
optimizer_index_cost_adj             integer     30
optimizer_mode                       string      ALL_ROWS
optimizer_secure_view_merging        boolean     TRUE

The query below, is a simple “Top N” query, where the top result is returned. Here it is, with bind variables in the same location as the application code:

SELECT PRODUCT_DESC
FROM
(
 SELECT PRODUCT_DESC
 , COUNT(*) AS CNT
 FROM USER_VISITS 
 JOIN PRODUCT ON PRODUCT.PRODUCT_OID = USER_VISITS.PRODUCT_OID
 WHERE PRODUCT.PRODUCT_DESC != 'Home' 
 AND VISIT_DATE
  BETWEEN
   ADD_MONTHS    
   (
    TRUNC    
    (
     TO_DATE   
     (
      :vCurrentYear
     , 'YYYY'
     )
    , 'YEAR'
    )
   , 3*(:vCurrentQuarter-1)
   )
  AND
   ADD_MONTHS    
   (
    TRUNC    
    (
     TO_DATE   
     (
      :vCurrentYear
     , 'YYYY'
     )
    , 'YEAR'
    )
   , 3*:vCurrentQuarter
   ) - INTERVAL '1' DAY   
 GROUP BY PRODUCT_DESC
 ORDER BY CNT DESC
)
WHERE ROWNUM <= 1;

The explain plan I receive when running the query above.

| Id  | Operation                         | Name                          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                    |                               |      1 |        |      1 |00:00:34.92 |   66343 |       |       |          |
|   2 |   VIEW                            |                               |      1 |      1 |      1 |00:00:34.92 |   66343 |       |       |          |
|*  3 |    FILTER                         |                               |      1 |        |      1 |00:00:34.92 |   66343 |       |       |          |
|   4 |     SORT ORDER BY                 |                               |      1 |      1 |      1 |00:00:34.92 |   66343 |  2048 |  2048 | 2048  (0)|
|   5 |      SORT GROUP BY NOSORT         |                               |      1 |      1 |     27 |00:00:34.92 |   66343 |       |       |          |
|   6 |       NESTED LOOPS                |                               |      1 |      2 |  12711 |00:00:34.90 |   66343 |       |       |          |
|   7 |        TABLE ACCESS BY INDEX ROWID| PRODUCT                       |      1 |     74 |     77 |00:00:00.01 |      44 |       |       |          |
|*  8 |         INDEX FULL SCAN           | PRODUCT_PRODDESCHAND_UNQ      |      1 |      1 |     77 |00:00:00.01 |       1 |       |       |          |
|*  9 |        INDEX FULL SCAN            | USER_VISITS#PK                |     77 |      2 |  12711 |00:00:34.88 |   66299 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=1)
   3 - filter(ADD_MONTHS(TRUNC(TO_DATE(TO_CHAR(:VCURRENTYEAR),'YYYY'),'fmyear'),3*(:VCURRENTQUARTER-1))<=ADD_MONTHS(TRUNC(TO_DATE(TO_CHAR(:VCURR
              ENTYEAR),'YYYY'),'fmyear'),3*:VCURRENTQUARTER)-INTERVAL'+01 00:00:00' DAY(2) TO SECOND(0))
   8 - filter("PRODUCT"."PRODUCT_DESC"<>'Home')
   9 - access("USER_VISITS"."VISIT_DATE">=ADD_MONTHS(TRUNC(TO_DATE(TO_CHAR(:VCURRENTYEAR),'YYYY'),'fmyear'),3*(:VCURRENTQUARTER-1)) AND
              "USER_VISITS"."PRODUCT_OID"="PRODUCT"."PRODUCT_OID" AND "USER_VISITS"."VISIT_DATE"<=ADD_MONTHS(TRUNC(TO_DATE(TO_CHAR(:VCURRENTYEAR),'YYYY')
              ,'fmyear'),3*:VCURRENTQUARTER)-INTERVAL'+01 00:00:00' DAY(2) TO SECOND(0))
       filter(("USER_VISITS"."VISIT_DATE">=ADD_MONTHS(TRUNC(TO_DATE(TO_CHAR(:VCURRENTYEAR),'YYYY'),'fmyear'),3*(:VCURRENTQUARTER-1)) AND
              "USER_VISITS"."VISIT_DATE"<=ADD_MONTHS(TRUNC(TO_DATE(TO_CHAR(:VCURRENTYEAR),'YYYY'),'fmyear'),3*:VCURRENTQUARTER)-INTERVAL'+01 00:00:00' DAY(2)
              TO SECOND(0) AND "USER_VISITS"."PRODUCT_OID"="PRODUCT"."PRODUCT_OID"))

TKPROF Row Source Generation

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.01          0          0          0           0
Fetch        2     35.10      35.13          0      66343          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     35.10      35.14          0      66343          0           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 62 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  COUNT STOPKEY (cr=66343 pr=0 pw=0 time=35132008 us)
      1   VIEW  (cr=66343 pr=0 pw=0 time=35131996 us)
      1    FILTER  (cr=66343 pr=0 pw=0 time=35131991 us)
      1     SORT ORDER BY (cr=66343 pr=0 pw=0 time=35131936 us)
     27      SORT GROUP BY NOSORT (cr=66343 pr=0 pw=0 time=14476309 us)
  12711       NESTED LOOPS  (cr=66343 pr=0 pw=0 time=22921810 us)
     77        TABLE ACCESS BY INDEX ROWID PRODUCT (cr=44 pr=0 pw=0 time=3674 us)
     77         INDEX FULL SCAN PRODUCT_PRODDESCHAND_UNQ (cr=1 pr=0 pw=0 time=827 us)(object id 52355)
  12711        INDEX FULL SCAN USER_VISITS#PK (cr=66299 pr=0 pw=0 time=44083746 us)(object id 52949)

However when I run the query with an ALL_ROWS hint I receive this explain plan (reasoning for this can be found here Jonathan’s Lewis’ response: http://www.freelists.org/post/oracle-l/ORDER-BY-and-first-rows-10-madness,4):

---------------------------------------------------------------------------------------------
| Id  | Operation                  | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                |     1 |    39 |   223  (25)| 00:00:03 |
|*  1 |  COUNT STOPKEY             |                |       |       |            |          |
|   2 |   VIEW                     |                |     1 |    39 |   223  (25)| 00:00:03 |
|*  3 |    FILTER                  |                |       |       |            |          |
|   4 |     SORT ORDER BY          |                |     1 |    49 |   223  (25)| 00:00:03 |
|   5 |      HASH GROUP BY         |                |     1 |    49 |   223  (25)| 00:00:03 |
|*  6 |       HASH JOIN            |                |   490 | 24010 |   222  (24)| 00:00:03 |
|*  7 |        TABLE ACCESS FULL   | PRODUCT        |    77 |  2849 |     2   (0)| 00:00:01 |
|*  8 |        INDEX FAST FULL SCAN| USER_VISITS#PK |   490 |  5880 |   219  (24)| 00:00:03 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=1)
   3 - filter(ADD_MONTHS(TRUNC(TO_DATE(:VCURRENTYEAR,'YYYY'),'fmyear'),3*(TO_NUMBER(:
              VCURRENTQUARTER)-1))<=ADD_MONTHS(TRUNC(TO_DATE(:VCURRENTYEAR,'YYYY'),'fmyear'),3*TO_N
              UMBER(:VCURRENTQUARTER))-INTERVAL'+01 00:00:00' DAY(2) TO SECOND(0))
   6 - access("USER_VISITS"."PRODUCT_OID"="PRODUCT"."PRODUCT_OID")
   7 - filter("PRODUCT"."PRODUCT_DESC"<>'Home')
   8 - filter("USER_VISITS"."VISIT_DATE">=ADD_MONTHS(TRUNC(TO_DATE(:VCURRENTYEAR,'YYY
              Y'),'fmyear'),3*(TO_NUMBER(:VCURRENTQUARTER)-1)) AND
              "USER_VISITS"."VISIT_DATE"<=ADD_MONTHS(TRUNC(TO_DATE(:VCURRENTYEAR,'YYYY'),'fmyear'),
              3*TO_NUMBER(:VCURRENTQUARTER))-INTERVAL'+01 00:00:00' DAY(2) TO SECOND(0))

And the TKPROF Row Source Generation:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        3      0.51       0.51          0        907          0          27
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.51       0.51          0        907          0          27

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 62 

Rows     Row Source Operation
-------  ---------------------------------------------------
     27  FILTER  (cr=907 pr=0 pw=0 time=513472 us)
     27   SORT ORDER BY (cr=907 pr=0 pw=0 time=513414 us)
     27    HASH GROUP BY (cr=907 pr=0 pw=0 time=512919 us)
  12711     HASH JOIN  (cr=907 pr=0 pw=0 time=641130 us)
     77      TABLE ACCESS FULL PRODUCT (cr=5 pr=0 pw=0 time=249 us)
  22844      INDEX FAST FULL SCAN USER_VISITS#PK (cr=902 pr=0 pw=0 time=300356 us)(object id 52949)

The query with the ALL_ROWS hint returns data instantly, while the other one takes about 70 times as long.

Interestingly enough BOTH queries generate plans with estimates that are WAY off. The first plan is estimating 2 rows, while the second plan is estimating 490 rows. However the real number of rows is correctly reported in the Row Source Generation as 12711 (after the join operation).

TABLE_NAME                       NUM_ROWS     BLOCKS
------------------------------ ---------- ----------
USER_VISITS                        196044       1049

INDEX_NAME                         BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR LAST_ANALYZED
------------------------------ ---------- ----------- ------------- ----------------- -------------------
USER_VISITS#PK                          2         860        196002          57761 07/24/2009 13:17:59

COLUMN_NAME                    NUM_DISTINCT LOW_VALUE            HIGH_VALUE                                 DENSITY     NUM_NULLS HISTOGRAM
------------------------------ ------------ -------------------- -------------------- -------------------------------- ---------- ---------------
VISIT_DATE                           195900 786809010E0910       786D0609111328                      .0000051046452272          0 NONE

I don’t know how the first one is estimating 2 rows, but I can compute the second’s cardinality estimates by assuming a 5% selectivity for the TO_DATE() functions:

SQL > SELECT ROUND(0.05*0.05*196044) FROM DUAL;

ROUND(0.05*0.05*196044)
-----------------------
                    490

However, removing the bind variables (and clearing the shared pool), does not change the cardinality estimates at all. I would like to avoid hinting this plan if possible and that is why I’m looking for advice. I also have a followup question.

Per one of Jonathan Lewis’ articles, adding a ROWNUM predicate in the WHERE clause causes the optimizer to switch to a FIRST ROWS n optimizer mode, as if a /*+ FIRST_ROWS(n) */ hint were provided for the query. To work around this issue, changing the query to use a ROW_NUMBER analytic function rather than the ROWNUM pseudo column could prevent the optimizer from switching to first rows optimization.

Another question you might ask is if bind peeking enabled? It is enabled by default on Oracle 10g (and 9i). Are there automatically generated histograms on any of the columns in the WHERE clause? Do any of the tables involved have out of date statistics (or cases where the statistics as of 10 PM do not match the statistics as of the current time)? Is it possible to examine 10053 trace files for the SQL statement?

Testing with either a CARDINALITY or OPT_ESTIMATE hint to see if the execution plan changes dramatically to improve performance is another possible area of investigation. The question then becomes whether this will be sufficient to over-rule the first rows optimizer so that it does not use an index access which will avoid a sort.

There may also be a problem with the accuracy of the EXPLAIN PLAN in SQL*Plus due to the bind variables in the SQL statement. If I recall correctly, explain plan does not peek at bind variables, as happens when the query is actually executed (but that may have been a 9i problem, where 10g uses DBMS_XPLAN for explain plans), and that SQL*Plus passes in numeric bind variables as if they are VARCHAR2. (I have an example in 11.1.0.7 which shows that this might not be the case).

The original poster provided a great test case to demonstrate what is happening:

DDL/DML

CREATE TABLE TEST_AGGR
(
 ID NUMBER
,  VAL DATE
,  PAD VARCHAR2(500)
,  ID2 NUMBER
);

CREATE TABLE TEST_JOIN
(
        ID2 NUMBER
,       PAD VARCHAR2(5)
,       PROD VARCHAR2(15)
);

CREATE SEQUENCE TEST_SEQUENCE;

INSERT INTO TEST_AGGR
SELECT TEST_SEQUENCE.NEXTVAL
, SYSDATE + DBMS_RANDOM.VALUE*100
, RPAD('X',499,'0')
, MOD(ROWNUM,78)
FROM DUAL
CONNECT BY ROWNUM <= 195000;

INSERT INTO TEST_JOIN
SELECT  ID2
,  
 (CASE
  WHEN ROWNUM IN (3,5) THEN 'Y'
  ELSE RPAD('X',4,'0')
 END)
, (CASE
  WHEN ROWNUM = 1 THEN 'test_prod'
  WHEN ROWNUM BETWEEN 2 AND 3 THEN 'DUPLICATE A1'
  WHEN ROWNUM BETWEEN 4 AND 5 THEN 'A DUPLICATE A2'
  ELSE UPPER(DBMS_RANDOM.STRING('A',15)) END)
FROM  (SELECT DISTINCT ID2 FROM TEST_AGGR);

CREATE INDEX TEST_AGGR_IDX ON TEST_AGGR(ID,VAL,ID2);
CREATE UNIQUE INDEX TEST_JOIN_UIDX ON TEST_JOIN(PROD,PAD);

 Statistics Gathering

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'TEST_AGGR',cascade=>true);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'TEST_JOIN',cascade=>true);

Bind Variable Setup

var a VARCHAR2(25);
var b VARCHAR2(25);

exec :a := '08/01/2009';
exec :b := '08/27/2009';

Non-hinted Query

SELECT /*repeatable_case_nohint*/ PROD
FROM
(
 SELECT TEST_JOIN.PROD
 , COUNT(*) CNT
 FROM TEST_AGGR
 JOIN TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2
 WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')
 AND PROD  'TEST_PROD'
 GROUP BY TEST_JOIN.PROD
 ORDER BY CNT DESC
)
WHERE ROWNUM <= 1

 Non-hinted XPLAN Output

SQL_ID  9smb486cd31b2, child number 0
-------------------------------------
SELECT /*repeatable_case_nohint*/ PROD FROM (  SELECT TEST_JOIN.PROD  , COUNT(*) CNT  FROM TEST_AGGR  JOIN TEST_JOIN ON
TEST_JOIN.ID2 = TEST_AGGR.ID2  WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')  AND PROD <> 'TEST_PROD'
GROUP BY TEST_JOIN.PROD  ORDER BY CNT DESC ) WHERE ROWNUM <= 1

Plan hash value: 301623847

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
-----------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                    |                |      1 |        |      1 |00:00:07.33 |   57334 |       |       |          |
|   2 |   VIEW                            |                |      1 |      2 |      1 |00:00:07.33 |   57334 |       |       |          |
|*  3 |    FILTER                         |                |      1 |        |      1 |00:00:07.33 |   57334 |       |       |          |
|   4 |     SORT ORDER BY                 |                |      1 |      2 |      1 |00:00:07.33 |   57334 | 73728 | 73728 |          |
|   5 |      SORT GROUP BY NOSORT         |                |      1 |      2 |     76 |00:00:07.33 |   57334 |       |       |          |
|   6 |       NESTED LOOPS                |                |      1 |      2 |  50637 |00:00:07.24 |   57334 |       |       |          |
|   7 |        TABLE ACCESS BY INDEX ROWID| TEST_JOIN      |      1 |     77 |     78 |00:00:00.01 |    2 |          |       |          |
|*  8 |         INDEX FULL SCAN           | TEST_JOIN_UIDX |      1 |      1 |     78 |00:00:00.01 |    1 |          |       |          |
|*  9 |        INDEX FULL SCAN            | TEST_AGGR_IDX  |     78 |      2 |  50637 |00:00:07.15 |   57332 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=1)
   3 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY'))
   8 - filter("TEST_JOIN"."PROD"<>'TEST_PROD')
   9 - access("TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2" AND
              "TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY'))
       filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND
              "TEST_JOIN"."ID2"="TEST_AGGR"."ID2"))

Hinted Query

SELECT /*+ ALL_ROWS repeatable_case_hint*/ PROD
FROM
(
 SELECT TEST_JOIN.PROD
 , COUNT(*) CNT
 FROM TEST_AGGR
 JOIN TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2
 WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')
 AND PROD  'TEST_PROD'
 GROUP BY TEST_JOIN.PROD
 ORDER BY CNT DESC
)
WHERE ROWNUM <= 1

Hinted XPLAN Output

SQL_ID  283wx8s0d04kn, child number 0
-------------------------------------
SELECT /*+ ALL_ROWS repeatable_case_hint*/ PROD FROM (  SELECT TEST_JOIN.PROD  , COUNT(*) CNT  FROM TEST_AGGR  JOIN
TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2  WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')  AND
PROD <> 'TEST_PROD'  GROUP BY TEST_JOIN.PROD  ORDER BY CNT DESC ) WHERE ROWNUM <= 1

Plan hash value: 828673535

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
---------------------------------------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY             |               |      1 |        |      1 |00:00:00.34 |     772 |       |       |          |
|   2 |   VIEW                     |               |      1 |     75 |      1 |00:00:00.34 |     772 |       |       |          |
|*  3 |    FILTER                  |               |      1 |        |      1 |00:00:00.34 |     772 |       |       |          |
|   4 |     SORT ORDER BY          |               |      1 |     75 |      1 |00:00:00.34 |     772 | 73728 | 73728 |          |
|   5 |      HASH GROUP BY         |               |      1 |     75 |     76 |00:00:00.34 |     772 |   808K|   808K|     1/0/0|
|*  6 |       HASH JOIN            |               |      1 |  50069 |  50637 |00:00:00.26 |     772 |   842K|   842K|     1/0/0|
|*  7 |        TABLE ACCESS FULL   | TEST_JOIN     |      1 |     77 |     78 |00:00:00.01 |       3 |       |       |          |
|*  8 |        INDEX FAST FULL SCAN| TEST_AGGR_IDX |      1 |  50737 |  50637 |00:00:00.10 |     769 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=1)
   3 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY'))
   6 - access("TEST_JOIN"."ID2"="TEST_AGGR"."ID2")
   7 - filter("TEST_JOIN"."PROD"<>'TEST_PROD')
   8 - filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY')))

Observations

The cardinality estimates in the non-hinted plan are way, way off. However, in the hinted plan the cardinalities are nearly correct. In the non-hinted plan the cardinality estimates are incorrect at steps 8 and 9.

I tried to work out the cardinality estimate for the access of TEST_JOIN_UIDX by first calculating the “Effective Index Selectivity” based on the following data:

TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT    DENSITY  NUM_NULLS
------------------------------ ------------------------------ ------------ ---------- ----------
TEST_JOIN                      PROD                                     76 .013157895          0

Since in step 8 of the plan we are filtering based on a != condition we want ALL the rows except that one. Since the number of rows in this roughly equal the number of distinct values I calculated a rough effective index selectivity estimate of:

(1-0.13157895)*78 = 67.7368419

This is right in line with a logical understanding of the data.

Based on the following data:

TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT
------------------------------ ------------------------------ ------------
TEST_AGGR                      VAL                                  194588

I would expect the following cardinality out of the TEST_AGGR_IDX index:

SQL > SELECT (TO_DATE(:b,'MM/DD/YYYY')-TO_DATE(:a,'MM/DD/YYYY'))/(MAX(VAL)-MIN(VAL))+2/194588 AS SELECTIVITY FROM TEST_AGGR;

SELECTIVITY
-----------
 .260014672

SQL > SELECT .260014672*195128 AS CARDINALITY FROM DUAL;

CARDINALITY
-----------
 50736.1429

This almost exactly matches the HINTED plan (error due to rounding).

Based on the estimates in the non-hinted plan I would have expected a 5% * 5% to occur but my calculations don’t match:

SQL > select .05*.05*(1/78)*195128 AS CARDINALITY from dual;

CARDINALITY
-----------
 6.25410256

After playing with the test case for a little while, I recalled seeing this behavior in the past with ROWNUM in the WHERE clause.  What is happening is that the ROWNUM<=1 predicate is being pushed into the inline view. Take a look (at the E-Rows column):

SELECT /*+ gather_plan_statistics */ PROD
FROM
(
 SELECT TEST_JOIN.PROD
 , COUNT(*) CNT
 FROM TEST_AGGR
 JOIN TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2
 WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')
 AND PROD <> 'TEST_PROD'
 GROUP BY TEST_JOIN.PROD
 ORDER BY CNT DESC
)
WHERE ROWNUM <= 1;

select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                    |                |      1 |        |      1 |00:00:02.84 |   57178 |       |       |          |
|   2 |   VIEW                            |                |      1 |      2 |      1 |00:00:02.84 |   57178 |       |       |          |
|*  3 |    FILTER                         |                |      1 |        |      1 |00:00:02.84 |   57178 |       |       |          |
|   4 |     SORT ORDER BY                 |                |      1 |      2 |      1 |00:00:02.84 |   57178 | 73728 | 73728 |          |
|   5 |      SORT GROUP BY NOSORT         |                |      1 |      2 |     76 |00:00:02.80 |   57178 |       |       |          |
|   6 |       NESTED LOOPS                |                |      1 |      2 |  50596 |00:00:03.04 |   57178 |       |       |          |
|   7 |        TABLE ACCESS BY INDEX ROWID| TEST_JOIN      |      1 |     77 |     78 |00:00:00.01 |       2 |       |       |          |
|*  8 |         INDEX FULL SCAN           | TEST_JOIN_UIDX |      1 |      1 |     78 |00:00:00.01 |       1 |       |       |          |
|*  9 |        INDEX FULL SCAN            | TEST_AGGR_IDX  |     78 |      2 |  50596 |00:00:02.88 |   57176 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=1)
   3 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY'))
   8 - filter("TEST_JOIN"."PROD"<>'TEST_PROD')
   9 - access("TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2" AND
              "TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY'))
       filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND
              "TEST_JOIN"."ID2"="TEST_AGGR"."ID2"))

 

SELECT /*+ gather_plan_statistics */ PROD
FROM
(
 SELECT TEST_JOIN.PROD
 , COUNT(*) CNT
 FROM TEST_AGGR
 JOIN TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2
 WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')
 AND PROD  'TEST_PROD'
 GROUP BY TEST_JOIN.PROD
 ORDER BY CNT DESC
)
WHERE ROWNUM <= 2;

select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                    |                |      1 |        |      2 |00:00:02.70 |   57178 |       |       |          |
|   2 |   VIEW                            |                |      1 |      3 |      2 |00:00:02.70 |   57178 |       |       |          |
|*  3 |    FILTER                         |                |      1 |        |      2 |00:00:02.70 |   57178 |       |       |          |
|   4 |     SORT ORDER BY                 |                |      1 |      3 |      2 |00:00:02.70 |   57178 |  9216 |  9216 | 8192  (0)|
|   5 |      SORT GROUP BY NOSORT         |                |      1 |      3 |     76 |00:00:02.73 |   57178 |       |       |          |
|   6 |       NESTED LOOPS                |                |      1 |      3 |  50596 |00:00:02.88 |   57178 |       |       |          |
|   7 |        TABLE ACCESS BY INDEX ROWID| TEST_JOIN      |      1 |     77 |     78 |00:00:00.01 |       2 |       |       |          |
|*  8 |         INDEX FULL SCAN           | TEST_JOIN_UIDX |      1 |      1 |     78 |00:00:00.01 |       1 |       |       |          |
|*  9 |        INDEX FULL SCAN            | TEST_AGGR_IDX  |     78 |      3 |  50596 |00:00:02.73 |   57176 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=2)
   3 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY'))
   8 - filter("TEST_JOIN"."PROD"<>'TEST_PROD')
   9 - access("TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2" AND
              "TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY'))
       filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND
              "TEST_JOIN"."ID2"="TEST_AGGR"."ID2"))

 

SELECT /*+ gather_plan_statistics */ PROD
FROM
(
 SELECT TEST_JOIN.PROD
 , COUNT(*) CNT
 FROM TEST_AGGR
 JOIN TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2
 WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')
 AND PROD  'TEST_PROD'
 GROUP BY TEST_JOIN.PROD
 ORDER BY CNT DESC
)
WHERE ROWNUM <= 3;

select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                    |                |      1 |        |      3 |00:00:02.71 |   57178 |       |       |          |
|   2 |   VIEW                            |                |      1 |      4 |      3 |00:00:02.71 |   57178 |       |       |          |
|*  3 |    FILTER                         |                |      1 |        |      3 |00:00:02.71 |   57178 |       |       |          |
|   4 |     SORT ORDER BY                 |                |      1 |      4 |      3 |00:00:02.71 |   57178 |  9216 |  9216 | 8192  (0)|
|   5 |      SORT GROUP BY NOSORT         |                |      1 |      4 |     76 |00:00:02.67 |   57178 |       |       |          |
|   6 |       NESTED LOOPS                |                |      1 |      4 |  50596 |00:00:02.93 |   57178 |       |       |          |
|   7 |        TABLE ACCESS BY INDEX ROWID| TEST_JOIN      |      1 |     77 |     78 |00:00:00.01 |       2 |       |       |          |
|*  8 |         INDEX FULL SCAN           | TEST_JOIN_UIDX |      1 |      1 |     78 |00:00:00.01 |       1 |       |       |          |
|*  9 |        INDEX FULL SCAN            | TEST_AGGR_IDX  |     78 |      4 |  50596 |00:00:02.83 |   57176 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=3)
   3 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY'))
   8 - filter("TEST_JOIN"."PROD"<>'TEST_PROD')
   9 - access("TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2" AND
              "TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY'))
       filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND
              "TEST_JOIN"."ID2"="TEST_AGGR"."ID2"))

 

SELECT /*+ gather_plan_statistics */ PROD
FROM
(
 SELECT TEST_JOIN.PROD
 , COUNT(*) CNT
 FROM TEST_AGGR
 JOIN TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2
 WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')
 AND PROD  'TEST_PROD'
 GROUP BY TEST_JOIN.PROD
 ORDER BY CNT DESC
)
WHERE ROWNUM <= 50;

select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                    |                |      1 |        |     50 |00:00:02.72 |   57178 |       |       |          |
|   2 |   VIEW                            |                |      1 |     51 |     50 |00:00:02.72 |   57178 |       |       |          |
|*  3 |    FILTER                         |                |      1 |        |     50 |00:00:02.72 |   57178 |       |       |          |
|   4 |     SORT ORDER BY                 |                |      1 |     51 |     50 |00:00:02.72 |   57178 |  9216 |  9216 | 8192  (0)|
|   5 |      SORT GROUP BY NOSORT         |                |      1 |     51 |     76 |00:00:02.65 |   57178 |       |       |          |
|   6 |       NESTED LOOPS                |                |      1 |     51 |  50596 |00:00:02.88 |   57178 |       |       |          |
|   7 |        TABLE ACCESS BY INDEX ROWID| TEST_JOIN      |      1 |     77 |     78 |00:00:00.01 |       2 |       |       |          |
|*  8 |         INDEX FULL SCAN           | TEST_JOIN_UIDX |      1 |      1 |     78 |00:00:00.01 |       1 |       |       |          |
|*  9 |        INDEX FULL SCAN            | TEST_AGGR_IDX  |     78 |     51 |  50596 |00:00:02.78 |   57176 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=50)
   3 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY'))
   8 - filter("TEST_JOIN"."PROD"<>'TEST_PROD')
   9 - access("TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2" AND
              "TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY'))
       filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND
              "TEST_JOIN"."ID2"="TEST_AGGR"."ID2"))

 

SELECT /*+ gather_plan_statistics */ PROD
FROM
(
 SELECT TEST_JOIN.PROD
 , COUNT(*) CNT
 FROM TEST_AGGR
 JOIN TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2
 WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')
 AND PROD  'TEST_PROD'
 GROUP BY TEST_JOIN.PROD
 ORDER BY CNT DESC
)
WHERE ROWNUM <= 74;

select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                    |                |      1 |        |   74 |00:00:02.71 |   57178 |       |       |          |
|   2 |   VIEW                            |                |      1 |     75 |   74 |00:00:02.71 |   57178 |       |       |          |
|*  3 |    FILTER                         |                |      1 |        |   74 |00:00:02.71 |   57178 |       |       |          |
|   4 |     SORT ORDER BY                 |                |      1 |     75 |   74 |00:00:02.71 |   57178 |  9216 |  9216 | 8192  (0)|
|   5 |      SORT GROUP BY NOSORT         |                |      1 |     75 |   76 |00:00:02.66 |   57178 |       |       |          |
|   6 |       NESTED LOOPS                |                |      1 |     75 |50596 |00:00:02.93 |   57178 |       |       |          |
|   7 |        TABLE ACCESS BY INDEX ROWID| TEST_JOIN      |      1 |     77 |   78 |00:00:00.01 |       2 |       |       |          |
|*  8 |         INDEX FULL SCAN           | TEST_JOIN_UIDX |      1 |      1 |   78 |00:00:00.01 |       1 |       |       |          |
|*  9 |        INDEX FULL SCAN            | TEST_AGGR_IDX  |     78 |     75 |50596 |00:00:02.83 |   57176 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=74)
   3 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY'))
   8 - filter("TEST_JOIN"."PROD"<>'TEST_PROD')
   9 - access("TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2" AND
              "TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY'))
       filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND
              "TEST_JOIN"."ID2"="TEST_AGGR"."ID2"))

 

SELECT /*+ gather_plan_statistics */ PROD
FROM
(
 SELECT TEST_JOIN.PROD
 , COUNT(*) CNT
 FROM TEST_AGGR
 JOIN TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2
 WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')
 AND PROD  'TEST_PROD'
 GROUP BY TEST_JOIN.PROD
 ORDER BY CNT DESC
)
WHERE ROWNUM <= 75;

select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY          |               |      1 |        |     75 |00:00:00.06 |     741 |       |       |          |
|   2 |   VIEW                  |               |      1 |     75 |     75 |00:00:00.06 |     741 |       |       |          |
|*  3 |    FILTER               |               |      1 |        |     75 |00:00:00.06 |     741 |       |       |          |
|   4 |     SORT ORDER BY       |               |      1 |     75 |     75 |00:00:00.06 |     741 |  9216 |  9216 | 8192  (0)|
|   5 |      HASH GROUP BY      |               |      1 |     75 |     76 |00:00:00.06 |     741 |   963K|   963K| 2375K (0)|
|*  6 |       HASH JOIN         |               |      1 |  50074 |  50596 |00:00:00.30 |     741 |  1035K|  1035K| 1164K (0)|
|*  7 |        TABLE ACCESS FULL| TEST_JOIN     |      1 |     77 |     78 |00:00:00.01 |       7 |       |       |          |
|*  8 |        INDEX FULL SCAN  | TEST_AGGR_IDX |      1 |  50742 |  50596 |00:00:00.10 |     734 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=75)
   3 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY'))
   6 - access("TEST_JOIN"."ID2"="TEST_AGGR"."ID2")
   7 - filter("TEST_JOIN"."PROD"<>'TEST_PROD')
   8 - access("TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY'))
       filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY')))

Compare the above output with what follows from Oracle 11.2.0.1, with the following parameters set:

ALTER SESSION SET optimizer_index_caching=0;
ALTER SESSION SET optimizer_index_cost_adj=100;
ALTER SESSION SET TRACEFILE_IDENTIFIER='optimizer_test100';
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';

 

SELECT /*+ gather_plan_statistics */ PROD
FROM
(
 SELECT TEST_JOIN.PROD
 , COUNT(*) CNT
 FROM TEST_AGGR
 JOIN TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2
 WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')
 AND PROD  'TEST_PROD'
 GROUP BY TEST_JOIN.PROD
 ORDER BY CNT DESC
)
WHERE ROWNUM <= 1;

select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |               |      1 |        |      1 |00:00:00.03 |     747 |       |       |          |
|*  1 |  COUNT STOPKEY             |               |      1 |        |      1 |00:00:00.03 |     747 |       |       |          |
|   2 |   VIEW                     |               |      1 |     75 |      1 |00:00:00.03 |     747 |       |       |          |
|*  3 |    SORT ORDER BY STOPKEY   |               |      1 |     75 |      1 |00:00:00.03 |     747 | 73728 | 73728 |          |
|   4 |     HASH GROUP BY          |               |      1 |     75 |     76 |00:00:00.03 |     747 |   964K|   964K| 2491K (0)|
|*  5 |      FILTER                |               |      1 |        |  44832 |00:00:00.06 |     747 |       |       |          |
|*  6 |       HASH JOIN            |               |      1 |  44262 |  44832 |00:00:00.05 |     747 |  1035K|  1035K| 1265K (0)|
|*  7 |        TABLE ACCESS FULL   | TEST_JOIN     |      1 |     77 |     78 |00:00:00.01 |       3 |       |       |          |
|*  8 |        INDEX FAST FULL SCAN| TEST_AGGR_IDX |      1 |  44852 |  44832 |00:00:00.02 |     744 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=1)
   3 - filter(ROWNUM<=1)
   5 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY'))
   6 - access("TEST_JOIN"."ID2"="TEST_AGGR"."ID2")
   7 - filter("TEST_JOIN"."PROD"<>'TEST_PROD')
   8 - filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY')))

– 

ALTER SESSION SET optimizer_index_caching=90;
ALTER SESSION SET optimizer_index_cost_adj=30;
ALTER SESSION SET TRACEFILE_IDENTIFIER='optimizer_test30';

 

SELECT /*+ gather_plan_statistics */ PROD
FROM
(
 SELECT TEST_JOIN.PROD
 , COUNT(*) CNT
 FROM TEST_AGGR
 JOIN TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2
 WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')
 AND PROD  'TEST_PROD'
 GROUP BY TEST_JOIN.PROD
 ORDER BY CNT DESC
)
WHERE ROWNUM <= 1;

select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |               |      1 |        |      1 |00:00:00.03 |     747 |       |       |          |
|*  1 |  COUNT STOPKEY             |               |      1 |        |      1 |00:00:00.03 |     747 |       |       |          |
|   2 |   VIEW                     |               |      1 |     75 |      1 |00:00:00.03 |     747 |       |       |          |
|*  3 |    SORT ORDER BY STOPKEY   |               |      1 |     75 |      1 |00:00:00.03 |     747 | 73728 | 73728 |          |
|   4 |     HASH GROUP BY          |               |      1 |     75 |     76 |00:00:00.03 |     747 |   964K|   964K| 2501K (0)|
|*  5 |      FILTER                |               |      1 |        |  44832 |00:00:00.06 |     747 |       |       |          |
|*  6 |       HASH JOIN            |               |      1 |  44262 |  44832 |00:00:00.05 |     747 |  1035K|  1035K| 1286K (0)|
|*  7 |        TABLE ACCESS FULL   | TEST_JOIN     |      1 |     77 |     78 |00:00:00.01 |       3 |       |       |          |
|*  8 |        INDEX FAST FULL SCAN| TEST_AGGR_IDX |      1 |  44852 |  44832 |00:00:00.02 |     744 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=1)
   3 - filter(ROWNUM<=1)
   5 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY'))
   6 - access("TEST_JOIN"."ID2"="TEST_AGGR"."ID2")
   7 - filter("TEST_JOIN"."PROD"<>'TEST_PROD')
   8 - filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY')))

 

ALTER SESSION SET optimizer_index_caching=100;
ALTER SESSION SET optimizer_index_cost_adj=1;
ALTER SESSION SET TRACEFILE_IDENTIFIER='optimizer_test1';

 

SELECT /*+ gather_plan_statistics */ PROD
FROM
(
 SELECT TEST_JOIN.PROD
 , COUNT(*) CNT
 FROM TEST_AGGR
 JOIN TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2
 WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')
 AND PROD  'TEST_PROD'
 GROUP BY TEST_JOIN.PROD
 ORDER BY CNT DESC
)
WHERE ROWNUM <= 1;

select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |               |      1 |        |      1 |00:00:01.16 |   57034 |       |       |          |
|*  1 |  COUNT STOPKEY          |               |      1 |        |      1 |00:00:01.16 |   57034 |       |       |          |
|   2 |   VIEW                  |               |      1 |     75 |      1 |00:00:01.16 |   57034 |       |       |          |
|*  3 |    SORT ORDER BY STOPKEY|               |      1 |     75 |      1 |00:00:01.16 |   57034 | 73728 | 73728 |          |
|   4 |     HASH GROUP BY       |               |      1 |     75 |     76 |00:00:01.16 |   57034 |   964K|   964K| 2506K (0)|
|*  5 |      FILTER             |               |      1 |        |  44832 |00:00:01.17 |   57034 |       |       |          |
|   6 |       NESTED LOOPS      |               |      1 |  44262 |  44832 |00:00:01.16 |   57034 |       |       |          |
|*  7 |        TABLE ACCESS FULL| TEST_JOIN     |      1 |     77 |     78 |00:00:00.01 |       3 |       |       |          |
|*  8 |        INDEX FULL SCAN  | TEST_AGGR_IDX |     78 |    575 |  44832 |00:00:01.14 |   57031 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=1)
   3 - filter(ROWNUM<=1)
   5 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY'))
   7 - filter("TEST_JOIN"."PROD"<>'TEST_PROD')
   8 - access("TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2" AND
              "TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY'))
       filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND
              "TEST_JOIN"."ID2"="TEST_AGGR"."ID2"))




SQL – ROW_NUMBER, MOD, Even Distribution

9 12 2009

December 9, 2009

A question appeared in the comp.databases.oracle.server Usenet group a couple years ago:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/41ee30267e646e10

Say I have a bunch of bowling players of different skill level as indicated by his avg_score in the table below.

I need to allot them into n teams (say 8), of equivalent strength on the TEAM level so no team ends up with mostly high-scorers and vic-versa.
(let’s say players may not be evenly divided into teams because n numbers are “sick”)

Is there a way to do to this ?

10gR2> create table players (id integer primary key, avg_score number, team_no integer);

10gR2> desc players
Name      Type
--------- -------
ID        INTEGER
AVG_SCORE NUMBER
TEAM_NO   INTEGER

 

10gR2> BEGIN
  2    FOR i IN 1..120
  3    LOOP
  4        INSERT INTO players (id, avg_score) VALUES(i,round(dbms_random.value(75,295)));
  5    END LOOP;
  6  END ;
  7  /

Needs work, but may be enough to get you started:

SELECT
  ID,
  AVG_SCORE,
  ROW_NUMBER() OVER (ORDER BY AVG_SCORE) RANKING,
  COUNT(*) OVER (PARTITION BY 1) ROWS_COUNT
FROM
  PLAYERS;

        ID  AVG_SCORE    RANKING ROWS_COUNT
---------- ---------- ---------- ----------
        74         78          1        120
        91         82          2        120
        95         83          3        120
        77         86          4        120
        61         87          5        120
        23         87          6        120
         1         90          7        120
        67         91          8        120
        62         97          9        120
        33         98         10        120
...
        88        271        111        120
        41        272        112        120
       104        274        113        120
        32        275        114        120
        36        275        115        120
        99        276        116        120
        71        277        117        120
        31        285        118        120
         3        286        119        120
       113        288        120        120

If we were to take the people at rank 1 and rank 120, they would have roughly the same average as the people at rank 2 and rank 119, and they would have roughly the same average as the people at rank 3 and 118, etc.  This does not work exactly as planned as the number of people must be evenly divisible by 2 * the number of groups, and this is not the case with 120 people and 8 groups.

We can have Oracle skip from 1 to 9 to 17 to … by using the MOD function, but we must recognize the mid-point so that we can switch the formula.

By sliding the above into an inline view, we can perform the analysis that is required.  I included three additional columns to help determine whether or not the formula is close:

SELECT
  ID,
  AVG_SCORE,
  DECODE(SIGN(RANKING-(ROWS_COUNT/2)),-1,MOD(RANKING-1,8)+1,((8-1)-MOD(RANKING-(8/2),8))+1) TEAM_NO,
  RANKING,
  SUM(AVG_SCORE) OVER (PARTITION BY DECODE(SIGN(RANKING-(ROWS_COUNT/2)),-1,MOD(RANKING-1,8)+1,((8-1)-MOD(RANKING-(8/2),8))+1)) TEAM_AVG,
  COUNT(*) OVER (PARTITION BY DECODE(SIGN(RANKING-(ROWS_COUNT/2)),-1,MOD(RANKING-1,8)+1,((8-1)-MOD(RANKING-(8/2),8))+1)) NUM_TEAM_MEMBERS
FROM
  (SELECT
    ID,
    AVG_SCORE,
    ROW_NUMBER() OVER (ORDER BY AVG_SCORE) RANKING,
    COUNT(*) OVER (PARTITION BY 1) ROWS_COUNT
  FROM
    PLAYERS)
ORDER BY
  RANKING;

        ID  AVG_SCORE    TEAM_NO    RANKING   TEAM_AVG NUM_TEAM_MEMBERS
---------- ---------- ---------- ---------- ---------- ----------------
        74         78          1          1       2603 15
        91         82          2          2       2602 15
        95         83          3          3       2592 15
        77         86          4          4       2709 15
        61         87          5          5       2701 15
        23         87          6          6       2690 15
         1         90          7          7       2686 15
        67         91          8          8       2689 15
        62         97          1          9       2603 15
        33         98          2         10       2602 15
        79         98          3         11       2592 15
       120        100          4         12       2709 15
         2        101          5         13       2701 15
        39        101          6         14       2690 15
        60        102          7         15       2686 15
       101        104          8         16       2689 15
...
        14        257          8        108       2689 15
        59        259          7        109       2686 15
        29        262          6        110       2690 15
        88        271          5        111       2701 15
        41        272          4        112       2709 15
       104        274          3        113       2592 15
        32        275          2        114       2602 15
        36        275          1        115       2603 15
        99        276          8        116       2689 15
        71        277          7        117       2686 15
        31        285          6        118       2690 15
         3        286          5        119       2701 15
       113        288          4        120       2709 15




SQL – ROW_NUMBER Analytical Function, Many to One Join

9 12 2009

December 9, 2009

A question appeared in the comp.databases.oracle.server Usenet group a couple years ago:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/deeb9d899c8e6376

I have a problem filtering my results which involves a many-to-many join. Below is a brief layout of the problem, but briefly, I have a test that can have multiple results and those results may also belong multiple tests (i have done the same test under a different id and I want to inherit the previous test result). I also have an event table that is required to keep track of all changes.

The problem I have is that I cannot filter my results so where a result belongs to 2 tests I don’t want the details where it belongs to the test in the query. If anyone can suggest a way of returning 2 lines instead of 3 I would be very grateful.

SELECT tr.test_acc, e.event_id, re.resultevent_id, re.result_id,
re2.resultevent_id, re2.event_id, tr2.test_acc
FROM TestRequest tr JOIN Event e ON tr.test_acc=e.test_acc
JOIN ResultEvent re ON e.event_id=re.event_id
JOIN ResultEvent re2 ON re.result_id=re2.result_id
JOIN Event e2 ON e2.event_id=re2.event_id
JOIN TestRequest tr2 ON e2.test_acc=tr2.test_acc
WHERE tr.test_acc=3418;

+----------+----------+----------------+-----------+----------------+----------+----------+
| test_acc | event_id | resultevent_id | result_id | resultevent_id | event_id | test_acc |
+----------+----------+----------------+-----------+----------------+----------+----------+
|     3418 |    42178 |           6345 |      6321 |           6345 |    42178 |     3418 |
|     3418 |    42179 |           6346 |      4126 |           4126 |    28004 |     2248 |
|     3418 |    42179 |           6346 |      4126 |           6346 |    42179 |     3418 |
+----------+----------+----------------+-----------+----------------+----------+----------+
 
3 rows in set (0.00 sec)

 

+-------------+
| TestRequest |
+-------------+
| test_acc    |
+-------------+
     1 |
       |
     * |
+-------------+
| Event       |
+-------------+
| test_acc    |
| event_id    |
+-------------+
     1 |
       |
     * |
+----------------+
| ResultEvent    |
+----------------+
| resultevent_id |
| event_id       |
| result_id      |
+----------------+

I think that I understand what you are trying to do.  I changed the table names slightly (added _) in the mock up:
First, the table creation:

CREATE TABLE TEST_REQUEST (
  TEST_ACC NUMBER(10));

CREATE TABLE EVENT (
  TEST_ACC NUMBER(10),
  EVENT_ID NUMBER(10));

CREATE TABLE RESULT_EVENT (
  RESULTEVENT_ID NUMBER(10),
  EVENT_ID NUMBER(10),
  RESULT_ID NUMBER(10));

As best I can tell, the data that is in the tables:

INSERT INTO TEST_REQUEST VALUES (3418);
INSERT INTO TEST_REQUEST VALUES (2248);

INSERT INTO EVENT VALUES (3418,42178);
INSERT INTO EVENT VALUES (3418,42179);
INSERT INTO EVENT VALUES (2248,28004);

INSERT INTO RESULT_EVENT VALUES (6345,42178,6321);
INSERT INTO RESULT_EVENT VALUES (4126,28004,4126);
INSERT INTO RESULT_EVENT VALUES (6346,42179,4126);

COMMIT;

I reformatted your query so that I could more easily see what is happening:

SELECT
  TR.TEST_ACC,
  E.EVENT_ID,
  RE.RESULTEVENT_ID,
  RE.RESULT_ID,
  RE2.RESULTEVENT_ID,
  RE2.EVENT_ID,
  TR2.TEST_ACC
FROM
  TEST_REQUEST TR,
  EVENT E,
  RESULT_EVENT RE,
  RESULT_EVENT RE2,
  EVENT E2,
  TEST_REQUEST TR2
WHERE
  TR.TEST_ACC=3418
  AND TR.TEST_ACC=E.TEST_ACC
  AND E.EVENT_ID=RE.EVENT_ID
  AND RE.RESULT_ID=RE2.RESULT_ID
  AND E2.EVENT_ID=RE2.EVENT_ID
  AND E2.TEST_ACC=TR2.TEST_ACC;

  TEST_ACC   EVENT_ID RESULTEVENT_ID  RESULT_ID RESULTEVENT_ID   EVENT_ID  TEST_ACC
---------- ---------- -------------- ---------- -------------- ---------- ----------
      3418      42179           6346       4126           4126      28004      2248
      3418      42179           6346       4126           6346      42179      3418
      3418      42178           6345       6321           6345      42178      3418

It appears that if you have 2 rows with the same RESULT_ID, you only want the first EVENT_ID, so you need to have some way of numbering the rows.  The ROW_NUMBER analytical function might be able to help:

SELECT
  TR.TEST_ACC,
  E.EVENT_ID,
  RE.RESULTEVENT_ID,
  RE.RESULT_ID,
  RE2.RESULTEVENT_ID,
  RE2.EVENT_ID,
  TR2.TEST_ACC,
  ROW_NUMBER() OVER (PARTITION BY RE.RESULT_ID ORDER BY RE2.EVENT_ID) RN
FROM
  TEST_REQUEST TR,
  EVENT E,
  RESULT_EVENT RE,
  RESULT_EVENT RE2,
  EVENT E2,
  TEST_REQUEST TR2
WHERE
  TR.TEST_ACC=3418
  AND TR.TEST_ACC=E.TEST_ACC
  AND E.EVENT_ID=RE.EVENT_ID
  AND RE.RESULT_ID=RE2.RESULT_ID
  AND E2.EVENT_ID=RE2.EVENT_ID
  AND E2.TEST_ACC=TR2.TEST_ACC;

  TEST_ACC   EVENT_ID RESULTEVENT_ID  RESULT_ID RESULTEVENT_ID   EVENT_ID TEST_ACC   RN
---------- ---------- -------------- ---------- -------------- ---------- ---------- --
      3418      42179           6346       4126           4126      28004      2248   1
      3418      42179           6346       4126           6346      42179      3418   2
      3418      42178           6345       6321           6345      42178      3418   1

Now, if we can filter out any of the rows that do not have RN=1, we may have a usable solution.  By sliding the above into an inline view (and adding column aliases as necessary in the inline view), we can add a WHERE clause to return only those rows with RN=1:

SELECT
  TEST_ACC,
  EVENT_ID,
  RESULTEVENT_ID,
  RESULT_ID,
  RESULTEVENT_ID2,
  EVENT_ID2,
  TEST_ACC2
FROM
  (SELECT
    TR.TEST_ACC,
    E.EVENT_ID,
    RE.RESULTEVENT_ID,
    RE.RESULT_ID,
    RE2.RESULTEVENT_ID RESULTEVENT_ID2,
    RE2.EVENT_ID EVENT_ID2,
    TR2.TEST_ACC TEST_ACC2,
    ROW_NUMBER() OVER (PARTITION BY RE.RESULT_ID ORDER BY RE2.EVENT_ID) RN
  FROM
    TEST_REQUEST TR,
    EVENT E,
    RESULT_EVENT RE,
    RESULT_EVENT RE2,
    EVENT E2,
    TEST_REQUEST TR2
  WHERE
    TR.TEST_ACC=3418
    AND TR.TEST_ACC=E.TEST_ACC
    AND E.EVENT_ID=RE.EVENT_ID
    AND RE.RESULT_ID=RE2.RESULT_ID
    AND E2.EVENT_ID=RE2.EVENT_ID
    AND E2.TEST_ACC=TR2.TEST_ACC)
WHERE
  RN=1;

  TEST_ACC   EVENT_ID RESULTEVENT_ID  RESULT_ID RESULTEVENT_ID2  EVENT_ID2 TEST_ACC2
---------- ---------- -------------- ---------- --------------- ---------- ----------
      3418      42179           6346       4126            4126      28004      2248
      3418      42178           6345       6321            6345      42178      3418




SQL – ROW_NUMBER – Retrieving Rows from the Midpoint

9 12 2009

December 9, 2009

A question appeared in the comp.databases.oracle.server Usenet group a couple of years ago:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/a8314487f8c414ac

I have a question about rownum which is not mentioned.

Lets say that I want to extract a 3 records out of a result where the middle one is the query.
For example, I have records:

ID      Value 
--      ------
1       Hello 
2       Hi 
3       Wow 
4       Shrek 
5       Shus 
6       What?

And I need a query that by receiving ID, it will extract the record with the id and the previous one to that and the next one. For example for selecting ID=4, it will return records: 3,4,5.

Is there a way to do it in Oracle?

But the ids are not seqeuntial – it can verify from each record, for example:

ID      Value 
225     Hello 
228     Hi 
258     Wow 
240     Shrek 
259     Shus 
230     What?

 I offered the following:

Note: LAG and LEAD will collapse the result into a single row.  If that is not desired, you will need a different approach:
The set up:

CREATE TABLE T1 ( 
  C1 NUMBER(12), 
  C2 VARCHAR2(20)); 
 
INSERT INTO T1 VALUES (1,'Hello'); 
INSERT INTO T1 VALUES (2,'Hi'); 
INSERT INTO T1 VALUES (3,'Wow'); 
INSERT INTO T1 VALUES (4,'Shrek'); 
INSERT INTO T1 VALUES (5,'Shus'); 
INSERT INTO T1 VALUES (6,'What?');

A simple query using the ROW_NUMBER analytical function:

SELECT 
  C1, 
  C2, 
  ROW_NUMBER() OVER (ORDER BY C1) RN 
FROM 
  T1; 
 
        C1 C2                           RN 
---------- -------------------- ---------- 
         1 Hello                         1 
         2 Hi                            2 
         3 Wow                           3 
         4 Shrek                         4 
         5 Shus                          5 
         6 What?                         6

Sliding the above into an inline view to retrieve only those on either side of RN=4:

SELECT 
  T.C1, 
  T.C2 
FROM 
  (SELECT 
    C1, 
    C2, 
    ROW_NUMBER() OVER (ORDER BY C1) RN 
  FROM 
    T1) T 
WHERE 
  T.RN BETWEEN (4 -1) AND (4 +1);
        C1 C2 
---------- ------ 
         3 Wow 
         4 Shrek 
         5 Shus

But, the above is not exactly what you need, unless C1 (your ID column) always starts at 1 and incements by 1.  Essentially listing the inline view twice with a join fixes the problem:

SELECT 
  T.C1, 
  T.C2 
FROM 
  (SELECT 
    C1, 
    C2, 
    ROW_NUMBER() OVER (ORDER BY C1) RN 
  FROM 
    T1) T, 
  (SELECT 
    C1, 
    ROW_NUMBER() OVER (ORDER BY C1) RN 
  FROM 
    T1) T2 
WHERE 
  T2.C1=4 
  AND T.RN BETWEEN (T2.RN -1) AND (T2.RN +1); 
 
        C1 C2 
---------- ----- 
         5 Shus 
         4 Shrek 
         3 Wow

A quick test:

DELETE FROM 
  T1 
WHERE 
  C1=3;

The first method results in:

        C1 C2 
---------- ----- 
         4 Shrek 
         5 Shus 
         6 What?

The second method results in:

        C1 C2 
---------- ----- 
         5 Shus 
         4 Shrek 
         2 Hi

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

A couple other people in the thread offered suggestions, including this one by Martin T.:
Maybe Analytic Functions can help:

select * from ( 
SELECT some_table.*,
 lead(ID) over (order by date) as LEAD_ID,
 lag(id) over (order by date) as LAG_ID 
from some_table 
) some_table_plus 
where ID      = :search_id 
    or LEAD_ID = :search_id 
    or LAG_ID  = :search_id 
order by date




Failure to Collect Fixed Object Statistics Leads to ORA-01013 or ORA-07445

9 12 2009

December 8, 2009 (note added December 9, 2009)

An interesting post from almost two years ago in the comp.databases.oracle.server Usenet group:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/e0599d3e043fc199

select distinct name from all_source;

.......
SYS_YOID0000052452$
......
3196 rows selected.

Ok no problem.

select distinct owner from v$access
SQL> /
select distinct owner from v$access
                           *
ERROR at line 1:
ORA-01013: user requested cancel of current operation

I had to terminate it as it become non-terminating.

“select owner from v$access” returns a mere 193 rows, and it cannot sort it?

I suspect there is exists an infinite loop somewhere.

This was attempted on 10gR2, 11gR1 and both had the same problem.

How would someone start investigating this problem?  Find something that appears out of the ordinary, and start probing.  This is the approach that I used:

-

I was able to reproduce this problem on Oracle 10.2.0.2 with the Oracle October 2006 CPU on 64 bit Windows 2003.

From the udump trace file:

ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION]
[__intel_new_memcpy+610] [PC:0x3236532] [ADDR:0x0] [UNABLE_TO_READ] []
Current SQL statement for this session:
select distinct owner from v$access
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
__intel_new_memcpy+           0000000000000000     000000000 000000000
0118AF5A0 610                                                7FF970C7598
000007FF95D155F0     CALL???  __intel_new_memcpy+  0000007FF 013DF42E8
001749686
                              610                  000000000
...

From a 10046 trace at level 8:

*** 2008-01-23 06:51:57.901
WAIT #3: nam='latch: library cache' ela= 8566 address=1398554576 number=214 tries=1 obj#=-1 tim=5204643696
WAIT #3: nam='latch: library cache' ela= 859 address=1398554576 number=214 tries=1 obj#=-1 tim=5205047765
WAIT #3: nam='latch: library cache' ela= 2958 address=1398554576 number=214 tries=1 obj#=-1 tim=5205183748
WAIT #3: nam='latch: library cache' ela= 551 address=1398554576 number=214 tries=1 obj#=-1 tim=5205267198
*** 2008-01-23 06:54:39.117
WAIT #3: nam='latch: library cache' ela= 813 address=1398554576 number=214 tries=1 obj#=-1 tim=5365848854
*** 2008-01-23 06:55:06.288
WAIT #3: nam='latch: library cache' ela= 30 address=1398554576 number=214 tries=1 obj#=-1 tim=5393019180
*** 2008-01-23 06:55:30.006
WAIT #3: nam='latch: library cache' ela= 68 address=1398554576 number=214 tries=1 obj#=-1 tim=5416746379
*** 2008-01-23 06:55:50.584
WAIT #3: nam='latch: library cache' ela= 33 address=1398554576 number=214 tries=1 obj#=-1 tim=5437323921
*** 2008-01-23 06:57:09.536
WAIT #3: nam='latch: library cache' ela= 111 address=1398554576 number=214 tries=1 obj#=-1 tim=5516279642
*** 2008-01-23 06:57:20.895
WAIT #3: nam='latch: library cache' ela= 77 address=1398554576 number=214 tries=1 obj#=-1 tim=5527627340
*** 2008-01-23 06:57:36.082
WAIT #3: nam='latch: library cache' ela= 246 address=1398554576 number=214 tries=1 obj#=-1 tim=5542815685
*** 2008-01-23 06:57:57.957
WAIT #3: nam='latch: library cache' ela= 123 address=1398554576 number=214 tries=1 obj#=-1 tim=5564704225
*** 2008-01-23 06:58:14.644
WAIT #3: nam='latch: library cache' ela= 63 address=1398554576 number=214 tries=1 obj#=-1 tim=5581385020
*** 2008-01-23 06:58:26.269
WAIT #3: nam='latch: library cache' ela= 62 address=1398554576 number=214 tries=1 obj#=-1 tim=5593004724
*** 2008-01-23 06:58:48.346
WAIT #3: nam='latch: library cache' ela= 42 address=1398554576 number=214 tries=1 obj#=-1 tim=5615094241
WAIT #3: nam='latch: library cache' ela= 8 address=1398554576 number=214 tries=1 obj#=-1 tim=5615168161
...

For an average 60 second interval interval, the session had the following latch statistics:

Latch         Child# Level   Gets  Misses  Sleeps  Sleeps1
LIBRARY CACHE    1       5   529418     25      0       25
LIBRARY CACHE    2       5   539720     36      0       36
LIBRARY CACHE    3       5   519189     15      0       15
LIBRARY CACHE    4       5   516501     55      0       55
LIBRARY CACHE    5       5   524907   1744      4     1740

On the waits, P1 is 13985, P2 is 214, P3 is 0, Buf is 99, SQL Hash is 3250939240.

SELECT
  SQL_TEXT
FROM
  V$SQL
WHERE
  HASH_VALUE=3250939240

SQL_TEXT
-----------------------------------
select distinct owner from v$access

 

SELECT
  ID,
  SUBSTR(OPERATION,1,12) OPERATION,
  SUBSTR(OPTIONS,1,12) OPTIONS,
  SUBSTR(OBJECT_OWNER||'.'||OBJECT_NAME,1,20) OBJECT,
  SUBSTR(OBJECT_TYPE,1,13) OBJECT_TYPE,
  PARENT_ID,
  DEPTH,
  POSITION,
  CPU_COST
FROM
  V$SQL_PLAN_STATISTICS_ALL
WHERE
  HASH_VALUE=3250939240
  AND CHILD_NUMBER=1
ORDER BY
  ID;

ID OPERATION    OPTIONS      OBJECT               OBJECT_TYPE PARENT_ID      DEPTH   POSITION   CPU_COST
-- ------------ ------------ -------------------- ------------- ---------- ---------- ---------- ----------
 1 HASH UNIQUE       .                                                  0          1          1    2142850
 2 NESTED LOOPS              .                                          1          2          1    1115000
 3 NESTED LOOPS              .                                          2          3          1    1080000
 4 MERGE JOIN CARTESIAN    .                                            3          4          1     730000
 5 FIXED TABLE  FULL         SYS.X$KSUSE          TABLE (FIXED)         4          5          1     380000
 6 BUFFER SORT         .                                                4          5          2     350000
 7 FIXED TABLE  FULL         SYS.X$KGLDP          TABLE (FIXED)         6          6          1     350000
 8 FIXED TABLE  FIXED INDEX  SYS.X$KGLLK (ind:1)  TABLE (FIXED)         3          4          2       3500
 9 FIXED TABLE  FIXED INDEX  SYS.X$KGLOB (ind:1)  TABLE (FIXED)         2          3          2       3500

 

SELECT
  ID,
  ACCESS_PREDICATES,
  FILTER_PREDICATES
FROM
  V$SQL_PLAN_STATISTICS_ALL
WHERE
  HASH_VALUE=3250939240
  AND CHILD_NUMBER=1
ORDER BY
  ID;

ID ACCESS_PREDICATES FILTER_PREDICATES
-- ----------------- -----------------
 1
 2
 3
 4
 5                   "S"."INST_ID"=USERENV('INSTANCE')
 6
 7
 8                   ("L"."KGLLKUSE"="S"."ADDR" AND "L"."KGLLKHDL"="D"."KGLHDADR" AND "L"."KGLNAHSH"="D"."KGLNAHSH")
 9                   ("O"."KGLNAHSH"="D"."KGLRFHSH" AND "O"."KGLHDADR"="D"."KGLRFHDL")


Then check a different server:

The query eventually completed on the 32 bit version of Oracle 10.2.0.2 with the Oracle October 2006 CPU.

PARSE 1|CPU S     0.000000|CLOCK S    0.006227|ROWs 0
EXEC  1|CPU S     0.000000|CLOCK S    0.000201|ROWs 0
FETCH 2|CPU S 13112.828125|CLOCK S  926.981803|ROWs 6

Row Source Execution Plan:
       (Rows 6)   HASH UNIQUE (cr=0 pr=0 pw=0 time=568347223 us)
    (Rows 3463)    NESTED LOOPS  (cr=0 pr=0 pw=0 time=3464796755 us)
    (Rows 3463)     NESTED LOOPS  (cr=0 pr=0 pw=0 time=3464592419 us)
 (Rows 1613768)      MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=21019488 us)
     (Rows 236)       FIXED TABLE FULL X$KSUSE (cr=0 pr=0 pw=0 time=2376 us)
 (Rows 1613768)       BUFFER SORT (cr=0 pr=0 pw=0 time=12951356 us)
    (Rows 6838)        FIXED TABLE FULL X$KGLDP (cr=0 pr=0 pw=0 time=41073 us)
    (Rows 3463)      FIXED TABLE FIXED INDEX X$KGLLK (ind:1) (cr=0 pr=0 pw=0 time=13094082350 us)
    (Rows 3463)     FIXED TABLE FIXED INDEX X$KGLOB (ind:1) (cr=0 pr=0 pw=0 time=166548 us)

Note the merge Cartesian join between the 236 rows in X$KSUSE and the 1613768 rows from X$KGLDP.

The wait events:
0.03 seconds on latch: library cache

Then keep probing:

I may have found something that may help the OP – it hit me when I found very slow performance with the same SQL statement on 32 bit Oracle 10.2.0.3 and 11.1.0.6, after looking at the DBMS_XPLANs.

The DBMS_XPLAN on 10.2.0.3:

--------------------------------------------------------------------------­----------------------------------------------
| Id  | Operation                  | Name            | Starts | E-Rows | A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------­----------------------------------------------
|   1 |  HASH UNIQUE               |                 |      1 |    105 |      5 |00:02:51.06 |       |       |          |
|   2 |   NESTED LOOPS             |                 |      1 |    105 |   1131 |00:02:51.04 |       |       |          |
|   3 |    NESTED LOOPS            |                 |      1 |     10 |   1131 |00:02:50.39 |       |       |          |
|   4 |     MERGE JOIN CARTESIAN   |                 |      1 |    100 |    180K|00:00:01.27 |       |       |          |
|*  5 |      FIXED TABLE FULL      | X$KSUSE         |      1 |      1 |    236 |00:00:00.01 |       |       |          |
|   6 |      BUFFER SORT           |                 |    236 |    100 |    180K|00:00:00.55 | 36864 | 36864 |32768  (0)|
|   7 |       FIXED TABLE FULL     | X$KGLDP         |      1 |    100 |    763 |00:00:00.01 |       |       |          |
|*  8 |     FIXED TABLE FIXED INDEX| X$KGLLK (ind:1) |    180K|      1 |   1131 |00:02:48.31 |       |       |          |
|*  9 |    FIXED TABLE FIXED INDEX | X$KGLOB (ind:1) |   1131 |     10 |   1131 |00:00:00.64 |       |       |          |
--------------------------------------------------------------------------­----------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter("S"."INST_ID"=USERENV('INSTANCE'))
   8 - filter(("L"."KGLLKUSE"="S"."ADDR" AND "L"."KGLLKHDL"="D"."KGLHDADR" AND "L"."KGLNAHSH"="D"."KGLNAHSH"))
   9 - filter(("O"."KGLNAHSH"="D"."KGLRFHSH" AND "O"."KGLHDADR"="D"."KGLRFHDL"))

 
Note the MERGE JOIN CARTESIAN, and how the estimated rows compares with the actual rows.

The DBMS_XPLAN on 11.1.0.6:

select distinct owner from v$access 

--------------------------------------------------------------------------­-----------------------------------------------
| Id  | Operation                   | Name            | Starts | E-Rows | A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------­-----------------------------------------------
|   1 |  HASH UNIQUE                |                 |      1 |      1 |      6 |00:00:40.28 |   951K|   951K|  860K (0)|
|   2 |   NESTED LOOPS              |                 |      1 |      1 |   2342 |00:00:40.27 |       |       |          |
|   3 |    MERGE JOIN CARTESIAN     |                 |      1 |      1 |   2842K|00:00:11.37 |       |       |          |
|   4 |     NESTED LOOPS            |                 |      1 |      1 |  16721 |00:00:00.38 |       |       |          |
|   5 |      FIXED TABLE FULL       | X$KGLDP         |      1 |    100 |  16721 |00:00:00.05 |       |       |          |
|*  6 |      FIXED TABLE FIXED INDEX| X$KGLOB (ind:1) |  16721 |      1 |  16721 |00:00:00.21 |       |       |          |
|   7 |     BUFFER SORT             |                 |  16721 |      1 |   2842K|00:00:02.91 |  4096 |  4096 | 4096  (0)|
|*  8 |      FIXED TABLE FULL       | X$KSUSE         |      1 |      1 |    170 |00:00:00.01 |       |       |          |
|*  9 |    FIXED TABLE FIXED INDEX  | X$KGLLK (ind:1) |   2842K|      1 |   2342 |00:00:15.49 |       |       |          |
--------------------------------------------------------------------------­-----------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - filter(("O"."KGLNAHSH"="D"."KGLRFHSH" AND "O"."KGLHDADR"="D"."KGLRFHDL"))
   8 - filter("S"."INST_ID"=USERENV('INSTANCE'))
   9 - filter(("L"."KGLLKUSE"="S"."ADDR" AND "L"."KGLLKHDL"="D"."KGLHDADR" AND "L"."KGLNAHSH"="D"."KGLNAHSH"))

The above executed more quickly, and the plan is slightly different, but the MERGE JOIN CARTESIAN is still present, as is the difference between the estimated and actual number of rows.

The fixed object stats must be wrong (I recall having a problem with that a couple years ago when perfoming the following)…

SQL> CONNECT / AS SYSDBA
SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS('ALL');
BEGIN DBMS_STATS.GATHER_FIXED_OBJECTS_STATS('ALL'); END;

*
ERROR at line 1:
ORA-20000: Insufficient privileges to analyze an object in Fixed Ob
ORA-06512: at "SYS.DBMS_STATS", line 17951
ORA-06512: at "SYS.DBMS_STATS", line 18404
ORA-06512: at "SYS.DBMS_STATS", line 18951
ORA-06512: at line 1

The same error occurs on Oracle 10.2.0.2, 10.2.0.3, and 11.1.0.6 as the internal user, SYS AS SYSDBA, and SYSTEM.

There must be another way:

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS',GATHER_FIXED=>TRUE)

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

The new DBMS_XPLANs:
10.2.0.3:

--------------------------------------------------------------------------­---------------------------------------------
| Id  | Operation                 | Name            | Starts | E-Rows | A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------­---------------------------------------------
|   1 |  HASH UNIQUE              |                 |      1 |      7 |      4 |00:00:00.09 |       |       |          |
|   2 |   NESTED LOOPS            |                 |      1 |   1822 |   1003 |00:00:00.08 |       |       |          |
|*  3 |    HASH JOIN              |                 |      1 |   1822 |   1003 |00:00:00.05 |   898K|   898K| 1099K (0)|
|*  4 |     HASH JOIN             |                 |      1 |   1822 |   1897 |00:00:00.03 |  1010K|  1010K|  639K (0)|
|*  5 |      FIXED TABLE FULL     | X$KSUSE         |      1 |    236 |    236 |00:00:00.01 |       |       |          |
|   6 |      FIXED TABLE FULL     | X$KGLLK         |      1 |   1822 |   1897 |00:00:00.01 |       |       |          |
|   7 |     FIXED TABLE FULL      | X$KGLDP         |      1 |   2892 |    649 |00:00:00.01 |       |       |          |
|*  8 |    FIXED TABLE FIXED INDEX| X$KGLOB (ind:1) |   1003 |      1 |   1003 |00:00:00.01 |       |       |          |
--------------------------------------------------------------------------­---------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("L"."KGLLKHDL"="D"."KGLHDADR" AND "L"."KGLNAHSH"="D"."KGLNAHSH")
   4 - access("L"."KGLLKUSE"="S"."ADDR")
   5 - filter("S"."INST_ID"=USERENV('INSTANCE'))
   8 - filter(("O"."KGLNAHSH"="D"."KGLRFHSH" AND "O"."KGLHDADR"="D"."KGLRFHDL"))

The MERGE JOIN CARTESIAN is gone and the execution time dropped from 2 minutes, 51 seconds to 0.09 seconds.

11.1.0.6:

--------------------------------------------------------------------------­----------------------------------------------
| Id  | Operation                  | Name            | Starts | E-Rows | A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------­----------------------------------------------
|   1 |  HASH UNIQUE               |                 |      1 |     19 |      1 |00:00:00.04 |  1037K|  1037K|  368K (0)|
|   2 |   NESTED LOOPS             |                 |      1 |   1139 |    134 |00:00:00.04 |       |       |          |
|   3 |    NESTED LOOPS            |                 |      1 |   1139 |    134 |00:00:00.03 |       |       |          |
|*  4 |     HASH JOIN              |                 |      1 |   1139 |   1144 |00:00:00.02 |  1010K|  1010K| 1205K (0)|
|*  5 |      FIXED TABLE FULL      | X$KSUSE         |      1 |    170 |    170 |00:00:00.01 |       |       |          |
|   6 |      FIXED TABLE FULL      | X$KGLLK         |      1 |   1139 |   1144 |00:00:00.01 |       |       |          |
|*  7 |     FIXED TABLE FIXED INDEX| X$KGLDP (ind:1) |   1144 |      1 |    134 |00:00:00.01 |       |       |          |
|*  8 |    FIXED TABLE FIXED INDEX | X$KGLOB (ind:1) |    134 |      1 |    134 |00:00:00.01 |       |       |          |
--------------------------------------------------------------------------­----------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("L"."KGLLKUSE"="S"."ADDR")
   5 - filter("S"."INST_ID"=USERENV('INSTANCE'))
   7 - filter(("L"."KGLLKHDL"="D"."KGLHDADR" AND "L"."KGLNAHSH"="D"."KGLNAHSH"))
   8 - filter(("O"."KGLNAHSH"="D"."KGLRFHSH" AND "O"."KGLHDADR"="D"."KGLRFHDL"))

The MERGE JOIN CARTESIAN is gone and the execution time dropped from 40.28 seconds to 0.04 seconds.

The OP may be able to run the same DBMS_STATS.GATHER_SCHEMA_STATS procedure to work around the problem.

How to determine if I collected statistics on the fixed tables?  A search on Metalink found this article from 2004:
https://metalink.oracle.com/metalink/plsql/f%3Fp%3D200:27:5000154048035945504::::p27_id,p27_show_header,p27_show_help:525959.996,1,1&usg=AFQjCNHJ_wI9tlazsGQ7AIUZ5RSlrp_8nw
(Edit: Note that the above link is gone in a Flash  If anyone is able to find the message on the Oracle support site -the site  formerly known as Metalink, please let me know of the address.)

In the article, Jonathan Lewis mentioned that tab_stats$ could be checked.
Test database on 64 bit Windows:

SELECT
  *
FROM
  SYS.TAB_STATS$

The above returned about 582 rows after running:

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS',GATHER_FIXED=>TRUE);

I then checked a production database, and found no rows returned. Using the suggestion from the Metalink article:

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(NULL);

The stats collection completed much more quickly, as it did not analyze the full SYS schema, and there were 582 rows returned by the query on SYS.TAB_STATS$.  It looks like this simple query may be used to determine if fixed object statistics need to be collected.





SQL – Filling in Gaps in the Source Data 2

8 12 2009

December 8, 2009

The following question recently appeared in the comp.databases.oracle.server group:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/d9c7420e0e36231d

Could anyone show me how to write a analytical query for my requirement.

Query:

SELECT distinct bug_when,
       login_name,
       SUM(incoming_count - outgoing_count)
        OVER (PARTITION BY login_name ORDER BY bug_when) AS OPEN
FROM RM_COMP_INCOM_OUTGO_BUGIDS_GT G,
     bz_components c,
     bz_profiles p
where  G.component_id= c.ID and
c.manager = p.userid
order by 2,1 desc;

Query returns data like this:

12/04/2009 ssmit 52
12/02/2009 ssmit 48
11/30/2009 ssmit 45
11/29/2009 ssmit 42

I want the data to be like this:

12/04/2009 ssmit 52
12/03/2009 ssmit 48 -- fill the gap with previous value
12/02/2009 ssmit 48
12/01/2009 ssmit 45
11/30/2009 ssmit 45
11/30/2009 ssmit 45
11/29/2009 ssmit 42

Table Desc:

CREATE GLOBAL TEMPORARY TABLE RM_COMP_INCOM_OUTGO_BUGIDS_GT
(
  BUG_WHEN        DATE,
  COMPONENT_ID    NUMBER,
  INCOMING_COUNT  NUMBER,
  OUTGOING_COUNT  NUMBER
);

Set up a test table for experimentation (note, I changed the value for test from 42 to 12 for extra clarity and added additional rows):

CREATE TABLE T1
(
  BUG_WHEN        DATE,
  COMPONENT_ID    VARCHAR2(10),
  INCOMING_COUNT  NUMBER,
  OUTGOING_COUNT  NUMBER
);

INSERT INTO T1 VALUES (TO_DATE('12/07/2009','MM/DD/YYYY'),'ssmit',60,60);
INSERT INTO T1 VALUES (TO_DATE('12/04/2009','MM/DD/YYYY'),'ssmit',52,52);
INSERT INTO T1 VALUES (TO_DATE('12/02/2009','MM/DD/YYYY'),'ssmit',48,48);
INSERT INTO T1 VALUES (TO_DATE('11/30/2009','MM/DD/YYYY'),'ssmit',45,45);
INSERT INTO T1 VALUES (TO_DATE('11/29/2009','MM/DD/YYYY'),'ssmit',42,42);
INSERT INTO T1 VALUES (TO_DATE('01/29/2009','MM/DD/YYYY'),'test',12,12);

COMMIT;

SELECT
  *
FROM
  T1;

BUG_WHEN  COMPONENT_ INCOMING_COUNT OUTGOING_COUNT
--------- ---------- -------------- --------------
07-DEC-09 ssmit                  60             60
04-DEC-09 ssmit                  52             52
02-DEC-09 ssmit                  48             48
30-NOV-09 ssmit                  45             45
29-NOV-09 ssmit                  42             42
29-JAN-09 test                   12             12

Let’s start with using the LEAD function to peek at the next row from the T1 table.

SELECT
  COMPONENT_ID,
  BUG_WHEN,
  LEAD(BUG_WHEN,1) OVER (PARTITION BY COMPONENT_ID ORDER BY BUG_WHEN) NEXT_BUG_WHEN,
  INCOMING_COUNT
FROM
  T1;

COMPONENT_ BUG_WHEN  NEXT_BUG_ INCOMING_COUNT
---------- --------- --------- --------------
ssmit      29-NOV-09 30-NOV-09             42
ssmit      30-NOV-09 02-DEC-09             45
ssmit      02-DEC-09 04-DEC-09             48
ssmit      04-DEC-09 07-DEC-09             52
ssmit      07-DEC-09                       60
test       29-JAN-09                       12

Above needs to be modified to permit ranges by subtracting 1 from the next date.  Also we need to fix the NULL values on the last row of each COMPONENT_ID grouping.

SELECT
  COMPONENT_ID,
  BUG_WHEN,
  NVL(LEAD(BUG_WHEN,1) OVER (PARTITION BY COMPONENT_ID ORDER BY BUG_WHEN)-1,BUG_WHEN) THROUGH_BUG_WHEN,
  NVL(LEAD(BUG_WHEN,1) OVER (PARTITION BY COMPONENT_ID ORDER BY BUG_WHEN)-1,BUG_WHEN)-BUG_WHEN RANGE_DAYS,
  INCOMING_COUNT
FROM
  T1;

COMPONENT_ BUG_WHEN  THROUGH_B RANGE_DAYS INCOMING_COUNT
---------- --------- --------- ---------- --------------
ssmit      29-NOV-09 29-NOV-09          0             42
ssmit      30-NOV-09 01-DEC-09          1             45
ssmit      02-DEC-09 03-DEC-09          1             48
ssmit      04-DEC-09 06-DEC-09          2             52
ssmit      07-DEC-09 07-DEC-09          0             60
test       29-JAN-09 29-JAN-09          0             12

Now, all we need to do is find a way to generate the missing dates between the BUG_WHEN date and the THROUGH_BUG_WHEN date.  If we slide the above into an inline view and join to an inline view with a counter generator, we end up with the following:

SELECT
  T1.COMPONENT_ID,
  T1.BUG_WHEN+V1.C BUG_WHEN,
  T1.INCOMING_COUNT
FROM
  (SELECT
    COMPONENT_ID,
    BUG_WHEN,
    NVL(LEAD(BUG_WHEN,1) OVER (PARTITION BY COMPONENT_ID ORDER BY BUG_WHEN)-1,BUG_WHEN) THROUGH_BUG_WHEN,
    NVL(LEAD(BUG_WHEN,1) OVER (PARTITION BY COMPONENT_ID ORDER BY BUG_WHEN)-1,BUG_WHEN)-BUG_WHEN RANGE_DAYS,
    INCOMING_COUNT
  FROM
    T1) T1,
  (SELECT
    ROWNUM-1 C
  FROM
    DUAL
  CONNECT BY
    LEVEL<=100) V1
WHERE
  V1.C<=T1.RANGE_DAYS
ORDER BY
  T1.COMPONENT_ID,
  T1.BUG_WHEN+V1.C DESC;

COMPONENT_ BUG_WHEN  INCOMING_COUNT
---------- --------- --------------
ssmit      07-DEC-09             60
ssmit      06-DEC-09             52
ssmit      05-DEC-09             52
ssmit      04-DEC-09             52
ssmit      03-DEC-09             48
ssmit      02-DEC-09             48
ssmit      01-DEC-09             45
ssmit      30-NOV-09             45
ssmit      29-NOV-09             42
test       29-JAN-09             12




SQL – Outer Joins, Inline Views, and DENSE_RANK

8 12 2009

December 7, 2009

Some time ago the following question appeared in the comp.databases.oracle.server Usenet group:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/a2112d7bcae62df5

 Learning SQL – will appreciate any help.
 Here is the case:

Two tables with 0..n relationship

TableA                  TableB
-----------             --------------
ColA                    ColB
                        ColA_FK
                        Col_C

Data

TableA.ColA
====================
 1
 2

Table B

ColB   ColA_FK    Col_C
 11      2              12345
 12      2              99999

 Resultset:

 --------------------------------------
 ColA   ColB      Col_C
 --------------------------------------
 1       -        -
 2       12       99999

In case no data exists in TableB for ColA=11,  1 from TableA shows up in result without any data from TableB
 However, in case of 2 from ColA,  we want to record from TableB with Col_C = 99999

I can do the outer join – however, how do I limit it so it picks only the row with 9999?

Here is the test case:

create table tableA (colA number not null);

create table tableB (colB number not null, colA_fk number_not null, colC number not null); 

insert into tableA values (1);
insert into tableA values (2); 

commit;

insert into tableB values (11,2,12345);
insert into tableB values (12,2,99999); 

commit;

 

select tableA.colA, tableB.colA_fk, tableB.colC from tableA left join
tableB on tableA.colA = tableB.colA_fk where tableB.colC = 99999;

--------------------------------------
  ColA   ColA_FK      ColC
  --------------------------------------
  2       12       99999

 I would also like to display another row:

 1       -    -

 in there.

Using the suggestion offered by Pat, with a small modification:

SELECT
  TABLEA.COLA,
  TABLEB.COLA_FK,
  TABLEB.COLC
FROM
  TABLEA
LEFT JOIN
  TABLEB
ON
  TABLEA.COLA=TABLEB.COLA_FK
  AND TABLEB.COLC=99999;

      COLA    COLA_FK       COLC
---------- ---------- ----------
         2          2      99999
         1

This is the way I would commonly set up a SQL statement to meet a similar requirements:

SELECT
  TABLEA.COLA,
  TABLEB.COLA_FK,
  TABLEB.COLC
FROM
  TABLEA,
  TABLEB
WHERE
  TABLEA.COLA=TABLEB.COLA_FK(+)
  AND TABLEB.COLC(+)=99999;

      COLA    COLA_FK       COLC
---------- ---------- ----------
         1
         2          2      99999

Will the value of interest always be 99999, or will it be the highest value with a matching COLA_FK?  If you are looking for the highest value, please supply the four digit version of Oracle that you are using (10.2.0.2, 11.1.0.6, 8.1.7.3, etc.).

The original poster replied:

Aha – that is very cool.  I had figured out the

TABLEA.COLA=TABLEB.COLA_FK(+)

but did not know I could do this:

AND TABLEB.COLC(+)=99999;

As a matter of fact you are right – how did you guess – in my cases, the interest of value would be max of whatever is in TABLEB.COLC – if the row with 99999 does not exist, then the sql returns the fow with data in COLC=12345.
 Also, working with ORACLE version 9.2.0.8.

It looks like the DENSE_RANK analytical function, an inline view, and an outer join are required.

First, let’s introduce a little more data to make certain that we cannot query for a specific value of COLC and return the expected
results:

INSERT INTO TABLEA VALUES (3);
INSERT INTO TABLEB VALUES (13,2,111111);
INSERT INTO TABLEB VALUES (13,3,11);

Next, we try an experiment with the DENSE_RANK function to separate the rows by the value of COLA_FK (caused by the PARTITION BY directive) and rank the values sorted from highest to lowest (caused by the DESC directive):

SELECT
  COLB,
  COLA_FK,
  COLC,
  DENSE_RANK() OVER (PARTITION BY COLA_FK ORDER BY COLC DESC) DR
FROM
  TABLEB;

      COLB    COLA_FK       COLC         DR
---------- ---------- ---------- ----------
        13          2     111111          1
        12          2      99999          2
        11          2      12345          3
        13          3         11          1

We are only interested in the rows with DR = 1, so we need a way to eliminate the unnecessary rows.  If we slide the above SQL statement into an inline view, we are able to add a WHERE clause that restricts the results to the rows containing the highest COLC value per COLA_FK value.  We can then alias the inline view (as B), and join it to TABLEA as before:

SELECT
  TABLEA.COLA,
  B.COLA_FK,
  B.COLC
FROM
  TABLEA,
  (SELECT
    COLB,
    COLA_FK,
    COLC,
    DENSE_RANK() OVER (PARTITION BY COLA_FK ORDER BY COLC DESC) DR
  FROM
    TABLEB) B
WHERE
  TABLEA.COLA=B.COLA_FK(+)
  AND B.DR(+)=1;

      COLA    COLA_FK       COLC
---------- ---------- ----------
         2          2     111111
         3          3         11
         1 




Inner and Outer Join Examples

8 12 2009

December 7, 2009

Some time ago someone asked the following in a forum:
http://groups.google.com/group/oracle_dba_experts/browse_thread/thread/41863b746cbef8d2

Can someone please help me in understanding inner and outer joins?  I seem to have a block when it comes to understanding this concept.

 Short answer:
* Inner join – the value must be in BOTH tables
* Outer join – the value must be in at least ONE table

Assume that the following table and columns in those tables, and data in those columns exist:

TABLE_1.ANIMAL 
  COW 
  PIG 
  ZEBRA 
  SHARK 
  ROOSTER 
  LION

 

TABLE_2.ANIMAL 
  COW 
  PIG 
  DOG

 

TABLE_3.ANIMAL 
  ZEBRA 
  LION 
  TIGER

Inner join TABLE_2 and TABLE_3

SELECT 
  T2.ANIMAL, 
  T3.ANIMAL 
FROM 
  TABLE_2 T2, 
  TABLE_3 T3 
WHERE 
  T2.ANIMAL=T3.ANIMAL; 
 
(no results) 
-------------------  

(Left) Outer join TABLE_2 and TABLE_3, include all rows from TABLE_2

SELECT 
  T2.ANIMAL, 
  T3.ANIMAL 
FROM 
  TABLE_2 T2, 
  TABLE_3 T3 
WHERE 
  T2.ANIMAL=T3.ANIMAL(+); 
 
T2.ANIMAL      T3.ANIMAL 
COW            (null) 
PIG            (null) 
DOG            (null) 
-------------------

Inner join TABLE_1 and TABLE_2

SELECT 
  T1.ANIMAL, 
  T2.ANIMAL 
FROM 
  TABLE_1 T1, 
  TABLE_2 T2 
WHERE 
  T1.ANIMAL=T2.ANIMAL; 
 
T1.ANIMAL         T2.ANIMAL 
COW               COW 
PIG               PIG 
-------------------

(Right) Outer join TABLE_1 and TABLE_2, include all rows from TABLE_2

SELECT 
  T1.ANIMAL, 
  T2.ANIMAL 
FROM 
  TABLE_1 T1, 
  TABLE_2 T2 
WHERE 
  T1.ANIMAL(+)=T2.ANIMAL; 
 
T1.ANIMAL         T2.ANIMAL 
COW               COW 
PIG               PIG 
(null)            DOG 
-------------------

(One Method, Full) Outer join TABLE_1 and TABLE_2, include all rows

SELECT 
  T1.ANIMAL, 
  T2.ANIMAL 
FROM 
  TABLE_1 T1, 
  TABLE_2 T2 
WHERE 
  T1.ANIMAL(+)=T2.ANIMAL 
UNION 
SELECT 
  T1.ANIMAL, 
  T2.ANIMAL 
FROM 
  TABLE_1 T1, 
  TABLE_2 T2 
WHERE 
  T1.ANIMAL=T2.ANIMAL(+); 
 
T1.ANIMAL         T2.ANIMAL 
COW               COW 
PIG               PIG 
(null)            DOG 
ZEBRA             (null) 
SHARK             (null) 
ROOSTER           (null) 
LION              (null)




SQL – Running Sum, Skipping Weekends

8 12 2009

December 7, 2009

Some time ago the following question appeared on the comp.databases.oracle.misc Usenet group: http://groups.google.com/group/comp.databases.oracle.misc/browse_thread/thread/cad8621182ae7d1b

This sounds like it should be super simple, but I can’t think how to do it using plain SQL.

I am going to display some rows, and I want to number them in the display, like using rownum, except that the number only goes up when the row has some property (I don’t care if it displays or not when it doesn’t go up).

To explain, imagine

select rownum  , the_date , to_char(dates.the_date,'DY') DAY 
from my_table 
order by the_date

shows 
        1       1-Jan-2008      TUE 
        2       2-Jan-2008      WED 
        3       3-Jan-2008      THU 
        4       4-Jan-2008      FRI 
        5       5-Jan-2008      SAT 
        6       6-Jan-2008      SUN 
        7       7-Jan-2008      MON 
        8       8-Jan-2008      TUE

but I don’t want to count the weekend.  what I want to show would be the following instead

        1       1-Jan-2008      TUE 
        2       2-Jan-2008      WED 
        3       3-Jan-2008      THU 
        4       4-Jan-2008      FRI 
                5-Jan-2008      SAT 
                6-Jan-2008      SUN 
        5       7-Jan-2008      MON 
        6       8-Jan-2008      TUE

Looks simple, but how to do this in plain SQL?

I provided the following solution:

Do it with the SUM analytical function and DECODE:

SELECT 
  TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1) THE_DATE, 
  TO_CHAR(TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1),'DY') DAY 
FROM 
  DUAL 
CONNECT BY 
  LEVEL<=20; 
 
THE_DATE  DAY 
--------- --- 
01-JAN-08 TUE 
02-JAN-08 WED 
03-JAN-08 THU 
04-JAN-08 FRI 
... 
19-JAN-08 SAT 
20-JAN-08 SUN

Step 1:

SELECT 
  SUM(DECODE(DAY,'SAT',0,'SUN',0,1)) OVER (ORDER BY THE_DATE) MY_COUNT, 
  THE_DATE, 
  DAY 
FROM 
  (SELECT 
    TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1) THE_DATE, 
    TO_CHAR(TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1),'DY') DAY 
  FROM 
    DUAL 
  CONNECT BY 
    LEVEL<=20); 
 
  MY_COUNT THE_DATE  DAY 
---------- --------- --- 
         1 01-JAN-08 TUE 
         2 02-JAN-08 WED 
         3 03-JAN-08 THU 
         4 04-JAN-08 FRI 
         4 05-JAN-08 SAT 
         4 06-JAN-08 SUN 
         5 07-JAN-08 MON 
         6 08-JAN-08 TUE 
         7 09-JAN-08 WED 
         8 10-JAN-08 THU 
         9 11-JAN-08 FRI 
         9 12-JAN-08 SAT 
         9 13-JAN-08 SUN 
        10 14-JAN-08 MON 
        11 15-JAN-08 TUE 
        12 16-JAN-08 WED 
        13 17-JAN-08 THU 
        14 18-JAN-08 FRI 
        14 19-JAN-08 SAT 
        14 20-JAN-08 SUN

Now to remove the numbers that should not print, using a second DECODE:

SELECT 
  DECODE(DAY,'SAT',NULL,'SUN',NULL,SUM(DECODE(DAY,'SAT',0,'SUN',0,1)) OVER (ORDER BY THE_DATE)) MY_COUNT, 
  THE_DATE, 
  DAY 
FROM 
  (SELECT 
    TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1) THE_DATE, 
    TO_CHAR(TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1),'DY') DAY 
  FROM 
    DUAL 
  CONNECT BY 
    LEVEL<=20); 
 
MY THE_DATE  DAY 
-- --------- --- 
1  01-JAN-08 TUE 
2  02-JAN-08 WED 
3  03-JAN-08 THU 
4  04-JAN-08 FRI 
   05-JAN-08 SAT 
   06-JAN-08 SUN 
5  07-JAN-08 MON 
6  08-JAN-08 TUE 
7  09-JAN-08 WED 
8  10-JAN-08 THU 
9  11-JAN-08 FRI 
   12-JAN-08 SAT 
   13-JAN-08 SUN 
10 14-JAN-08 MON 
11 15-JAN-08 TUE 
12 16-JAN-08 WED 
13 17-JAN-08 THU 
14 18-JAN-08 FRI 
   19-JAN-08 SAT 
   20-JAN-08 SUN

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Maxim Demenko provided the following solution:

SQL> alter session set nls_territory='AMERICA'; 
 
Session altered. 
 
SQL> alter session set nls_date_language='AMERICAN'; 
 
Session altered. 
 
SQL> 
SQL> with my_table as ( 
   2    select date '2008-01-01' + rownum -1 the_date 
   3    from dual connect by level <=10 
   4  ) 
   5  select 
   6  case 
   7  when 
   8  to_char(the_date,'d') not in (1,7) 
   9  then row_number() 
  10  over(partition by case when to_char(the_date,'d') not in (1,7) then 1 
  11  end order by the_date) 
  12  end row_num, 
  13  the_date, 
  14  to_char(the_date,'DY') DAY 
  15  from my_table 
  16  order by the_date; 
 
    ROW_NUM THE_DATE           DAY 
---------- ------------------ ------------ 
          1 01-JAN-08          TUE 
          2 02-JAN-08          WED 
          3 03-JAN-08          THU 
          4 04-JAN-08          FRI 
            05-JAN-08          SAT 
            06-JAN-08          SUN 
          5 07-JAN-08          MON 
          6 08-JAN-08          TUE 
          7 09-JAN-08          WED 
          8 10-JAN-08          THU

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  
Ken Denny provided the following solution:

select r_num, the_date, to_char(dates.the_date,'DY') DAY 
  from (select rownum r_num, the_date 
          from (select the_date from my_table 
                  where to_char(dates.the_date,'DY') IN 
                     ('MON','TUE','WED','THU','FRI') 
                  order by the_date) 
        union 
        select null r_num, the_date from my_table 
          where to_char(dates.the_date,'DY') IN ('SAT','SUN')) 
   order by the_date;




SQL – Combining Overlapping Date Rows

8 12 2009

December 7, 2009

Some time ago the following question appeared on the comp.databases.oracle.server Usenet group:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/aff40e7ccf09d9cc

On Oracle 10G, lets say I have a table that looks like this:

create table t1 (key varchar2(10), start_dtm date, end_dtm date);

and I have data some of which over laps:

insert into t1 values ('1234', to_date('01/01/2001', 'DD/MM/YYYY'), to_date('30/03/2001', 'DD/MM/YYYY'));
insert into t1 values ('1234', to_date('01/02/2001', 'DD/MM/YYYY'), to_date('30/03/2001', 'DD/MM/YYYY'));
insert into t1 values ('1234', to_date('15/02/2001', 'DD/MM/YYYY'), to_date('15/04/2001', 'DD/MM/YYYY'));
insert into t1 values ('1234', to_date('01/07/2001', 'DD/MM/YYYY'), to_date('30/12/2001', 'DD/MM/YYYY'));
Ie:

1st Jan - 30th March
    2nd Feb - 30th March
       15th Feb - 15th April

1st July - 30th Dec

I want to return only two rows from the 4 that look like

1st jan - 15th April
1st July - 30th Dec

ie – if the dates are continuous, squash all the continuous rows into 1 with the min start and max end date but if there is a gap it has to become a second row.  There can be any number of rows and over laps etc. I reckon this can be done with some analytics trickery, but I just cannot seem to figure out how.

Any ideas?

I provided the following solution:

First, we experiment with LAG so that we are able to examine the previous row when sorted by START_DTM and END_DTM:

SELECT
  T1.KEY,
  T1.START_DTM,
  T1.END_DTM,
  LEAD(T1.START_DTM) OVER (PARTITION BY T1.KEY ORDER BY T1.START_DTM,T1.END_DTM) M_START_DTM,
  LEAD(T1.END_DTM) OVER (PARTITION BY T1.KEY ORDER BY T1.START_DTM,T1.END_DTM) M_END_DTM
FROM
  T1
ORDER BY
  T1.START_DTM,
  T1.END_DTM;

KEY        START_DTM END_DTM   M_START_D M_END_DTM
---------- --------- --------- --------- ---------
1234       01-JAN-01 30-MAR-01 01-FEB-01 30-MAR-01
1234       01-FEB-01 30-MAR-01 15-FEB-01 15-APR-01
1234       15-FEB-01 15-APR-01 01-JUL-01 30-DEC-01
1234       01-JUL-01 30-DEC-01

If we now slide the above into an inline view and see if the START_DTM is greater than M_END_DTM (the previous row’s END_DTM), we may have found a new series of dates (note, I just noticed that this may have problems if there are two rows with discrete date ranges that fall entirely within a third, larger date range), we will output 1 if true, or 0 if false:

SELECT
  KEY,
  START_DTM,
  END_DTM,
  M_START_DTM,
  M_END_DTM,
  DECODE(SIGN(START_DTM-NVL(M_END_DTM,START_DTM)),1,1,0) C
FROM
  (SELECT
    T1.KEY,
    T1.START_DTM,
    T1.END_DTM,
    LAG(T1.START_DTM) OVER (PARTITION BY T1.KEY ORDER BY T1.START_DTM,T1.END_DTM) M_START_DTM,
    LAG(T1.END_DTM) OVER (PARTITION BY T1.KEY ORDER BY T1.START_DTM,T1.END_DTM) M_END_DTM
  FROM
    T1
  ORDER BY
    T1.START_DTM,
    T1.END_DTM);

KEY        START_DTM END_DTM   M_START_D M_END_DTM          C
---------- --------- --------- --------- --------- ----------
1234       01-JAN-01 30-MAR-01                              0
1234       01-FEB-01 30-MAR-01 01-JAN-01 30-MAR-01          0
1234       15-FEB-01 15-APR-01 01-FEB-01 30-MAR-01          0
1234       01-JUL-01 30-DEC-01 15-FEB-01 15-APR-01          1

In the above, a 1 is output whenever there is a jump in the date range – if we use the COUNT analytical function to create a running count of the 1s and slide the above into an inline view, we actually create a column that may be used for grouping:

SELECT
  KEY,
  START_DTM START_DTM,
  END_DTM END_DTM,
  SUM(C) OVER (PARTITION BY KEY ORDER BY START_DTM,END_DTM) G
FROM
(SELECT
  KEY,
  START_DTM,
  END_DTM,
  M_START_DTM,
  M_END_DTM,
  DECODE(SIGN(START_DTM-NVL(M_END_DTM,START_DTM)),1,1,0) C
FROM
  (SELECT
    T1.KEY,
    T1.START_DTM,
    T1.END_DTM,
    LAG(T1.START_DTM) OVER (PARTITION BY T1.KEY ORDER BY T1.START_DTM,T1.END_DTM) M_START_DTM,
    LAG(T1.END_DTM) OVER (PARTITION BY T1.KEY ORDER BY T1.START_DTM,T1.END_DTM) M_END_DTM
  FROM
    T1
  ORDER BY
    T1.START_DTM,
    T1.END_DTM));

KEY        START_DTM END_DTM            G
---------- --------- --------- ----------
1234       01-JAN-01 30-MAR-01          0
1234       01-FEB-01 30-MAR-01          0
1234       15-FEB-01 15-APR-01          0
1234       01-JUL-01 30-DEC-01          1

We are now able to group on the column G by again sliding the above into an inline view:

SELECT
  KEY,
  MIN(START_DTM) START_DTM,
  MAX(END_DTM) END_DTM
FROM
(SELECT
  KEY,
  START_DTM START_DTM,
  END_DTM END_DTM,
  SUM(C) OVER (PARTITION BY KEY ORDER BY START_DTM,END_DTM) G
FROM
(SELECT
  KEY,
  START_DTM,
  END_DTM,
  M_START_DTM,
  M_END_DTM,
  DECODE(SIGN(START_DTM-NVL(M_END_DTM,START_DTM)),1,1,0) C
FROM
  (SELECT
    T1.KEY,
    T1.START_DTM,
    T1.END_DTM,
    LAG(T1.START_DTM) OVER (PARTITION BY T1.KEY ORDER BY T1.START_DTM,T1.END_DTM) M_START_DTM,
    LAG(T1.END_DTM) OVER (PARTITION BY T1.KEY ORDER BY T1.START_DTM,T1.END_DTM) M_END_DTM
  FROM
    T1
  ORDER BY
    T1.START_DTM,
    T1.END_DTM)))
GROUP BY
  KEY,
  G;

KEY        START_DTM END_DTM
---------- --------- ---------
1234       01-JAN-01 15-APR-01
1234       01-JUL-01 30-DEC-01

Let’s add a little more data to see what happens:

insert into t1 values ('1234', to_date('10/10/2001', 'DD/MM/YYYY'), to_date('29/12/2001', 'DD/MM/YYYY'));
insert into t1 values ('1234', to_date('31/12/2001', 'DD/MM/YYYY'), to_date('15/01/2002', 'DD/MM/YYYY'));

KEY        START_DTM END_DTM
---------- --------- ---------
1234       01-JAN-01 15-APR-01
1234       01-JUL-01 30-DEC-01
1234       31-DEC-01 15-JAN-02

Note the addition of the last row in the output – you may want to determine if that should be part of the previous group of date.  You should be able to fix this by adding or subtracting a date in the SIGN( ) function.

—-

I originally thought that you were trying to eliminate this row from the output:

KEY        START_DTM END_DTM
---------- --------- ---------
1234       01-FEB-01 30-MAR-01

For the above, there are several methods:

SELECT
  T1.KEY,
  T1.START_DTM,
  T1.END_DTM
FROM
  T1
WHERE
  (T1.KEY,
  T1.START_DTM,
  T1.END_DTM) NOT IN (
    SELECT
      T1.KEY,
      T1.START_DTM,
      T1.END_DTM
    FROM
      T1,
      T1 T2
    WHERE
      T1.KEY=T2.KEY
      AND T1.START_DTM BETWEEN T2.START_DTM AND T2.END_DTM
      AND T1.END_DTM BETWEEN  T2.START_DTM AND T2.END_DTM
      AND (
        T1.START_DTM<>T2.START_DTM
        OR T1.END_DTM<>T2.END_DTM));

KEY        START_DTM END_DTM
---------- --------- ---------
1234       01-JAN-01 30-MAR-01
1234       15-FEB-01 15-APR-01
1234       01-JUL-01 30-DEC-01

 

SELECT
  T1.KEY,
  T1.START_DTM,
  T1.END_DTM
FROM
  T1,
  (SELECT
    T1.KEY,
    T1.START_DTM,
    T1.END_DTM
  FROM
    T1,
    T1 T2
  WHERE
    T1.KEY=T2.KEY
    AND T1.START_DTM BETWEEN T2.START_DTM AND T2.END_DTM
    AND T1.END_DTM BETWEEN  T2.START_DTM AND T2.END_DTM
    AND (
      T1.START_DTM<>T2.START_DTM
      OR T1.END_DTM<>T2.END_DTM)) T3
WHERE
  T1.KEY=T3.KEY(+)
  AND T1.START_DTM=T3.START_DTM(+)
  AND T1.END_DTM=T3.END_DTM(+)
  AND T3.KEY IS NULL;

KEY        START_DTM END_DTM
---------- --------- ---------
1234       01-JAN-01 30-MAR-01
1234       15-FEB-01 15-APR-01
1234       01-JUL-01 30-DEC-01

 

SELECT
  T1.KEY,
  T1.START_DTM,
  T1.END_DTM
FROM
  T1
MINUS
SELECT
  T1.KEY,
  T1.START_DTM,
  T1.END_DTM
FROM
  T1,
  T1 T2
WHERE
  T1.KEY=T2.KEY
  AND T1.START_DTM BETWEEN T2.START_DTM AND T2.END_DTM
  AND T1.END_DTM BETWEEN  T2.START_DTM AND T2.END_DTM
  AND (
    T1.START_DTM<>T2.START_DTM
    OR T1.END_DTM<>T2.END_DTM);

KEY        START_DTM END_DTM
---------- --------- ---------
1234       01-JAN-01 30-MAR-01
1234       15-FEB-01 15-APR-01
1234       01-JUL-01 30-DEC-01

 

SELECT
  KEY,
  START_DTM,
  END_DTM
FROM
  (SELECT
    T1.KEY,
    T1.START_DTM,
    T1.END_DTM,
    MIN(T1.START_DTM) OVER (PARTITION BY T1.KEY, T1.END_DTM) M_START_DTM,
    MAX(T1.END_DTM) OVER  (PARTITION BY T1.KEY, T1.START_DTM) M_END_DTM
  FROM
    T1)
WHERE
  START_DTM=M_START_DTM
  AND END_DTM=M_END_DTM;

KEY        START_DTM END_DTM
---------- --------- ---------
1234       01-JAN-01 30-MAR-01
1234       15-FEB-01 15-APR-01
1234       01-JUL-01 30-DEC-01

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Maxim Demenko provided the following solution:

Maybe, something like this

SQL> select key, min(start_dtm) start_dtm, max(end_dtm) end_dtm
   2    from (select key,
   3                 start_dtm,
   4                 end_dtm,
   5                 sum(interval_change) over(partition by key order by end_dtm, start_dtm) interval_no
   6            from (select key,
   7                         start_dtm,
   8                         end_dtm,
   9                         case
  10                           when (lag(end_dtm)
  11                                 over(partition by key order by end_dtm,
  12                                      start_dtm) - start_dtm) > 0 then
  13                            0
  14                           else
  15                            1
  16                         end interval_change
  17                    from t1))
  18   group by key, interval_no
  19   order by key, interval_no
  20  ;

KEY        START_DTM            END_DTM
---------- -------------------- --------------------
1234       01-Jan-2001          15-Apr-2001
1234       01-Jul-2001          30-Dec-2001

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The original poster followed up with the following solution that he developed:

Using the same table as above and this data:

create table t1 (key varchar2(10), start_dtm date, end_dtm date);

insert into t1 values( 9999, to_date('01/01/2008', 'DD/MM/YYYY'), to_date('01/06/2008', 'DD/MM/YYYY'));
insert into t1 values( 9999, to_date('01/02/2008', 'DD/MM/YYYY'), to_date('01/06/2008', 'DD/MM/YYYY'));
insert into t1 values( 9999, to_date('01/02/2008', 'DD/MM/YYYY'), to_date('01/05/2008', 'DD/MM/YYYY'));
insert into t1 values( 9999, to_date('01/02/2008', 'DD/MM/YYYY'), to_date('01/07/2008', 'DD/MM/YYYY'));

This gives a set of rows like:

|------------------------------------------|
   |---------------------------------------|
   |---------------------|
      |------------------------------------------|

And we expect this to turn into a single row like

01/01/08 |----------------------------------------------| 01/07/2008

 

insert into t1 values( 9999, to_date('01/08/2008', 'DD/MM/YYYY'), null);
insert into t1 values( 9999, to_date('01/09/2008', 'DD/MM/YYYY'), to_date('01/10/2008', 'DD/MM/YYYY'));

This gives a set of rows like:

|--------------------------------------->
   |------------|

And we want an row that looks like

01/08/2008 |----------------------------> (null end date)

I think this query does it:

select key, min(start_dtm), max(end_dtm)
from
(
  select key, start_dtm, end_dtm, max (grp) over (partition by key order by start_dtm asc) grp2
  from
  (
    SELECT key,
           start_dtm,
           NVL (end_dtm, '31-dec-4712') end_dtm,
           case
             when
               (start_dtm not between lag(start_dtm) over (partition by key order by start_dtm asc)
                   and nvl( lag(end_dtm) over (partition by key order by start_dtm asc), '31-dec-4712') )
                or lag(start_dtm) over (partition by key order by start_dtm asc) is null
                then
                  row_number() over (partition by key order by start_dtm asc)
             end grp
    FROM t1
    order by key, start_dtm asc
  ) a
) b
group by key, grp2

GRP                              MIN(START MAX(END_D
--------------------------------- --------- ---------
09999                             01-JAN-08 01-JUL-08
09999                             01-AUG-08 31-DEC-12

Basically I said, if you order the rows by increasing start date, then if rows overlap, the current start_dtm must be between the previous start_dtm and end_dtm – if there is no previous start_dtm or they don’t overlap, then its a new group.

The innermost select outputs something like:

KEY                               START_DTM END_DTM          GRP
--------------------------------- --------- --------- ----------
09999                             01-JAN-08 01-JUN-08          1
09999                             01-FEB-08 01-JUL-08
09999                             01-FEB-08 01-JUN-08
09999                             01-FEB-08 01-MAY-08
09999                             01-AUG-08 31-DEC-12          5
09999                             01-SEP-08 01-OCT-08

Then we use analytics again to fill in the blanks in the enclosing query (b):

KEY                               START_DTM END_DTM         GRP2
--------------------------------- --------- --------- ----------
09999                             01-JAN-08 01-JUN-08          1
09999                             01-FEB-08 01-JUL-08          1
09999                             01-FEB-08 01-JUN-08          1
09999                             01-FEB-08 01-MAY-08          1
09999                             01-AUG-08 31-DEC-12          5
09999                             01-SEP-08 01-OCT-08          5

and then simply group by KEY, GRP in the outer query to get the result.

I am glad I went through the pain of figuring this out (took me a good 90 minutes) as I really feel like I get analytics now.  It also allowed me to replace a temporary table and several 100 lines of PLSQL that was performing rather poorly and written by someone who doesn’t get analytics!








Follow

Get every new post delivered to your Inbox.

Join 139 other followers