Oracle Database Administration for Microsoft SQL Server DBAs part 24 takes the administration topics with which the SQL Server DBA is familiar, translates them into Oracle terms, and then expands on Oracle functionality. Definitions and comparative terms run throughout the book so the SQL Server DBA can easily leverage existing knowledge. This Oracle Press guide also expands on some of the features in Oracle that do not match up directly with SQL Server, and looks at other processes often performed on an Oracle database that would not typically be a standard practice in SQL Server environments | 212 Oracle Database Administration for Microsoft SQL Server DBAs If not IOT the column IOT_TYPE is blank SQLPLUS select owner table_name IOT_TYPE from dba_tables OWNER TABLENAME IOTTYPE ACB01 ACB01 ACB01 ACB01 MSTR_TBL PRODUCTS SALES WORKTBL IOT IOT An iot_type of null means that the table is not an IOT. In Oracle it is typical to use b-tree indexes for the primary keys. The primary key indexes for Oracle are unique and help enforce data integrity but they do not need to be clustered. So if using an IOT is faster for access to a table why would you use a b-tree index instead As an example consider a table in which the primary key is an ID for the object or symbol that makes the row unique but you typically access the table by the date perhaps the effective date or load date . You could place an additional index on the IOT table but access might not be as fast as it would be if there were a b-tree index to access the table by date. And then both indexes must be maintained which might slow down the updates and inserts. Function-Based Indexes Oracle s function-based index type can dramatically reduce query time. In SQL Server if you need to use a string function or another function to compare the column in the where clause the index will not be used. However in Oracle you can create function-based indexes with the exact function to use so you can use an index instead of a full-table scan. Function-based indexes can be useful for large tables even with simple functions like UPPER to do string comparisons. f Example of using a function-based index SQLPLUS select employee_name from tbll where to_char hiredate MON MAY Plan SELECT STATEMENT TABLE ACCESS FULL TBL1 SQLPLUS create index IDX_TBL1_FUNC on TBL1 to_char hiredate MON Index created. Chapter 8 Performance and Tuning 213 SQLPLUS select employee_name from tbl1 where to_char hiredate MON MAY Plan SELECT STATEMENT TABLE ACCESS BY INDEX ROWID TBL1 INDEX RANGE SCAN IDX_TBL1_FUNC The function-based index can be a composite
