TL_ID      VALID_FROM                                  BATCH_ID      CURRENT_FLAG      SOURCE      PUBLISHED_FLAG
10001      01-SEP-15      100      Y                            5                   Y
10002      01-SEP-15      100      Y                            5                   Y
10003       02-SEP-15               101           Y                                     8                         Y
10001      29-SEP-15      129      Y                            8                   N
10002      01-OCT-15      131      Y                            8                   N
10003       01-OCT-15               131           Y                                     8                        N
10004      02-OCT-15               132            Y                                     8                        N

I have a table called lookup and it has fixed records from older database which can be identified by SOURCE = '5'. We used to receive new records from daily batch and those records can be identified by SOURCE = '8'.  

TL_ID & VALID_FROM are primary key for this table.

My requirement is,

1. Whenever a new record comes it should check the table for any match using TL_ID (For ex, 10001, 10002,10003 in the above case). If it matches it should simply update the following column in existing record
PUBLISHED_FLAG = 'Y' in the new record.

2. Suppose if no matches found (for ex, TL_ID 10004) then simply update the PUBLISHED_FLAG = 'Y'.

The table might have 1 to 2 million records and chance for growing upto 5m. So I am looking for optimal solution and planning to schedule this function/procedure to run on daily basis.
Subhashini ElangoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Will the valid_from date be the date it was inserted into the database?

What I'm trying to find is if there is a way to identify a "new" record without having to go through ALL rows.
Subhashini ElangoAuthor Commented:
Yes, VALID_FROM is a systimestamp.
slightwv (䄆 Netminder) Commented:
It looks like you want current_flag and published_flag set to 'Y' for the maximum valid_from date and the rest set to 'N'.

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Subhashini ElangoAuthor Commented:
Please see attached spreadsheet incase if the above example is not aligned properly.
slightwv (䄆 Netminder) Commented:
Assuming my assumption is correct, take a look at this.

I created a test table with your data and added another 2 million random rows to it.

On my very small play database the update took 5 minutes.  Do you need more efficient that that?

update tab1 t1_u
set (current_flag,published_flag) =
	case when rn=1 then 'Y' else 'N' end current_flag,
	case when rn=1 then 'Y' else 'N' end published_flag
from (
	select tl_id, valid_from,
		row_number() over(partition by tl_id order by valid_from desc) rn
	from tab1
	) t1_s
	where t1_s.tl_id=t1_u.tl_id
		and t1_s.valid_from=t1_u.valid_from

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
This might run faster depending on how many new rows you get in a day.

This version has the requirement that it MUST run daily since it only updates the previous days rows.
	for i in (select tl_id, valid_from from tab1 where valid_from >= trunc(sysdate)-1 and valid_from < trunc(sysdate) ) loop
		update tab1 set current_flag='N', published_flag='N' where tl_id=i.tl_id;
		update tab1 set current_flag='Y', published_flag='Y' where tl_id=i.tl_id and valid_from=i.valid_from;
	end loop;

Open in new window

Subhashini ElangoAuthor Commented:
Hi Pal

Many thanks for the solutions first. I feel the first solution will be good as the second one only updates the previous day rows. It will be problem if the previous day batch was failed for some reasons.

I accept your first solution but the requirements are slightly changed. Any help would be much appreciated.

1. The current_flag should be set to 'Y' for maximum valid_from date and 'N' for the rest (your case statement do this correctly). However for published_flag, it should be set to 'Y' for all the records.

2. We have an another column called VALID_TO which has the default value of '31-12-9999' always. The VALID_TO field in the second record should be changed to Maximum VALID_FROM value of the first record.

3. If the record is an intra-day, ex 2 or more records come on the same day (batch_id) then keep the most recent one and delete the other records for that TL_ID

4. If it is new record (TL_ID), simply update the PUBLISHED_FLAG = 'Y'

Please see expected results in the attached file. Could you please suggest.
slightwv (䄆 Netminder) Commented:
>>However for published_flag, it should be set to 'Y' for all the records.

I don't see the problem.  Just remove the case statement and hard-code a 'Y'.  It does seem strange to have a column that only stores one specific value...

Since the question asked was answered and was not accurate, for the rest of the requirements,  I suggest you close this one and ask a new question.

Some of the new requirements don't make sense...  #4 for example.  current can be 'N' for a "new" record?  published is already a 'Y' based on #1 (everything is a 'Y').
Subhashini ElangoAuthor Commented:
Hi Slightwv

Thanks for the reply. I accept your solution to the current scenario and will raise a new question for the updated requirements.

Much appreciated for the speedy response.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.