Re: Indexes and Foreign Keys

"Richard Foote" <richard.foote@xxxxxxxxxxxxxxxxxx> wrote in message
news:GYbRf.5391$dy4.2655@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Hi Mark,

Just on the issue of deleting a PK value.

I agree one would hopefully only delete a PK value if all the child rows
are already gone (Oracle won't like it otherwise unless you cascade delete
of course) but the question is how does Oracle confirm there are no
matching FK values? Yes lookup parent tables are mostly small, but the
child tables could be large, very very large.

And the only way for Oracle to confirm there's nothing in these
potentially massive child tables that could violate a parent record being
deleted is to use a suitable index on the FK column or perform an
expensive full table scan.

A common problem I come across is not indexing those FKs where parent
values are commonly deleted, especially when performance of the delete
operation is at issue.

Cheers

Richard

If the cardinality of the FK is high (lots of unique values) then having an
index is usually a good idea. But if the cardinality is low (especially if
fewer than 10) then it is usually just as fast for the cascade delete to do
a table scan as it is to use the index to delete the rows. This is not
unusual if the FK is associated to a parent "code" table, such as division
code, country code, etc where the main purpose is to use the parent table to
ensure the validity of the data on the FK.

Generally speaking an index will be used to satisfy an SQL statement if the
number of blocks needed to be accessed can be reduced compared to a table
scan. But if there are only 10 unique values of the FK, then there is
probably at least one row on each block for any particular value. Obviously
this assumes that the distribution of values in the FK is roughly the same
(not highly skewed) and other factors such as the length of the row (which
determines the average number of rows in a block). If you are often deleting
parent rows when all the matching child rows have already been deleted, then
having an index is a good idea because the data would be highly skewed in
that situation (zero rows for that particular value).

There are some other factors that might need to be considered such as how
often such a cascade delete is performed, and concurrency considerations
that could dictate using the index even if it is not faster than a table
scan.


.

Relevant Pages

  • RE: Parent-Child relations in a dataset
    ... How do I keep pending parent and children in synch? ... that actually exist in the database. ... use those placeholder values for your pending child rows. ... "SELECT SCOPE_IDENTITY" query to retrieve the last identity ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Updating and Retrieving Data
    ... How do I keep pending parent and children in synch? ... that actually exist in the database. ... use those placeholder values for your pending child rows. ... "SELECT SCOPE_IDENTITY" query to retrieve the last identity ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: datatable and sql Adapter
    ... If your database has the correct referential integrity already set up, ... disconnected...the problem is how to NOT violate primary key/ identity ... Parent we'll say invoice ... how am i to get the child rows the id key of the parent to ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: datatable and sql Adapter
    ... Im Inserting into Datatables From a Text File ... Parent we'll say invoice ... how am i to get the child rows the id key of the parent to ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Database and Dataset setup.
    ... The first solution is the better choice, but it won't work like that. ... deleting the rows out of the parent table sets the child rows to the deleted ... updating the child table first would try to add the child rows before ...
    (microsoft.public.dotnet.framework.adonet)