Rebuild Index

An index rebuilt either Online or Offline.

Online Index Rebuild Features:
* ALTER INDEX REBUILD ONLINE;
* DMLs are allowed on the base table
* It is comparatively Slow
* Base table is referred for the new index
* Base table is locked in shared mode and DDLs are not possible
* Intermediate table stores the data changes in the base table, during the index rebuild to update the new index later
Sql> alter index index_name rebuild tablespace tablespace_name online

Offline Index Rebuild Features:
* ALTER INDEX REBUILD; (Default)
* Does not refer the base table and the base table is exclusively locked
* New index is created from the old index
* No DML and DDL possible on the base table
* Comparatively faster

Sql> alter index index_name rebuild tablespace tablespace_name

So, the base table is not referred for data when the index is rebuilt offline.

When an index is rebuilt offline there is no FTS on the base table. When index is rebuilt online all the blocks from the base table are accessed.

These conclusions are when we donot make an scenario when the index is unusable and then there
is data load to the base table, and finally the index is rebuilt.

Oracle 9i Architecture

Based on windows

Oracle9i on Windows is a stable, reliable, and high performing system upon which to build applications. Each release of the database provides new platform-specific features for high performance on Windows.

Oracle9i operates the same way on Windows as it does on other platforms. The architecture offers several advantages on Windows, such as:

* Thread-Based Architecture

* File I/O Enhancement

* Row file Support

Thread-Based Architecture

The internal process architecture of Oracle9i database is thread-based. Threads are objects within a process that run program instructions. Threads allow concurrent operations within a process so that a process can run different parts of its program simultaneously on different processors. A thread-based architecture provides the following advantages:

Faster context switching

Simpler SGA allocation routine, because it does not require use of shared memory

Faster spawning of new connections, because threads are created more quickly than processes

Decreased memory usage, because threads share more data structures than processes

Internally, the code to implement the thread model is compact and separate from the main body of Oracle code. Exception handlers and routines track and de-allocate resources. They add robustness, with no downtime because of resource leaks or an ill-behaved program.

Oracle9i database is not a typical Windows process. On Windows, an Oracle instance (threads and memory structures) is a Windows service: a background process registered with the operating system. The service is started by Windows and requires no user interaction to start. This enables the database to open automatically at startup.

When running multiple Oracle instances on Windows, each instance runs its own Windows service with multiple component threads. Each thread may be required for the database to be available, or it may be optional and specific to certain platforms.

Oracle Thread Description Required/Optional

DBWO database writer Required

LWGR log writer Required

PMON process monitor Required

SMON System monitor Required

Oracle9i for Windows is supplied as a set of executables and dynamic link libraries (DLLs). Executable images can be modified using ORASTACK to change the size of the stack used by the threads of the Oracle process. (Oracle Corporation recommends you use this tool only under the guidance of Oracle Support Services.)

File I/O Enhancement

Oracle9i database supports 64-bit file I/O to allow use of files larger than 4 gigabytes (GB). In addition, physical and logical raw files are supported as data, log, and control-files to support Oracle Real Application Clusters on Windows and for those cases where performance needs to be maximized.

All Oracle9i file I/O routines support 64-bit file offsets, meaning there are no 2 GB or 4 GB file size limitations when it comes to data, log, or control files, as there are on some other platforms. In fact, the limitations that are in place are generic Oracle limitations across all platforms. These limits include 4 million database blocks for each file, 16KB maximum block size, and 64K files for each database. If these values are multiplied, then maximum file size for a database file on Windows is 64 GB, and maximum total database size supported (with 16KB database blocks) is 4 petabytes.

Row file Support

Windows supports raw files, similar to UNIX. Using raw files for database or log files can have a slight performance gain. Raw files are unformatted disk partitions that can be used as one large file. Raw files have the benefit of no file system overhead, because they are unformatted partitions. However, standard Windows commands do not support manipulating or backing up raw files. As a result, raw files are generally used only by very high-end installations and by Oracle Real Application Clusters, where they are required.

To Oracle9i, raw files are no different from other Oracle9i database files. They are treated in the same way by Oracle as any other file and can be backed up and restored through Recovery Manager or OCOPY.