sql - Updating NULL Field vs Most Recent Record -

i have table contains null field. needs populated table. while statement getting information other table simple concerned performance of update.

the update done script scheduled run every 30 minutes.

which better:

• update using field null statement

  update table1 set freefield=(select name table2 table1.keyfield=table2.field) freefield null;

• update using statement updates last x records

  update table1 set freefield=(select name table2 table1.keyfield=table2.field) rowid in ( select rowid ( select keyfield table1 order keyfield desc ) rownum < 300 );

table1.keyfield , table2.field indexed , have primary/fk relation. table1.freefield , table2.name not indexed , text fields.

currently table 100k record grow massively. i'm asking going take longer search null fields in table or order , use recent number specified.

the final plan implement trigger records updated @ creation cannot implemented until next release of our software qc reasons etc.

database oracle 10.2.0.5

the table has several indexes including primary key (incremental number) used sort , update recent records.

you can use function-based index identify rows null. such index small.

create index index_name      on owner.table_name(case when your_column null 'x' end) compress 1; 

to update column do:

update owner.table_name    set your_column = <your-logic-here>  (case when your_column null 'x' end) = 'x'; 

edit after initial question changed: 2 options doesn't give same result. option 1 implement requirement "update missing missing freefield".

if more 300 inserts per 30 minutes, not of them updated option 2. also, have introduced unnecessary dependency may or may not hold true in future: whenever record r1.keyfield > record r2.keyfield, record r1 newer r2.