Re: Processing new rows in staging table quickly...
Date: Thu, 18 Apr 2019 10:21:32 +0000
Message-ID: <DB7PR10MB20905F5E4F28048B2241A80285260_at_DB7PR10MB2090.EURPRD10.PROD.OUTLOOK.COM>
I agree about not overloading the date column and also was wondering why there's data being left in the staging table.
If the PROCESSED column has a default of 'N', and you update that to NULL to mean processed it keeps the index very small and efficient. Remember to document/comment that NULL means processed! Failing that, make sure you have a frequency histogram on the PROCESSED column.
Neil.
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of niall.litchfield_at_gmail.com <niall.litchfield_at_gmail.com> Sent: 18 April 2019 09:44
To: post.ethan_at_gmail.com
Cc: ORACLE-L
Subject: Re: Processing new rows in staging table quickly...
The FBI approach will work just fine. Personally, I'd probably do one of the following:
- add a "PROCESSED" column accepting 'Y' or 'N' and index/query on that, Mostly for human readability.
- ask, if this is a "staging" table why are there ever any "processed" rows left in it.
On Thu, Apr 18, 2019 at 12:33 AM <post.ethan_at_gmail.com<mailto:post.ethan_at_gmail.com>> wrote:
I have a staging table in which 99% of the rows are going to have a date for stats_processed_time.
I will need to only select for rows that have not been processed.
What is the best strategy here? Should I allow nulls and select like below? Or should I put a date, way out in the future, say 2099, and select for that instead and then update to correct data once things are processed? Perhaps allow nulls and a bitmap or FBI solution (I would like to avoid solutions which can’t be ported to Postgres.)
…
where b.access_key=v_access_key
and b.bucket=v_bucket
and b.stats_processed_time is null
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Apr 18 2019 - 12:21:32 CEST