Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>The fastest way is to create a new one with <code>CREATE TABLE AS SELECT</code> using <code>NOLOGGING</code> option. I mean:</p> <pre><code>ALTER TABLE table_to_delete RENAME TO tmp; CREATE TABLE table_to_delete NOLOGGING AS SELECT .... ; </code></pre> <p>Of course you have to recreate constraints with no validate, indexes with nologging, grants, ... but is very very fast.</p> <p>If you have the trouble in production, you can do the following:</p> <pre><code>ALTER TABLE table_to_delete RENAME to tmp; CREATE VIEW table_to_delete AS SELECT * FROM tmp; -- Until there can be instantly CREATE TABLE new_table NOLOGGING AS SELECT .... FROM tmp WHERE ...; &lt;create indexes with nologging&gt; &lt;create constraints with novalidate&gt; &lt;create other things...&gt; -- From here ... DROP VIEW table_to_delete; ALTER TABLE new_table RENAME TO table_to_delete; -- To here, also instantly </code></pre> <p>You have take care of:</p> <ul> <li>Stored procedures can be invalidated, but they will be recompiled the second time are called. You have to test it.</li> <li><code>NOLOGGING</code> means that <strong>minimal</strong> redo are generated. If you have DBA role, run a <code>ALTER SYSTEM CHECKPOINT</code> to ensure no data lost if instance crash. </li> <li>For <code>NOLOGGING</code> the tablespace have to be also in <code>NOLOGGING</code>.</li> </ul> <p>Another option better than create milions of inserts is:</p> <pre><code>-- Create table with ids DELETE FROM table_to_delete WHERE ID in (SELECT ID FROM table_with_ids WHERE ROWNUM &lt; 100000); DELETE FROM table_with_ids WHERE ROWNUM &lt; 100000; COMMIT; -- Run this 50 times ;-) </code></pre> <p>The PLSQL choice is not advisable because can create the <em>Snapshot too old</em> message due that you are commiting (and closing the transaction) with an opened cursor (the looped one) you want to continue using it. Oracle allows it but it's not a good practice.</p> <p>UPDATE: Why I can ensure the last PLSQL block is going to work? Because I supose that:</p> <ul> <li>No other one is using this temporary table for any reason (dba or jobs gathering statistics, dab tasks like move, inserting records, and so on). That can be ensured because is an auxiliar table only for this.</li> <li>Then, with the last assertion, the query is going to be executed <em>exactly</em> with the same plan and is going to return the rows with the same order.</li> </ul>
    singulars
    1. This table or related slice is empty.
    1. PODeleting a LOT of data in Oracle
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USFerranB
    1. USFerranB
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    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. CODo you mean create a new table with the rows that I'm keeping, then drop the original table and rename the new one? Would the original table be, for some non-zero amount of time, non-existant? If so, this sadly won't work as this is in production. :(
      singulars
      1. PO
      1. USSarah Mei
    2. COYes, exactly. You have no downtime?
      singulars
      1. PO
      1. USFerranB
    3. COWe do, but fairly infrequently - it's happened once in the year I've been here. The policy is generally that in order to require downtime, your change better have NO OTHER ALTERNATIVE. :)
      singulars
      1. PO
      1. USSarah Mei
 

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