I was reading couple articles from Tom Kyte in Oracle magazine and I found it would be great to mention some here for reminder/reference ...
Fast Full Index Scan va Full Index Scan
*****************************************
FFI reads the entire index, unsorted. It is called as Tiny version of table. (If you want to make a tiny version of table on some columns, it could be an option as long as you have at least one NOT NULL column). FFI uses Mutiblock IO and it reads the entire index including leaf blocks, branches and root and just ignore branches and root.
FIS however, reads single block, it starts from root and goes down to branche and the leaf blocks and then when it hits the leaf blocks, it reads the bottom of the index.
Null values in bitmap indexes
***********************************
Unlike B*Tree indexes, bitmap indexes always index NULL values.Every row in table is indexed by bitmap index not matter if the value is NULL or NOT NULL
Wide table
***************
If you have a wide table but couple columns are in use not all, in order to improve the performance of fetching records, the following two options can be considered :
Option1
---------
Create index on all necessary columns and Oracle does use FFIS as tiny version of table.
Option2
---------
Break down table to two table, one with most frequent used columns and one with less frequent used columns. Have a view on these two tables and use the view, Whenever most frequent used columns are accessed, Oracle automatically elimiate the second table.
Basic sample :
maintab (col1, col2, col3, col4, col5)
tab1 (col1, col2, col3)
tab2 (col1, col4, col5)
view as select col1,col2,col3, col4,col4 from tab1, tab2 where tab1.col1=tab2.col1
DDL in trigger
*******************
It is wrong to use any DDL in trigger as DDL does implicit commit. Still with pragma autonomous_transaction, it is wrong to use DDL in trigger. If there is no choice, it is better to submit DDL as a job instead of executing DDL in trigger..
Easy Connect
*****************
10g on wards, allows to use easy connect to connect to DB. For easy connect, in sqlnet.ora we should have EZCONNECT in NAMES.DIRECTORY_PATH. If this setting is in place, easy connect can be used in this way - //host:port/db
