We have a database that's recently been converted from SQL Server 2005 to Oracle 220.127.116.11.
There's a table that contains inventory usage statistics by month. It's keyed on the part number and the period:
Both are CHAR:
ACCT_PER CHAR (6) -- Format is YYYYMM
There are a bunch of other columns in the table tracking total of orders, issues, prices etc, but they aren't particularly relevant. There is one row per item per month where the the item actually has usage.
There was a query used in SQL Server where the developer performed a cross join - joining the table to itself to effectively generate a list of periods - which was then used as a part of a larger query in order to give rolling totals for each month for each inventory item.
The query looked like this:
SELECT DISTINCT A.STOCK_CODE
, CAST((B.ACCT_PER + '01') AS DATETIME) AS ACCT_PER
FROM INVENT_STATS A
CROSS JOIN INVENT_STATS B
It ran pretty quickly, scanning 900,000 rows pretty darn quick. Looking at the actual execution plan, it doesn't seem to need to sort anything because the clustered index returns the rows in order, joins them together and returns the distinct list of stock codes and periods.
In Oracle, because there's no such thing as a clustered index, the explain plan is telling me that it's sorting, which is probably why it runs and runs and runs. Considering that a cross join is a.num_rows * b.num_rows, that's a lot of sorting it needs to do on a 900K row table.
The goal is that if we have this data in INVENT_STATS:
What we get is one row returned for each month of the year (working under the assumption that every month at least something out of inventory is going to be ordered or used somewhere, this generating at least one row per month for at least something in inventory.
That then feeds other parts of reports so that they can get rolling report values on amounts and quantities issued, ordered etc.
I believe that the answer lies somewhere in the use of SELECT ... OVER (PARTITION BY ) in order to get what we need but we can't quite hit the syntax and hoping for some help from here.
(I am still searching EE and other sites for answers, but wanted to get this posted to help expedite a solution for the developer).