TIP 94#: Good to know/be reminded of

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 :

Create index on all necessary columns and Oracle does use FFIS as tiny version of table.

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


goryunov said...

Thanks Shervin,

could you please wrap sentences
since part of them not readable
(as far as they gone outside of
post column right border)

Facebook Applications said...

This one is awesome . Thanks alot for posting everything on my blog.