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  |
 |
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 (3 Merged) [message #564129 is a reply to message #564124] |
Tue, 21 August 2012 09:40  |
 |
Michel Cadot
Messages: 54226
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
From your previous topic:
BlackSwan wrote on Thu, 16 August 2012 02:25Please 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:49Welcome 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 |
|
|
|
|
|
| a number of months the contract was open [message #564138 is a reply to message #564124] |
Tue, 21 August 2012 10:44 |
|
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 |
|
Michel Cadot
Messages: 54226
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: Optimizing query [message #564182 is a reply to message #564181] |
Tue, 21 August 2012 23:40 |
|
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 #564184 is a reply to message #564182] |
Tue, 21 August 2012 23:45 |
|
Michel Cadot
Messages: 54226
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: a number of months the contract was open [message #564201 is a reply to message #564168] |
Wed, 22 August 2012 01:20 |
|
Michel Cadot
Messages: 54226
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 #564327 is a reply to message #564325] |
Wed, 22 August 2012 11:14 |
|
Michel Cadot
Messages: 54226
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 |
|
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 |
|
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 #564346 is a reply to message #564345] |
Wed, 22 August 2012 22:59 |
|
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
)
|
|
|
|
Goto Forum:
Current Time: Fri May 24 06:36:11 CDT 2013
Total time taken to generate the page: 0.11597 seconds
|
] 
Members
Search
Help
Register
Login
Home
