Unused indexes
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Unused indexes

  1. #1
    Join Date
    Mar 2004
    Location
    IA USA
    Posts
    257

    Unused indexes

    DBAs,

    We have a troublesome application whose performance is very poor.
    I turned on the monitoring for all the indexes belonging to that application.

    After 24 hours monitoring, I found 210 out 425 indexes have not been used.
    60% of those unused indexes are foreign key related.

    Can I get rid of those unused indexes?
    Especially those on the big tables.
    Is it a good idea?

    Thanks!

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    NO.
    Indexes on FK are needed otherwise you will have locking issues.

    Tamil

  3. #3
    Join Date
    Mar 2004
    Location
    IA USA
    Posts
    257
    Quote Originally Posted by tamilselvan
    NO.
    Indexes on FK are needed otherwise you will have locking issues.

    Tamil
    Thanks!

    But after 24 hours monitoring, they are not used at all.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    so?

  5. #5
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    I agree, leave FK's indexed.

    Your space saving benefits would soon be overshadowed.

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Quote Originally Posted by tamilselvan
    Indexes on FK are needed otherwise you will have locking issues.
    That was true for pre-9i databases and only, if you deleted a parent key (with DELETE CASCADE defined on FK) or if you updated a PK column of the parent that was referenced by child's FK.

    But since 9.0, even when any of those two (rather unusual) situation occurs, Oracle doesn't need index on FK to prevent locking of child table. In short, indexes on FKs are not needed any more to prevent locking.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by jmodic
    That was true for pre-9i databases and only, if you deleted a parent key (with DELETE CASCADE defined on FK) or if you updated a PK column of the parent that was referenced by child's FK.

    But since 9.0, even when any of those two (rather unusual) situation occurs, Oracle doesn't need index on FK to prevent locking of child table. In short, indexes on FKs are not needed any more to prevent locking.
    Modic, I disagree with your points.
    I do not know how you tested.
    Here is my test.

    The following demo was tested in Oracle 9.2.0.5

    The easiest way to check FK Indexes are needed is disable
    table lock on the child table.

    SQL> get cr_p
    1 CREATE TABLE supplier
    2 ( supplier_id numeric(10) not null,
    3 supplier_name varchar2(10) not null,
    4 contact_name varchar2(10),
    5 CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
    6 );
    7 CREATE TABLE products
    8 ( product_id numeric(10) not null,
    9 supplier_id numeric(10) not null,
    10 CONSTRAINT fk_supplier
    11 FOREIGN KEY (supplier_id)
    12 REFERENCES supplier(supplier_id)
    13 ON DELETE CASCADE
    14* );
    15
    SQL> @cr_p

    Table created.


    Table created.

    SQL> desc supplier
    Name Null? Type
    -------------------------------- -------- ---------------------------
    SUPPLIER_ID NOT NULL NUMBER(10)
    SUPPLIER_NAME NOT NULL VARCHAR2(10)
    CONTACT_NAME VARCHAR2(10)

    SQL> insert into supplier values (1, 'Name-1','Scott');

    1 row created.

    SQL> insert into supplier values (2 , 'Name-2','Tom');

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> desc products
    Name Null? Type
    --------------------------------------- -------- ---------------------------
    PRODUCT_ID NOT NULL NUMBER(10)
    SUPPLIER_ID NOT NULL NUMBER(10)

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

    1 row created.

    SQL> insert into products values (2,2) ;

    1 row created.

    SQL> commit;

    Commit complete.

    REM The next command is very important to understand how Oracle works

    SQL> alter table products disable table lock;

    Table altered.

    SQL> delete supplier where supplier_id = 1 ;
    delete supplier where supplier_id = 1
    *
    ERROR at line 1:
    ORA-00069: cannot acquire lock -- table locks disabled for PRODUCTS


    Why should I get an error here?
    Why should oracle try to acquire "Table Lock" on products?
    I am just deleting one row from the parent table and one row from the child by
    way cascade delete.


    SQL> rollback;

    Rollback complete.

    SQL> alter table products enable table lock;

    Table altered.

    SQL> delete supplier where supplier_id = 1 ;

    1 row deleted.

    SQL> commit;

    Commit complete.

    Once Oracle table lock was enabled, Oracle was able to delete rows on both table.

    Let us see how an Index on FK plays an important role.


    SQL> select * from supplier ;

    SUPPLIER_ID SUPPLIER_N CONTACT_NA
    ----------- ---------- ----------
    2 Name-2 Tom

    SQL> select * from products ;

    PRODUCT_ID SUPPLIER_ID
    ---------- -----------
    2 2

    I inserted deleted rows again.

    SQL> insert into supplier values (1,'Name-1','Scott');

    1 row created.

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

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> alter table products enable table lock;

    Table altered.

    SQL> create index products_fk_idx on products(supplier_id);

    Index created.

    SQL> alter table products disable table lock;

    Table altered.

    SQL> select * from supplier;

    SUPPLIER_ID SUPPLIER_N CONTACT_NA
    ----------- ---------- ----------
    2 Name-2 Tom
    1 Name-1 Scott

    SQL> select * from products ;

    PRODUCT_ID SUPPLIER_ID
    ---------- -----------
    2 2
    1 1

    SQL> delete supplier where supplier_id = 1;

    1 row deleted.

    SQL> commit;

    Commit complete.

    SQL> select * from supplier;

    SUPPLIER_ID SUPPLIER_N CONTACT_NA
    ----------- ---------- ----------
    2 Name-2 Tom

    SQL> select * from products ;

    PRODUCT_ID SUPPLIER_ID
    ---------- -----------
    2 2

    Even when the "table lock" is disabled but an index on FK is present,
    Oracle is able to delete rows in both parent and child table.


    Tamil
    Last edited by tamilselvan; 12-09-2005 at 10:44 PM.

  8. #8
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    so what, drop those unused indexes and if you run something like an erp system wait for the end of the quarter of year end when those processes that run only once and awhile and see what happens. Are they realy hurting you? can you prove it?
    I'm stmontgo and I approve of this message

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Almost by definition, dropping objects that are not used is never going to help improve performance. The trouble here is that you're guessing abouyt the cause of your poor performance instead of measuring the system activity.

    Gather a 15 minute interval statspack run tas a starting point, and see what the major waits on the system are and what the most resource intensive SQL statements are. You'll then have a starting point for actual analysis, instead of guesswork.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Quote Originally Posted by tamilselvan
    Modic, I disagree with your points.
    Nothing wrong with that, Selvan.

    REM The next command is very important to understand how Oracle works

    SQL> alter table products disable table lock;

    Table altered.
    Well, maybe the above command was appropriate for yor demonstration, but in reality this is one of the most obscure command that you'll never find in any of the serious application on the oracle database.

    I didn't say oracle doesn't need any lock on the table when you delete the row from the primary that has ON DELET CASCADE on the referencing foreign key. I just said you doesn't have locking isues any more if you don't have forign key indexed. And I still stick to it, despite your demonstration. The fact that you don't need an index on FK to prevent DML operations on child when you delete a row or update a PK column on the parent was one of the more prominent features advertised when Oracle 9i came out. And it wasn't just marketing hype, it realy works at it was advertised.

    SQL> alter table products disable table lock;

    Table altered.

    SQL> delete supplier where supplier_id = 1 ;
    delete supplier where supplier_id = 1
    *
    ERROR at line 1:
    ORA-00069: cannot acquire lock -- table locks disabled for PRODUCTS

    Why should I get an error here?
    Because you disabled Oracle from doing it the way it normaly does it with your DISABLE TABLE LOCK command. But in reality you will never use that command, would you? So would you have a locking isues if you don't have an index on that foreign key? No, of course not, if you would allow Oracle to do the things as it was deseigned for.

    It is well known that since Oracle 9i, if you delete a row from the parent table and if the foreign key was defined with DELETE CASCADE, Oracle will have to obtain a shared table lock on the child table for a very short period of time, "but then releases it immediately after obtaining it", as it says in the documentation. So yes, it still needs a shared table lock, but only momentarily - after it gets it, it releases it immediately and other transactions are happily permited to perform DML operations on the child table. They arent blocked, they don't have any locking issues at all.

    So, in your demonstration, ommit the "ALTER TABLE DISABLE TABLE LOCK" statement and after deleting a row from the parent table, try to insert/update/delete rows from your child table - you'll se that you are permited to do so, even when your FK is not indexed. You have no locking issues on child table whatsoever! That is a big difference compared from pre-9i releases. If you would try to do so with the pre-9i database, your DML on child table will fail, because the delete on the parent table will cause a permanent (for the duration of the parent transaction) lock on the child table.

    Now, I didn't say you don't need indexes on FK columns at all in 9i and above. Perhaps your queries need them. Also, if you have an ON DELETE CASCADE and you delete rows from your parent table, you'd probably wan't to have an index on it for performance reasons. What I said was: you don't need them simply because of the locking issues. That was true until 8i, but since 9i you can perform DML operations on the unindexed child table even while the transactions on your parent table are modifying rferenced primary/unique key.

    Demonstration with your example (also performed on 9.2.0):
    Code:
    SQL> select * from supplier;
    
    SUPPLIER_ID SUPPLIER_N CONTACT_NA
    ----------- ---------- ----------
              1 Name-1     Scott
              2 Name-2     Tom
    
    SQL> select * from products;
    
    PRODUCT_ID SUPPLIER_ID
    ---------- -----------
             1           1
             2           2
    
    SQL> select constraint_name, r_constraint_name, delete_rule, status
      2  from user_constraints where table_name = 'PRODUCTS' and constraint_type = 'R';
    
    CONSTRAINT_NAME                R_CONSTRAINT_NAME              DELETE_RU STATUS
    ------------------------------ ------------------------------ --------- --------
    FK_SUPPLIER                    SUPPLIER_PK                    CASCADE   ENABLED
    
    SQL> select * from user_indexes where table_name = 'PRODUCTS';
    
    no rows selected
    
    SQL> delete from supplier where supplier_id = 1;
    
    1 row deleted.
    
    SQL>
    Now, in another session let's try to perform some DML on the child table:
    Code:
    SQL> insert into products values (3,2);
    
    1 row created.
    So, we vere able to perform DML on child table even though we doesn't have any index on the foreign key columns of the child table. Did we encountered any locking isues? Certanly not.

    In case we tried that on 8.1.7 or below, our child transaction would be blocked by the permanent child table lock obtained by the transaction on the parent table.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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


Click Here to Expand Forum to Full Width