Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>If you're only going to use the historical data for analysis then I prefer Florin's solution as the amount of data you need to look at for each query remains smaller. It makes the analysis queries more difficult as you need to UNION ALL but everything else will run "quicker" (it may not be noticable).</p> <p>If some applications/users require access to the historical data the better solution would be to rename your table and create a view on top of it with the query that you need.</p> <p>The problem with re-writing all your queries is that you're going to forget one or get one incorrect, either now or in the future. A view removes that problem for you as the query is static, every time you query the view the additional conditions you require are automatically added.</p> <p>Something like:</p> <pre><code>rename orders to order_history; create or replace view orders as select * from order_history where is_historical = 'FALSE'; </code></pre> <p>Two further points.</p> <ol> <li>I wouldn't bother with <code>TRUE</code> / <code>FALSE</code>, if the table gets large it's a lot of additional data to scan. Create your column as a VARCHAR2(1) and use <code>T</code> / <code>F</code> or <code>Y</code> / <code>N</code>, they are as immediately obvious but are smaller. Alternatively use a NUMBER(1,0) and <code>1</code> / <code>0</code>.</li> <li><p>Don't forget to put a constraint on your table so that the IS_HISTORICAL column can only have the values you've chosen.</p> <p>If you're only ever going to have the two values then you may want to consider a <a href="http://docs.oracle.com/cd/E11882_01/server.112/e25789/datainte.htm#CNCPT1660" rel="nofollow">CHECK CONSTRAINT</a>:</p> <pre><code>alter table order_history add constraint chk_order_history_historical check ( is_historical in ('T','F') ); </code></pre> <p>Otherwise, maybe you should do this anyway, use a <a href="http://docs.oracle.com/cd/E11882_01/server.112/e25789/datainte.htm#CNCPT1649" rel="nofollow">FOREIGN KEY CONSTRAINT</a>. Define an extra table, ORDER_HISTORY_TYPES</p> <pre><code>create table order_history_types ( id varchar2(1) , description varchar2(4000) , constraint pk_order_history_types primary key (id) ); </code></pre> <p>Fill it with your values and then add the foreign key:</p> <pre><code>alter table order_history add constraint fk_order_history_historical foreign key (is_historical) references order_history_types (id) </code></pre></li> </ol>
    singulars
    1. This table or related slice is empty.
    1. POHow can i make certain Oracle Table Rows marked as 'historical' invisible/un-available?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USBen
    1. USBen
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow can i make certain Oracle Table Rows marked as 'historical' invisible/un-available?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO+1 pretty view :)
      singulars
      1. PO
      1. USFlorin Ghita
    2. COMaybe, don't put a `select *`, just select the column, we don't care about the `is_historical`
      singulars
      1. PO
      1. USPlouf
    3. COYou should select everything _but_ the column not cared about @Plouf. As I don't know anything about the table I didn't write it. It's not really relevant anyway. The columns selected in the view is irrelevant as long as each query on the view doesn't select every column.
      singulars
      1. PO
      1. USBen
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload