SWAT Blog

Archive for the ‘Database’ Category

Generate Date Ranges in Oracle PL/SQL

Tuesday, January 28th, 2014

Here is an easy way to generate date ranges in oracle pl/sql:

WITH t AS
  (SELECT to_date('01-OCT-2012') start_date,
    to_date('30-SEP-2013') end_date
  FROM dual
  )
  SELECT add_months(TRUNC(start_date,'mm'),LEVEL      - 1) START_DATE,
    add_months(add_months(TRUNC(start_date,'mm'),LEVEL - 1),1)-1 END_DATE
  FROM t
    CONNECT BY TRUNC(end_date,'mm') >= add_months(TRUNC(start_date,'mm'),LEVEL - 1)

OUTPUT:

START_DATE  END_DATE  
----------- -----------
01-OCT-2012 31-OCT-2012 
01-NOV-2012 30-NOV-2012 
01-DEC-2012 31-DEC-2012 
01-JAN-2013 31-JAN-2013 
01-FEB-2013 28-FEB-2013 
01-MAR-2013 31-MAR-2013 
01-APR-2013 30-APR-2013 
01-MAY-2013 31-MAY-2013 
01-JUN-2013 30-JUN-2013 
01-JUL-2013 31-JUL-2013 
01-AUG-2013 31-AUG-2013 
01-SEP-2013 30-SEP-2013 

 12 rows selected

SQL Server Comma Delimited to table

Tuesday, March 1st, 2011
One of the frequently asked questions in a lot of SQL Server forums is how to handle a comma-delimited value passed as a parameter in a stored procedure.
To better illustrate the question, let’s say you have a website wherein you have a page that lets the users select data items which are sent back to the database for further processing in variety of formats, and of all the formats, comma separated is probably the format which developers struggle with most if they do not know how to consume this structure.

Databases do not understand data arranged horizontally, so how to convert the data from CSV (horizontal) to a table (vertical) ?

Fortunately there are a couple of tricks up database’s sleeve.

1. Dynamic SQL Approach
This method involves creating a SQL statement during execution time to evaluate the where condition. But this approach is as ugly as it sounds nasty. Creating SQL statments on the the fly would results in lot of activity in shared pool. i.e. Compilation of statement, execution plan generation and final execution of query. All of these consume precious server resources and hence should avoided if at all possible.
Due to the obvious pit-falls in this approach I am not even going to attempt to show how-to.
2. Common Table Expression (CTE) based Approach
Comman table expressions (CTE) is probably one of the most powerful programming construct introduced in the SQL Server 2005. It can be used in hierarchical queries, special aggregations and various other problem solving. Various uses of CTE are discussed elsewhere in my blog, which can be found using the search button on the blog homepage.
Below is a CTE based implementation. The good aspect about this approach is that it is completely object independent so the function can be used for any table in any schema of the database as long as you have sufficient permissions.

USE [AdventureWorks]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N‘[dbo].[ufnCSVToTable]’)
AND type in (N‘FN’, N‘IF’, N‘TF’, N‘FS’, N‘FT’))
DROP FUNCTION [dbo].[ufnCSVToTable]
GO
CREATE FUNCTION [dbo].[ufnCSVToTable](@str VARCHAR(4000), @sep CHAR(1))
RETURNS @table TABLE
( [colx] VARCHAR(4000) NULL )
AS
BEGIN
WITH r0 AS
(
SELECT 1 n,1 m
UNION ALL
SELECT n+1, m+ CASE WHEN SUBSTRING(@str,n,1)=@sep THEN 1 ELSE 0 END
FROM r0 WHERE n<=LEN(@str)
)
INSERT INTO @table
SELECT SUBSTRING(@str,MIN(n), MAX(n)-MIN(n)) colx
FROM r0 GROUP BY m;
RETURN;
END;
GO
–Independent usage
SELECT * FROM [dbo].[ufnCSVToTable](this,is,a,comma,separated,sentence,‘,’)
Output:
colx
this
is
a
comma
separated
sentence


–Usage with table
DECLARE @str VARCHAR(1000); SET @str = ‘1,2’;
SELECT *
FROM Person.Address Ad
INNER JOIN [dbo].[ufnCSVToTable](@str,‘,’) CSV ON Ad.AddressID = CSV.colx

Voila!

I hope you find this function useful.

PostgreSQL Databases Backup & Restore with pg_dump command

Friday, February 11th, 2011

Make sure you should conncete to your server via ssh command line.
Login as super user type the following command.

[root@linux10 ~]#su – postgres

Get list of database(s) to backup.

[postgres@linux10 ~$]#psql -l

List of databases

Name | Owner | Encoding | Collation | Ctype | Access privileges

———–+———-+———-+————-+————-+———————–

postgres | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 |

mine | mine | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 |

template0| postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres

:postgres=CTc/postgres

template1 | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres

: postgres=CTc/postgres

(4 rows)

~

~

(END)

Make a backup using pg_dump, pg_dump is a utility for backing up a PostgreSQL database. It dumps only one database at a time.

Dump a mine database:-

[postgres@linux10 ~$]#pg_dump mine > /opt/mine.out

To restore a mine database:-

[postgres@linux10 ~$]#psql -d mine -f mine.out

Or, If you have new server first create database then apply command:-

[postgres@linux10 ~$]#createdb mine
[postgres@linux10 ~$]#psql mine
[postgres@linux10 ~$]#psql -d mine -f mine.out

Second option is use to pg_dumpall command.It dumps (backs up) each database, and preserves cluster-wide data such as users and groups. You can use it as follows:-

[postgres@linux10 ~$]#pg_dumpall > /opt/all.db.out

To restore backup use the following command:

[postgres@linux10 ~$]#psql -f all.db.out postgres

How To query contiguous data ranges – Oracle

Saturday, January 22nd, 2011

I recently presented a way of querying contiguous data ranges using SQL Server In this post I am going to tackle the same problem but solving it in Oracle instead.
Often times we are confronted with problems that seem very easy to start with but once you get your teeth into it, you realise it wasn’t as easy as initially thought. In this blog, I am attempting to work with data ranges that are not easily groupable.
Problem Statement: Using the data structure listed below, group the data ranges by dataKey when the dataKey is contigous, when breaks in data key are identified, create a new reporting output line. Query input-

grp dataRange dataKey Comments
A 1000 1
A 1001 1
A 1002 1
A 1003 1
A 1004 1
A 1005 2 — Notice dataKey is not contiguous
A 1006 2
A 1007 1
A 1008 1
A 1009 1
A 1010 1
A 1011 1

Desired Query output-

dataKey DataRangeBreakDown
1 A [1000 .. 1004]
2 A [1005 .. 1006]
1 A [1007 .. 1011]

Approach: This is an interesting puzzle, because we cannot simply group the data by “dataKey” column, doing so would ignore the breaks in data. The key to solving this refreshing problem is by “somehow” introducing a pseudo-column which does the heavy lifting for us. Table below illustrates the new pseudo-column, if we introduce this column using SQL, rest is just simple group by. Before peeking at the final solution, I would encourage you to have a go, but if you can’t for any reason I have presented final solution later in the blog.

grp dataRange dataKey New Column
A 1000 1 0
A 1001 1 0
A 1002 1 0
A 1003 1 0
A 1004 1 0
A 1005 2 1
A 1006 2 1
A 1007 1 2
A 1008 1 2
A 1009 1 2
A 1010 1 2
A 1011 1 2

Setup Data:

 DROP TABLE tmpDataRange; CREATE TABLE
  tmpDataRange AS SELECT 'A' grp, 1000 AS dataRange, 1 AS dataKey FROM Dual UNION ALL
  SELECT 'A', 1001, 1 FROM Dual UNION ALL SELECT 'A', 1002, 1 FROM Dual UNION ALL SELECT
  'A', 1003, 1 FROM Dual UNION ALL SELECT 'A', 1004, 1 FROM Dual UNION ALL SELECT 'A',
  1005, 2 FROM Dual UNION ALL SELECT 'A', 1006, 2 FROM Dual UNION ALL SELECT 'A', 1007, 1
  FROM Dual UNION ALL SELECT 'A', 1008, 1 FROM Dual UNION ALL SELECT 'A', 1009, 1 FROM
  Dual UNION ALL SELECT 'A', 1010, 1 FROM Dual UNION ALL SELECT 'A', 1011, 1 FROM Dual ;
  

Solution:
Welcome to the solution. I have tackled the problem using the aproach above using Common Table Expressions (CTE) feature. I think CTEs are fantastic even for writing daily garden variety queries as they make the query so much more understandable by being written in the way we digest other information, i.e. Top to Bottom. Overview of solution query:
Data0 – Instantiate the problem statement input data
Data1 – Assign incremental row numbers to input data
Data2 – Self join the data to the previous row
Data3 – This is where the “magic” happens – Build a cumulative data column which increments on every dataKey break
Data4 – Is concerned with grouping and presenting the data in format requested Presented below is the final solution:

 WITH data0 AS (SELECT grp, dataRange, dataKey FROM
  tmpDataRange), data1 AS (SELECT grp, dataRange, dataKey, LAG(dataKey) OVER(ORDER BY
  dataRange ASC) lagDataKey FROM data0), data2 AS (SELECT grp, dataRange, dataKey, CASE
  WHEN dataKey=NVL(lagDataKey,dataKey) THEN 0 ELSE 1 END cumu FROM data1), data3 AS
  (SELECT grp, dataRange, dataKey, SUM(cumu) OVER(ORDER BY dataRange) cumulativeWindow
  FROM data2), data4 AS (SELECT cumulativeWindow, dataKey, MAX(grp) || ' [' ||
  TO_CHAR(MIN(dataRange)) ||' .. '|| TO_CHAR(MAX(dataRange)) ||']' DataRangeBreakDown
  FROM data3 GROUP BY cumulativeWindow, dataKey) SELECT dataKey, DataRangeBreakDown FROM
  data4; 

Variations: One of the great things about this solution is that it is data-type agnostic. Feel free to change the data-type of the dataRange column to DATE for example as shown below and it still works.

 DROP TABLE tmpDataRange; CREATE TABLE tmpDataRange AS SELECT 'A' grp,
  SYSDATE AS dataRange, 1 AS dataKey FROM Dual UNION ALL SELECT 'A', SYSDATE+1, 1 FROM
  Dual UNION ALL SELECT 'A', SYSDATE+2, 1 FROM Dual UNION ALL SELECT 'A', SYSDATE+3, 1
  FROM Dual UNION ALL SELECT 'A', SYSDATE+4, 1 FROM Dual UNION ALL SELECT 'A', SYSDATE+5,
  2 FROM Dual UNION ALL SELECT 'A', SYSDATE+6, 2 FROM Dual UNION ALL SELECT 'A',
  SYSDATE+7, 1 FROM Dual UNION ALL SELECT 'A', SYSDATE+8, 1 FROM Dual UNION ALL SELECT
  'A', SYSDATE+9, 1 FROM Dual UNION ALL SELECT 'A', SYSDATE+10, 1 FROM Dual UNION ALL
  SELECT 'A', SYSDATE+11, 1 FROM Dual ; 

Would love to hear your thoughts on this, or if you have a better solution why not share it with everyone.

How To query contiguous data ranges – SQL Server

Wednesday, January 12th, 2011

Often times we are confronted with problems that seem very easy to start with but once you get your teeth into it, you realise it wasn’t as easy as initially thought.
In this blog, I am attempting to work with data ranges that are not easily groupable.

Problem Statement:


Using the data structure listed below, group the data ranges by dataKey when the dataKey is contigous, when breaks in data key are identified, create a new reporting output line.
Query input-

grp dataRange dataKey Comments
A 1000 1
A 1001 1
A 1002 1
A 1003 1
A 1004 1
A 1005 2 — Notice dataKey is not contiguous
A 1006 2
A 1007 1
A 1008 1
A 1009 1
A 1010 1
A 1011 1

Desired Query output-

dataKey DataRangeBreakDown
1 A [1000 .. 1004]
2 A [1005 .. 1006]
1 A [1007 .. 1011]

Approach:


This is an interesting puzzle, because we cannot simply group the data by “dataKey” column, doing so would ignore the breaks in data. The key to solving this refreshing problem is by “somehow” introducing a pseudo-column which does the heavy lifting for us.
Table below illustrates the new pseudo-column, if we introduce this column using SQL, rest is just simple group by.

Before peeking at the final solution, I would encourage you to have a go, but if you can’t for any reason I have presented final solution later in the blog.

grp dataRange dataKey New Column
A 1000 1 0
A 1001 1 0
A 1002 1 0
A 1003 1 0
A 1004 1 0
A 1005 2 1
A 1006 2 1
A 1007 1 2
A 1008 1 2
A 1009 1 2
A 1010 1 2
A 1011 1 2

Setup Data:

IF OBJECT_ID('tempDB..#tmpDataRange') IS NOT NULL
DROP TABLE #tmpDataRange;
WITH testData AS (
 SELECT 'A' grp, 1000 AS dataRange, 1 AS dataKey UNION ALL
 SELECT 'A', 1001, 1 UNION ALL
 SELECT 'A', 1002, 1 UNION ALL
 SELECT 'A', 1003, 1 UNION ALL
 SELECT 'A', 1004, 1 UNION ALL
 SELECT 'A', 1005, 2 UNION ALL
 SELECT 'A', 1006, 2 UNION ALL
 SELECT 'A', 1007, 1 UNION ALL
 SELECT 'A', 1008, 1 UNION ALL
 SELECT 'A', 1009, 1 UNION ALL
 SELECT 'A', 1010, 1 UNION ALL
 SELECT 'A', 1011, 1
 )
SELECT grp, dataRange, dataKey
INTO #tmpDataRange
FROM testData;

Solution:


Welcome to the solution.
I have tackled the problem using the aproach above using Common Table Expressions (CTE) feature of SQL Server 2005+ Engine. CTEs are great new addition to query toolkit, besides allowing us to do fancy stuff like hierarchical queries, I think CTEs are fantastic even for writing daily garden variety queries as they make the query so much more understandable by being written in the way we digest other information, i.e. Top to Bottom.
Overview of solution query:
Data0 – Instantiate the problem statement input data
Data1 – Assign incremental row numbers to input data
Data2 – Self join the data to the previous row
Data3 – This is where the “magic” happens – Build a cumulative data column which increments on every dataKey break
Data4 and Data5 – Are concerned with grouping and presenting the data in format requested

Presented below is the final solution:

WITH
data0 AS (SELECT grp, dataRange, dataKey FROM #tmpDataRange),
data1 AS (SELECT UPPER(grp) grp,dataRange,dataKey, ROW_NUMBER()OVER(ORDER BY dataRange ASC) rNum FROM data0),
data2 AS (SELECT A.grp,A.dataRange,A.dataKey,A.rNum,
 CASE WHEN A.dataKey=ISNULL(B.dataKey,A.dataKey)THEN 0 ELSE 1 END cumuWindow
 FROM data1 A LEFT JOIN data1 B ON A.rNum=b.rNum+1),
data3 AS (SELECT o.grp,o.dataRange,o.dataKey,
 (SELECT SUM(i.cumuWindow) FROM data2 i WHEREi.rNum <= o.rNum) cumuWindow
 FROM data2 o),
data4 AS (SELECT dataKey,cumuWindow,MIN(grp) grp, MIN(dataRange)mn, MAX(dataRange) mx
 FROM data3
 GROUP BY dataKey,cumuWindow)
SELECT dataKey, grp + '['+CAST(mn AS VARCHAR(10))+'..'+CAST(mx ASVARCHAR(10))+']' DataRangeBreakDown
FROM data4

Variations:
One of the great things about this solution is that it is data-type agnostic. Feel free to change the data-type of the dataRange column to DATE for example as shown below and it still works.

IF OBJECT_ID('tempDB..#tmpDataRange') IS NOT NULL
DROP TABLE #tmpDataRange;
WITH testData AS (
 SELECT 'A' grp, GETDATE() AS dataRange, 1 AS dataKey UNION ALL
 SELECT 'A', GETDATE()+1, 1 UNION ALL
 SELECT 'A', GETDATE()+2, 1 UNION ALL
 SELECT 'A', GETDATE()+3, 1 UNION ALL
 SELECT 'A', GETDATE()+4, 1 UNION ALL
 SELECT 'A', GETDATE()+5, 2 UNION ALL
 SELECT 'A', GETDATE()+6, 2 UNION ALL
 SELECT 'A', GETDATE()+7, 1 UNION ALL
 SELECT 'A', GETDATE()+8, 1 UNION ALL
 SELECT 'A', GETDATE()+9, 1 UNION ALL
 SELECT 'A', GETDATE()+10, 1 UNION ALL
 SELECT 'A', GETDATE()+11, 1
 )
SELECT grp, dataRange, dataKey
INTO #tmpDataRange
FROM testData;

Would love to hear your thoughts on this, or if you have a better solution why not share it with everyone.

I am sure you would like to know how the same problem can be solved in Oracle. Find it here

Oracle 10g Advanced Query Rewrite – Query Equivalence

Friday, June 26th, 2009

Occasionally there are instances where we have very little or no control over the query generated by application layer, especially if it a 3rd party application. If you’ve identified a problematic query originating from such an application, what do do ?

Well if your enterprise is on Oracle 10g, you are in luck.

Oracle 10g introduced Query equivalence, which can be used to substitute any SQL DML statement for another (including use of the new SQL Model clause), and is particularly useful when SQL is generated by an application and cannot be changed, but the DBA or advanced developer knows of a better way to phrase the query, perhaps using new data structures that they might have created.

Lets see step by step

create table dept
as
select * from scott.dept;

Problem Query

select * from dept;

DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

Optimized Query

create or replace view vw_optimised_dept
as
select deptno, LOWER(dname) dname, loc
from dept order by loc;

Query Equivalence

begin


sys.dbms_advanced_rewrite.declare_rewrite_equivalence

( name           => 'test_equivalence',
source_stmt      => 'select * from dept',
destination_stmt => 'select * from vw_optimised_dept',
validate         => FALSE,
rewrite_mode     => 'TEXT_MATCH' );
end;

/

That is it!
Next time Oracle 10g optimiser comes across this query it would rewrite it.

select * from dept;

DEPTNO DNAME LOC
———- ————– ————-
40 operations BOSTON
30 sales CHICAGO
20 research DALLAS
10 accounting NEW YORK

Note the DNAME column in the lowercase, proving that our problematic query was rewritten.
It is however important to note that the text match rewrite can distinguish contexts where the difference between uppercase and lowercase is significant and where it is not.

For example, the following statements are equivalent:

SELECT X, 'aBc' FROM Y;
Select x, 'aBc' From y;

But following statements are not:

SELECT X, 'aBc' FROM Y;
SELECT X colx, 'aBc' coly FROM Y Y_ALIAS;

If you wish to remove this rewrite at a later stage-

exec sys.dbms_advanced_rewrite.drop_rewrite_equivalence( 'test_equivalence' );

select * from dept;

DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

In this post I have only picked up one sample usage, for a more detailed discussion please visit Oracle documentation on Advanced Query Rewrite.

  • © 2004-2015 Special Work & Technology Limited