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

  • From: <post.ethan@xxxxxxxxx>
  • To: "'Reen, Elizabeth '" <elizabeth.reen@xxxxxxxx>, <neil_chandler@xxxxxxxxxxx>, <niall.litchfield@xxxxxxxxx>
  • Date: Thu, 18 Apr 2019 11:36:36 -0500

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@xxxxxxxx> 
Sent: Thursday, April 18, 2019 9:35 AM
To: 'neil_chandler@xxxxxxxxxxx' <neil_chandler@xxxxxxxxxxx>;
post.ethan@xxxxxxxxx; niall.litchfield@xxxxxxxxx
Cc: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx>
oracle-l-bounce@xxxxxxxxxxxxx < <mailto:oracle-l-bounce@xxxxxxxxxxxxx>
oracle-l-bounce@xxxxxxxxxxxxx> On Behalf Of [External] Neil Chandler
Sent: Thursday, April 18, 2019 6:22 AM
To:  <mailto:post.ethan@xxxxxxxxx> post.ethan@xxxxxxxxx;
<mailto:niall.litchfield@xxxxxxxxx> niall.litchfield@xxxxxxxxx
Cc: ORACLE-L < <mailto:oracle-l@xxxxxxxxxxxxx> 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:  <mailto:oracle-l-bounce@xxxxxxxxxxxxx> oracle-l-bounce@xxxxxxxxxxxxx
< <mailto:oracle-l-bounce@xxxxxxxxxxxxx> oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of  <mailto:niall.litchfield@xxxxxxxxx> niall.litchfield@xxxxxxxxx <
<mailto:niall.litchfield@xxxxxxxxx> niall.litchfield@xxxxxxxxx>
Sent: 18 April 2019 09:44
To:  <mailto:post.ethan@xxxxxxxxx> 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=m
vNR112CPT81d7wzQi8ZlOve3BX8yJbEPGJ75mVrpu0&s=5kPvdtx7NNWd2rHRZy66IXxXdzA_St5
reaaNxbPk7OA&e=> 

Other related posts: