Sybase IQ - First impressions

Sybase IQ is a column-store database unlike regular databases which are row-store. This means that Sybase IQ breaks every row up into columns and stores these columns in seperate data pages. Apparently, this allows for much better data compression and also automatic indexing of all columns (perhaps because each column's data pages are stored in the form of a Btree a la Oracle's Index-Organized Tables). Sybase IQ is recommended for analytical (read DW) environments where queries need not necessarily read all the columns although they may read a large number of rows. I am not able to relate to this fully because in regular star schemas (sales, production, inventory, etc), every query picks up all the measures anyway and will probably have filters on most dimensions. So where is the question of improving IO by only reading the required columns? I guess this is more relevant to the finance area where there are perhaps multiple dimensions (like Account, Project, Department, Location, Currency, etc) and maybe multiple measures also. In such a case, if a typical query only needs to look at, say, 40% of the columns from every row, then storing the data in a column-store DBMS like Sybase IQ may have significant advantages?

There is an excellent article on how the Sybase IQ optimiser works, how to interpret query plans, important parameters to understand, etc. Incidentally, the explain plan produced by Sybase IQ is very impressive and has tons of information!

This site seems to have some amazing presentations related to Sybase from TechWave 2003! If you are a Sybase user, you will find loads of useful stuff here. The presentation BID209 has many useful tips on when and what type of indexes to create, for example.

MOLAP vs ROLAP (DOLAP)

The obvious differences between MOLAP and ROLAP are probably around performance (MOLAP is faster), maintenance (ROLAP is easier) and perhaps size (cubes may not scale well beyond some GB). However, there are some fundamental differences in functionality which we seem to miss out when deciding one way or the other. MOLAP inherently supports a lot of time-series analysis which can only be achieved using extensive analytic functions (if the DBMS supports it, that is) in ROLAP. So, if you have a smallish DW (less than 100 GB, say) and do extensive time-series analysis (e.g., YTD sales comparison), weighted averages, etc., read this and you may decide on MOLAP (or MOLAP + ROLAP)!

Migrating from SQL Server to Oracle

Looks like migration between databases can be quite a painful job indeed! There are bound to be issues around data types, temporary tables and stored procedures returning result sets to start with. The Oracle Migration Work Bench should be able to do a decent job though (it would be pointless to attempt a manual migration). These docs should cover all the areas where migration is likely to be an issue. An older version of the migration whitepaper is here. If you are estimating for a migration, this whitepaper is a MUST READ!

This blog may be useful for general tips and issues regarding migrations (the person has moved out of Oracle now and I haven't read the blog mysef).