`

oracle临时表的运用研究

OracleDAOCC++C# 
阅读更多
问题的提出：请看下面的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;
    }
0
顶
1
踩
分享到：
| spring DAO中批处理的使用
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    ORACLE数据库优化技术研究.pdf

    子查询的嵌套层次越深，效率越低，因此应尽量减少子查询的数量或使用临时表来提高性能。 此外，数据规范化也是优化的重要方面，通过消除冗余数据，提高数据一致性，降低更新异常和插入异常，从而提高数据库的整体...

    ORACLE核心应用技术研究.doc

    Oracle核心应用技术研究 Oracle ERP最佳技术实践E-BUSINESS SUITE是Oracle为企业提供的一整套集成的财务、供应链和人力资源管理解决方案。本文档将深入探讨Oracle的核心应用技术，包括数据库（DB）、PL/SQL编程语言...

    基于成本的ORACLE子查询性能研究.pdf

    2. 内嵌视图（内嵌式子查询）：在FROM子句中使用的子查询，通常作为临时表来使用。这种子查询的结果集被当作一个整体参与主查询的处理。在某些情况下，优化器可能会尝试合并视图以提高效率。 3. 嵌套子查询：出现在...

    Oracle数据库数据恢复的若干方法研究.pdf

    Oracle数据库数据恢复的若干方法研究主要探讨了在面对不同场景下如何有效地恢复Oracle数据库中的数据。Oracle数据库是一种广泛应用于企业级的信息系统，对于数据的高可用性和安全性有着极高的要求。本文作者通过实例...

    oracle 复杂查询语句的使用

    Oracle数据库系统支持复杂的查询语句，这使得用户能够高效地从多表环境中获取所需的数据。在处理大型数据库时，理解并熟练运用这些复杂查询技术至关重要，因为它们可以帮助优化查询性能，减少不必要的数据处理，提高...

    基于Oracle复杂油藏数据统计查询方法与实际案例.pdf

    5. 通过创建视图（View）或者临时表（Temporary Table）等方式，优化数据处理流程，提高数据查询效率。 案例中的具体统计数据需求包括油层岩石性质、原油物性以及地层水性质等方面的数据。对于这些需求，本文详细...

    北京邮电大学软件工程研究生的课程---数据库设计开发-44.pptx

    由于经常涉及大范围的排序和聚合，应创建大型的临时表空间以处理超出内存的操作。同时，较小的共享SQL区域配合较大的数据缓冲区高速缓冲可以提高数据读取效率。 4.4.2 ORACLE空间设计 空间设计是数据库规划的重要...

    数据库SQL查询语句优化方法的研究.pdf

    f) **适当使用临时表**：在处理大量数据时，使用临时表可以提高处理效率。 g) **合理使用GROUP BY和HAVING**：避免在GROUP BY或HAVING子句中使用计算表达式，这可能导致不必要的计算。 h) **限制返回结果**：使用...

    行业-102 透彻研究通过explain命令得到的SQL执行计划（3）.rar

    7. **额外信息**：例如，是否进行了排序、临时表的创建、回表操作等。 在“行业-102 透彻研究通过explain命令得到的SQL执行计划（3）”中，可能会详细讲解如何解读这些信息，特别是针对复杂查询的执行计划。这部分...

    水晶报表 练习 教程 demo 资料

    在水晶报表的设计过程中，你将学习如何连接到Oracle数据库，选择合适的表和字段，以及如何定义数据源。报表设计涉及多个方面，包括但不限于： 1. **字段选择**：根据业务需求，从数据库中选取相关的字段，例如省份...

    简析甲骨刻辞中的使令动词“令”.pdf

    这表明使令句不仅在文献记录中有广泛存在，而且在实际运用中也成为了兼语句式中较为普遍的形式。 综上所述，甲骨文中的使令动词“令”不仅是研究古汉语语法的重要组成部分，也是理解古代汉语使令句式的关键。通过对...

    标准黑白英文07.docx

    他在总账团队负责费用记账，熟练运用Oracle系统。他还创建了杠杆收购和合并模型，评估潜在交易对公司收益、资本结构、信用比率和所有权的前瞻性影响。 - 此外，他根据折现现金流、可比公司、先例交易和拆分价值分析...

    基于J2ee商品交易系统

    4. **数据库设计**：SQL Server 2000作为本系统的数据库，需要设计合理的数据表结构，包括商品表、用户表、订单表、交易状态表等，确保数据的一致性和完整性。同时，为了优化查询性能，可能还需要建立索引，进行分区...

    java 项目实例开发代码.rar

    在本Java项目实例开发代码中，我们可以深入学习和理解如何运用Java编程语言和Hibernate框架进行实际项目的开发。这个压缩包包含了一系列的代码示例，旨在帮助开发者提升在企业级应用开发中的技能。以下是对其中涉及...

    NotesFX:NotesFX（JavaFX中的粘滞便笺）

    JavaFX是Oracle公司推出的用于构建桌面、移动和嵌入式设备的富客户端应用的框架。它提供了丰富的用户界面组件和强大的2D/3D图形功能，使得开发者能够创建出美观且交互性强的应用程序。JavaFX的跨平台特性使得同一...

Magicbox
Global site tag (gtag.js) - Google Analytics