Set-based operation Versus Cursor-based operation

We develop a query to retrieve/process data. We could have our processing operation done in row level(one row after another row) or in the level of the whole data set (the whole set at once). We could use a cursor to process each row at a time (Cursor-based operation). The following examples* could help us understand the difference between set-based operation  and  cursor-based operation.

> SET-based operation

UPDATE s                       -- update the entire set of data at once.

   SET StatusCode = 'ACTIVE', ModDate = dbo.DateTrunc('day', GETDATE())

   FROM dbo.Site s

        INNER JOIN dbo.Contact c WITH (NOLOCK) ON s.SiteNo = c.SiteNo

> Cursor-based operation (row-based/serial) 

DECLARE @ls_SiteNo      CHAR(10)

DECLARE crsModule1 CURSOR LOCAL STATIC FORWARD_ONLY FOR

   SELECT s.SiteNo

   FROM dbo.Site s

   INNER JOIN dbo.Contact c WITH (NOLOCK) ON s.SiteNo = c.SiteNo

OPEN crsModule1

FETCH NEXT FROM crsModule1 INTO @ls_SiteNo

WHILE (@@fetch_status = 0 ) BEGIN

     UPDATE dbo.Site

     SET StatusCode = 'ACTIVE', ModDate = dbo.DateTrunc('day', GETDATE())

     WHERE SiteNo = @ls_SiteNo     -- update each row at a time.              

     FETCH NEXT FROM crsModule1 INTO @ls_SiteNo

END

CLOSE crsModule1

DEALLOCATE crsModule1

1) Database engines are optimized for set-based operation. However, there are some cases that a serial operation is the only or better option. Generally speaking, if we can avoid using a cursor, we would be better off.

2) When we deal with a smaller set of data, the difference between the two operations might be very minimal or we wouldn't even notice any difference in performance. But as the volume of data grows bigger, the performance difference will probably become more obvious.

3) Personally speaking, I believe that a set-based operation would be processed still serially inside of the database engine in the end. However, experts still recommend that we use a set-based operation if possible. It seems to me that it is a better idea to let the database engine take care of the serial data operation. It is probably not a good idea for us to manually figure out how to serially process data because the database engines normally know better than we do about the data operation. Just a thought...

-----------------------------------------------------------------

* Thank to Mr. Gord Gray who kindly provided me with these examples to help me understand on this subject.
 

Data Pivoting - how to create a pivot query

1.  Pivoting data
Itzik Ben-Gan describes, "pivoting data is a technique that rotates data from a state of rows to a state of columns" in his article, 'Pivoting Data' (SQL Server Magazine, January 2011). Why do we ever have to rotate data then?  Data pivoting allows us to summarize(aggregate) a given data set and re-organize(pivot) the structure of the original data set for a better report purposes.

2.  Creating a pivot query   
Let's say we have a table, called 'SalesOrderHeader.'  And we need to summarize the data and horizontally show SUM of TotalDue of each YEAR for all the TerritoryIDs.

 SELECT     TerritoryID
                   , TotalDue
                  , YEAR(OrderDate) OrderYear
  FROM      Sales.SalesOrderHeader

The report is supposed to summarize the data as following

3. A few things to identify before writing a PIVOT query.
- The data is to be grouped by TerritoryID (Grouping element)
- TotalDue column is to be aggregated (Aggregation element)
- The distinct values of YEAR(OrderDate) are used as the column headings of the pivoted result (Pivoted element. To be displayed horizontally.). And we need to know the distinct values of YEAR(OrderDate) in advance in order to write our Pivot query.  i.e) 2005, 2006, 2006, 2007

4. Writing a pivot query in SQL Server
4.1) SQL Server with CASE expression.

 SELECT     TerritoryID,
SUM (CASE WHEN YEAR(OrderDate) = 2005  THEN TotalDue ELSE 0 END) as [2005],
SUM (CASE WHEN YEAR(OrderDate) = 2006  THEN TotalDue ELSE 0 END) as [2006],
SUM (CASE WHEN YEAR(OrderDate) = 2007  THEN TotalDue ELSE 0 END) as [2007],
SUM (CASE WHEN YEAR(OrderDate) = 2008  THEN TotalDue ELSE 0 END) as [2008]
       FROM     Sales.SalesOrderHeader
       GROUP BY    TerritoryID
       ORDER BY    TerritoryID;

4.2) SQL Server with PIVOT operator

WITH BaseTable AS
 (
    SELECT     TerritoryID
                      , TotalDue
                      , YEAR(OrderDate) OrderYear
     FROM      Sales.SalesOrderHeader
  )
  SELECT    TerritoryID
    , [2005], [2006], [2007], [2008]
FROM    BaseTable
PIVOT  ( SUM(TotalDue)  FOR  OrderYear  IN ([2005],[2006],[2007],[2008]))  as PvtResult ;

5. Things to remember
- When using PIVOT operator, creating a base table using CTE is preferred. Include the columns needed only: a grouping column, pivoted column and aggregated column. It is because any column that is not pivoted or aggregated will be used as a grouping element.
-  IN list restricts the rows that are pivoted and supplies the pivoted column names. So if you omit [2007] from the above query, summation on TotalDue for year 2007 won't be done even if there are some data for year 2007.
- IN list is NOT DYNAMIC. We can not use a sub-query to populate this IN list. It should be hard-coded.
  (If the IN list is unknown, we can create a dynamic SQL statement. Dynamic pivoting is beyond the scope of this post. )
---------------------------------------------------
Reference: 
-  Querying Microsoft SQL Server 2012 Training Kit by Itzik Ben-Gan, Dejan Sarka, Ron Talmage     http://technet.microsoft.com
-  http://sqlmag.com/t-sql/create-pivoted-tables-3-steps  "Create Pivoted Tables in 3 Steps" by Kathi Kellenberger

NCHAR, NVARCHAR, NTEXT / NCLOB - supporting Unicode characters

As more companies deploy their database globally, their database needs to be able to handle Unicode. Unicode enables us to represent all the characters that are expressed in most of human written languages.

The following datatypes allow us to handle unicode characters.
   -  NCHAR, NVARCHAR,  NTEXT, NVARCHAR(max), NCLOB

And the following example shows how we can use these datatypes.

CREATE TABLE  unicodeDataTable
  (  text_id       number           Primary KEY,
     uni_text      nvarchar(20)

   );

INSERT INTO unicodeDataTable(text_id, uni_text) VALUES (1,  N'이동훈' );
INSERT INTO unicodeDataTable(text_id, uni_text) VALUES (2,  N'こんにちは' );

SELECT * FROM  unicodeDataTable;

   text_id       uni_text

   1               이동훈
   2               こんにちは

--------------------------------------------------------- 

NTEXT :  (SQL Server)  NText is going to be deprecated. Microsoft recommends that we use NVARCHAr(max).

NCLOB & CLOB  : (Oracle) store up to 8 to 128 terabytes of character data (11g) 

Table Variables vs. Temp Tables in SQL Server

SQL Server has 'table variables' that doesn't exist in Oracle. In Oracle, we may use a cursor to get the same task done.  In addition, it seems worth taking a note on the difference between Table Variables and Temp Tables in SQL Server.

(1) How to create and use a Table variable :  Primary Key, Unique Key and Not Null allowed.

     DECALRE @tableVariableName TABLE
      (
         Column1    INT   IDENTITY(1,1),
         Column2    VARCHAR(10) NOT NULL,
         Column3    MONEY,
         ...
      )

      INSERT INTO  @tableVariableName
      SELECT customerID, name, salary
      FROM  customers


      SELECT *   FROM @tableVariableName

(2) Differences between Temp tables and Table Variables

•  DDL operation - Temp tables can be altered with a DDL operation while Table variables cannot be.
• Statistics - SQL Server collects statistices for temp tables but not for table variables. 
• Data access - SQL Server uses various strategies to access temp tables with the table statistices collected (index density, distribution, selectivity, etc). But it accesses table variables through a table scan only.
•  Performance - many people say that we get slightly better performance benefit with a table variable when working on a small data set (< 100,000 rows). But for a bigger data sets, a temp table seems to be a better choice.

How to view the structure of a table in SQL Server

EXEC  sp_columns  table_name


* sp_columns : one of the catalog stored procedures that retrieve information from the system tables.
                       They are created by installmaster at installation and located in the sybsystemprocs database.
                       They are owned by the System Administrator.


* We would use DESC / DESCRIBE command in Oracle & MySQL.


----------------------------------------------------------------------------
Reference:   http://msdn.microsoft.com/en-us/library/aa259626(v=sql.80).aspx

Space management in Oracle - Shrink Segments

In Oracle database, table rows are stored in data blocks. Usually a block contains mutiple rows. However, in some cases, a certain row can reside in more than one data block. Also, the rows can moves from a block to another block. 

* Row migration -- When the length of a row increases, exceeding the available free space in the block, the entire row moves to a new data block. The system leaves a pointer at the original location of the row. That pointer points to the new migrated location of the row. 

* Row Chaining -- Another case is when we insert a very long(large) row that doesn't fit into a single data block. Then, the system stores the rows in a chain of data blocks(one or more)

* As we add, delete and modify data in a table, the rows could be easily spread out(location-wise). We all know the logical structure of the database: Segments, Extents, Data Blocks. A segment is a set of extents. And an extent is a specific number of contiguous data blocks. As we insert more data, delete and update data, the segment can be easily a sparsely populated segment. Then, what is the problem with a sparsely populated segment? We might have to read in more data blocks when we run a query because the rows are spread out across more data blocks. It is especially true for a full table scan. Reading more data blocks could degrade our database performance.

As shown below, we can improve our database performance and increase space utilization after SHRINK segment(space).  The tablespace must be in Automatic Segment Space Managed(ASSM) mode, and ROW MOVEMENT attribute of the table should be enabled in order to use the SHRINK feature.

 

The SHRINK process first moves sparsed rows so that the rows are put together. Then, it adjusts the HWM(the High Water Mark) so that the unused space can be released. With COMPACT option, it allows us to move the rows without adjusting HWM.

Flashback Database Vs. RMAN Recovery

1)  Flashback Database Vs. RMAN incomplete recovery
Flashing back a database to an earlier point in time yields a same result as an incomplete(Point-int-time) RMAN recovery would yield. But the difference is that Flashback Database uses the flashback logs stored in FRA and RMAN incomplete recovery uses a backup set and archived logs.

I was personally curious whether Flashing back a database is really faster than doing a incomplete RMAN recovery. Oracle says that it is. But I wanted to test myself. So I tested and found that it is true. I was even impressed with how quickly the testing flashback job was completed. I dropped a user(schema) in the testing database. The schema had a lot of data. Then I logged in the testing database through RMAN session and ran the flashback database command. It only took a few seconds.


2) When to use Flashback database?  When to use RMAN recovery?
Flashback technology seems to be very effective when we want to recover from some errors that has happened recently(not a few days ago or not a couple of weeks ago).  How far back can we flash back our database?  It depends on how long we keep our undo data(undo_retention) or our flashback logs (db_flashback_retention_target).  It seems that these two parameters are not normally set to a large number. They are probably set to a relatively smaller number with the consideration of disk space management. undo_retention is set to 15 minuites by default. And db_flashback_retention_target seems to be set to something like 48 hours according to Oracle's documentation in OTN.

So I would probably use RMAN Incomplete Recovery if I want to recover my database to a relatively more earlier point in time. (example:  5 days ago, 1 week ago, or 3 weeks ago)  Flashback database basically involves with rolling back our database incrementally to earlier point in time. Rolling back our database incrementally all the way back to the point in time 2 weeks ago or a month ago will probably takes much longer than restoring and recovering the database with a backupset and relatively a small set of archived logs.

In sum, I think that RMAN Backup/Recovery and Flashback technology are just complementary to making a proper and effective database recovery strategy. I guess that it is good to have both of them in place.


(3) How to configure/enable Flashback Database
- We need to first configure Fast Recovery Area(FRA) because the flashback logs are only stored in FRA.
- We also need to ensure that our database is running in Archivelog mode
- Set db_flashback_retention_target parameter.
    SQL> alter system set db_flashback_retention_target = 2880 scope=both;
- Open the database in MOUNT EXCLUSIVE mode.
- Alter database to Flashback ON
     SQL> alter database flashback ON;