Oracle MODEL clause giving unusual output

oracleoracle-12c

This is my table structure:

Name         Null Type        
------------ ---- ----------- 
PAID_MM           VARCHAR2(2) 
GA                NUMBER      
SA                NUMBER      
MA                NUMBER      
TOTAL_AMOUNT      NUMBER      
AMT_VAR           NUMBER

and this is my data:

PAID_MM  GA     SA     MA     TOTAL_AMOUNT  AMT_VAR
01       98.5   48.16  161.47  308.13          0
02       116.34 53.14  115.46  284.94          0
03       288.03 25.31  136.04  449.38          0
04       138.41 118.84 148.5   405.75          0
05       75.69  190.74 51.7    318.13          0
06       451.18  0     214.84  666.02          0

I want calculate the AMT_VAR as (TOTAL_AMOUNT of current month – TOTAL_AMOUNT of previous month / TOTAL_AMOUNT of previous month) *100.

I am using Oracle MODEL clause as this:

SELECT 
  PAID_MM, 
  TOTAL_AMOUNT, 
  TRUNC(AMT_VAR*100, 2) AS VARIAN
FROM 
  AGNT_PAID_SUMM
MODEL RETURN UPDATED ROWS
DIMENSION BY (PAID_MM)
MEASURES (AMT_VAR, TOTAL_AMOUNT)
RULES 
(
AMT_VAR [PAID_MM] = ((TOTAL_AMOUNT[CV(PAID_MM)] - TOTAL_AMOUNT[CV(PAID_MM)      -1])/TOTAL_AMOUNT[CV(PAID_MM) -1])
)
ORDER BY PAID_MM

and I am getting the following output:

PAID_MM  TOTAL_AMOUNT VARIAN
01       308.13       NULL
02       284.94       NULL
03       449.38       NULL
04       405.75       NULL  
05       318.13       NULL  
06       666.02       NULL
07       339.47       NULL
08       445.86       NULL
09       548.52       NULL
10       363.94       NULL
11       362.8        -0.31
12       409.37       12.83

This looks quite weird to me. Can somebody help me understand why it's not working for all the rows except the last two ones?

I am using Oracle 12c.

Best Answer

The computed dimension expressions are false.
PAID_MM is a string (e.g. '03') but CV(PAID_MM)-1 is a number (e.g. 2, which is different from '02'), therefore you get NULL for your expressions, unless CV(PAID_MM)-1 has more than 1 digit and then you have no leading '0' to loose.

Here is working code which uses to_char(CV(PAID_MM)-1,'FM00') instead of CV(PAID_MM)-1:

SELECT 
  PAID_MM, 
  TOTAL_AMOUNT, 
  TRUNC(AMT_VAR*100, 2) AS VARIAN
FROM 
  AGNT_PAID_SUMM
MODEL RETURN UPDATED ROWS
DIMENSION BY (PAID_MM)
MEASURES (AMT_VAR, TOTAL_AMOUNT)
RULES 
(
AMT_VAR [PAID_MM] = ((TOTAL_AMOUNT[CV(PAID_MM)] - TOTAL_AMOUNT[to_char(CV(PAID_MM)-1,'FM00')])/TOTAL_AMOUNT[to_char(CV(PAID_MM)-1,'FM00')])
)
ORDER BY PAID_MM

+---------+--------------+--------+
| PAID_MM | TOTAL_AMOUNT | VARIAN |
+---------+--------------+--------+
| 01      | 308.13       | (null) |
+---------+--------------+--------+
| 02      | 284.94       | -7.52  |
+---------+--------------+--------+
| 03      | 449.38       | 57.71  |
+---------+--------------+--------+
| 04      | 405.75       | -9.7   |
+---------+--------------+--------+
| 05      | 318.13       | -21.59 |
+---------+--------------+--------+
| 06      | 666.02       | 109.35 |
+---------+--------------+--------+

Related Solutions

Corrupt date fields causing query failure in Oracle

The date looks fine:

SQL> CREATE TABLE testdate (d date);
Table created
SQL> INSERT INTO testdate
  2     VALUES (to_date(to_char((120-100)*100+107-100)||'-12-31','yyyy-mm-dd'));
1 row inserted
SQL> SELECT DUMP(d), d FROM testdate;
DUMP(D)                                                                          D
-------------------------------------------------------------------------------- -----------
Typ=12 Len=7: 120,107,12,31,1,1,1                                                31/12/2007

Are you able to use the specific conversion function TO_CHAR on those lines? For instance SELECT to_char(confidential_until, 'yyyy-mm-dd') FROM mytable WHERE id = :x

Oracle Spatial – Better Way to Cross Join on Numbers Table for Line Vertices

I know a bit about Oracle performance and pretty much nothing about custom data types, but I'll try to give you a plan to improve performance.

1) Verify that you cannot get an explain plan.

It's possible to get explain plans even if you don't have sophisicated database software. What happens if you execute set autotrace on explain?

You could also try DBMS_XPLAN. First save off the plan by wrapping your query with a few extra key words:

explain plan for (SELECT... your query goes here);

Then execute this:

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

It's possible that neither of those will work and you truly cannot get an explain plan. I just wanted to verify that because with an explain plan it'll be much easier for the community to help you.

2) Consider requirements.

You said that 20 seconds isn't good enough. Have you or someone else defined exactly what is good enough? Is there any room for negotiation? Does your query need to be exactly one SELECT query? Could you populate a global temporary table in one step and select the results you wanted in the next? Could you create a stored procedure that returns a result set and call that?

3) Establish a lower bound for the time required to complete the query.

I suggest running a simple query that "cheats" to figure out what a well-optimized query would look like. For example, how long does this query that gets only the first vertices take?

SELECT
    ROWNUM
    ,ROAD_ID
    ,VERTEX_INDEX
    ,SDE.ST_X(ST_POINT) AS X
    ,SDE.ST_Y(ST_POINT) AS Y
FROM
(
    SELECT  
          ROWNUM
          ,a.ROAD_ID
          ,1 VERTEX_INDEX
          ,SDE.ST_PointN(a.SHAPE, 1) AS ST_POINT
    FROM  ENG.ROAD a
)
ORDER BY ROAD_ID, VERTEX_INDEX;

I suspect that will give you 4000 rows. If you multiply that query's response time by 17.5/4 that could give you a good lower bound for the total execution time.

If your lower bound for the total execution time is longer than what you established in step 2 then you either need to get creative with your data model by calculating results ahead of time and storing them in tables or you need to renegotiate the required response time.

4) Benchmark to figure out which functions are contributing the most to your execution time.

You were on the right track with Update #1 but you need to try to control for the amount of work being done. For example, is it possible to write a group of relatively simple queries that execute each function exactly 10000 times? How do the response times compare?

5) Go to work.

Depending on the requirements established in step 2 and what you found in step 4 try any trick that you can think of to reduce the query runtime. Are you able to pre-compute results and save off them? If the problem relates to the number of times the functions are executed then the undocumented materialize hint may be helpful. That forces Oracle to create a hidden temp table behind the scenes to store the results. I do not know if it is compatible with the special data types that you are using.

For example, maybe something like this performs better? Apologies if it does not compile but I have no way to test.

WITH ROAD_CTE (ROAD_ID, VERTEX_INDEX, SHAPE) AS
(
    SELECT /*+ materalize */
      a.ROAD_ID
    , b.NUMBERS VERTEX_INDEX
    , a.SHAPE
    FROM ENG.ROAD a
    CROSS JOIN ENG.NUMBERS b
    WHERE b.NUMBERS <= SDE.ST_NUMPOINTS(a.SHAPE)
)
, CTE_WITH_ST_POINT (ROAD_ID, VERTEX_INDEX, ST_POINT) AS
(
    SELECT /*+ materalize */
      rcte.ROAD_ID
    , rcte.VERTEX_INDEX
    , SDE.ST_PointN(rcte.SHAPE, rcte.VERTEX_INDEX) ST_POINT
    FROM ROAD_CTE rcte
)
SELECT 
      ROAD_ID
    , VERTEX_INDEX
    , SDE.ST_X(ST_POINT) AS X
    , SDE.ST_Y(ST_POINT) AS Y
FROM CTE_WITH_ST_POINT
ORDER BY ROAD_ID, VERTEX_INDEX;

If you're still stuck after all of this I suspect that it'll at least give you additional information that you can edit into the question. Good luck!

Best Answer

Related Solutions

Corrupt date fields causing query failure in Oracle

Oracle Spatial – Better Way to Cross Join on Numbers Table for Line Vertices

Related Question