RE: Processing new rows in staging table quickly...

  • From: "Reen, Elizabeth " <dmarc-noreply@xxxxxxxxxxxxx> (Redacted sender "elizabeth.reen" for DMARC)
  • To: "'neil_chandler@xxxxxxxxxxx'" <neil_chandler@xxxxxxxxxxx>, "post.ethan@xxxxxxxxx" <post.ethan@xxxxxxxxx>, "niall.litchfield@xxxxxxxxx" <niall.litchfield@xxxxxxxxx>
  • Date: Thu, 18 Apr 2019 14:35:24 +0000

                Staging tables should always be cleaned up.  Otherwise they 
grow and get unwieldy.

Liz

From: [External] oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> 
On Behalf Of [External] Neil Chandler
Sent: Thursday, April 18, 2019 6:22 AM
To: post.ethan@xxxxxxxxx; niall.litchfield@xxxxxxxxx
Cc: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
Subject: Re: Processing new rows in staging table quickly...

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@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx
<oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>> on behalf 
of niall.litchfield@xxxxxxxxx<mailto:niall.litchfield@xxxxxxxxx
<niall.litchfield@xxxxxxxxx<mailto:niall.litchfield@xxxxxxxxx>>
Sent: 18 April 2019 09:44
To: post.ethan@xxxxxxxxx<mailto:post.ethan@xxxxxxxxx>
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@xxxxxxxxx<mailto:post.ethan@xxxxxxxxx>> 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<https://urldefense.proofpoint.com/v2/url?u=http-3A__www.orawin.info&d=DwMF-g&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=mvNR112CPT81d7wzQi8ZlOve3BX8yJbEPGJ75mVrpu0&s=5kPvdtx7NNWd2rHRZy66IXxXdzA_St5reaaNxbPk7OA&e=>

Other related posts: