Now that I have got your attention with my sales pitch. Let me quote a sales pitch we have heard quite often from Exadata Sales rep "Drop the indexes on Exadata ; It is a magical unicorn and it will guarantee performance even without them" Well, 50 years of computer science begs to differ. This … Continue reading Drop those Indexes and see the Magical Unicorn of Exadata do it’s Magic !
spool index_rebuild.sql select 'alter index '||owner||'.'||index_name ||' rebuild online nologging;' from dba_indexes where owner=upper('SAMPLE'); spool off
How to drop all indexes in a SCHEMA --Login as the schema user whose indexes you want to drop SQL>set pages 10000 SQL>set wrap off SQL>set heading off SQL>spool /u2/scripts/idxswx.txt SQL> select ' drop index ' || index_name ||';' from user_indexes; drop index IND1; drop index IND2; drop index IND3; SQL> spool off; -- This … Continue reading How to drop all indexes in a SCHEMA : Oracle 10g
Index Re-building is a debatable concept amongst DBA's. Experts like Ask Tom think rebuilding indexes doesn't create any considerable performance improvement. http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2913600659112 Testimony to this is a small exercise I did in my production environment. We have a table with over 10 million rows. A full table scan on this takes approximately 15 minutues. the … Continue reading Myths about Index Rebuilding