Knowledge Sharing

Pivot function exists both in Oracle as well as MS-SQL. It is used to convert column result into rows for the specific values. There is not much difference between Oracle and MS-SQL. It can be better explained with the help of example. Let’s create a table called ProductSales which stores the information about sales person, product and the sales amount. create table ProductSales (          salesperson   varchar(50),          Product        varchar(20),          salesAmount decimal(14,4) ) Insert the below values insert into ProductSales values ('Jithesh','Pickles',100) insert into ProductSales values ('Jithesh','Pickles',25) insert into ProductSales values ('Jithesh','Oranges',300) insert into ProductSales values ('kb','Oranges',50) insert into ProductSales values ('kn','Oranges',500) insert into ProductSales values ('kn','Pickles',44) Table looks like salesperson  

In the previous post we had discussed about the features and use of views. Now it is the time to discover the use of materialized views. It stores the data which is coming from the view as a table. This is most useful in the data warehousing field. Materialized views improve the performance not only because of the presence of index but also below mentioned points. • Aggregations can be pre computed and stored in the index to minimize expensive computations during query execution. • Tables are pre joined and results get stored • Combination of tables and aggregation can be stored We should carefully decide to go for materialized view or not because it will take more overhead on the memory and also maintenance. Consider the below points • The query which are having more joins • More aggregate results • As a normal index on table, this also gives performance improvement only if we make use of the index We cannot have self join in materialized view in MS-Sql as well as

In continuation with the topic 'Oracle Vs MS-SQL : Views' , I would like to say about indexes before taking you to the materialized views, because materilazied views use the Index. Index is not a new concept or strictly database concept. It is similar as book index which helps to find out a paricular topic instead of searching all the pages to find out the topic. Index in database is used to improve the performance of the query. Index concept is there in both oralce as well as  MS-SQL.  Indexes are most useful on larger tables, on columns that are likely to appear in 'where ' clauses. We can create index on multiple columns and it is better to create less number of index with multiple column instead of creating index on each columns. If we create more index on the table then the DML operation will take longer time to execute. There are two types of Index, first one is Btree and the other is Bitmap, but MS-SQL does not support Bitmap seraching. Btree A B-tree consist

In continuation with the discussion on Oracle vs. MSSQL, today I will address the views in two databases. There are two different types of views – simple views and materialized views. Views are created to hide the complexity of SQL statements to the end-applications and querying agents. Views can include queries to multiple tables, rules and transformations, and even aggregations. Simple views do not contain data, just SQL statements which use the underlying tables to fetch data while executing. Materialized views persist data in and queries to underlying tables are not made unless the view is refreshed explicitly. With simple views, it is also possible to perform an update statement into the base table. In such case the following operators should not be present in the SQL inside the view. Set Operators ( INTERSECT, MINUS|EXCEPT, UNION, UNION ALL ) DISTINCT GROUP BY ORDER BY JOIN INTO The updateable views should not contain subqueries as well. The syntax to create v

Sql set operations allows us to combine the results from multiple query into one. It is purely based on the mathematical set theory. Each sql statement can be treated as one set and we can combine this result by the operator. We should make note of the point that if you want to use these operators there should be equal number of columns selected in all the queries and also data type of each column should match with the same column of other queries. In case of oracle same data type in all the queries are not required since it will implicitly convert the data type but not in all the cases. For e.g handling null. Select 1 num, a name from dual Union Select null, ‘jithesh’ name from dual; ERROR at line 1: ORA-01790: expression must have same datatype as corresponding expression Operator ORACLE MS-SQL UNION Available Available UNION ALL Available Available INTERSECT Available Available MINUS Available except We will use the below ment