Home » SQL & PL/SQL » SQL & PL/SQL » Need a number of months the contract was open (6 Merged) (11.2.0.2)
Need a number of months the contract was open (6 Merged) [message #564124] Tue, 21 August 2012 09:27 Go to next message
anjulis
Messages: 32
Registered: August 2012
Member
I have a contract that has several categories with payments in each one. If there is a payment=0, then a category is considered closed. The contract is considered closed when all categories are closed.

Date         Category    Payments
01/01/07       1            5
01/01/07       2            7.5 
01/01/07       3            6
05/01/07       2            0  
09/01/07       1            0
11/01/07       3            0 


(date here is in mm/dd/yy)
I need a SQL that will return the number of months within the year that contract was open. In the example above, the last category closed on 11/01, so the result should be 10. If the last line was not there, the contract would still be open in 2007 and the result should be 12.
Need a number of months the contract was open [message #564126 is a reply to message #564124] Tue, 21 August 2012 09:31 Go to previous messageGo to next message
anjulis
Messages: 32
Registered: August 2012
Member
I have a contract that has several categories with payments in each one. If there is a payment=0, then a category is considered closed. The contract is considered closed when all categories are closed.

Date         Category    Payments
01/01/07       1            5
01/01/07       2            7.5 
01/01/07       3            6
05/01/07       2            0  
09/01/07       1            0
11/01/07       3            0 


(date here is in mm/dd/yy)
I need a SQL that will return the number of months within the year that contract was open. In the example above, the last category closed on 11/01, so the result should be 10. If the last line was not there, the contract would still be open in 2007 and the result should be 12.
Re: Need a number of months the contract was open [message #564127 is a reply to message #564126] Tue, 21 August 2012 09:34 Go to previous messageGo to next message
BlackSwan
Messages: 23159
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Need a number of months the contract was open (3 Merged) [message #564129 is a reply to message #564124] Tue, 21 August 2012 09:40 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:

BlackSwan wrote on Thu, 16 August 2012 02:25
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


Michel Cadot wrote on Thu, 16 August 2012 07:49
Welcome to the forum.

With any SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Is "1/4/2005" April 1st or January 4th?
...


Post what is requested and post a test case that is more complete than the 6 lines you posted, it must show several cases of what the queries should return and not only 1.

Regards
Michel




[Updated on: Tue, 21 August 2012 09:42]

Report message to a moderator

Re: Need a number of months the contract was open (3 Merged) [message #564137 is a reply to message #564129] Tue, 21 August 2012 10:25 Go to previous messageGo to next message
anjulis
Messages: 32
Registered: August 2012
Member
Michael,
I can change the post to reflect the reqirements you posted but I don't see the way to edit or delete the post. Shall I create another topic?
a number of months the contract was open [message #564138 is a reply to message #564124] Tue, 21 August 2012 10:44 Go to previous messageGo to next message
anjulis
Messages: 32
Registered: August 2012
Member
I have a contract that has several categories with payments in each one. If there is a payment=0, then a category is considered closed. The contract is considered closed when all categories are closed.
RECORD_DATE               CATEGORY               PAYMENT                
------------------------- ---------------------- ---------------------- 
01-JAN-07                 1                      5                      
01-JAN-07                 2                      5                      
01-JAN-07                 3                      5                      
01-MAY-07                 2                      0                      
01-SEP-07                 1                      0                      
01-NOV-07                 3                      0                      

01-FEB-08                 1                      5                      
01-FEB-08                 2                      5                      
01-NOV-08                 1                      0                      

01-FEB-09                 1                      5                      
01-FEB-09                 2                      5                      
01-NOV-09                 1                      0                      
01-NOV-09                 2                      0                      

I need a SQL that will return the number of months within the year that contract was open. In the example above I need to get the result (only numbers, I just added the explanation here to be clear):

2007 10 -- last category closed on 11/01
2008 11 -- contract was open on 02/01 and never closed
2009 9 -- contract was open on 02/01 and closed on 11/01

Here is the SQL for the initial data
with Contracts as (
 select TO_DATE('01/01/07','mm/dd/yy') record_date, 1 category, 5 payment from dual union all
 select TO_DATE('01/01/07','mm/dd/yy') record_date, 2 category, 5 payment from dual union all
 select TO_DATE('01/01/07','mm/dd/yy') record_date, 3 category, 5 payment from dual union all
 select TO_DATE('05/01/07','mm/dd/yy') record_date, 2 category, 0 payment from dual union all
 select TO_DATE('09/01/07','mm/dd/yy') record_date, 1 category, 0 payment from dual union all
 select TO_DATE('11/01/07','mm/dd/yy') record_date, 3 category, 0 payment from dual union all
 select TO_DATE('02/01/08','mm/dd/yy') record_date, 1 category, 5 payment from dual union all
 select TO_DATE('02/01/08','mm/dd/yy') record_date, 2 category, 5 payment from dual union all
 select TO_DATE('11/01/08','mm/dd/yy') record_date, 1 category, 0 payment from dual union all
 select TO_DATE('02/01/09','mm/dd/yy') record_date, 1 category, 5 payment from dual union all
 select TO_DATE('02/01/09','mm/dd/yy') record_date, 2 category, 5 payment from dual union all
 select TO_DATE('11/01/09','mm/dd/yy') record_date, 1 category, 0 payment from dual union all
 select TO_DATE('11/01/09','mm/dd/yy') record_date, 2 category, 0 payment from dual 
)
Re: a number of months the contract was open [message #564146 is a reply to message #564138] Tue, 21 August 2012 11:37 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with Contracts as (
  2   select TO_DATE('01/01/07','mm/dd/yy') record_date, 1 category, 5 payment from dual union all
  3   select TO_DATE('01/01/07','mm/dd/yy') record_date, 2 category, 5 payment from dual union all
  4   select TO_DATE('01/01/07','mm/dd/yy') record_date, 3 category, 5 payment from dual union all
  5   select TO_DATE('05/01/07','mm/dd/yy') record_date, 2 category, 0 payment from dual union all
  6   select TO_DATE('09/01/07','mm/dd/yy') record_date, 1 category, 0 payment from dual union all
  7   select TO_DATE('11/01/07','mm/dd/yy') record_date, 3 category, 0 payment from dual union all
  8   select TO_DATE('02/01/08','mm/dd/yy') record_date, 1 category, 5 payment from dual union all
  9   select TO_DATE('02/01/08','mm/dd/yy') record_date, 2 category, 5 payment from dual union all
 10   select TO_DATE('11/01/08','mm/dd/yy') record_date, 1 category, 0 payment from dual union all
 11   select TO_DATE('02/01/09','mm/dd/yy') record_date, 1 category, 5 payment from dual union all
 12   select TO_DATE('02/01/09','mm/dd/yy') record_date, 2 category, 5 payment from dual union all
 13   select TO_DATE('11/01/09','mm/dd/yy') record_date, 1 category, 0 payment from dual union all
 14   select TO_DATE('11/01/09','mm/dd/yy') record_date, 2 category, 0 payment from dual 
 15   ),
 16   data as (
 17    select record_date, extract(year from record_date) cur_year, category, 
 18           last_value (payment) 
 19             over (partition by extract(year from record_date), category
 20                   order by record_date
 21                   rows between unbounded preceding and unbounded following)
 22             last_payment
 23    from contracts
 24   )
 25  select cur_year, 
 26         round(months_between (decode(max(last_payment), 
 27                                      0, max(record_date), 
 28                                         add_months(trunc(to_date(cur_year,'YYYY'),'YEAR'),12)),
 29                               min(record_date))) val
 30  from data
 31  group by cur_year
 32  order by cur_year
 33  /
  CUR_YEAR        VAL
---------- ----------
      2007         10
      2008         11
      2009          9

3 rows selected.

Regards
Michel

[Updated on: Tue, 21 August 2012 11:38]

Report message to a moderator

Re: a number of months the contract was open [message #564168 is a reply to message #564146] Tue, 21 August 2012 14:49 Go to previous messageGo to next message
anjulis
Messages: 32
Registered: August 2012
Member
Michael, thanks a lot. It works perfectly. There is only one problem: the table on which I run it has over a million rows, so it takes a very long time to run the first part of the query. Is there any way to to change something in the query so it runs faster?
Optimizing query [message #564180 is a reply to message #564124] Tue, 21 August 2012 22:58 Go to previous messageGo to next message
anjulis
Messages: 32
Registered: August 2012
Member
I am running a query:

select contract, record_date, extract(year from record_date) cur_year, 
       last_value (guarantee) 
              over (partition by extract(year from record_date), contract
                    order by record_date
                    rows between unbounded preceding and unbounded following)
              last_payment
     from ia_mortality


The query gives correct results on a small subset of data. The problem is that my table has over a million rows, the query runs for a very long time and then gives an error message, saying that there is not enough dataspace.

Is there a way to optimize or change it somehow so it gives me the same results but faster?
Re: Optimizing query [message #564181 is a reply to message #564180] Tue, 21 August 2012 22:59 Go to previous messageGo to next message
BlackSwan
Messages: 23159
Registered: January 2009
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Optimizing query [message #564182 is a reply to message #564181] Tue, 21 August 2012 23:40 Go to previous messageGo to next message
anjulis
Messages: 32
Registered: August 2012
Member
The query is never able to finish, so to produce PLAN I added "where rownum<5000". With this adjustment it runs very fast.

1. DDL:
CREATE TABLE "ACTVAL"."IA_MORTALITY"
  (
    "CONTRACT"       VARCHAR2(10 BYTE),
    "ANNUITANT"      NUMBER,
    "RATE_BASIS"     VARCHAR2(2 BYTE),
    "TYPE_INDICATOR" VARCHAR2(1 BYTE),
    "RECORD_TYPE"    VARCHAR2(1 BYTE),
    "RECORD_DATE" DATE,
    "RECORD_SOURCE" VARCHAR2(1 BYTE),
    "ACTIVE_IND"    VARCHAR2(1 BYTE),
    "ACTIVE_IND_DATE" DATE,
    "REVERSAL_FLAG" VARCHAR2(1 BYTE),
    "REVERSAL_FLAG_DATE" DATE,
    "REVERSAL_SOURCE" VARCHAR2(1 BYTE),
    "GUARANTEE"       NUMBER,
    "AAR"             NUMBER
  )
  SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )
  TABLESPACE "ACTVAL" ;
CREATE UNIQUE INDEX "ACTVAL"."IA_MORTALITY_PK" ON "ACTVAL"."IA_MORTALITY"
  (
    "CONTRACT", "ANNUITANT", "RATE_BASIS", "TYPE_INDICATOR", "RECORD_DATE", "RECORD_SOURCE"
  )
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE
  (
    INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 
FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )
  TABLESPACE "ACTVAL" ;
  CREATE INDEX "ACTVAL"."IDX_IA_MORTALITY_CONTRACT_RD" ON "ACTVAL"."IA_MORTALITY"
    (
      "CONTRACT",
      "RECORD_DATE"
    )
    PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE
    (
      INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
    )
    TABLESPACE "ACTVAL" ;


2. Explain plan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 2013588408    

--------------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |  4999 |   146K|       |   771K  (4)| 03:00:00 
|   1 |  WINDOW SORT        |              |  4999 |   146K|  5536M|   771K  (4)| 03:00:00 
|*  2 |   COUNT STOPKEY     |              |       |       |       |            |          
|   3 |    TABLE ACCESS FULL| IA_MORTALITY |   120M|  3446M|       |   140K  (5)| 00:32:51 
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM<5000)

3. When I try to set SQL_TRACE ON I get
SQL Error: ORA-01031: insufficient privileges
01031. 00000 - "insufficient privileges"
*Cause: An attempt was made to change the current username or password
without the appropriate privilege. This error also occurs if
attempting to install a database without the necessary operating
system privileges.

Does this information help? The query is pretty simple, uses only one table, has only one partition in it.

[Edit MC: trim the lines]

[Updated on: Wed, 22 August 2012 01:23] by Moderator

Report message to a moderator

Re: Optimizing query [message #564183 is a reply to message #564180] Tue, 21 August 2012 23:41 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
gives an error message, saying that there is not enough dataspace


Copy and paste your SQL*Plus session.

Regards
Michel
Re: Optimizing query [message #564184 is a reply to message #564182] Tue, 21 August 2012 23:45 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In your test case, do not inclde the schema and tablespace names or storage parameters we have not the same ones.
Keep your lines of code in 80 character width: no more than 80 characters on each line.
Always set TRIMOUT and TRIMSPOOL to ON in SQL*Plus.

Regards
Michel

[Updated on: Tue, 21 August 2012 23:49]

Report message to a moderator

Re: Optimizing query [message #564186 is a reply to message #564183] Tue, 21 August 2012 23:46 Go to previous messageGo to next message
BlackSwan
Messages: 23159
Registered: January 2009
Senior Member
21:45:21 SQL> set autotrace  trace exp
21:45:45 SQL> select sysdate from dual;

Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation	 | Name | Rows	| Cost (%CPU)| Time	|
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |	|     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL	 |	|     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

21:46:00 SQL> 

Re: Optimizing query [message #564188 is a reply to message #564184] Tue, 21 August 2012 23:49 Go to previous messageGo to next message
anjulis
Messages: 32
Registered: August 2012
Member
Michel, I use SQL Developer. What do you want me to paste? It just shows the query (in the top message) and, after about an hour - an error message at the bottom.
Re: Optimizing query [message #564189 is a reply to message #564188] Tue, 21 August 2012 23:49 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And do you think the ACTUAL error message is not important?

Regards
Michel

[Updated on: Tue, 21 August 2012 23:50]

Report message to a moderator

Re: Optimizing query [message #564190 is a reply to message #564188] Tue, 21 August 2012 23:51 Go to previous messageGo to next message
anjulis
Messages: 32
Registered: August 2012
Member
Blackswan, I get "Unknown Autotrace option no action taken."
Re: Optimizing query [message #564191 is a reply to message #564190] Tue, 21 August 2012 23:54 Go to previous messageGo to next message
BlackSwan
Messages: 23159
Registered: January 2009
Senior Member
>Blackswan, I get "Unknown Autotrace option no action taken."

it works when you use sqlplus that is delivered with *EVERY* Oracle installation!
Re: Optimizing query [message #564192 is a reply to message #564190] Tue, 21 August 2012 23:57 Go to previous messageGo to next message
anjulis
Messages: 32
Registered: August 2012
Member
Michel, I don't know whether the exact wording of an error message is important. It seems to be a standard message, saying that there is not enough dataspace and suggesting to add another dataspace file by using alter table add dataspace.
To reproduce it I would need to run the query again and wait for an hour.
Regardless of that message, I would really like to get the query running faster, if possible. one hour is too long.

It is the same query you wrote for me in my other topic (calculating months for a contract). (first part of it)
Re: Optimizing query [message #564193 is a reply to message #564192] Wed, 22 August 2012 00:04 Go to previous messageGo to next message
anjulis
Messages: 32
Registered: August 2012
Member
Backswan, here it is from SQLPlus

Execution Plan
----------------------------------------------------------
Plan hash value: 2013588408

--------------------------------------------------------------------------------
------------

| Id  | Operation           | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)
| Time     |

--------------------------------------------------------------------------------
------------

|   0 | SELECT STATEMENT    |              |  4999 |   146K|       |   771K  (4)
| 03:00:00 |

|   1 |  WINDOW SORT        |              |  4999 |   146K|  5536M|   771K  (4)
| 03:00:00 |

|*  2 |   COUNT STOPKEY     |              |       |       |       |
|          |

|   3 |    TABLE ACCESS FULL| IA_MORTALITY |   120M|  3446M|       |   140K  (5)
| 00:32:51 |

--------------------------------------------------------------------------------
------------


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

   2 - filter(ROWNUM<5000)

Re: Optimizing query [message #564194 is a reply to message #564193] Wed, 22 August 2012 00:26 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Yes, the actual message is important as there is NO error saying "not enough dataspace"
2/ You cannot make this query faster
3/ What it should is to search for another way to do what you asked than the one I gave in your previous topic.
Did you try to search another way?

Regards
Michel
Re: Optimizing query [message #564198 is a reply to message #564194] Wed, 22 August 2012 01:12 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As the question is closely related to the previous topic, I merge the 2 topics.

Regards
Michel
Re: a number of months the contract was open [message #564201 is a reply to message #564168] Wed, 22 August 2012 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Create an index on (category, extract(year from record_date), record_date) and try the following query:
SQL> with Contracts as (
  2   select TO_DATE('01/01/07','mm/dd/yy') record_date, 1 category, 5 payment from dual union all
  3   select TO_DATE('01/01/07','mm/dd/yy') record_date, 2 category, 5 payment from dual union all
  4   select TO_DATE('01/01/07','mm/dd/yy') record_date, 3 category, 5 payment from dual union all
  5   select TO_DATE('05/01/07','mm/dd/yy') record_date, 2 category, 0 payment from dual union all
  6   select TO_DATE('09/01/07','mm/dd/yy') record_date, 1 category, 0 payment from dual union all
  7   select TO_DATE('11/01/07','mm/dd/yy') record_date, 3 category, 0 payment from dual union all
  8   select TO_DATE('02/01/08','mm/dd/yy') record_date, 1 category, 5 payment from dual union all
  9   select TO_DATE('02/01/08','mm/dd/yy') record_date, 2 category, 5 payment from dual union all
 10   select TO_DATE('11/01/08','mm/dd/yy') record_date, 1 category, 0 payment from dual union all
 11   select TO_DATE('02/01/09','mm/dd/yy') record_date, 1 category, 5 payment from dual union all
 12   select TO_DATE('02/01/09','mm/dd/yy') record_date, 2 category, 5 payment from dual union all
 13   select TO_DATE('11/01/09','mm/dd/yy') record_date, 1 category, 0 payment from dual union all
 14   select TO_DATE('11/01/09','mm/dd/yy') record_date, 2 category, 0 payment from dual 
 15   ),
 16   last_values as (
 17     select extract(year from record_date) cur_year, category, payment last_payment
 18     from contracts
 19     where (category, extract(year from record_date), record_date) in 
 20           (select category, extract(year from record_date), max(record_date) 
 21            from contracts group by category, extract(year from record_date))
 22   )
 23  select l.cur_year,
 24         round(months_between (decode(max(l.last_payment), 
 25                                      0, max(c.record_date), 
 26                                         add_months(trunc(to_date(l.cur_year,'YYYY'),'YEAR'),12)),
 27                               min(c.record_date))) val
 28  from contracts c, last_values l
 29  where l.category = c.category
 30    and l.cur_year = extract(year from c.record_date)
 31  group by l.cur_year
 32  order by l.cur_year
 33  /
  CUR_YEAR        VAL
---------- ----------
      2007         10
      2008         11
      2009          9

Tell us the result (and post the execution plan triming the output).

Regards
Michel

[Updated on: Wed, 22 August 2012 01:22]

Report message to a moderator

Re: a number of months the contract was open [message #564262 is a reply to message #564201] Wed, 22 August 2012 06:55 Go to previous messageGo to next message
anjulis
Messages: 32
Registered: August 2012
Member
Michel, here is the exact error message:

SQL Error: ORA-01652: unable to extend temp segment by 4096 in tablespace ACTVAL
01652. 00000 - "unable to extend temp segment by %s in tablespace %s"
*Cause: Failed to allocate an extent of the required number of blocks for
a temporary segment in the tablespace indicated.
*Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
files to the tablespace indicated.

As of trying to do it different way: I have tried to extract just the records with latest values, using subquery "WHERE record_date = (select max(record_date), from ...)" for later joining it with the whole table but got the same result.
Re: a number of months the contract was open [message #564278 is a reply to message #564262] Wed, 22 August 2012 08:31 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you add the index before? It is the key of this query.
Post the execution plan.
What is your temporary tablespace size?

Regards
Michel
Re: a number of months the contract was open [message #564325 is a reply to message #564278] Wed, 22 August 2012 11:09 Go to previous messageGo to next message
anjulis
Messages: 32
Registered: August 2012
Member
Michel,

You second version, with index, took only 4 minutes to run. It is perfectly acceptable. Thank you very much. Interesting that using the first version (with partition) took so much longer.
I think, this problem is solved.
Re: a number of months the contract was open [message #564327 is a reply to message #564325] Wed, 22 August 2012 11:14 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The first solution is mmore efficient in some cases and not in others and it cannot take profit of indexes.
The second version is the very old one to create SQL for such case and it can take profit of appropriate index, this is why I asked you to create a specific index.

If you post the execution plan, we maybe can find a faster query (I do not ask something just for pleasure).

Regards
Michel

[Updated on: Wed, 22 August 2012 11:16]

Report message to a moderator

Re: a number of months the contract was open [message #564334 is a reply to message #564327] Wed, 22 August 2012 13:08 Go to previous messageGo to next message
anjulis
Messages: 32
Registered: August 2012
Member
Here is the execution plan for the first part of the query (the one inside with)

SQL> set autotrace  trace exp
SQL> select extract(year from record_date) cur_year, contract, annuitant, rate_BASIS, type_indicator
, record_date, guarantee last_payment
  2       from ia_mortality
  3       where (contract, annuitant, rate_BASIS,  extract(year from record_date), record_date) in 
  4             (select contract, annuitant, rate_BASIS, extract(year from record_date), max(record_
date) 
  5              from ia_mortality group by contract, annuitant, rate_BASIS, extract(year from recor
d_date))
  6  ;

Execution Plan
----------------------------------------------------------
Plan hash value: 1945866263

--------------------------------------------------------------------------------
-------------------

| Id  | Operation               | Name            | Rows  | Bytes |TempSpc| Cost
 (%CPU)| Time     |

--------------------------------------------------------------------------------
-------------------

|   0 | SELECT STATEMENT        |                 |   112 |  9296 |       |  148
5K  (3)| 05:46:44 |

|*  1 |  FILTER                 |                 |       |       |       |
       |          |

|   2 |   HASH GROUP BY         |                 |   112 |  9296 |    11G|  148
5K  (3)| 05:46:44 |

|*  3 |    HASH JOIN            |                 |   120M|  9536M|  5515M|   76
7K  (2)| 02:59:10 |

|   4 |     INDEX FAST FULL SCAN| IA_MORTALITY_PK |   120M|  4136M|       | 9653
8   (3)| 00:22:32 |

|   5 |     TABLE ACCESS FULL   | IA_MORTALITY    |   120M|  5400M|       |   14
0K  (5)| 00:32:51 |

--------------------------------------------------------------------------------
-------------------


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

   1 - filter("RECORD_DATE"=MAX("RECORD_DATE"))
   3 - access("CONTRACT"="CONTRACT" AND "ANNUITANT"="ANNUITANT" AND
              "RATE_BASIS"="RATE_BASIS" AND EXTRACT(YEAR FROM
              INTERNAL_FUNCTION("RECORD_DATE"))=EXTRACT(YEAR FROM INTERNAL_FUNCT
ION("RECORD_DATE")))

Here is for the whole query:
explain plan for
with  
  last_values as (

    select extract(year from record_date) cur_year, contract, annuitant, rate_BASIS, type_indicator, record_date, guarantee last_payment
     from ia_mortality
     where (contract, annuitant, rate_BASIS,  extract(year from record_date), record_date) in 
           (select contract, annuitant, rate_BASIS, extract(year from record_date), max(record_date) 
            from ia_mortality group by contract, annuitant, rate_BASIS, extract(year from record_date))
   )
  
  select ssn, gender, dod, contract, annuitant, cur_year,
          decode (extract(year from DOD),cur_year,1,0) death,
          case when DOD is null or DOD = to_date('01/01/0001','mm/dd/yy') then 1
               else round(months_between (decode(max(last_payment), 0, max(record_date), 
                                                 add_months(trunc(to_date(cur_year,'YYYY'),'YEAR'),12)),
                                           min(record_date))/12,2) 
          end exprosure
   from last_values d, participants p, ia_contracts c
   where p.ssn = c.annt1_ssn and d.contract=c.contract_number and d.annuitant=1
  group by ssn, gender, dod, contract, annuitant, cur_year
  
  union all
  
  select ssn, gender, dod, contract, annuitant, cur_year,
          decode (extract(year from DOD),cur_year,1,0) death,
          case when DOD is null or DOD = to_date('01/01/0001','mm/dd/yy') then 1
               else round(months_between (decode(max(last_payment), 0, max(record_date), 
                                                 add_months(trunc(to_date(cur_year,'YYYY'),'YEAR'),12)),
                                           min(record_date))/12,2) 
          end exprosure
   from last_values d, participants p, ia_contracts c
   where p.ssn = c.annt2_ssn and d.contract=c.contract_number and d.annuitant=2
  group by ssn, gender, dod, contract, annuitant, cur_year
  order by contract, annuitant, cur_year


------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                           
| Id  | Operation                  | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                                                                                                                                                           
------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                           
|   0 | SELECT STATEMENT           |                             | 63256 |  5229K|       | 13289   (3)| 00:03:07 |                                                                                                                                                                                           
|   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |       |            |          |                                                                                                                                                                                           
|   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6675_439805BA |       |       |       |            |          |                                                                                                                                                                                           
|*  3 |    HASH JOIN SEMI          |                             | 42037 |  3612K|  6319M|  1564K  (3)| 06:05:09 |                                                                                                                                                                                           
|   4 |     TABLE ACCESS FULL      | IA_MORTALITY                |   120M|  4940M|       |   140K  (5)| 00:32:51 |                                                                                                                                                                                           
|   5 |     VIEW                   | VW_NSO_1                    |   120M|  5170M|       |   869K  (3)| 03:22:47 |                                                                                                                                                                                           
|   6 |      SORT GROUP BY         |                             |   120M|  4136M|  5995M|   869K  (3)| 03:22:47 |                                                                                                                                                                                           
|   7 |       TABLE ACCESS FULL    | IA_MORTALITY                |   120M|  4136M|       |   138K  (4)| 00:32:21 |                                                                                                                                                                                           
|   8 |   SORT ORDER BY            |                             | 63256 |  5229K|  5744K| 12525  (48)| 00:02:56 |                                                                                                                                                                                           
|   9 |    UNION-ALL               |                             |       |       |       |            |          |                                                                                                                                                                                           
|  10 |     HASH GROUP BY          |                             | 42037 |  3530K|  3984K|  6746   (3)| 00:01:35 |                                                                                                                                                                                           
|* 11 |      HASH JOIN             |                             | 42037 |  3530K|  3216K|  6218   (3)| 00:01:28 |                                                                                                                                                                                           
|* 12 |       HASH JOIN            |                             | 42037 |  2709K|  2432K|  2747   (3)| 00:00:39 |                                                                                                                                                                                           
|* 13 |        VIEW                |                             | 42037 |  1929K|       |    81   (3)| 00:00:02 |                                                                                                                                                                                           
|  14 |         TABLE ACCESS FULL  | SYS_TEMP_0FD9D6675_439805BA | 42037 |  1765K|       |    81   (3)| 00:00:02 |                                                                                                                                                                                           
|  15 |        TABLE ACCESS FULL   | IA_CONTRACTS                |   879K|    15M|       |  1430   (3)| 00:00:21 |                                                                                                                                                                                           
|  16 |       TABLE ACCESS FULL    | PARTICIPANTS                |  1421K|    27M|       |  1445   (4)| 00:00:21 |                                                                                                                                                                                           
|  17 |     HASH GROUP BY          |                             | 21219 |  1699K|  1936K|  5779   (3)| 00:01:21 |                                                                                                                                                                                           
|* 18 |      HASH JOIN             |                             | 21219 |  1699K|  1536K|  5520   (3)| 00:01:18 |                                                                                                                                                                                           
|* 19 |       HASH JOIN            |                             | 21219 |  1284K|  2432K|  2120   (3)| 00:00:30 |                                                                                                                                                                                           
|* 20 |        VIEW                |                             | 42037 |  1929K|       |    81   (3)| 00:00:02 |                                                                                                                                                                                           
|  21 |         TABLE ACCESS FULL  | SYS_TEMP_0FD9D6675_439805BA | 42037 |  1765K|       |    81   (3)| 00:00:02 |                                                                                                                                                                                           
|* 22 |        TABLE ACCESS FULL   | IA_CONTRACTS                |   443K|  6503K|       |  1436   (3)| 00:00:21 |                                                                                                                                                                                           
|  23 |       TABLE ACCESS FULL    | PARTICIPANTS                |  1421K|    27M|       |  1445   (4)| 00:00:21 |                                                                                                                                                                                           
------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                           
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
---------------------------------------------------                                                                                                                                                                                                                                                          
                                                                                                                                                                                                                                                                                                             
   3 - access("CONTRACT"="CONTRACT" AND "ANNUITANT"="ANNUITANT" AND "RATE_BASIS"="RATE_BASIS" AND                                                                                                                                                                                                            
              EXTRACT(YEAR FROM INTERNAL_FUNCTION("RECORD_DATE"))="EXTRACT(YEARFROMRECORD_DATE)" AND                                                                                                                                                                                                         
              "RECORD_DATE"="MAX(RECORD_DATE)")                                                                                                                                                                                                                                                              
  11 - access("P"."SSN"="C"."ANNT1_SSN")                                                                                                                                                                                                                                                                     
  12 - access("D"."CONTRACT"="C"."CONTRACT_NUMBER")                                                                                                                                                                                                                                                          
  13 - filter("D"."ANNUITANT"=1)                                                                                                                                                                                                                                                                             
  18 - access("P"."SSN"="C"."ANNT2_SSN")                                                                                                                                                                                                                                                                     
  19 - access("D"."CONTRACT"="C"."CONTRACT_NUMBER")                                                                                                                                                                                                                                                          
  20 - filter("D"."ANNUITANT"=2)                                                                                                                                                                                                                                                                             
  22 - filter("C"."ANNT2_SSN" IS NOT NULL)                                                 
Re: a number of months the contract was open [message #564341 is a reply to message #564334] Wed, 22 August 2012 17:04 Go to previous messageGo to next message
anjulis
Messages: 32
Registered: August 2012
Member
Michel, I guess I was wrong to say that it runs fast now. It does run for quite a while. I have simplified it but it still takes a long time to run,
even the first part.
(The main table ia_mortality has a huge number of records).
Here is the execution plan and the query:

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 
Plan hash value: 2811324640                                                                                                                                                                                                                                                                                  
                                                                                                                                                                                                                                                                                                             
-----------------------------------------------------------------------------------------------------                                                                                                                                                                                                        
| Id  | Operation                 | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                                                                                                                                                                        
-----------------------------------------------------------------------------------------------------                                                                                                                                                                                                        
|   0 | SELECT STATEMENT          |                 | 25680 |  2081K|       |  1733K  (2)| 06:44:23 |                                                                                                                                                                                                        
|   1 |  HASH GROUP BY            |                 | 25680 |  2081K|       |  1733K  (2)| 06:44:23 |                                                                                                                                                                                                        
|   2 |   VIEW                    | VM_NWVW_2       | 25680 |  2081K|       |  1733K  (2)| 06:44:23 |                                                                                                                                                                                                        
|*  3 |    FILTER                 |                 |       |       |       |            |          |                                                                                                                                                                                                        
|   4 |     HASH GROUP BY         |                 | 25680 |  3836K|    11G|  1733K  (2)| 06:44:23 |                                                                                                                                                                                                        
|*  5 |      HASH JOIN            |                 |    73M|    10G|  3479M|   971K  (2)| 03:46:46 |                                                                                                                                                                                                        
|*  6 |       INDEX FAST FULL SCAN| IA_MORTALITY_PK |    76M|  2609M|       | 97284   (3)| 00:22:42 |                                                                                                                                                                                                        
|*  7 |       HASH JOIN           |                 |    73M|  8212M|    68M|   335K  (3)| 01:18:20 |                                                                                                                                                                                                        
|*  8 |        HASH JOIN          |                 |   879K|    58M|    38M|  7357   (2)| 00:01:44 |                                                                                                                                                                                                        
|   9 |         TABLE ACCESS FULL | IA_CONTRACTS    |   879K|    28M|       |  1430   (3)| 00:00:21 |                                                                                                                                                                                                        
|  10 |         TABLE ACCESS FULL | PARTICIPANTS    |  1421K|    48M|       |  1445   (4)| 00:00:21 |                                                                                                                                                                                                        
|* 11 |        TABLE ACCESS FULL  | IA_MORTALITY    |    76M|  3407M|       |   140K  (5)| 00:32:50 |                                                                                                                                                                                                        
-----------------------------------------------------------------------------------------------------                                                                                                                                                                                                        
                                                                                                                                                                                                                                                                                                             
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
---------------------------------------------------                                                                                                                                                                                                                                                          
                                                                                                                                                                                                                                                                                                             
   3 - filter("RECORD_DATE"=MAX("RECORD_DATE"))                                                                                                                                                                                                                                                              
   5 - access("CONTRACT"="CONTRACT" AND "ANNUITANT"="ANNUITANT" AND                                                                                                                                                                                                                                          
              "RATE_BASIS"="RATE_BASIS" AND EXTRACT(YEAR FROM                                                                                                                                                                                                                                                
              INTERNAL_FUNCTION("RECORD_DATE"))=EXTRACT(YEAR FROM INTERNAL_FUNCTION("RECORD_DATE")))                                                                                                                                                                                                         
   6 - filter("ANNUITANT"=1)                                                                                                                                                                                                                                                                                 
   7 - access("CONTRACT"="C"."CONTRACT_NUMBER")                                                                                                                                                                                                                                                              
   8 - access("P"."SSN"="C"."ANNT1_SSN")                                                                                                                                                                                                                                                                     
  11 - filter("ANNUITANT"=1)    
with  
  last_values as -- get latest value for each contract+annt+year+rate_basis
  (

    select extract(year from record_date) cur_year, contract, annuitant, rate_BASIS, type_indicator, record_date, guarantee last_payment
     from ia_mortality
     where (contract, annuitant, rate_BASIS,  extract(year from record_date), record_date) in 
           (select contract, annuitant, rate_BASIS, extract(year from record_date), max(record_date) 
            from ia_mortality group by contract, annuitant, rate_BASIS, extract(year from record_date))
   )
  
  -- group on a RB level, for each RB get an exposure and death
     select ssn, gender, dob, dod, contract, type_indicator, origin_code, annuitant, cur_year, rate_basis,
          decode (extract(year from DOD),cur_year,1,0) death,
          case when DOD is null or DOD = to_date('01/01/0001','mm/dd/yy') then 1
               else round(months_between (decode(max(last_payment), 0, max(record_date), 
                                                 add_months(trunc(to_date(cur_year,'YYYY'),'YEAR'),12)),
                                           min(record_date))/12,2) 
          end exposure
    from last_values d, participants p, ia_contracts c
    where p.ssn = c.annt1_ssn and d.contract=c.contract_number and d.annuitant=1
   group by ssn, gender, dob, dod, contract, type_indicator, origin_code, annuitant, cur_year, rate_basis

The index is in place:
CREATE INDEX "ACTVAL"."IDX_IA_MORTALITY_CARYD" ON "ACTVAL"."IA_MORTALITY"
  (
    "CONTRACT",
    "ANNUITANT",
    "RATE_BASIS",
    EXTRACT(YEAR FROM "RECORD_DATE"),
    "RECORD_DATE"
  )

The main table with millions of rows is:

CREATE TABLE "ACTVAL"."IA_MORTALITY"
  (
    "CONTRACT"       VARCHAR2(10 BYTE),
    "ANNUITANT"      NUMBER,
    "RATE_BASIS"     VARCHAR2(2 BYTE),
    "TYPE_INDICATOR" VARCHAR2(1 BYTE),
    "RECORD_TYPE"    VARCHAR2(1 BYTE),
    "RECORD_DATE" DATE,
    "RECORD_SOURCE" VARCHAR2(1 BYTE),
    "ACTIVE_IND"    VARCHAR2(1 BYTE),
    "ACTIVE_IND_DATE" DATE,
    "REVERSAL_FLAG" VARCHAR2(1 BYTE),
    "REVERSAL_FLAG_DATE" DATE,
    "REVERSAL_SOURCE" VARCHAR2(1 BYTE),
    "GUARANTEE"       NUMBER,
    "AAR"             NUMBER
  )
 


Do you see anything in the plan that could be optimized?
Re: a number of months the contract was open [message #564342 is a reply to message #564341] Wed, 22 August 2012 17:05 Go to previous messageGo to next message
anjulis
Messages: 32
Registered: August 2012
Member
I noticed that the plan does not mention the index that I created at your suggestion. Could that be the problem? If yes, why could it be? The index is there.
Re: a number of months the contract was open [message #564343 is a reply to message #564342] Wed, 22 August 2012 17:27 Go to previous messageGo to next message
BlackSwan
Messages: 23159
Registered: January 2009
Senior Member
>where p.ssn = c.annt1_ssn and d.contract=c.contract_number and d.annuitant=1
why don't indexes exist for each of the columns above?
Re: a number of months the contract was open [message #564344 is a reply to message #564343] Wed, 22 August 2012 22:14 Go to previous messageGo to next message
anjulis
Messages: 32
Registered: August 2012
Member
There are indexes for each of those fields.
SSN is the PK for participants table (p),
CONTRACT_NUMBER is the PK for IA_CONTRACTS table (c),
there is an index for ANNT1_SSN as well.

The first query takes a long time as well, even with index.
Re: a number of months the contract was open [message #564345 is a reply to message #564344] Wed, 22 August 2012 22:48 Go to previous messageGo to next message
BlackSwan
Messages: 23159
Registered: January 2009
Senior Member
anjulis wrote on Wed, 22 August 2012 20:14
There are indexes for each of those fields.
SSN is the PK for participants table (p),
CONTRACT_NUMBER is the PK for IA_CONTRACTS table (c),
there is an index for ANNT1_SSN as well.


http://www.orafaq.com/forum/mv/msg/183557/564181/136107/#msg_564181
Re: a number of months the contract was open [message #564346 is a reply to message #564345] Wed, 22 August 2012 22:59 Go to previous message
anjulis
Messages: 32
Registered: August 2012
Member
here are ddls for all 3 tables. For some reason, according to the plan, indexes are not used.


CREATE TABLE "ACTVAL"."IA_MORTALITY"
  (
    "CONTRACT"       VARCHAR2(10 BYTE),
    "ANNUITANT"      NUMBER,
    "RATE_BASIS"     VARCHAR2(2 BYTE),
    "TYPE_INDICATOR" VARCHAR2(1 BYTE),
    "RECORD_TYPE"    VARCHAR2(1 BYTE),
    "RECORD_DATE" DATE,
    "RECORD_SOURCE" VARCHAR2(1 BYTE),
    "ACTIVE_IND"    VARCHAR2(1 BYTE),
    "ACTIVE_IND_DATE" DATE,
    "REVERSAL_FLAG" VARCHAR2(1 BYTE),
    "REVERSAL_FLAG_DATE" DATE,
    "REVERSAL_SOURCE" VARCHAR2(1 BYTE),
    "GUARANTEE"       NUMBER,
    "AAR"             NUMBER
  )
 
CREATE INDEX "ACTVAL"."IDX_IA_MORTALITY_CARYD" ON "ACTVAL"."IA_MORTALITY"
  (
    "CONTRACT",
    "ANNUITANT",
    "RATE_BASIS",
    EXTRACT(YEAR FROM "RECORD_DATE"),
    "RECORD_DATE"
  )

CREATE UNIQUE INDEX "ACTVAL"."IA_MORTALITY_PK" ON "ACTVAL"."IA_MORTALITY"
  (
    "CONTRACT", "ANNUITANT", "RATE_BASIS", "TYPE_INDICATOR", "RECORD_DATE", "RECORD_SOURCE"
  )
  CREATE INDEX "ACTVAL"."IDX_IA_MORTALITY_CONTRACT_RD" ON "ACTVAL"."IA_MORTALITY"
    (
      "CONTRACT",
      "RECORD_DATE"
    )

-----------
CREATE TABLE "ACTVAL"."IA_CONTRACTS"
  (
    "CONTRACT_NUMBER" VARCHAR2(10 BYTE),
    "ISSUE_DATE" DATE,
    "OPTION_CODE" NUMBER NOT NULL ENABLE,
    "ORIGIN_CODE" NUMBER NOT NULL ENABLE,
    "ANNT1_SSN"   VARCHAR2(9 BYTE),
    "ANNT1_REPORTED_DOD" DATE,
    "ANNT2_SSN" VARCHAR2(9 BYTE),
    "ANNT2_REPORTED_DOD" DATE,
    "PMT_MODE" NUMBER NOT NULL ENABLE,
    CONSTRAINT "IA_CONTRACTS_PK" PRIMARY KEY ("CONTRACT_NUMBER") USING INDEX 
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 
MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT 
FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "ACTVAL" ENABLE,

    CONSTRAINT "ANNT1_SSN_FK" FOREIGN KEY ("ANNT1_SSN") REFERENCES "ACTVAL"."PARTICIPANTS" ("SSN") ENABLE,
    CONSTRAINT "ANNT2_SSN_FK" FOREIGN KEY ("ANNT2_SSN") REFERENCES "ACTVAL"."PARTICIPANTS" ("SSN") ENABLE
  )
CREATE UNIQUE INDEX "ACTVAL"."IA_CONTRACTS_PK" ON "ACTVAL"."IA_CONTRACTS"
  (
    "CONTRACT_NUMBER"
  )
  CREATE INDEX "ACTVAL"."IDX_ANNT2_SSN" ON "ACTVAL"."IA_CONTRACTS"
    (
      "ANNT2_SSN"
    )
  CREATE INDEX "ACTVAL"."IDX_ANNT1_SSN" ON "ACTVAL"."IA_CONTRACTS"
    (
      "ANNT1_SSN"
    )

-------

CREATE TABLE "ACTVAL"."PARTICIPANTS"
  (
    "SSN"    VARCHAR2(9 BYTE),
    "PIN"    NUMBER,
    "GENDER" VARCHAR2(1 BYTE),
    "DOB" DATE,
    "DOD" DATE,
    "XREF" VARCHAR2(9 BYTE),
    CONSTRAINT "PARTICIPANTS_PK" PRIMARY KEY ("SSN") USING INDEX PCTFREE 10 
INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 81920 NEXT 1048576 
MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) 
TABLESPACE "ACTVAL" ENABLE
  )
Previous Topic: SQL that gives me Jobs due tomorrow?
Next Topic: Previous record for multiple combination
Goto Forum:
  


Current Time: Sun Dec 21 22:25:15 CST 2014

Total time taken to generate the page: 0.07997 seconds