Roll Up operations in Oracle 10g XE

The purpose of this post is to let you guys know the basic OLAP rollup operation using Oracle 10g XE which is a free DBMS


First to begin with

Rollup is formally defined as an OLAP operation involving computing all of the data relationships for one or more dimensions. To do this, a computational relationship or formula might be defined.

I guess that didnt help even a bit. :p


To make things simpler


I would say a rollup  creates subtotals(totalling is an example relation considered in this case) that roll up from the most detailed level to a grand total, following a grouping list specified in the ROLLUP clause. 


ROLLUP takes as its argument an ordered list of grouping columns.


First, it calculates the standard aggregate values specified in the GROUP BY clause.


Then, it creates progressively higher-level subtotals, moving from right to left through the list of grouping columns. Finally, it creates a grand total.


To explain this consider the following example



create table Prac (
pid int primary key,
name varchar2(10),
country varchar2(15),
cost int
);


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


After Filling in data


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

PID	NAME	COUNTRY	COST
1	Rahul	IN	1000
2	Rohit	IN	1000
3	Rahul	US	1500
4	Rohit	US	1500
5	Amit	US	2000

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


Then i execute a simple GROUP BY query on SUM(Cost) Group By Country


select country,sum(cost) from prac group by country

COUNTRY	SUM(COST)
US	5000
IN	2000

I get the above result i.e sum of cost for each country.... this is simple




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


Now a Rollup is an extension of Group By Clause


Consider the following 


select country,sum(cost) from prac group by rollup(country)

COUNTRY	SUM(COST)
IN	2000
US	5000
-	7000

Now the only difference is apart from producing a country-wise sum of cost.. the query also rolled them up to produce their total i.e 7000


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


Now Consider another example
select name,country,sum(cost) from prac group by rollup(name,country)

NAME	COUNTRY	SUM(COST)
Amit	US	2000
Amit	-	2000
Rahul	IN	1000
Rahul	US	1500
Rahul	-	2500
Rohit	IN	1000
Rohit	US	1500
Rohit	-	2500
-	-	7000

Here we see that we get Sum of cost for every Name,Country pair
Additionally we also get some more sub-totals.


Now at level 1 we get Sum(Cost) of every Name-Country pair now grouped by Name(since name is written before Country) for every pair.. hence arguements passed to rollup operation is an ordered list i.e order matters here .


And at level 2 further we get Sum(Cost) of every subtotals generated at level 1 now grouped by nothing i.e the entire total.


-----------------------------------------------------------
To explain the importance of ordering of parameters in rollup we consider yet another example


select name,country,sum(cost) from prac group by rollup(country,name)


Corresponding result set generated is-

NAME	COUNTRY	SUM(COST)
Rahul	IN	1000
Rohit	IN	1000
-	IN	2000
Amit	US	2000
Rahul	US	1500
Rohit	US	1500
-	US	5000
-	-	7000

Here as we observe, at level 1 we get Sum(Cost) of every Name-Country pair now grouped by Country(since country is written before Name) for every pair


Again at level 2 further we get Sum(Cost) of every subtotals generated at level 1 now grouped by nothing i.e the entire total.




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




Now we read the definition yet again..
Rollup is formally defined as an OLAP operation involving computing all of the data relationships for one or more dimensions. 

 "computing all of the data relationships for one or more dimensions"
Means
Like here computing subtotals at every level grouping one or more columns(dimensions)

Implement Simple Lexical Analyzer to count number of keywords,operators,symbols and delimiters

import java.util.*;
import java.io.*;

class LexicalAnalyzer

{
    public static void main(String args[]) throws Exception
    {
        int kc=0,oc=0,sc=0,dc=0;
        File f=new File("input.txt");
     

        FileReader fi=new FileReader(f);
        BufferedReader br=new BufferedReader(fi);
        String temp;
        String words[];

        while((temp=br.readLine())!=null)
        {
            //temp=br.readLine();
            words=temp.split("[\\s ,;\\(\\)\\{\\}]"); //delimiters are whitspace characters , ; ( ) { }
            //For reference on regular expression for split method see
// http://docs.oracle.com/javase/1.4.2/docs/api/java/util/regex/Pattern.html#sum

dc+=words.length-1;
            for(int i=0;i<words.length;i++)
                if(isKeyword(words[i])==true)
                    kc++;
                else if(isOperator(words[i])==true)
                    oc++;
                else
                    sc++;
        }

            System.out.println("Keywords:"+kc);
            System.out.println("Operators:"+oc);
            System.out.println("Symbols:"+sc);
            System.out.println("Delimiters:"+dc);
           


 
    }


    //implement boolean returning methods like these below to detect anything
    public static boolean isKeyword(String x)
    {
        String keywords[]={"auto","break","case","char","const","continue","default","do","double","else","enum","extern","float","for","goto","if","int","long","register","return","short","signed","sizeof","static","struct","switch","typedef","union","unsigned","void","volatile","while"};
        for(int i=0;i<keywords.length;i++)
            if(x.equals(keywords[i])==true)
                return true;
        return false;
    }
    public static boolean isOperator(String x)
    {
        String operator[]={"--","++","~","+","-","*","/","^","&","%","!","<",">","|","="};
        for(int i=0;i<operator.length;i++)
            if(x.equals(operator[i])==true)
                return true;
        return false;
    }

}

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

input.txt

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

int main ()

{

int a,b,c;

float x,y;

a = a + b;

}

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

Output:

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

Keywords:3

Operators:2

Symbols:9

Delimiters:8

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

Simulate Pentium's Branch Prediction Logic

import java.util.*;
import java.io.*;


/**********************************
 * @author Rohit E Iyer 09-221
 * @date 25/02/2012
 *
 * AMP Practical No.3
 * TITLE: Branch Prediction Logic
 *----------------------------------
 * Instructions
 * like JC JA JB may branch or not
 * This logic predicts branching on
 * the basis of history
 * 
 *-----------------------------------
 * BranchPredictionLogic.java
 * *******************************/

class Instruction
{
String inst;
int history;
Instruction()
{
history=0; // Assume Strongly Taken
}
}



class BranchPredictionLogic
{
public static void main(String args[]) throws Exception
{
int d,ch;
String his="Strongly Taken";
Instruction i1;
String HistoryArray[]={"Strongly Taken","Weakly Taken","Weakly Not Taken","Strongly Not Taken"};
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
System.out.println("Active Prefetch Queue A");

i1=new Instruction();
System.out.println("Enter instructions:");
while(true)
{
i1.inst=br.readLine();
if(isBranchInst(i1.inst)==true)
break;
}

System.out.println(i1.inst+" is a Branch Instruction");

System.out.println("\n\n***DECODE1 STAGE***");
System.out.println("*****Branch Target Buffer*****");

System.out.println("Source:----\nTarget:-----\nHistory: Strongly Taken");
do{
System.out.println("\n\n\n*****EXECUTE STAGE*****");
System.out.println("Does branch take place?? Enter 1 or 0 : ");
d=Integer.parseInt(br.readLine());

updateLogic(i1,d);

System.out.println("***New Branch Target Buffer***");
his=HistoryArray[i1.history];
System.out.println("Source:----\nTarget:----\nHistory: "+his);

System.out.println("Do you want to continue:(1/0): ");
ch=Integer.parseInt(br.readLine());

}while(ch!=0);

}

public static boolean isBranchInst(String x)
{
String jumpInstructions[]={"JC","JMP"};

for(int i=0;i<jumpInstructions.length;i++)
if(x.equals(jumpInstructions[i])==true)
return true;

return false;
}

public static void updateLogic(Instruction i,int n)
{
int history[]={0,1,2,3};
int historyAfterTaken[]={0,0,1,2};
int historyNotTaken[]={1,2,3,3};

if(n==1)
{
System.out.println("\nContents of B queue are valid!!!!!");
i.history=historyAfterTaken[i.history];
}
else if(n==0)
{
System.out.println("Contents of B queue not valid\nActive PrefetchQueue is A");
i.history=historyNotTaken[i.history];
}
}
}

Simulate Instruction Pairing in Pentium Processor

import java.util.*;
import java.io.*;

/**********************************
 * @author Rohit E Iyer 09-221
 * @date 18/02/2012
 *
 * AMP Practical No.2
 * TITLE:Instruction Pairing
 *----------------------------------
 * Instructions
 * {"MUL","DIV","JMP","CALL","JCC"}
 * cannot proceed in V-pipeline
 * If they occur in V-pipeline they are
 * shifted to U-pipeline
 *-----------------------------------
 * PipelineExp.java
 * *******************************/


class PipelineExp
{

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

BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
int k=1;
int U[]=new int[5];
int V[]=new int[5];

System.out.println("Enter program  no of lines:");
int n=Integer.parseInt(br.readLine());;

String prog[]=new String[n+1];

System.out.println("Enter program line by line");
for(int i=1;i<=n;i++)
prog[i]=br.readLine();


//--------------STEP1:First 2 instructions are fetched-----------//
if(k<=n) //Handle the condition if there's just a single instruction
U[0]=k++;
else
U[0]=0;
if(k<=n)
V[0]=k++; //Handle the condition if there's just a single instruction
else
V[0]=0;

display(U,V,prog);

//-------------STEP2:First 2 inst. go in Decode1 stage and Instr. 3 & 4 are fetched if n>2------------//
U[1]=U[0];
V[1]=V[0];
if(k<=n)
U[0]=k++;
else
U[0]=0;
if(k<=n)
V[0]=k++;
else
V[0]=0;
display(U,V,prog);

//-----------STEP3:Shift pipeline one stage at a time after checking for Instruction Pairing-------//
int x=0;

while(x==0)

{
if(isAlright(prog,U[1],V[1])==true)
{
U[4]=U[3];
U[3]=U[2];
U[2]=U[1];
U[1]=U[0];
if(k<=n)
U[0]=k++;
else
U[0]=0;

V[4]=V[3];
V[3]=V[2];
V[2]=V[1];
V[1]=V[0];
if(k<=n)
V[0]=k++;
else
V[0]=0;

display(U,V,prog);
if(U[4]==n || V[4]==n)
x=1;
}
else
{
U[4]=U[3];
U[3]=U[2];
U[2]=U[1];
U[1]=V[1];
V[1]=0;
V[4]=V[3];
V[3]=V[2];
V[2]=V[1];
display(U,V,prog);
if(U[4]==n || V[4]==n)
x=1;  //if the last instruction is displayed in writeback stage stop
}

}
System.out.println("Program Execution Completed");
}

public static boolean isAlright(String prog[],int x,int y)
//checks if the instruction in Decode1 stage of V-pipeline can stay in V-pipeline or not
{
String class1[]={"MOV","ADD","SUB","INC","DEC","PUSH","POP","LEA","NOP"}; //really not required :P :P
String class2[]={"MUL","DIV","JMP","CALL","JCC"};

if(y!=0)
{
for(int i=0;i<class2.length;i++)
if(prog[y].equals(class2[i]))
return false;
}
return true;
}
public static void display(int U[],int V[],String prog[]) throws Exception
//U r free to change this method the way u want the pipelines to get displayed
//Display U-array and V-array however u want.
{

System.out.println("*********U-PIPELINE********: ");

if(U[0]!=0)
System.out.println("FETCH STAGE : "+prog[U[0]]);
if(U[1]!=0)
System.out.println("DECODE1 STAGE : "+prog[U[1]]);
if(U[2]!=0)
System.out.println("DECODE2 STAGE : "+prog[U[2]]);
if(U[3]!=0)
System.out.println("EXECUTE STAGE : "+prog[U[3]]);
if(U[4]!=0)
System.out.println("WRITEBACK STAGE : "+prog[U[4]]);

System.out.println("\n\n*********V-PIPELINE********: ");

if(V[0]!=0)
System.out.println("FETCH STAGE : "+prog[V[0]]);
if(V[1]!=0)
System.out.println("DECODE1 STAGE : "+prog[V[1]]);
if(V[2]!=0)
System.out.println("DECODE2 STAGE : "+prog[V[2]]);
if(V[3]!=0)
System.out.println("EXECUTE STAGE : "+prog[V[3]]);
if(V[4]!=0)
System.out.println("WRITEBACK STAGE : "+prog[V[4]]);
System.out.println("\n\n\n");
System.in.read(); //Wait for ENTER press
}


}

Simulate FILE TRANSFER PROTOCOL over TCP - using Java Socket Programming

FTPServer.java

import java.io.*;
import java.net.*;


/**********************************
 * @author Rohit E Iyer
 * @date 03/02/2012
 *
 * ACN Practical No.1
 * Simulate FTP over TCP
 *
 * FTPServer.java
 * *******************************/

class FTPServer
{
public static void main(String args[])throws IOException
{
int c;
String s,inp;

//CONNECTION ESTABLISHMENT PHASE

ServerSocket ss=new ServerSocket(5432);

System.out.println("***WAITING for SERVER***\n\n");

Socket soc=ss.accept();

//DATA TRANSFER PHASE

System.out.println(soc+" AND SERVER CONNECTED!!!");
DataInputStream i=new DataInputStream(soc.getInputStream());
DataOutputStream o=new DataOutputStream(soc.getOutputStream());

getFilelist(i,o);

inp=i.readUTF(); //readUTF reads in Unicode Text Format hence

FileInputStream fin = new FileInputStream("F:/"+inp);

System.out.println("GET MESSAGE RECEIVED for FILE: "+inp);
while((c=fin.read())!=-1)
{
o.write(c);
o.flush();
}
fin.close();
}



public static void getFilelist(DataInputStream din,DataOutputStream dout) throws IOException
{
int p=0;
File folder = new File("F:/");
File[] listOfFiles = folder.listFiles();
for (File listOfFile : listOfFiles)

//listFiles() returns both files as well as directories so display only Files
if (listOfFile.isFile())
{
p++;
}
dout.write(p);
dout.flush();
for (File listOfFile : listOfFiles)
if (listOfFile.isFile())
{
dout.writeUTF(listOfFile.getName());
dout.flush();
}
}
}



-----------------------------------------------------------------------------------------------
FTPClient.java


import java.io.*;
import java.net.*;
import java.util.*;

/**********************************
 * @author Rohit E Iyer
 * @date 03/02/2012
 *
 * ACN Practical No.1
 * Simulate FTP over TCP
 *
 * FTPClient.java
 * *******************************/


class FTPClient
{
public static void main(String args[])throws IOException
{

String list[]=new String[20];
int p=0,ch;
Socket s=new Socket(InetAddress.getByName("localhost"),5432);
System.out.println("CLIENT connected to SERVER at"+ s);
DataInputStream i=new DataInputStream(s.getInputStream());
DataOutputStream o=new DataOutputStream(s.getOutputStream());

ch=getFile(i,o,list);

FileOutputStream fout = new FileOutputStream("F:/Download/"+list[ch-1]);
try
{
System.out.println("Transferring FILE: "+list[ch-1]+"\n\n");
do
{
ch=i.read();
fout.write(ch);
}while(ch!=-1);
}
catch(SocketException e) //Exception indicates FILE TRANSFER complete.
{
System.out.println("***FILE TRANSFER COMPLETE***");
}

}

public static int getFile(DataInputStream din,DataOutputStream dout,String flist[]) throws IOException
{
Scanner src=new Scanner(System.in);
int i=0,ch=0;
System.out.println("***FILE-LIST from SERVER***\n\n");
ch=din.read();
for(i=0;i<ch;i++)
{
flist[i]=din.readUTF();
System.out.println((i+1)+". "+flist[i]);
}
System.out.println("Enter File to be Requested");
ch=src.nextInt();
dout.writeUTF(flist[(ch-1)]);
dout.flush();
return (ch);
}
}

Java Program to simulate U & V pipelines of Pentium processor - ***DEPENDENT INSTRUCTIONS CONSIDERED...!!!***

import java.util.*;

/*********************************
 * AMP Pracs-1
 * --------------------------------------
* TITLE : SuperScalar Architecture
 * Simulate U & V pipelines
 * of Pentium processor -
 *--------------------------------------- 
 * (DEPENDENT Instructions
 * considered)
 *
 * @author Rohit Iyer 09-221
 * SEM 6 Vidyalankar Institute of Technology
 * Mumbai University
 *  
 * @date 03/02/2012            
 *********************************/

class PipelineExp
{
public static void main(String args[]) throws Exception
{
Scanner src=new Scanner(System.in);
System.out.println("Enter no of instructions:");
int n=src.nextInt();

int U[]=new int[5];   // 5 Stages Fetch,Decode1,Decode2,Execute,Write back
int V[]=new int[5];

int umax,vmax;
if(n%2==1)
{
umax=n; //umax=Max instruction number in U pipeline
vmax=n-1;    //vmax=Max instruction number in V pipeline
}
else
{
vmax=n;
umax=n-1;
}
int x=n;
int p=1;

if(n%2==1)
x=x+1;
x=((x-2)/2)+5; //x=Number of T-states required for n instructions

int j=1;
for(int i=1;i<=x;i++)
{
p=1;
U[4]=U[3];
U[3]=U[2];
U[2]=U[1];

if(U[1]!=0 && V[1]!=0)
{
System.out.println("Are instructions I"+U[1]+"and I"+V[1]+" pairable?? (1-YES/0-NO)");
p=src.nextInt(); //p=1-->Pairable  p=0-->NotPairable
if(p==0)
x++; //Penalty of 1 T-state since instructions not pairable
}

if(p==0)
U[1]=V[1]; //****D1 of V comes in D1 of U*****

if((U[1]==0) || p==1)
U[1]=U[0];

if(i<3 || p==1)
U[0]=j++;


V[4]=V[3]; //E goes to W
V[3]=V[2]; //D2 goes to E

if(p==1 || V[1]==0)
{
V[2]=V[1]; //D1 goes to D2
V[1]=V[0]; //F goes to D1
}
else
{
V[2]=0; //V-pipeline STALLED in D1 stage
V[1]=0;
}
if(p==1)
V[0]=j++;

for(int k=0;k<4;k++)
{
if(U[k]>umax)
U[k]=0;
if(V[k]>vmax)
V[k]=0;
}

System.out.println("\n\nPipeline U status\n\n");

if(U[0]!=0)
System.out.println("Fetch Stage: I"+U[0]);
if(U[1]!=0)
System.out.println("Decode1 Stage: I"+U[1]);
if(U[2]!=0)
System.out.println("Decode2 Stage: I"+U[2]);
if(U[3]!=0)
System.out.println("Execute Stage: I"+U[3]);

if(U[4]!=0)
System.out.println("Writeback Stage: I"+U[4]);


System.out.println("\n\nPipeline V status\n\n");

if(V[0]!=0)
System.out.println("Fetch Stage: I"+V[0]);
if(V[1]!=0)
System.out.println("Decode1 Stage: I"+V[1]);
if(V[2]!=0)
System.out.println("Decode2 Stage: I"+V[2]);
if(V[3]!=0)
System.out.println("Execute Stage: I"+V[3]);
   if(V[4]!=0)
System.out.println("Writeback Stage: I"+V[4]);

System.in.read();
}
}
}