Displays partition information for the specified index, or all indexes. Read more…
Traditionally, performing a function on an indexed column in the where clause of a query guaranteed an index would not be used. Oracle 8i introduced Function Based Indexes to counter this problem. Rather than indexing a column, you index the function on that column, storing the product of the function, not the original column data. When a query is passed to the server that could benefit from that index, the query is rewritten to allow the index to be used. The following code samples give an example of the use of Function Based Indexes:
You have to periodically check your indexes to see if they become skewed and, therefore, good candidates for rebuild.
Index monitoring could be initiated and stopped using ALTER INDEX syntax shown below.
Let’s create a virtual index
07:59:12 orcl> create index hr.emp2_emp_id_virtual on hr.employees2(employee_id) nosegment;
Optimizer stats can play a key part in deciding execution plan. Here is an example
Table “RSODSACTREQ” has 313783 of total rows
Exporting and Importing Statistics
Caveat: Always use import/export and use imp/exp utility on schema user who owns tables. I have wasted a week where I was exporting as DBA for XYZ user and then importing into different system under different username.
Our preferred v$sqlarea query is to actually report on physical disk I/O per statement execution. Hit ratios are informative but sometimes misleading. Logical I/O is less relevant. If the statement executes 1,000,000 logical I/Os but still only takes less than one-tenth of a second, who cares? It is the total physical I/O that consumes nearly all the time and identifies the potentially incorrect SQL. For example: