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

From: <post.ethan_at_gmail.com>
Date: Thu, 18 Apr 2019 11:36:36 -0500
Message-ID: <02ea01d4f604$e5d72010$b1856030$_at_gmail.com>



Yeah I guess that would solve the problem. I hadn't thought too hard about that aspect yet but I probably don't need the data around after it's processed.  

Thanks for the advice, it will be used elsewhere if not in this table.  

From: Reen, Elizabeth <elizabeth.reen_at_citi.com> Sent: Thursday, April 18, 2019 9:35 AM
To: 'neil_chandler_at_hotmail.com' <neil_chandler_at_hotmail.com>; post.ethan_at_gmail.com; niall.litchfield_at_gmail.com Cc: ORACLE-L <oracle-l_at_freelists.org> Subject: RE: Processing new rows in staging table quickly...  

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

Liz  

From: [External] <mailto:oracle-l-bounce_at_freelists.org> oracle-l-bounce_at_freelists.org < <mailto:oracle-l-bounce_at_freelists.org> oracle-l-bounce_at_freelists.org> On Behalf Of [External] Neil Chandler Sent: Thursday, April 18, 2019 6:22 AM
To: <mailto:post.ethan_at_gmail.com> post.ethan_at_gmail.com;
<mailto:niall.litchfield_at_gmail.com> niall.litchfield_at_gmail.com
Cc: ORACLE-L < <mailto:oracle-l_at_freelists.org> oracle-l_at_freelists.org> 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: <mailto:oracle-l-bounce_at_freelists.org> oracle-l-bounce_at_freelists.org
< <mailto:oracle-l-bounce_at_freelists.org> oracle-l-bounce_at_freelists.org> on
behalf of <mailto:niall.litchfield_at_gmail.com> niall.litchfield_at_gmail.com <
<mailto:niall.litchfield_at_gmail.com> niall.litchfield_at_gmail.com>
Sent: 18 April 2019 09:44
To: <mailto:post.ethan_at_gmail.com> 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

<https://urldefense.proofpoint.com/v2/url?u=http-3A__www.orawin.info&d=DwMF-
g&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=m vNR112CPT81d7wzQi8ZlOve3BX8yJbEPGJ75mVrpu0&s=5kPvdtx7NNWd2rHRZy66IXxXdzA_St5 reaaNxbPk7OA&e=> -- http://www.freelists.org/webpage/oracle-l
Received on Thu Apr 18 2019 - 18:36:36 CEST

Original text of this message