Tweet
Display partition information for a specified index
Displays partition information for the specified index, or all indexes. Read more…
Function Based Indexes
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:
When to Rebuild a B-Tree Index
You have to periodically check your indexes to see if they become skewed and, therefore, good candidates for rebuild.
Index Monitoring
Index monitoring could be initiated and stopped using ALTER INDEX syntax shown below.
Building virtual index using the NOSEGMENT clause.
Let’s create a virtual index
07:59:12 orcl> create index hr.emp2_emp_id_virtual on hr.employees2(employee_id) nosegment;
Index created.
How optimizer stats with Histograms can change execution Plan
Optimizer stats can play a key part in deciding execution plan. Here is an example
Table “RSODSACTREQ” has 313783 of total rows
How to Backup/Export Oracle Optimizer Statistics into Table
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.
Identifying a Poor performing SQL
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:
