Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Finding index keys in a v large table that are not in a large table.

Re: Finding index keys in a v large table that are not in a large table.

From: D Rolfe <dwrolfeFRUITBAT_at_orindasoft.com>
Date: Thu, 03 Feb 2005 13:10:01 +0000
Message-ID: <420222A9.6090906@orindasoft.com>

stephen O'D wrote:

>
> Every so often, a job starts up that gets all the new audit_seqs that
> are
> not in the audit_summary table and inserts the new records into it.
>
> These tables are getting pretty big eg Audit is >15Million rows and
> Audit_summary is around a few million.
>
> Anyone have any thoughts on the most efficient way to extract the new
> audit_seqs in the audit table?
>

Add a column "summarised_n_or_null varchar2(1) default 'N'" to the 'audit' table. Build an index on it.

Modify your batch job so that it uses 'where summarised_n_or_null = 'N'' as selection criteria and sets the flag to null at the end of the process. The additional index will be small because 99% of the rows will already have summaries.

Alternativly you could create the summary with a database trigger.....

David Rolfe
Orinda Software
Dublin, Ireland

www.orindasoft.com Received on Thu Feb 03 2005 - 07:10:01 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US