RE: [sqr-users] Re-Indexing a Table in SQR

["George Jansen" <GJANSEN@aflcio.org>]

You can use the ALTER INDEX ... REBUILD command. Of course if the data is 
inserted out of order, there's only so much this will do--you can have a nice, 
tight index with the minimum of leaf blocks but a rotten clustering factor. 
Anyway, as an example of what you can do, here's a screen capture:

/tmp>cat rebuild.sqr
BEGIN-PROGRAM

BEGIN-SELECT
To_Char(Sysdate, 'YYYY-MM-DD HH24:MI:SS') AS &point_a
  show  'started program at: '
  show &point_a
FROM dual
END-SELECT

BEGIN-SQL
DELETE FROM insertion_target;
END-SQL

BEGIN-SQL
INSERT INTO insertion_target
        (dummy)
SELECT object_id
FROM (SELECT object_id FROM all_objects
      ORDER BY owner, object_name);
END-SQL

BEGIN-SQL
ALTER INDEX insertion_target$dummy REBUILD;
END-SQL

BEGIN-SELECT
To_Char(last_ddl_time,  'YYYY-MM-DD HH24:MI:SS') AS &point_b
  show 'rebuilt index at'
  show &point_b
FROM user_objects
WHERE object_type = 'INDEX'
  AND object_name = 'INSERTION_TARGET$DUMMY'
END-SELECT

END-PROGRAM
/tmp>    
/tmp>sqr rebuild.sqr
Enter Username: scott
Enter Password: 

SQR V4.3.2.2
Copyright (C) SQRIBE Technologies, 1994-98.  All Worldwide Rights Reserved.

started program at: 
2006-05-12 13:55:16
rebuilt index at
2006-05-12 13:55:42

SQR: End of Run.





_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users