Oracle优化总结

1.分区
2.java中调用子程序

 

1.索引的创建及其使用,分区必须与表同时创建
--分别创建表空间
space1,space2,space3

 

1.1范围分区

--创建表及其范围分区
create table stu
(
    id number,
    name varchar2(10),
    birth date
)
partition by range(birth)
(
    partition p1 values less than (to_date('2007-10-1','yyyy-mm-dd')) tablespace space1,
    partition p2 values less than (to_date('2008-10-1','yyyy-mm-dd')) tablespace space2,
    partition p3 values less than (to_date('2009-10-1','yyyy-mm-dd')) tablespace space3
);

insert into students values(1,'ss',to_date('2007-10-1','yyyy-mm-dd'));
insert into students values(1,'ss',to_date('2008-10-1','yyyy-mm-dd'));
insert into students values(1,'ss',to_date('2009-10-1','yyyy-mm-dd'));
insert into students values(1,'ss',to_date('2007-11-1','yyyy-mm-dd'));
insert into students values(1,'ss',to_date('2010-12-1','yyyy-mm-dd'));

 

1.2列表分区

create table ad
(
    id number,
    ad varchar2(20)	
)
partition by list(ad)
(
    partition p1 values('上海') tablespace space1,
    partition p2 values('北京') tablespace space2
);

insert into ad values(1,'北京');
insert into ad values(2,'北京');
insert into ad values(3,'北京');
insert into ad values(4,'上海');
insert into ad values(5,'上海');
insert into ad values(6,'上海');

 

1.3散列分区

--创建表及其散列分区
create table num
(
       id number
)
partition by hash(id)
(
          partition p1 tablespace space1,
          partition p2 tablespace space2
)
;

insert into num values(1);
insert into num values(2);
insert into num values(3);
insert into num values(4);
insert into num values(5);
insert into num values(6);

 

1.4索引分区

--索引分区
--本地分区只能一个所以对应一个表空间
--全局索引分区可以1个索引对应多个表空间
create index index_id1 on num(id) local
(
       partition p1 tablespace space1,
       partition p2 tablespace space2
);

create index index_id on num(id)
global partition by range(id)
(
       partition p1 values less than(4),
       partition p2 values less than(maxvalue)
);

 
1.5使用分区

--增加分区
alter table student add partition p4 values less than(maxvalue) tablespace space4 ;
--删除一个分区
alter table student drop partition p2;
--查询分区信息
select * from user_tab_partitions;

 

2.java中调用函数与存储过程

package test;
import hib.HibernateSessionFactory;

import java.sql.CallableStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.junit.Test;

import entity.Student;
public class HibTest {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		
		HibTest h=new HibTest();
		h.procTest();
		//h.funTest();
		//h.search();
	}
	@Test
	public void search(){
		Session session =HibernateSessionFactory.getSession();
		List<Student> list=session.createQuery("from  Student as st").list();
		System.out.println(list.size());
		for(Student st:list){
		  System.out.print(st.getStuname()+"   ");
		}
		session.close();
	}
	
	public void addStu(){
//		Session session =HibernateSessionFactory.getSession();
//		Transaction tr=session.beginTransaction();
//		Student stu=new Student();
//		stu.setStuname("zhangdan");
//		stu.setBirthday(new Date());
//		session.save(stu);
//		tr.commit();
//		
//		session.close();
	}
	/**
	 * oracle中的过程
	 * create or replace procedure mypro(num1 in out number,num2 in out number)
		as
  		n number;
		begin
  		n:=num1;
  		num1:=num2;
  		num2:=n;
		end;
	 *
	 */
	public void procTest(){
		Session session =HibernateSessionFactory.getSession();
		CallableStatement  cs=null;
		try {
			cs=session.connection().prepareCall("begin proc(?,?); end;");
			//输入参数
			cs.setInt(1, 10);
			cs.setInt(2, 20);
			//输出参数
			cs.registerOutParameter(1, Types.NUMERIC);
			cs.registerOutParameter(2, Types.NUMERIC);
			Transaction tr=session.beginTransaction();
			cs.execute();
			tr.commit();
		    int x=cs.getInt(1);
		    int y=cs.getInt(2);
		    System.out.println(x);
		    System.out.println(y);
			session.close();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
	}
	/**
	 * oracle中的函数
	 *  create or replace function myfun(n1 number,n2 varchar2)
 		return varchar2 is
   		n varchar2(10);
 		begin
   			select stuname into n from student  where num=n1 and stuname=n2;
   		return n;
 		end;
	 *
	 */
	public void funTest(){
		Session session =HibernateSessionFactory.getSession();
		CallableStatement  cs=null;
		try {
			cs=session.connection().prepareCall("{? = call myfun(?,?)}");
			cs.registerOutParameter(1, Types.VARCHAR);
			cs.setInt(2,2);
			cs.setString(3, "刘德华");
			Transaction tr=session.beginTransaction();
			cs.execute();
			tr.commit();
			String result=cs.getString(1);
			System.out.println("========="+result);
			session.close();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
	}
}