Reduce Disk I/O | Process   «Prev  Next»

Using CTAS to re-sequence table rows - Exercise

Re-sequence a table

Objective:Re-sequence the ORDERS rows into a new tablespace.

Exercise scoring

This exercise is worth a total of 10 points. Once you complete your answers, submit them to a tutor.

Background

You have noticed from your bstat-estat report that your ORDERS table has been getting a high amount of disk I/O. You are running Oracle with 8k blocks and each row is 80 bytes in length. Over 95% of the queries against the order table use the ORDER_NBR_IDX index and access an average of 10 rows per query. Your index creation syntax is on a file called /tmp/order_idx.sql.

Instructions

You have been asked to re-sequence the ORDERS rows into a new tablespace. You have already created an ORDER_NEW tablespace for this purpose and planned to do this on Christmas Day when there is nobody on the database. Write the statements CTAS, RENAME, index create and ANALYZE statements required to accomplish this task.
Hints: Check the clustering_factor for the index.

Submitting your exercise

Enter your queries in the text box below and click the Submit to submit your responses.