SQL Server: Partition view usage and limitations.

Partition views are just like any other view. They are build on two or more member tables and display records from those table with some differences and limitations. Partition can be used instead of partitioning. Enterprise edition of SQL Sever provides us facility of physical data partition of a big table. But partitioning is only available in enterprise edition. If you want to implement similar data partitioning in standard edition you can use partition views. But there are some limitations of partition views that we will see.

Partition view allows data of a larger table to be split into smaller member or base tables. We need a check constraint on column of each base table and this check constraint will determine the partition range for each member table. Range of this check constraint must be mutually exclusive in each member table so that database engine can decide on which table new incoming row should go in case of a DML.

Now take a look at one example:

create table member1 (
id int not null,
name varchar(100),
age int check (age < 18),
constraint member1_pk primary key (id, age)
);
GO

create table member2 (
id int not null,
name varchar(100),
age int check (age >= 18 AND age < 60),
constraint member2_pk primary key (id, age)
);
GO

create table member3 (
id int not null,
name varchar(100),
age int check (age >= 60),
constraint member3_pk primary key (id, age)
);
GO

create view members as
select * from member1
union all
select * from member2
union all
select * from member3;
GO

Here "members" is a partition view and "age" column of base tables will work as a partition column. As you can see check constraint on age column has exclusive conditions. And any specified value of age can satisfy, at most, one of the constraints and condition of each constraint is forming a set of disjointed or non-overlapping intervals.
One more condition for partition view is that primary key of all the member tables mush have partition column. Thats way we have to create a composite primary key having id and age columns.

Now we are going to insert some records into "members view" and see what happens to base tables:


insert into members(id, name, age)
values (1,'x', 35);

insert into members(id, name, age)
values (2,'y', 15);

insert into members(id, name, age)
values (3,'z', 5);

insert into members(id, name, age)
values (4,'p', 75);

insert into members(id, name, age)
values (5,'q', 18);

insert into members(id, name, age)
values (6,'r', 27);
GO

select 'members' as tab, COUNT(*) as count from members
union all
select 'member1', COUNT(*) from member1
union all
select 'member2', COUNT(*) from member2
union all
select 'member3', COUNT(*) from member3;
GO

tab           count
members 6
member1 2
member2 3
member3 1

So insert and select are woking file. Lets see what happens to update.

update members
set age = 79
where id = 6;
GO

select 'members' as tab, COUNT(*) as count from members
union all
select 'member1', COUNT(*) from member1
union all
select 'member2', COUNT(*) from member2
union all
select 'member3', COUNT(*) from member3;
GO

tab         count
members       6
member1       2
member2       2
member3       2

Above update statement moved row having id = 6 from member2 to member3 table.

Now talk about some limitations of partition views. INSERT, UPDATE, and DELETE actions against a partitioned view are not allowed if there is a self-join with the same view or with any of the member tables in the statement.
For example consider this update:

update m
set m.name = 'a'
from members m inner join member1 m1 on m.id = m1.id and m1.id = 2;
GO

Msg 4439, Level 16, State 6, Line 2
Partitioned view 'eGPartitionTest.dbo.members' is not updatable because the source query contains references to partition table '[m1]'.

Also target of a MERGE statement cannot be a partitioned view.

For more details on partition view usage and limitations refer following links:
-Using partitioned views
-Create view

ORACLE: SQL Server table variable equivalent in oracle.

In SQL Server we can define a table variable and use it like a regular table. We can insert, update, delete records from that table variable. There table variable becomes very useful if we do not want to use collections or temp tables. Also scope of table variable remains only within a T-SQL block on which it was declared. Take a look at the below T-SQL code written for SQL Server:

--Declare a table variable. Syntax is like any other database table.
DECLARE @v_tab TABLE (a INT, b VARCHAR(100));
DECLARE @v_cnt INT;
begin
--Insert records into a table variable.
INSERT  INTO @v_tab(a,b)
SELECT object_id, name FROM sys.tables;

SELECT @v_cnt = COUNT(*) 
FROM @v_tab;

PRINT @v_cnt;

--delete few records.
delete FROM @v_tab 
WHERE b like '%mail%';

SELECT @v_cnt = COUNT(*) 
FROM @v_tab;

PRINT @v_cnt;

end;
GO

Unfortunately Oracle does not provide any such capabilities in PL/SQL. If we want to achieve similar functionality we have to use collections, records and table() function. But it becomes little complicated then SQL Server. Take a look at below script.

create or replace type a_record as object   ( object_name varchar2(30), created_date date, object_type number );
/
create or replace type a_tab_variable as table of a_record; 
/

DECLARE 
       a_tab a_tab_variable;
       v_cnt INT;
BEGIN
       select  a_record ( object_name, created, object_id )
       bulk collect into a_tab
       from user_objects;  
  
   SELECT count(*)
INTO v_cnt  
FROM TABLE(a_tab);   

DBMS_OUTPUT.PUT_LINE( v_cnt );
         
         FOR a_rec IN ( 
                   SELECT b.object_name
                        FROM TABLE(a_tab) b    
                         WHERE b.created_date < (SYSDATE - 2)
                               AND ROWNUM < 101) 
         LOOP
                   DBMS_OUTPUT.PUT_LINE( a_rec.object_name );
         END LOOP;                  
END;
/

Oracle: JDBC connectivity

First of all make sure you have proper JDK installation and java home path is set. In order to confirm that JDK configured correctly go to command prompt and type "javac". It should prompt you various javac usage options.
Now make sure that you have correct jdbc drivers. For this go to oracle website and download correct driver if not done already. Now append the location of jdbc drivers into your classpath. For example if I execute "echo %classpath%" into my system it will show me something like this:

D:\ankur\KB\java\JDBC\program>echo %classpath%
.;D:\ankur\KB\java\JDBC;D:\app\achourasia\product\11.2.0\dbhome_1\jdbc\lib\ojdbc6.jar;

You can set classpath into following location on windows machine:

My Compute --> Properties --> Advanced System Settings --> Environment Variables --> User variables --> edit classpath.

In order to test that everything is fine run following small test.

Execute following sql into SCOTT schema:

CREATE TABLE rectangles(
       seq INT,
  length INT, 
  width  INT,
  area NUMBER(19,5)
);

INSERT INTO rectangles (seq, length, width) VALUES (1, 10, 34);
                                                                            
INSERT INTO rectangles (seq, length, width) VALUES (2, 100, 89);

INSERT INTO rectangles (seq, length, width) VALUES (3, 20, 76);

INSERT INTO rectangles (seq, length, width) VALUES (4, 35, 14);

INSERT INTO rectangles (seq, length, width) VALUES (5, 1, 9);

Now execute following java from file system:

import java.sql.*;
public class JdbcRectangle {

public int calculateArea(int length, int width){
return (length * width);
}

public static void main(String args[]) throws SQLException {

try {
String username = "SCOTT";
String password = "SCOTT";
String database = "pune705";
JdbcRectangle j = new JdbcRectangle();

//Create a connection.
Connection conn1 = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:" + database, username, password);;
//stmt for select query
Statement stmt = conn1.createStatement();
//stmt for update query.
Statement stmt1 = conn1.createStatement();

ResultSet rs = stmt.executeQuery("SELECT seq, length, width FROM rectangles where area is null");

boolean b = false;
int seq  = 0;
int len  = 0;
int wdt  = 0;
int area = 0;
int i    = 0;

while(rs.next()){
seq = rs.getInt("seq");
len = rs.getInt("length");
wdt = rs.getInt("width");
area = j.calculateArea(len,wdt);

b = stmt1.execute("UPDATE rectangles SET area = " + area + " WHERE seq = " + seq);

System.out.println("SEQ: " + (++i) + " Length: " + len + " Width " + wdt + " Area: " + area + " is_updated: " + String.valueOf(b));
}

rs.close();
stmt.close();
stmt1.close();
conn1.close();

}

catch(SQLException e) {
e.printStackTrace();
}
}
} 


If everything allright it will show following message into command prompt and reflect changes into database:

D:\ankur\KB\java\JDBC\program>java JdbcRectangle
SEQ: 1 Length: 10 Width 34 Area: 340 is_updated: false
SEQ: 2 Length: 100 Width 89 Area: 8900 is_updated: false
SEQ: 3 Length: 20 Width 76 Area: 1520 is_updated: false
SEQ: 4 Length: 35 Width 14 Area: 490 is_updated: false
SEQ: 5 Length: 1 Width 9 Area: 9 is_updated: false