need suggestion on creating a view on top of a data set, where the last record for a claim represents the current state

Alaska Cowboy
Alaska Cowboy used Ask the Experts™
on
I have claims transactions where there might be adjustments, but the last record represents the current state.

I thought about putting the last record into a separate table, but wondered about creating a view of some sort.

in the attached, the green columns (G-M) represent the business key, so I have to recognize all transactions that match this key, then present the last one received.

Can I create a view that would show the last record ? If so, this seems like it would be a performance nightmare. Record count is about 1,000,000 per year, so far ~7M records.

It seems like from a data integrity point of view, it's preferable to use the view and not have to monkey with another table, but I guess the performance is the driver.

The logic for this is not simple, but

create  view my_tables_view as
select field1, field2, field3, field4, field5, field6, max(seq_no) as max_seqno
from my_table

create view_2 my_current_records as
select * from my_table
where seq_no in (select max_seqno from my_tables_view)

then the user just happily runs a query against "my_current_records"

but wouldn't this be a performance nightmare ?

The alternative is have a table with current records, and then update it when a newer transaction arrives. That seems clean but wanted to get an experts opinion . . .

multiple-transactions.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Didn't look at the spreadsheet.

Try with the row_number data warehouse window function and see how it might work for you:

select field1, field2, field3, field4, field5, field6
from (
select field1, field2, field3, field4, field5, field6, row_number() over(partition by field1, field2, field3, field4, field5, field6 order by seq_no desc) myrownum
)
where myrownum=1;

Author

Commented:
slightvw, ok, good, I've seen that before but never worked with it, excellent.

what about performance ?

Author

Commented:
just to be sure, it needs to retrieve field_n, so I assume this will work:

select field1, field2, field3, field4, field5, field6 .... field_n
from (
select field1, field2, field3, field4, field5, field6, ... , field_n, row_number() over(partition by field1, field2, field3, field4, field5, field6 order by seq_no desc) myrownum
)
where myrownum=1;
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>>what about performance ?

The window functions were build to against data warehouses and tend to perform better than other raw SQL statements.

Will they out perform some denormalized table structure like you talked about? probably not but you need to take into account the processing to maintain the 'last row' table method.

Also, if you are running 11g there is a new feature called result caching.  This can GREATLY enhance long running query performance in a warehouse type enviornment.

>>so I assume this will work:

It should.

Author

Commented:
I tried this without field_n, but got an error, "FROM keyword not found where expected".

Must this be set up with privileges by the DBA ?

select pe.hicn, pe.dos, pe.servc_prov_id, pe.servc_prov_id_qlfr, pe.prsctn_servc_refnc_no, pe.fill_nbr, pe.dspsg_stus_cd, pe.pde_det_skey
from
(
select pe.hicn, pe.dos, pe.servc_prov_id, pe.servc_prov_id_qlfr, pe.prsctn_servc_refnc_no, pe.fill_nbr, pe.dspsg_stus_cd, pe.pde_det_skey, row_number()
  over (partition by pe.hicn, pe.dos, pe.servc_prov_id, pe.servc_prov_id_qlfr, pe.prsctn_servc_refnc_no, pe.fill_nbr, pe.pde_det_skey order by pe.pde_det_skey desc) myrownum
)
where myrownum=1
I would do this:

select field1, field2, field3, field4, field5, field6
from mytable
where seq_no = (SELECT Max(seq_no) FROM mytable);

Just ensure seq_no is the primary key or has an unique index, and performance should be very fast.

Hope it helps.

Author

Commented:
gplana, thanks.

but your query only returns one row. I need it to be grouped by field1, field2, field3, field4, field5, field6 and then take the max(seq_no)
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>> "FROM keyword not found where expected".

I apologize.  I missed the FROM...


Change:
...
pe.fill_nbr, pe.pde_det_skey order by pe.pde_det_skey desc) myrownum
)
where myrownum=1

To:
...
pe.fill_nbr, pe.pde_det_skey order by pe.pde_det_skey desc) myrownum
from yourTableName
)
where myrownum=1
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>I would do this:

It also hits the table twice.
Oh, sorry I didn't understood.

In that case, in order to avoid performance issues, I would create a table identified by field1, 2, 3, 4, 5 and 6, and addint the seq_no attribute. Then I would put triggers to your table in order to sincronize its data with this new sumarize table.

When insert into mytable, insert to sumarize table or update, depending if it exists a row on sumarize table with field1, 2,3,4,5 and 6 values. So the maximum value for seq_no field is kept on this table for every field1,2,3,4,5 and 6 combination.

When update into mytable, you should also see if you need t update sumarize table

When delete into mytable you should update or delete on sumarize.

You should put index cleverly in orde to make good performance for these operations inside trigger.

Then, as you will have your new table with the data you want, you can just query this new table and avoid this huge view.

Hope it helps. Regards.

Author

Commented:
gplana, ok, thanks. that's what I was thinking too. I'm trying to understand the analytic approach as well.

Author

Commented:
slightwv, ok, thanks.

I'm working through this now from here: http://www.orafaq.com/node/55

and I am using the OVER by and seeing it build to my expectations (but not quite there yet), will keep you posted.
Tell me if I can help you anymore.

Author

Commented:
I ran the below query with results, but it doesn't order by "seqno desc" (in my case pe.pde_det_skey desc)

select pe.hicn, to_date(pe.dos,'yyyymmdd') as DOS, pe.prsctn_servc_refnc_no, pe.oth_troop_amt, pe.rptd_gap_disc, pe.rec_id, pe.adjtmt_deln_cd, pe.pde_det_skey, count(*)
       over (partition by pe.hicn, to_date(pe.dos,'yyyymmdd'), pe.prsctn_servc_refnc_no, pe.pde_det_skey order by pe.pde_det_skey desc) clm_count
from medpartd.cgdp_stg_pde_det pe
where pe.hicn in ('138032007A')
  and pe.prsctn_servc_refnc_no = '000006907724'
  and pe.dos = '20110301'

Results below, I was expecting it to be sorted desc by pde_det_skey desc
HICN	DOS	PRSCTN_SERVC_REFNC_NO	OTH_TROOP_AMT	RPTD_GAP_DISC	REC_ID	ADJTMT_DELN_CD	PDE_DET_SKEY	CLM_COUNT
1	139992007A	01-Mar-2011	000006907724	0000000{	0000000{	ACC		7464649	1
2	139992007A	01-Mar-2011	000006907724	0000000{	0000000{	ACC		7484789	1

Open in new window

Author

Commented:
sorry, column headings are off.
just add an order by at the end:

select pe.hicn, to_date(pe.dos,'yyyymmdd') as DOS, pe.prsctn_servc_refnc_no, pe.oth_troop_amt, pe.rptd_gap_disc, pe.rec_id, pe.adjtmt_deln_cd, pe.pde_det_skey, count(*)
       over (partition by pe.hicn, to_date(pe.dos,'yyyymmdd'), pe.prsctn_servc_refnc_no, pe.pde_det_skey order by pe.pde_det_skey desc) clm_count
from medpartd.cgdp_stg_pde_det pe
where pe.hicn in ('138032007A')
  and pe.prsctn_servc_refnc_no = '000006907724'
  and pe.dos = '20110301'
order by pe.pde_det_skey desc;

Author

Commented:
gplana, I need to do and ORDER BY DESC within the sets of data in the partition

so I don't want the returned record set to be ordered by seq_no DESC, but within the partition groups.

in the returned data below, it's ordered by pde_seqno ASC (within the group), even though I specified pde_seqno DESC in the over clause.
HICN	        DOS	        Prescript #     Amt 1           Amt 2           Type   CD	PDE_DET_SKEY	CLM_COUNT
139992007A	01-Mar-2011	000006907724	0000000{	0000000{	ACC		7464649	         1
139992007A	01-Mar-2011	000006907724	0000000{	0000000{	ACC		7484789	         1
218882850A	05-Jan-2010	000006109848	0000000{	0000000{	REJ	D	7175434	         1
218882850A	05-Jan-2010	000006109848	0000000{	0000000{	ACC	A	7228131	         1

Open in new window

but you should order on the parent query. Have you tried last query I have putted ?

Author

Commented:
I don't want it ordered by pde_det_skey, as below # 1

I don't want this
HICN	        DOS	        Prescript #     Amt 1           Amt 2           Type   CD	PDE_DET_SKEY	CLM_COUNT
139992007A	01-Mar-2011	000006907724	0000000{	0000000{	ACC		7484789	         1
139992007A	01-Mar-2011	000006907724	0000000{	0000000{	ACC		7464649	         1
218882850A	05-Jan-2010	000006109848	0000000{	0000000{	ACC	A	7228131	         1
218882850A	05-Jan-2010	000006109848	0000000{	0000000{	REJ	D	7175434	         1

I want this
218882850A	05-Jan-2010	000006109848	0000000{	0000000{	ACC	A	7228131	         1
218882850A	05-Jan-2010	000006109848	0000000{	0000000{	REJ	D	7175434	         1
139992007A	01-Mar-2011	000006907724	0000000{	0000000{	ACC		7484789	         1
139992007A	01-Mar-2011	000006907724	0000000{	0000000{	ACC		7464649	         1

so that in a subsequent query I can add the function row_number() and "pick off" row number 1

Open in new window

Sorry, I think I don't fully understand. Which fields do you want to order by ?
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
You are doing a count(*) over(...).  

The order by in a window functions doesn't sort the results per say.  It logically does and applies the 'order' to the window function not the overall resultset.

The ROW_NUMBER() will assign a 1,2,3 to the ordered rows and group set in the partition by clause.  This does not mean the result set will be in that order.

Author

Commented:
slightwv, ok, good. I was running the query using the OVER PARTITION for the first time, and just building it one step at a time, so I can see what's going on. So I haven't added the row_number() yet, first time it bombed so then I started from scratch, using the examples here: http://www.orafaq.com/node/55

I have the partition working but now need to get the row_number() function going, stand by
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>>http://www.orafaq.com/node/55

Blog posts are great and can help from time to time.  

I would also check the docs.  They might have better and more detailed explainations:

http://download.oracle.com/docs/cd/E11882_01/server.112/e16579/analysis.htm#DWHSG8680

Author

Commented:
well, I tried the query below but got "a non-numeric character was found where a numeric was expected", not sure now to debug this . . .

select hicn, to_date(dos,'yyyymmdd') as DOS, prsctn_servc_refnc_no, oth_troop_amt, rptd_gap_disc, rec_id, adjtmt_deln_cd, pde_det_skey
from (select pe.hicn, to_date(pe.dos,'yyyymmdd') as DOS, pe.prsctn_servc_refnc_no, pe.oth_troop_amt, pe.rptd_gap_disc, 
             pe.rec_id, pe.adjtmt_deln_cd, pe.pde_det_skey, row_number() 
      over (partition by pe.hicn, to_date(pe.dos,'yyyymmdd'), pe.prsctn_servc_refnc_no, pe.pde_det_skey order by pe.pde_det_skey desc) curr_rows
      from medpartd.cgdp_stg_pde_det pe
      where (pe.hicn in ('138032007A') and pe.prsctn_servc_refnc_no = '000006907724'  and pe.dos = '20110301')
      or
      (pe.hicn in ('212202850A') and pe.prsctn_servc_refnc_no = '000006109848'  and pe.dos = '20100105')
      )
where curr_rows=1

Open in new window

Author

Commented:
I changed to this but still not working, debugging now . . .
select hicn, to_date(dos,'yyyymmdd') as DOS, prsctn_servc_refnc_no, oth_troop_amt, rptd_gap_disc, rec_id, adjtmt_deln_cd, pde_det_skey
from (select pe.hicn, to_date(pe.dos,'yyyymmdd') as DOS, pe.prsctn_servc_refnc_no, pe.oth_troop_amt, pe.rptd_gap_disc,
             pe.rec_id, pe.adjtmt_deln_cd, pe.pde_det_skey, row_number()
      over (partition by pe.hicn, to_date(pe.dos,'yyyymmdd'), pe.prsctn_servc_refnc_no, pe.pde_det_skey order by pe.pde_det_skey desc
      from medpartd.cgdp_stg_pde_det pe
      where (pe.hicn in ('138032007A') and pe.prsctn_servc_refnc_no = '000006907724'  and pe.dos = '20110301')
      or
      (pe.hicn in ('212202850A') and pe.prsctn_servc_refnc_no = '000006109848'  and pe.dos = '20100105'))
      ) curr_rows
where curr_rows=1    
 
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Break it down into smaller pieces.

Does the inner query run?

Are you using sqlplus?  It should help flag down the general area of the error.
select pe.hicn, to_date(pe.dos,'yyyymmdd') as DOS, pe.prsctn_servc_refnc_no, pe.oth_troop_amt, pe.rptd_gap_disc, 
             pe.rec_id, pe.adjtmt_deln_cd, pe.pde_det_skey, row_number() 
      over (partition by pe.hicn, to_date(pe.dos,'yyyymmdd'), pe.prsctn_servc_refnc_no, pe.pde_det_skey order by pe.pde_det_skey desc) curr_rows
      from medpartd.cgdp_stg_pde_det pe
      where (pe.hicn in ('138032007A') and pe.prsctn_servc_refnc_no = '000006907724'  and pe.dos = '20110301')
      or
      (pe.hicn in ('212202850A') and pe.prsctn_servc_refnc_no = '000006109848'  and pe.dos = '20100105')

Open in new window

Author

Commented:
yes, working on breaking it down, thanks. I'm using pl*sql developer

Author

Commented:
got it working !

I worked with the DBA and got this, gave me the results I expected:

   select pe.hicn, to_date(pe.dos,'yyyymmdd') as DOS, pe.prsctn_servc_refnc_no, pe.oth_troop_amt, pe.rptd_gap_disc, pe.rec_id, pe.
adjtmt_deln_cd, pe.pde_det_skey
    from
       (select b.*, max(pde_det_skey)
        over (partition by hicn, to_date(dos,'yyyymmdd'), prsctn_servc_refnc_no) "MAX_PDE_DET_SKEY"
        from medpartd.cgdp_stg_pde_det b) pe
    where pe.pde_det_skey = pe.max_pde_det_skey
HICN               DOS          Prescript. #      Amt 1         Amt 2           type    Code     Seqno
139992007A	01-Mar-2011	000006907724	0000000{	0000000{	ACC		7484789
218882850A	05-Jan-2010	000006109848	0000000{	0000000{	ACC	A	7228131

Open in new window

and also should have good performance. Good work !!

Author

Commented:
slightwv and gplana, thanks for working with me on this, big help.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Always glad to help.
you are welcome

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial