oracle临时表的运用研究

izfdxolxpt

问题的提出：请看下面的SQL语句：
select *
from (select rownum r_id, t_tmp.*
from (select p.*,
o.NAME_ZH,
o.NAME_EN,
o.NAME_JP,
o.ORG_DESC_ZH,
o.ORG_DESC_EN,
o.ORG_DESC_JP,
c.name_zh city_name_zh,
c.name_en city_name_en,
c.name_jp city_name_jp,
d.name_zh district_name_zh,
d.name_en district_name_en,
d.name_jp district_name_jp,
r.name_zh country_name_zh,
r.name_en country_name_en,
r.name_jp country_name_jp,
a.name_zh area_name_zh,
a.name_en area_name_en,
a.name_jp area_name_jp,
decode(t.propid,, 0, 1) isKeyHotel,
decode(s.propid,, 0, 1) isExchangeRateSet
from t_prop                  p,
t_city                  c,
t_country               r,
t_district              d,
t_area                  a,
T_PROPORG               o,
t_keyhotel              t,
t_prop_exchangerate_set s
where 1 = 1
and (p.prop = :1 or p.prop = :2 or p.prop = :3 or
p.prop = :4 or p.prop = :5 or p.prop = :6 or
p.prop = :7 or p.prop = :8 or p.prop = :9 or
p.prop = :10 or p.prop = :11 or p.prop = :12 or
p.prop = :13 or p.prop = :14 or p.prop = :15 or
p.prop = :16 or p.prop = :17 or p.prop = :18 or
p.prop = :19 or p.prop = :20 or p.prop = :21 or
p.prop = :22 or p.prop = :23 or p.prop = :24 or
p.prop = :25 or p.prop = :26 or p.prop = :27 or
p.prop = :28 or p.prop = :29 or p.prop = :30 or
p.prop = :31 or p.prop = :32 or p.prop = :33 or
p.prop = :34 or p.prop = :35 or p.prop = :36 or
p.prop = :37 or p.prop = :38 or p.prop = :39 or
p.prop = :40 or p.prop = :41 or p.prop = :42 or
p.prop = :43 or p.prop = :44 or p.prop = :45 or
p.prop = :46 or p.prop = :47 or p.prop = :48 or
p.prop = :49 or p.prop = :50 or p.prop = :51 or
p.prop = :52 or p.prop = :53 or p.prop = :54 or
p.prop = :55 or p.prop = :56 or p.prop = :57 or
p.prop = :58 or p.prop = :59 or p.prop = :60 or
p.prop = :61 or p.prop = :62 or p.prop = :63 or
p.prop = :64 or p.prop = :65 or p.prop = :66 or
p.prop = :67 or p.prop = :68 or p.prop = :69 or
p.prop = :70 or p.prop = :71 or p.prop = :72 or
p.prop = :73 or p.prop = :74 or p.prop = :75 or
p.prop = :76 or p.prop = :77 or p.prop = :78 or
p.prop = :79 or p.prop = :80 or p.prop = :81 or
p.prop = :82 or p.prop = :83 or p.prop = :84 or
p.prop = :85 or p.prop = :86 or p.prop = :87 or
p.prop = :88 or p.prop = :89 or p.prop = :90 or
p.prop = :91 or p.prop = :92 or p.prop = :93 or
p.prop = :94 or p.prop = :95 or p.prop = :96 or
p.prop = :97 or p.prop = :98 or p.prop = :99 or
p.prop = :100 or p.prop = :101 or p.prop = :102 or
p.prop = :103............................
.. or p.prop = :5443)
and p.countryid = r.countryid(+)
and p.cityid = c.cityid(+)
and p.areaid = a.areaid(+)
and p.districtid = d.districtid(+)
and p.ORGID = o.ORGID(+)
and p.prop = t.propid(+)
and p.prop = s.propid(+)
order by to_number(p.PROP)) t_tmp
where rownum <= :5444)
where r_id >= :5445

里面有N多个or影响了查询时的性能，想办法解决这个问题
问题的解决方案：
采用临时表，把这些or的条件先插入临时表中，然后用联合查询的方法来解决，联合查询毕竟比用这么多的or快。
解决方案的可行性分析：
根据临时表的特性，理论上是可行的，不过，改这些东西是限制在我们的forcore框架之下的，所以，改起来就不那么容易了，下面我就把我的这个解决过程写出来和大家探讨一下，看是否有更好的解决办法。
我们首先再熟悉一下Oracle临时表：
创建Oracle临时表，可以有两种类型的临时表：
1.会话级的临时表
2.事务级的临时表
两中类型临时表的区别：
会话级临时表采用on commit preserve rows；而事务级则采用on commit delete rows；用法上，会话级别只有当会话结束临时表中的数据才会被截断，而且事务级临时表则不管是commit、rollback或者是会话结束，临时表中的数据都将被截断如果不用连接池，分别按两种类型的临时表用jdbc连接做实验，得到的和我们想像的是一样的结果，如果用连接池，当连接池中只有一个连接时，也可以得到我们想要的结果。关于这部分的试验代码类似如下即可：表admin_work_area为会话级的临时表,这里的连接池只配置一个连接
public void testJDBC() {
try {
OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:oracle:thin:@192.168.20.230:1521:FOG");
ods.setUser("fog");
ods.setPassword("fog");
ods.setConnectionCachingEnabled(true);
java.util.Properties jup = new java.util.Properties();
jup.setProperty("InitialLimit", "1");
jup.setProperty("MinLimit", "1");
jup.setProperty("MaxLimit", "1");
ods.setConnectionCacheProperties(jup);
Connection cx0 = ods.getConnection();
Statement stmt = cx0.createStatement();
stmt.execute("insert into admin_work_area(class) values ('1072')");
ResultSet rs = stmt.executeQuery("select * from admin_work_area");
while (rs.next()) {
String classId = rs.getString("class");
System.out.println(classId);
}
rs.close();
stmt.close();
Connection cx1 = ods.getConnection();
Statement stmt1 = cx1.createStatement();
//stmt1.execute("insert into admin_work_area(class) values ('1072')");
ResultSet rs1 = stmt1.executeQuery("select * from admin_work_area");
while (rs1.next()) {
String classId = rs1.getString("class");
System.out.println(classId);
}
rs1.close();
stmt1.close();
cx0.close();
cx1.close();
} catch (Exception e) {
System.out.println(e.getMessage());
}
}


开始解决问题：用了类似如上的直接连数据库的试验代码发现和我们想像的一样，感觉这个问题很容易解决，就直接在forcore里面进行修改，因为forcore里面配置的是连接池，于是就出现了问题，一会可以看到我们我们插入的数据，一会看不到我们插入的数据，这是由于每次用的连接池的连接不一样所致，而且连接池中的连接是不会销毁完的，经过反复试验和在网上查找相关资料，我们也没有办法保证我们用时只用一个连接，每次不让连接池给我们取不同的连接。在我们的 forcore中DAO实现类中一般的代码如下：
public List selectAdminWorkArea(String classId){
List list = new ArrayList();
list = (List)getSqlMapClientTemplate().queryForList("tp.selectAdminWorkArea");
return list;
}


我们开始的时候，把它变为如下以运用临时表：
public List selectAdminWorkArea(String classId){
List list = new ArrayList();
getSqlMapClientTemplate().insert("tp.insertAdminWorkArea", classId);//多了一个插入数据的部分
list = (List)getSqlMapClientTemplate().queryForList("tp.selectAdminWorkArea");
return list;
}


多出的部分是我们用来向临时表里插入数据，下面的语句是用来和临时表联合进行查询，我们的临时表用的是会话级的，按我们的想法，这样用就可以了，可是发现我们插入的数据，在我们下面取的时候根本看不到，因为可能每用一个getSqlMapClientTemplate()，他就会取一个不同的连接，后来我们把他改为如下：
public List selectAdminWorkArea(String classId){
List list = new ArrayList();
SqlMapClient sqlMapClient = this.getSqlMapClient();
sqlMapClient.insert("tp.insertAdminWorkArea", classId);
list = (List)sqlMapClient.queryForList("tp.selectAdminWorkArea");
return list;
}


这样改了以后还是不行，我们有点陷入困境，后来想想给他弄个事务，看会是我们想要的不，于是又改为如下：
public List selectAdminWorkArea(String classId){
List list = new ArrayList();
SqlMapClient sqlMapClient = this.getSqlMapClient();
try{
sqlMapClient.startTransaction();
sqlMapClient.insert("tp.insertAdminWorkArea", classId);
list = (List)sqlMapClient.queryForList("tp.selectAdminWorkArea");
sqlMapClient.endTransaction();
}catch(Exception e){
System.out.println(e.getMessage());
}
return list;
}


有点进展，于是进行测试 ，发现，还是有时可以看到数据，有时看不到数据，这是，我们想可能是连接数的问题，就改了连接池，经过几次测试，发现当只有一个连接的时候，可以保证得到我们想要的情况，如果大于一个时，就会出现不确定的情况。我们经过多方面试验没有办法解决在连接池既有多个连接又能得到我们想要的结果，于是没办法，只能委曲求全，重新配一个连接池，在要用到临时表的时候，就用这个连接池，在需要修改的DAO的地方，我们重新建一个DAO，让他继承原来的DAO，修改其中相应的方法，改成代码如上所写，连接池用我们自己配的，这样问题就得到了解决.连接池的配置就直接用oracle自己
<bean id="tp_dataSource" class="oracle.jdbc.pool.OracleDataSource">
<property name="URL" value="${datasource.url}" />
<property name="user" value="${datasource.username}" />
<property name="password" value="${datasource.password}" />
</bean>

问题的解决过程就是如此，目前因为没有找到更好的办法，就先这样解决了，如有更好的办法请写到上面大家学习一下。
下面在项目中用的代码：
  public List<HotelInfoExcelVo> findHotelInfoExcelVoAll(HotelSearchVO hotelSearchVO) {
List<HotelInfoExcelVo> list = new ArrayList<HotelInfoExcelVo>();
List<String> props = new ArrayList<String>();
props = hotelSearchVO.getProps();
SqlMapClient sqlMapClient = this.getSqlMapClient();
Map map = new HashMap();
map.put("hotelSearchVO", hotelSearchVO);
map.put("tp_flag", "1");
try{
sqlMapClient.startTransaction();
sqlMapClient.startBatch();
for(int i = 0 ; i < props.size(); i ++){
String propId = props.get(i);
sqlMapClient.insert("Prop.insertTpPropid",propId);
}
sqlMapClient.executeBatch();
list = sqlMapClient.queryForList("Prop.findHotelInfoExcelVoAll", map);
sqlMapClient.endTransaction();
}catch(Exception e){
logger.error("class name TPDAO--method findHotelInfoExcelVoAll error!"+e);
}
return list;
}