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

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