StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Whether there will be any performance implications from storing the old rows in the DataNode table depends on how the DataNode rows are accessed. If the reads are all single-row lookups for the current row, the number of rows in the table is relatively immaterial-- it's not going to take any more work to find the current row for a particular ID than it would to get the row for that ID from the current DataNode table (I'm assuming here that ID is the key for the table). On the other hand, if you have a number of queries that are doing table scans of the DataNode table, then quadrupling the number of rows will increase the time required to run those queries. </p> <p>If you want to go down the path of putting the historical rows in the DataNode table, you would probably want to add an EXPIRATION_DATE column that is NULL for the current row and populated for the expired rows. You could then create a function-based index based on the EXPIRATION_DATE that would have data for only the current rows, i.e.</p> <pre><code>CREATE INDEX idx_current_ids ON DataNode( (CASE WHEN expiration_date IS NULL THEN id ELSE null END) ); </code></pre> <p>which would be used in a query like</p> <pre><code>SELECT * FROM DataNode WHERE (CASE WHEN expiration_date IS NULL THEN id ELSE null END) = <<some id>> </code></pre> <p>Obviously, you'd probably want to create a view that has this condition rather than rewriting it every time you need the current row, i.e.</p> <pre><code>CREATE VIEW CurrentDataNode AS SELECT (CASE WHEN expiration_date IS NULL THEN id ELSE null END) id, type, name, status FROM DataNode; SELECT * FROM CurrentDataNode WHERE id = <<some value>> </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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