Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle 9i Limitation (Bug?) in Identifying Key Preserved Tables in Views

Re: Oracle 9i Limitation (Bug?) in Identifying Key Preserved Tables in Views

From: Dave Hau <davehau_nospam_123_at_nospam_netscape.net>
Date: Tue, 27 May 2003 19:44:27 GMT
Message-ID: <3ED3C017.8090203@nospam_netscape.net>


Dan, let me know the results of your benchmark tests. I'm doing a huge delete at work also and I'd like to know whether the IN approach, or the updatable view approach is faster.

Breaking up the rows to be deleted into batches is a good idea but don't overdo it though. The tradeoff is that for each batch, you do a commit and that requires disk I/O for writing to the redo log. Also, you want to have as huge a buffer cache as possible so that you can cache most of the rollback segment onto the buffer cache. On commit, the rollback data can be discarded so preferably, you'd want none of the rollback data to be written to disk if at all possible, for efficiency purpose.

If there's any way you can partition the data in such a way that you can drop an entire partitioned table at a time, that'd be even faster.

Cheers,
Dave

Dan Berkery wrote:

> Dave, thanks for the alternate construction.  In my focus on the key
> preserved view construction, I missed this option.  An embarassing
> oversight as it is far more straightforward approach.  In my defense,
> I had ruled out most subquery based approaches since I could not
> afford anything that executed one subquery per row in foo_output. 
> Your query using IN, however, seems to provide a potential for a very
> efficient execution plan that identified the unique set of foo_ids and
> then used that set to do foo_output lookups and performed the delete.
> 
> Over the weekend I will run some benchmark tests to see how the IN
> construction performs compared to a delete from inline view
> construction (with the schema modified so that Oracle recognizes the
> foo_output table as key preserved--just a hack to see how the query
> might perform if my initial construction were supported by Oracle).  A
> key criteria for me is the ability to delete in batches (say 200M rows
> deleted 10M at a time--with commits--so that the rollback buffers are
> not blown out) so I will use that as the benchmark test.
> 
> I will post a comparison of the performance of the two approaches.  (I
> don't have access to a huge box right now so I will probably run the
> comparison as 20M rows deleted 1M at a time).  If the IN formulation
> is just as fast, I will write off my concerns about this issue.
> 
> Thanks,
> Dan
> 
> "Dave Hau" <davehau-no-spam-123_at_no-spam.netscape.net> wrote in message news:<V8xAa.568$Te4.79_at_newssvr19.news.prodigy.com>...
> 

>>Consider this updatable view:
>>
>>delete from
>>(select * from emp, dept
>>where emp.deptno = dept.deptno);
>>
>>14 rows deleted
>>
>>rollback;
>>
>>Now consider this updatable view:
>>
>>delete from
>>(select * from emp, dept
>>where emp.deptno = 1
>>and dept.deptno = 1);
>>
>>ORA-01752: cannot delete from view without exactly one key-preserved table
>>
>>If you think about how Oracle most likely implements updatable views, this
>>makes sense. My guess is that Oracle looks at the join columns to make sure
>>they constitute a unique key on the non-key-preserved table. In the first
>>example above, emp.deptno and dept.deptno are the join columns. Because
>>dept.deptno is the primary key of the dept table, and emp.deptno is not the
>>primary key of the emp table, Oracle knows that emp is the key-preserved
>>table and dept is the non-key-preserved table in this updatable view.
>>
>>Back to your example, you found that the following updatable view works:
>>
>>delete from
>>(select * from job_foos jf, foo_output fo
>>where jf.foo_id = fo.foo_id
>>and jf.job_id = fo.seq_num);
>>
>>But this one doesn't:
>>
>>delete from
>>(select * from job_foos jf, foo_output fo
>>where jf.foo_id = fo.foo_id
>>and jf.job_id = 1);
>>
>>This is because for the first one, Oracle sees that the join columns for
>>job_foos are foo_id and job_id, and this is the composite primary key for
>>the job_foos table. So the updatable view works. However, for the second
>>one, the only join column for job_foos is foo_id which is not a unique key
>>for the job_foos table. The join column for foo_output is foo_id which is
>>not a unique key for the foo_output table either. So the updatable view
>>fails.
>>
>>What this means is: for updatable views, you need to have join columns
>>constitute unique keys for all but one of the tables joined. The one table
>>where the join columns do not constitute a unique key becomes the
>>key-preserved table.
>>
>>In your case, I suggest you do this instead:
>>
>>delete from foo_output
>>where foo_id in
>>(select foo_id from job_foos where job_id = 1);
>>
>>Cheers,
>>Dave
>>
>>
>>
>>
>>
>>"Dan Berkery" <danberkery_at_yahoo.com> wrote in message
>>news:d5831952.0305261059.4979ee86_at_posting.google.com...
>>
>>>My apologies in advance for the long post, but this issue has been
>>>nagging at me for months.
>>>
>>>My basic issue is that I want to use an inline updateable view
>>>construction to do deletes on some very large tables. The details are
>>>described below (with an example). However, before I get started, I
>>>understand that there are other ways to do what is described below ,
>>>but I think the inline view construction would be the most efficient
>>>way to do what I need to do. At the very bottom of the message are
>>>all the gory details of the real problem, but my key driver is
>>>efficienty. Since the tables I am working with are very large--up to
>>>200 million rows for the larges, efficiency is a big concern.
>>>
>>>To get the essentials out first, I tested my example shown below on
>>>Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production running on
>>>XP Home (I know an odd combination but it is a laptop and the OS is
>>>not likely to matter for this one). The production systems run on
>>>very large unix boxes (fully loaded p690s, superdomes, and sunfires
>>>with all the trimmings).
>>>
>>>Below is a quick example to illustrate the issue (it is adapted from
>>>the real problem but simplified to the essence).
>>>
>>>The system has a set of objects that are the fundamental work unit of
>>>the system; let's call them foos with a schema and use the following
>>>table creation command:
>>>
>>>CREATE TABLE foo (
>>> foo_id NUMBER NOT NULL PRIMARY KEY)
>>>/
>>>
>>>The way the system is used is to run jobs that process a subset of the
>>>foos; using a table job_foos that maps foos to jobs (this is a clsssic
>>>many to many resolution table since a foo will be part of zero of more
>>>jobs and each job includes one of more foos):
>>>
>>>CREATE TABLE job_foos (
>>> job_id NUMBER NOT NULL,
>>> foo_id NUMBER NOT NULL,
>>> PRIMARY KEY(job_id, foo_id))
>>>/
>>>
>>>The jobs create a bunch of outputs but I just need one to illustrate
>>>the issue (I included the gratuitous sub_seq_num to help illustrate a
>>>point later):
>>>
>>>CREATE TABLE FOO_OUTPUT (
>>> foo_id NUMBER NOT NULL,
>>> seq_num NUMBER NOT NULL,
>>> sub_seq_num NUMBER NOT NULL,
>>> some_value NUMBER NOT NULL,
>>> PRIMARY KEY(foo_id, seq_num, sub_seq_num))
>>>/
>>>
>>>Now the gripe. As part of the business process, as the first step in
>>>a job we delete an existing outputs for that job (actually it is a bit
>>>more complicated but that is largely beside the point). I thought
>>>this would be very easy using an updateable inline view construction
>>>for a delete statement like this:
>>>
>>>DELETE FROM
>>> (SELECT
>>> *
>>> FROM
>>> FOO_OUTPUT fo,
>>> JOB_FOOS jf
>>> WHERE fo.foo_id = jf.foo_id
>>> AND jf.job_id = 1)
>>>/
>>>
>>>This generates an ORA-01752: cannot delete from view without exactly
>>>one key-preserved table. This bothers me because I believe the key
>>>requirements for a key-preserved view to allow deletes are satisfied:
>>>1) each record returned from the view corresponds to exactly one
>>>record in foo_output and 2) there is no other table referenced in the
>>>view for which this is true.
>>>
>>>To illustrate that there is nothing fundamentally wrong with my
>>>concept the following statement is acceptable to Oracle (but makes no
>>>sense in my application)
>>>
>>>DELETE FROM
>>> (SELECT
>>> *
>>> FROM
>>> FOO_OUTPUT fo,
>>> JOB_FOOS jf
>>> WHERE fo.foo_id = jf.foo_id
>>> AND jf.job_id = fo.seq_num) -- changed literal for job_id to join
>>>criteria
>>>/
>>>
>>>Based on this statement being acceptable and the first not being
>>>acceptable, I think the SQL engine is missing the fact that specifying
>>>that a parent table's (job_foos' in this case) primary key component
>>>is equal to a constant is just as valid as joining it to the child
>>>table (foo_output in this case) in determining whether the child table
>>>is key-preserved.
>>>
>>>Just to see if I could trick the SQL engine into seeing thing my way,
>>>I tried the following (unsuccessful) construction.
>>>
>>>DELETE FROM
>>> (SELECT
>>> *
>>> FROM
>>> (SELECT
>>> fo.*,
>>> 1 job_id
>>> FROM FOO_OUTPUT fo) fov,
>>> JOB_FOOS jf
>>> WHERE fov.foo_id = jf.foo_id
>>> AND jf.job_id = fov.job_id)
>>>/
>>>
>>>This generates the same ORA-01752: cannot delete from view without
>>>exactly one key-preserved table.
>>>
>>>Again, to illustrate that there is nothing fundamentally wrong with my
>>>new concept the following statement is acceptable to Oracle (but makes
>>>no sense in my application)
>>>
>>>DELETE FROM
>>> (SELECT
>>> *
>>> FROM
>>> (SELECT
>>> fo.*,
>>> 1 job_id
>>> FROM FOO_OUTPUT fo) fov,
>>> JOB_FOOS jf
>>> WHERE fov.foo_id = jf.foo_id
>>> AND jf.job_id = fov.seq_num) -- changed join to column from fo
>>>/
>>>
>>>Am I missing something or should Oracle handle this?
>>>
>>>Maybe Tom Kyte is reading and can point out my fundamental lack of
>>>understanding of key Oracle concepts (actually his posts are always
>>>far too nice to do that)--his posts are what got me so fired up to use
>>>key preserved views in the first place.
>>>
>>>Thanks,
>>>Dan Berkery
>>>
>>>PS For those who are interested, a little more background (not that
>>>any of this is really your problem, but in case anyone is interested).
>>> The fundamental thing I am trying to do here is to selectively delete
>>>a bunch of records (but not all) from foo_output as quickly as
>>>possible without blowing out rollback segments. This is not academic
>>>as I have honestly run on systems where foo_output has 170M records
>>>(fully loaded p690s, superdomes, and sun fires with all the
>>>trimmings), so efficency matters. The best idea I have so far is to
>>>do a delete on inlined view construction with limitations on rownum
>>>(eg delete rows in 10M row chunks)--actually that is a bit of a
>>>simplification since job_foos is broken up into batches and I can use
>>>that to limit the delete's size. The delete is actually a bit hairier
>>>than in the example because I have to join to a couple of other tables
>>>to identify which records should be deleted. Fortunately, the schema
>>>design is such that none of these other tables imperil delete from
>>>inlined, key preserved view query construction. To add to the fun, in
>>>the previous version of the real system I have never actually managed
>>>to saturate the IO subsystem (multiple high end RAID arrays) so in the
>>>next version I want to try to run as many as 50 deletes in parallel by
>>>taking advantage of the fact that the foos in job_foos are mapped into
>>>a set of MECE batches (a batch contains multiple foos within a job and
>>>each foo in a job is in exactly one batch). Obviously, the odds that
>>>I blow out rollback segments and/or generate snapshot too old errors
>>>(the tables are concurrently being inserted into, selected from, and
>>>deleted frow) is large but the customer has purchased some serious
>>>hardware and expects that it be used as efficiently as possible (and I
>>>agree with them!).
>>>
>>>If you are still reading all the way down here, thanks a lot for
>>>taking the time. At least I asked a question that has not been asked
>>>before :).
>>>
>>>Cheers,
>>>Dan Berkery
>>>
>>>PPS The email is just for SPAM so please respond to the news group.
>>
Received on Tue May 27 2003 - 14:44:27 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US