Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2008
    Posts
    3

    Unanswered: fk oracle vs sql server

    Hello,
    we seen a diferen explain plan for row deletion on oracle & sql server when fk are involved:

    sql server

    create table master (a int primary key)

    create table child (b int primary key, a int references master(a))

    insert into master values (1)

    insert into child values (1,1)

    Now we try to delete on master table and see execution plan

    set showplan_all on

    delete from master
    |--Assert(WHERECASE WHEN NOT [Expr1010] IS NULL THEN (0) ELSE NULL END))
    |--Nested Loops(Left Semi Join, OUTER REFERENCES[dba].[dbo].[master].[a]), DEFINE[Expr1010] = [PROBE VALUE]))
    |--Clustered Index Delete(OBJECT[dba].[dbo].[master].[PK__master__27C3E46E]))
    | |--Top(ROWCOUNT est 0)
    | |--Clustered Index Scan(OBJECT[dba].[dbo].[master].[PK__master__27C3E46E]))
    |--Clustered Index Scan(OBJECT[dba].[dbo].[child].[PK__child__29AC2CE0]), WHERE[dba].[dbo].[child].[a]=[dba].[dbo].[master].[a]))

    As you can see it makes a scan on child table to see if is there any row referencing to master table.

    Now oracle:

    SQL> create table master (a number primary key);


    SQL> create table child (b number primary key, a number references master(a));

    SQL> insert into master values (1)

    SQL> insert into child values (1,1)

    now explain plan:

    SQL> explain plan for delete from master;

    Explicado.

    SQL> select * from table (dbms_xplan.display);

    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------

    Plan hash value: 1541769243

    -----------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    -----------------------------------------------------------------------------
    | 0 | DELETE STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
    | 1 | DELETE | MASTER | | | | |
    | 2 | TABLE ACCESS FULL| MASTER | 1 | 13 | 2 (0)| 00:00:01 |
    -----------------------------------------------------------------------------

    As you can see on oracle there is no check on chilld table.

    Now the question... does it mean tha oracle manages fk more efficiently than sql server? We have a lot of troubles on sql server when deleting on parent tables which have a lot of child tables with fks referencig it because it checks on ALL child tables, is it oracle different? or is the display of explain plan more detailed on sql server?

    Thanks a lot and sorry for my poor english..

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    The Oracle explain plan doesn't show how the constraint is checked. I don't know the relative performance of Oracle and SQL Server re constraint checking, but Oracle certainly does it fast enough - provided you index the foreign key column, i.e. :
    Code:
    create index child_a on child(a);
    Without the index, Oracle will lock the entire child table during a delete from the parent. Oracle does not automatically index foreign keys, you have to explicitly add the index.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by andrewst
    Without the index, Oracle will lock the entire child table during a delete from the parent.
    Are you sure about that?
    Just because it scans the table, that doesn't mean it actually locks it (at least not for read access)

  4. #4
    Join Date
    Nov 2008
    Posts
    3
    lot of thanx andrewst for your answer

    So oracle ignores fk.This really surprised me, because the cost in the xplain plan is not affected; even if you create 4 child tables the cost is the same:

    SQL> create table child2 (b number primary key, a number references master(a));

    Tabla creada.

    SQL> create table child3 (b number primary key, a number references master(a));

    Tabla creada.

    SQL> create table child4 (b number primary key, a number references master(a));

    Tabla creada.

    SQL> insert into child2 values (1,1);

    1 fila creada.

    SQL> insert into child3 values (1,1);

    1 fila creada.

    SQL> insert into child4 values (1,1);

    1 fila creada.

    SQL> explain plan for delete from master;

    Explicado.

    SQL> select * from table (dbms_xplan.display);

    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------

    Plan hash value: 1541769243

    -----------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    -----------------------------------------------------------------------------
    | 0 | DELETE STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
    | 1 | DELETE | MASTER | | | | |
    | 2 | TABLE ACCESS FULL| MASTER | 1 | 13 | 2 (0)| 00:00:01 |
    -----------------------------------------------------------------------------

    Really strange, it seems a little poor vs sql server plan!!! (on sql server Total subtree cost increases on 70%)

    Thanx in advance

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    So oracle ignores fk.
    Definitely not, you will get an error when actually running that statement.

    Quote Originally Posted by oracleman69
    it seems a little poor vs sql server plan!
    Why? As you are deleting the whole table (no WHERE clause), the only sensible thing to do is a full table scan
    (on sql server Total subtree cost increases on 70%)
    You can't compare execution plan costs between two completely different databases systems That simply doesn't make sense.

    You can't even compare the costs for two different SQL statements in Oracle, because the cost value is not an absolute figure it's just relative for a single statement and allows the planer to choose the best plan for that specific statement.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by shammat
    Are you sure about that?
    Just because it scans the table, that doesn't mean it actually locks it (at least not for read access)
    Well, it used to - apparently not any longer:
    Concurrency Control, Indexes, and Foreign Keys.

    It never affected read access, but it prevented anyone updating the child table until the transaction that was deleting from the parent table ended.

    Even now, without the index, each delete from the parent table requires a full scan of the child table to check for references - not good for performance.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by oracleman69
    So oracle ignores fk.
    I presume you mean, Oracle ignores the cost of enforcing the FK constraint in its query plans? I had never thought about that before, but I believe you are correct. I'm not convinced it matters, though, since the whole point of query plans is to choose the best way to implement the request; the existence of foreign keys referencing the table being deleted from doesn't have any bearing on this - whichever way Oracle accesses the rows to be deleted, it has to perform the same foreign key checking, it is a constant overhead for the purposes of comparing plans, so computing it is a waste of time.

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by andrewst
    Even now, without the index, each delete from the parent table requires a full scan of the child table to check for references - not good for performance.
    Yes, very true.
    Another good reason for always indexing FK columns

  9. #9
    Join Date
    Nov 2008
    Posts
    3
    Quote Originally Posted by andrewst
    I presume you mean, Oracle ignores the cost of enforcing the FK constraint in its query plans? I had never thought about that before, but I believe you are correct. I'm not convinced it matters, though, since the whole point of query plans is to choose the best way to implement the request; the existence of foreign keys referencing the table being deleted from doesn't have any bearing on this - whichever way Oracle accesses the rows to be deleted, it has to perform the same foreign key checking, it is a constant overhead for the purposes of comparing plans, so computing it is a waste of time.
    Yes........ but, anyway, that info is relevant!: if a child table has 100 records the check on that table (when deleting on parent table) will be less than on a 100 million child table... That "big" increment of final cost could be important to show on a deletion explain plan...

    Are you sure the checks to fks are the same on oracle than on sql server?

    Lot of thanx!

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by oracleman69
    Yes........ but, anyway, that info is relevant!: if a child table has 100 records the check on that table (when deleting on parent table) will be less than on a 100 million child table... That "big" increment of final cost could be important to show on a deletion explain plan...
    I know what you mean, but as I said before - for the purposes of query optimisation this information is deemed to be irrelevant and unimportant - because it is the same for all plans. However, I do see your point somewhat: if you had an unindexed foreign key on a large child table and the plan showed this child table being FULL scanned for each parent row, at great cost, it might prompt you to add that missing index!

    Quote Originally Posted by oracleman69
    Are you sure the checks to fks are the same on oracle than on sql server?
    They will be the same logically, yes. The physical method of implementation no doubt varies greatly under the hood.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •