Systems Engineering and RDBMS

Archive for the ‘Oracle’ Category

New way of paging in SQL Server 2012 – Offset clause

Posted by decipherinfosys on March 21, 2012

For any web application, paging of records is a necessary requirement.  We have written several posts in the past on how to achieve paging in Oracle, SQL Server and DB2 LUW.  Those posts showed how paging can be achieved using CTE/custom procedural code/row_number() function (SQL Server), using rownum (Oracle) or rownumber()/fetch first clause (DB2).  Here are some of those posts to familiarize yourself with the different ways of paging in different RDBMS:

Whitepaper on paging in Oracle, SQL Server and DB2 LUW (bit dated – wrote it in 2007): https://decipherinfosys.wordpress.com/2007/01/18/paging-data-in-oracle-ms-sql-server-and-db2-luw-using-database-code/

Paging of Record Sets and COUNT(STOPKEY) Optimization in Oracle: https://decipherinfosys.wordpress.com/2007/08/09/paging-and-countstopkey-optimization/

Paging of record sets in SQL Server 2005 or SQL Server 2008: https://decipherinfosys.wordpress.com/2007/08/10/paging-of-record-sets-in-sql-server-2005/

Dynamic SQL, Dynamic Ordering and Paging: https://decipherinfosys.wordpress.com/2008/04/22/dynamic-sql-dynamic-ordering-and-paging/

In today’s post, we will introduce a new clause that is now available in SQL Server 2012 which makes writing the paging logic a lot more simpler to read and implement.  Here is an example:


DECLARE
@NbrofRecords INT = 15,
@PageNbr INT = 3,
@OrgTypeID INT = 23,
@OrgName NVARCHAR(100) = NULL;


SELECT OM.ORG_MASTER_ID, OMTX.ORG_TYPE_LKUP_ID, OTL.ORG_TYPE_NAME
, COALESCE (MFFM.FUND_FAMILY_NAME, OM.ORG_NAME) AS ORG_NAME, MFFM.MUTUAL_FUND_FAMILY_MASTER_ID
, COALESCE (CM.FIRST_NAME, ' ') + COALESCE (CM.MIDDLE_NAME, ' ') + COALESCE (CM.LAST_NAME, ' ') AS CONTACT_NAME
, CM.CONTACT_MASTER_ID FROM dbo.ORG_MASTER AS OM
INNER JOIN dbo.ORG_MASTER_TYPE_XREF AS OMTX
ON OM.ORG_MASTER_ID = OMTX.ORG_MASTER_ID
INNER JOIN dbo.ORG_TYPE_LKUP AS OTL
ON OMTX.ORG_TYPE_LKUP_ID = OTL.ORG_TYPE_LKUP_ID
LEFT OUTER JOIN dbo.ORG_TYPE_CONTACT_XREF AS OTCX
ON OMTX.ORG_MASTER_ID = OTCX.ORG_MASTER_ID
AND OMTX.ORG_TYPE_LKUP_ID = OTCX.ORG_TYPE_LKUP_ID
LEFT OUTER JOIN dbo.CONTACT_MASTER AS CM
ON OTCX.CONTACT_MASTER_ID = CM.CONTACT_MASTER_ID
LEFT OUTER JOIN dbo.MUTUAL_FUND_FAMILY_MASTER AS MFFM
ON OM.ORG_MASTER_ID = MFFM.ORG_MASTER_ID
WHERE OM.MARK_FOR_DELETION = 0
AND OTL.ORG_TYPE_LKUP_ID = @OrgTypeID
AND (OM.ORG_NAME LIKE @OrgName OR MFFM.FUND_FAMILY_NAME LIKE @OrgName OR @OrgName IS NULL)
ORDER BY OM.ORG_NAME ASC
OFFSET (@NbrofRecords * (@PageNbr - 1)) ROWS
FETCH NEXT @NbrofRecords ROWS ONLY;
GO

As you can see from above, the only change was to append the OFFSET clause and the FETCH NEXT to your actual query and use two parameters – one to denote the Page Number that you want and the other to denote how many records do you want per page.  So, say your query returns 1000 records and your per page display of records requirement is 15 records per page and you want to display the data from the third page – in that scenario, @PageNbr will have a value of 3 and @NbrofRecords will have a value of 15 so the offset would be by 15 * (3-1) = 30 i.e. you are going to start from the 31st record using the OFFSET clause and then FETCH the NEXT 15 records.  Pretty easy to write, understand and implement, isn’t it?  Do remember that these clauses go along with the ORDER BY clause i.e. you cannot use these on unordered data (will get an error) – if you want data ordered in a particular way, you should be using order by anyways which is always the case for paging for web applications.

Will need to play around a bit to ascertain the performance impact in the case of complex queries with different columns (from different tables) in the order by clause or conditional ordering to see what the impact is between the old vs the new approach.  Will post the results on the blog when we do those tests.

Posted in DB2 LUW, Oracle, SQL Server | Leave a Comment »

Converting PL/SQL code into web services

Posted by decipherinfosys on February 25, 2011

Native XML DB Web Services in Oracle 11g makes the conversion of PL/SQL code into web services much easier.  Here are some links on this topic to get you started:

  • Oracle documentation – here.
  • Oracle-Base post – here.

Posted in Oracle | Leave a Comment »

OVER clause – much needed enhancements

Posted by decipherinfosys on February 23, 2011

For those of us who work in both Oracle and SQL Server, it is always a natural reaction to look for something equivalent in the other RDBMS in our day to day work.  One of the areas in which Oracle has excelled far ahead of SQL Server has been it’s rich support for analytics.  OVER clause capabilities is just one of those things.  MVP Itzik has posted several suggestions/requests on connect for such enhancements – you can see them here.  It also has links from other readers as well which point to the rich functionality of Oracle and DB2 in that regard. You should also read this whitepaper from Itzik and Sujata on this topic.

As per this update on connect, these did not make it in SQL Server 2008 and might be included in the next release – Denali.

This came up again when a client who is responsible for releasing their product on Oracle as well as SQL Server needed to simulate some of that rich analytic function capability in SQL Server.  There are always workarounds available and we have covered some of them in our previous blog posts (example – for first_value(), last_value() workaround) but it is time that SQL Server had that support now.

Posted in DB2 LUW, Oracle, SQL Server | Leave a Comment »

High Availability and Disaster Recovery

Posted by decipherinfosys on January 28, 2011

These two terms are often used very interchangeably.

High Availability typically refers to the solutions that use fault tolerance and /or load balancing in order to provide high availability of the applications.  HA is essentially the ability to continue operations when a component fails – could be a CPU, memory failure, disk failure, complete failure of a server etc..  With HA, there is usually no loss of service.  Clustering servers together is a form of HA, having redundant power supplies or redundant network controllers is a form of HA, having proper RAID arrays is a form of HA, having proper load balancing in place is a form of HA.  The primary goal of a HA environment is uptime/providing continuous service w/o disruption.

Disaster Recovery provides increased availability.  It is the process/ability to restore operations that are critical for the business to run after a disaster (human induced or natural causes example – power failure in the production site, floods, earthquake, hurricane etc.).  The key difference between DR and HA is that the recovery time taken in the case of DR is typically more than a HA.  With DR, there is a small loss of service while the DR systems are activated and take over the load in the DR site.

Here are some posts/articles which delve into these differences in more detail:

http://www.channeldb2.com/profiles/blogs/disaster-recovery-high

http://nexus.realtimepublishers.com/sgudb.php

http://www.drj.com/2010-articles/online-exclusive/understanding-high-availability-and-disaster-recovery-in-your-overall-recovery-strategy.html

http://gestaltit.com/all/tech/storage/bas/define-high-availability-disaster-recovery/

Posted in DB2 LUW, Oracle, SQL Server, Technology, Unix | 1 Comment »

Insert data in one table with data from other table(s)

Posted by decipherinfosys on January 27, 2011

This post is in response to this question from a reader:

“Your post on updating data in one table with data from another table was very helpful.  I need to also do an insert into a table (a summary table) with data from a bunch of other tables which are in the same database and also in database(s) on other instances.  Right now, I have a very convoluted process of scheduled jobs, row by row scripts to help me do that.  There is some transformation (mostly lookups) involved as well.  Is there an easier/simpler way to do this?  Appreciate your thoughts on this”

The post that the question above referred to was this one – here.  In that post, we had covered how you can update the data in a table with the data from another table.  Doing an insert into a table with data from other tables (within the same schema or separate schemas or separate databases or separate databases on separate instances) is also fairly simple.  There are many ways of doing it – we will cover the “INSERT INTO …. SELECT FROM … ” approach in this blog post.

Let’s consider this hypothetical scenario to illustrate this (using SQL Server lingo though the same approach is available in Oracle and DB2 as well) – Say the summary table “tblEmpSummary” that this reader wanted to insert the data into resides in a database called EXECUTIVE in a schema called ANALYSIS.  It contains summary information on the employees – their name, their department, their performance review rating, their expense reports per week and the weekly benefit expenses for those employees.

And the source of the data are these:

a) Other table called EMP_PERF_REVIEW in the same schema in the same database (contains EMP_PERF_RATING column that we want and can join on EMP_MASTER_ID column),

b) Other table(s) called EMP_MASTER, EMP_DEPT_XREF and DEPT_MASTER from the schema EMPLOYEE on the same database (contains FIRST_NAME, LAST_NAME, DEPT_NAME information and we can join on EMP_MASTER_ID and DEPT_MASTER_ID),

c) Other table called EMP_EXPENSE_RPT from the EMP Schema in the ACCOUNTING database in the same SQL Server instance (contains WEEK_ENDING_DATE and TOTAL_AMOUNT fields that we are interested in and can join in using EMP_MASTER_ID column), and

d) A view called VW_EMP_PTO from a schema called BENEFIT from the HR database that resides on another instance (Contains PTO_ACCRUED and TOTAL_AMOUNT field for the benefits that we are interested in and can join in using EMP_MASTER_ID).

So, we have data coming from:

a) table in the same schema/same database,
b) tables in the same database but a different schema,
c) tables in a separate database on the same SQL Server instance, and
d) a view from a database in a separate SQL Server instance all together.

If suppose all of our data was coming from (a) alone, all we would have needed to do would be this:

INSERT INTO Analysis.tblEmpSummary (<list of column names here>)
SELECT <list of the column names from one source>
FROM Analysis.EMP_PERF_REVIEW
WHERE <filter conditions>
<any other conditions like GROUP BY, ORDER BY etc. that we need>

Please do note that the WHERE clause above can contain a NOT EXISTS check on the tblEmpSummary to ensure that no dupes are being inserted – needless to state, this INSERT code would be wrapped in a TRY…CATCH block with proper error handling.

If we now extend it to include the data from (b) as well, the above SQL would change to:

INSERT INTO Analysis.tblEmpSummary (<list of column names here>)
SELECT <list of the column names from the two sources – use the proper aliases>
FROM Analysis.EMP_PERF_REVIEW as EPR
INNER JOIN Employee.EMP_MASTER as EM ON EPR.EMP_MASTER_ID = EM.EMP_MASTER_ID
INNER JOIN Employee.EMP_DEPT_XREF as EDX ON EM.EMP_MASTER_ID = EDX.EMP_MASTER_ID
INNER JOIN Employee.DEPT_MASTER as DM ON EDX.DEPT_MASTER_ID = DM.DEPT_MASTER_ID
WHERE <filter conditions>
<any other conditions like GROUP BY, ORDER BY etc. that we need>

As can be seen from above, we have assumed that the select privileges are present and you would be able to join the tables easily then.

Now, if we have to include (c) scenario as well and assuming that we have select privileges in place already on that object, we can either create views/synonyms for those objects in our schema or simply refer to those objects if the permissions are available using a three part naming convention:

<database_name>.<schema_name>.<object_name>

So, the above SQL then gets modified to:

INSERT INTO Analysis.tblEmpSummary (<list of column names here>)
SELECT <list of the column names from the three sources – use the proper aliases>
FROM Analysis.EMP_PERF_REVIEW as EPR
INNER JOIN Employee.EMP_MASTER as EM ON EPR.EMP_MASTER_ID = EM.EMP_MASTER_ID
INNER JOIN Employee.EMP_DEPT_XREF as EDX ON EM.EMP_MASTER_ID = EDX.EMP_MASTER_ID
INNER JOIN Employee.DEPT_MASTER as DM ON EDX.DEPT_MASTER_ID = DM.DEPT_MASTER_ID
INNER JOIN Accounting.Emp.EMP_EXPENSE_RPT as EER ON EM.EMP_MASTER_ID = EER.EMP_MASTER_ID

WHERE <filter conditions>
<any other conditions like GROUP BY, ORDER BY etc. that we need>

Now, that leaves us with the last part – (d).  Since it resides in a database on a separate instance, we can first create a linked server to it first and then either reference it using a couple of ways as was shown in that blog post on linked server.  Or we can simply create a synonym for it and use it.  Assuming we are using a four part naming convention to reference it, the above SQL now becomes:

INSERT INTO Analysis.tblEmpSummary (<list of column names here>)
SELECT <list of the column names from the four sources – use the proper aliases>
FROM Analysis.EMP_PERF_REVIEW as EPR
INNER JOIN Employee.EMP_MASTER as EM ON EPR.EMP_MASTER_ID = EM.EMP_MASTER_ID
INNER JOIN Employee.EMP_DEPT_XREF as EDX ON EM.EMP_MASTER_ID = EDX.EMP_MASTER_ID
INNER JOIN Employee.DEPT_MASTER as DM ON EDX.DEPT_MASTER_ID = DM.DEPT_MASTER_ID
INNER JOIN Accounting.Emp.EMP_EXPENSE_RPT as EER ON EM.EMP_MASTER_ID = EER.EMP_MASTER_ID
INNER JOIN LinkedSrvHR.HR.Benefit.VW_EMP_PTO as VWP ON EM.EMP_MASTER_ID = VWP.EMP_MASTER_ID

WHERE <filter conditions>
<any other conditions like GROUP BY, ORDER BY etc. that we need>

This should cover all the scenarios that you can possibly run into.  You can also try this with MERGE command.  Any transformations like look-up of values that you can do in T-SQL, you can do over the record set that you get using the above SQL.  It is essentially now a data-set for you that is coming from 4 different sources.  What you do with that data-set is entirely up to you – use a CASE statement over it, do aggregation over it, dump it into a temporary table/table variable for further processing etc. – all the normal T-SQL operations you would be able to do including inserting it into the summary table.

In addition to the technique from above, if this is something that you need to do as a quick and dirty thing and you do not intend to keep the new table around post the analysis, you can also do:

select … into <new table> from <old table or set of tables from one or multiple sources> where <the where condition over here>

The “select into” is the same as CTAS in Oracle.  You can read more on that here.

Posted in DB2 LUW, Oracle, SQL Server | Leave a Comment »

Generating a list of missing numbers

Posted by decipherinfosys on January 3, 2011

A reader recently asked a question about generating a list of missing numbers from a column.  They were using an auto-incremental number column and due to deletes done on the table by different transactions, they had gaps in the values in this column.  They were not interested in re-using those values but wanted to generate a quick report to see the patterns of the gaps created.

Generating such a list is pretty simple by using a number table or by using a UDF to simulate such a number table.  We had covered in our blog posts before how to go about writing such a UDF – you can read more on it here (for SQL Server) and here (for Oracle).  For this post, we will use sample code for SQL Server 2008.

USE TESTDB
GO
/*Declare a table variable and insert some records into it
Simulate the gaps by creating records with missing numbers - 4, 6, 8, 12, 13 and 14*/
DECLARE @TABLE TABLE (ID_VAL INT)
INSERT INTO @TABLE VALUES (1), (2), (3), (5), (7), (9), (10), (11), (15), (16);

Now, let’s make use of that UDF that we had shown in one of our previous posts and generate a list of running numbers.  You will see that that function takes in two input parameters – one for the number of rows that need to be returned and the other one which feeds it the starting value from which that number list starts from.  Since in this case, the reader wanted it to be starting from 1, the second parameter has a value of 1 and the first parameter i.e. total number of rows is the maximum value in the table which in our example above is 16.

So, now all that we need to do is do a left outer join between the UDF and the table variable above:

declare @max_val int
select @max_val = MAX(id_val) from @table


select x.data_value, y.id_val
from dbo.UDF_GEN_SEQUENCE (@max_val, 1) as x
left outer join @TABLE as y
on x.data_value = y.ID_VAL

Based on this, we will get all the data from the UDF which will provide us with the list of the running numbers and only the matching records from the table variable.  Now, it is as simple as adding a IS NULL check in the where clause to get only those numbers that are missing from our table variable.

select x.data_value, y.id_val
from dbo.UDF_GEN_SEQUENCE (@max_val, 1) as x
left outer join @TABLE as y
on x.data_value = y.ID_VAL
where y.ID_VAL is null

And here is the output:

data_value    id_val
4             NULL
6             NULL
8             NULL
12            NULL
13            NULL
14            NULL

Posted in Oracle, SQL Server | Leave a Comment »

Excellent post on NoSQL

Posted by decipherinfosys on October 14, 2010

MVP Michael Otey has a good article in the sqlmag on NoSQL – you can read that here.  Go through the link on the slideshow explaining the rationale of Twitter’s move to NoSQL from MySQL – it’s an excellent presentation.

Posted in Open Source, Oracle, SQL Server, Technology | Leave a Comment »

OLAP work in Oracle

Posted by decipherinfosys on June 23, 2010

A colleague who is experienced in the Microsoft BI tools wanted to learn more about the OLAP capabilities in Oracle.  Here are some links to get started which we thought we would share with our readers as well in case you are also interested in learning about it.

Oracle OLAP: http://www.oracle.com/technology/products/bi/olap/index.html

Developing Oracle OLAP Objects: http://www.oracle.com/technology/products/bib/htdocs/tutorials/developing_olap_objects/overview_developing_olap_objects.html

Oracle Warehouse Builder (OWB): http://www.oracle.com/technology/products/warehouse/index.html

Oracle self service education on OTN (Oracle Technology Network): http://www.oracle.com/technology/products/warehouse/selfserv_edu/self_service_education.html

OWB Dimensional Modeling: http://www.oracle.com/technology/products/warehouse/selfserv_edu/dimensional_modeling.html

All of the above links are very good starting points to pick up OLAP in Oracle.  As you start to go through the links, there are many supporting documents listed on those pages and if you are already familiar with the MSFT solutions and tool sets, it won’t take you much time to start drawing parallels with how things work in Oracle.

And here are two good books on the topic – “The MultiDimensional Data Modeling Toolkit: Making  your Business Intelligence Applications Smart with Oracle OLAP” and “Oracle Warehouse Builder 11g: Getting Started“.

Posted in Oracle, SQL Server | Leave a Comment »

SQL Net Compression

Posted by decipherinfosys on June 10, 2010

Read an excellent post by Jonathan Lewis on SQL Net Compression:

http://jonathanlewis.wordpress.com/2010/05/07/sqlnet-compression/

Posted in Oracle | Leave a Comment »

Dropping a column from a compressed table in Oracle

Posted by decipherinfosys on June 9, 2010

If you are using compression in Oracle, you might be already aware of how to drop a column from a compressed table but in case you are not – here is a simple series of steps that you can follow to drop it:

SQL> CREATE TABLE TEST COMPRESS for all operations AS SELECT * FROM INVOICE_HDR;

Table created.

SQL> ALTER TABLE TEST ADD (DOCK_ZONE VARCHAR2 (100));

Table altered.

Now, let’s try to drop it using the ALTER TABLE command:

SQL> ALTER TABLE TEST DROP COLUMN DOCK_ZONE;
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

As you can see from above, we get the error back.  Now, let’s set that column to unused:

SQL> ALTER TABLE TEST SET UNUSED (DOCK_ZONE);

Table altered.

And we can drop it now:

SQL> ALTER TABLE TEST DROP unused columns;

Table altered.

Posted in Oracle | 6 Comments »