Databases
StackOverflow2013
Postsdbo
POSQL optimization question (oracle)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL optimization question (oracle)
    primarykey
    data
    text
    <p>Edit: Please answer one of the two answers I ask. I <em>know</em> there are other options that would be better in a different case. These other potential options (partitioning the table, running as one large delete statement w/o committing in batches, etc) are <b>NOT</b> options in my case due to things outside my control.</p> <p>I have several very large tables to delete from. All have the same foreign key that is indexed. I need to delete certain records from all tables.</p> <pre><code>table source id --primary_key import_source --used for choosing the ids to delete table t1 id --foreign key --other fields table t2 id --foreign key --different other fields </code></pre> <p>Usually when doing a delete like this, I'll put together a loop to step through all the ids:</p> <pre><code>declare my_counter integer := 0; begin for cur in ( select id from source where import_source = 'bad.txt' ) loop begin delete from source where id = cur.id; delete from t1 where id = cur.id; delete from t2 where id = cur.id; my_counter := my_counter + 1; if my_counter &gt; 500 then my_counter := 0; commit; end if; end; end loop; commit; end; </code></pre> <p>However, in some code I saw elsewhere, it was put together in separate loops, one for each delete.</p> <pre><code>declare type import_ids is table of integer index by pls_integer; my_count integer := 0; begin select id bulk collect into my_import_ids from source where import_source = 'bad.txt' for h in 1..my_import_ids.count delete from t1 where id = my_import_ids(h); --do commit check end loop; for h in 1..my_import_ids.count delete from t2 where id = my_import_ids(h); --do commit check end loop; --do commit check will be replaced with the same chunk to commit every 500 rows as the above query </code></pre> <p>So I need one of the following answered:</p> <p>1) Which of these is better?</p> <p>2) How can I find out which is better for my particular case? (IE if it depends on how many tables I have, how big they are, etc)</p> <p>Edit: </p> <p>I <em>must</em> do this in a loop due to the size of these tables. I will be deleting thousands of records from tables with hundreds of millions of records. This is happening on a system that can't afford to have the tables locked for that long.</p> <p>EDIT:</p> <p>NOTE: I am <em>required</em> to commit in batches. The amount of data is too large to do it in one batch. The rollback tables will crash our database.</p> <p>If there is a way to commit in batches other than looping, I'd be willing to hear it. Otherwise, don't bother saying that I shouldn't use a loop...</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USTom H
    1. USDavid Oneill
    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. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL optimization question (oracle)
      1. USPhilip Schlump
      1. VTFavorite
    2. VO
      singulars
      1. POSQL optimization question (oracle)
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIf you feel you that you have to resort to loops because of the tables' large size, have you (and/or DBA) looked at using the database engine's partitioning functionality? That's one of the "use cases" that partitioning helps with. For example, if you have a 10 terabyte table with a billion rows, it's easier to delete a partition (based on ID) than to iterate over millions of rows.
      singulars
      1. POSQL optimization question (oracle)
      1. USJasDev
    2. COYes, we have considered this. We have partitioned pieces of our database. However, the t1 and t2 (etc) tables are accessed in several different ways (based on fields other than id). As such, any partitioning to them would hurt overall. I'm leaving out a lot of detail that doesn't effect my question, but does mean that we can't partition t1, t2, etc.
      singulars
      1. POSQL optimization question (oracle)
      1. USDavid Oneill
    3. COYou know that no matter how many rows you delete, you won't lock the table, right? You would only have lock contention if you were deleting a row that some other process was trying to update which seems highly unlikely. And if someone is trying to update a row that you are trying to delete, it seems awfully reasonable for that to block.
      singulars
      1. POSQL optimization question (oracle)
      1. USJustin Cave
 

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