sql - How can i make certain Oracle Table Rows marked as 'historical' invisible/un-available? -

sql - How can i make certain Oracle Table Rows marked as 'historical' invisible/un-available? -

i have huge existing order management application.

now, in main order table, adding new column: is_historical. if value is: true, means order historical now, , should not show in application.

now, have modify many sql queries in existing application select orders is_historical 'false' - i.e add together next in clause:

, is_historical='false'

question: *is there easier way - not have modify many application queries (to hide away historical orders)? orders marked is_historical='true' should become invisible/un-available read/updates!!*

note: right table sizes not huge, intend partition table is_historical true/false.

if you're going utilize historical info analysis prefer florin's solution amount of info need @ each query remains smaller. makes analysis queries more hard need union else run "quicker" (it may not noticable).

if applications/users require access historical info improve solution rename table , create view on top of query need.

the problem re-writing queries you're going forget 1 or 1 incorrect, either or in future. view removes problem query static, every time query view additional conditions require automatically added.

something like:

rename orders order_history; create or replace view orders select * order_history is_historical = 'false';

two farther points.

i wouldn't bother true / false, if table gets big it's lot of additional info scan. create column varchar2(1) , utilize t / f or y / n, obvious smaller. alternatively utilize number(1,0) , 1 / 0.

don't forget set constraint on table is_historical column can have values you've chosen.

if you're ever going have 2 values may want consider check constraint:

alter table order_history add together constraint chk_order_history_historical check ( is_historical in ('t','f') );

otherwise, maybe should anyway, utilize foreign key constraint. define table, order_history_types

create table order_history_types ( id varchar2(1) , description varchar2(4000) , constraint pk_order_history_types primary key (id) );

fill values , add together foreign key:

alter table order_history add together constraint fk_order_history_historical foreign key (is_historical) references order_history_types (id)

sql database oracle