the use of temporary table oracle

2010-06-13  来源:本站原创  分类:Database  人气:199 

Of the problem: Consider the following SQL statement:

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

Inside there are N number of inquiries or affect the performance, think of ways to solve this problem

Solution to the problem:

Use of temporary tables, the first of these conditions or insert the temporary table, then check with the joint approach to the joint inquiry, after all, or than so much faster.

Feasibility of the solution:

According to the characteristics of the temporary table, theoretically possible, however, to change these things is limited under the framework of our forcore, therefore, are not that easy to change, here I put my writing out of this settlement process and We explore to see if there is a better solution.

We first re-familiarize yourself with the Oracle temporary table:

Create Oracle temporary table, you can have two types of temporary tables:

1. Session-level temporary tables

2. Transaction-level temporary tables

Two differences in the types of temporary tables:

Session level temporary table with on commit preserve rows; the transaction level is used on commit delete rows; usage, session-level session ended only when the data in the temporary table will be truncated, and the transaction-level temporary table is whether commit, rollback or the session ends, the temporary table data will be truncated if it is not the connection pool, respectively, according to two types of temporary table to experiment with a jdbc connection to obtain and we imagine that the same result, if the use of connection pooling, when connection pool is only one connection, you can get the result we want. On this part of the test code can be similar to the following: Table admin_work_area temporary table for the session level, where the connection pool to configure a connection only

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());
                }

        }

Begin to solve the problem: with a similar link directly above the test code the database was found and we thought it would feel very easy to solve this problem, which directly modify the forcore because forcore which is the connection pool configuration, so it was a problem, one will see that we can insert our data, one will see us into the data, it is because every time we use a different connection pool of connections caused by the connection and the connection pool is not complete destruction , and through repeated trial and find relevant information online, we have no way to guarantee that when we use only one connection, each connection pool for us not to take a different connection. In our forcore the DAO implementation class in the general code:

public List selectAdminWorkArea(String classId){
                List list = new ArrayList();
                list = (List)getSqlMapClientTemplate().queryForList("tp.selectAdminWorkArea");
                return list;
        }

When we started, it becomes as to the use of temporary tables:

public List selectAdminWorkArea(String classId){
                List list = new ArrayList();
                getSqlMapClientTemplate().insert("tp.insertAdminWorkArea", classId);// Another part of the inserted data
                list = (List)getSqlMapClientTemplate().queryForList("tp.selectAdminWorkArea");
                return list;
        }

Some more temporary table is used to insert the data, the following statement is used and the joint inquiry temporary table, we use the temporary table is session level, and according to our ideas, so you can use, and However, we found that insertion of data, when we take the following can not see, because they may each use a getSqlMapClientTemplate (), he would take a different connection, then we put him to read as follows:

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;
        }

After such a change or not, we have a little trouble, then think of him get hold of affairs, we want to see is not, so he read as follows:

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;
        }

Little progress, then tested, discovered, or sometimes you can see the data, and sometimes do not see the data, which is the number of connections we want to be the problem, they changed the connection pool, after several tests, found that only one connection , they can guarantee getting what we want, and if more than one time, there will be uncertainty. After many tests we have no way to solve the existing number of connections in the connection pool and can get the result we want, so no recourse but to make compromises in order to re-assign a connection pool, use the temporary table at the time, the use of The connection pool, in the need to modify the DAO place, we re-build a DAO, so that his successor had the DAO, modify the appropriate way to change Daima As written, the connection pool with our own distribution, so that problems get resolved. connection pool configuration on the oracle himself directly

<bean>
    <property name="URL" value="${datasource.url}" />
    <property name="user" value="${datasource.username}" />
    <property name="password" value="${datasource.password}" />
  </bean>

Problem solving process is that, at present because there is no better way to find, the first such solution, and if a better way to learn about you please write the above.

The following code used in the project:

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;
    }
相关文章
  • the use of temporary table oracle 2010-06-13

    Of the problem: Consider the following SQL statement: 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

  • The temporary table oracle 2011-09-20

    Reference material 1 Oracle temporary table usage http://www.iteye.com/topic/371390 2 oracle temporary table to learn http://shmily-zjl.iteye.com/blog/670458 3 oracle temporary table summarizes the usage of http://fangrn.iteye.com/blog/567191 4 oracl

  • dynamically create a temporary table oracle 2011-01-13

    CREATE OR REPLACE PACKAGE BODY "DAILYREPORTNEW1" is procedure welldailyreportnew1 (startdate in varchar2, enddate in varchar2, orgid in varchar2, my_ref_cursor out ref_cursor) is begin DECLARE flg NUMBER; sqlstr1 varchar2 (1000); - create a temp

  • the efficiency of the temporary table oracle 2011-05-11

    Reproduced a summary of online search problem is a little inspiration Summary: Physical tables and temporary tables memory table three table 10W times I have done to increase the data query to delete the revised work the following conclusions: In the

  • oracle temporary table usage summary 2010-03-26

    1 Introduction At present, all using Oracle as the database application support platform, most of the relatively large amount of data the system, that is, the amount of data table under normal circumstances are 1 million more than the amount of data.

  • (Transfer) oracle temporary table usage summary 2010-07-22

    http://blog.csdn.net/wyzxg/archive/2007/11/13/1882347.aspx oracle temporary table usage summary collection Keyword: Basic concepts, principles The characteristics of the temporary table, when to use Ago, the new company's interviewer asked a question

  • Understanding of the session from the oracle temporary table with the link 2011-03-10

    Spend more time with the oracle, but simply to use, with little concern for some basic things, such as the oracle of the difference between conversation and links, I'm too lazy to see a lot of introduction, just read the oracle temporary table usage,

  • ORACLE The temporary table 2011-06-26

    You can use the temporary table ORACLE Reproduced Temporary Tables Temporary tables directory 1 Introduction 2 Details 3 Create a temporary table Similarities and differences between 4 and SQL SERVER 1 Introduction In addition to the ORACLE database

  • orcale create temporary table with the lock 2011-04-27

    Recently used the oracle temporary table and lock it briefly summarize here. Mainly for anti-lock concurrent, temporary tables can also be used for anti-concurrent. Details on the usage of oracle locks can be found in this blog: http://book.51cto.com

  • oracle 9i temporary table space 2010-03-04

    Taken from: http://hi.baidu.com/zzztou/blog/item/f8733707008383c87b8947b3.html Normally, the complete Select statement, create index using the TEMP table space and some sort is done, Oracle will automatically release the temporary section of a swap o

  • Oracle temporary table (reproduced) 2010-05-05

    1 Introduction At present, all using Oracle as the database application support platform, most of the relatively large amount of data the system, that is, the amount of data table under normal circumstances are 1 million more than the amount of data.

  • Oracle in a temporary table (working with real table) 2010-06-16

    1 Introduction At present, all supporting platform using oracle as a database application, most of the relatively large amount of data the system, that is, the amount of data table under normal circumstances are 1 million more than the amount of data

  • oracle temporary table 2010-06-23

    oracle temporary tables referenced in this article http://www.blogjava.net/pdw2009/archive/2007/06/20/125383.html In Oracle8i or later, you can create the following two temporary table: 1. Session-specific temporary table CREATE GLOBAL TEMPORARY <TAB

  • (Transfer) Oracle Application of temporary table 2010-07-22

    http://hi.baidu.com/edeed/blog/item/6d6e2834a71d113f5bb5f5ab.html Oracle Application of temporary table 2007-12-27 13:56 I do projects in recent years less use of temporary tables Temporary Table, in fact Temp Table or can compare a wide range of app

  • oracle create table space. create the user. to change the user default tablespace. delete the temporary table space 2010-08-28

    Create temporary table space create temporary tablespace stbss_tmp tempfile 'E:\oracle\product\10.2.0\oradata\orcl\stbss_temp01.dbf' size 32m autoextend on next 32m maxsize 2048m extent management local; Create a data table space create tablespace st

  • Oracle temporary table (change) 2010-09-01

    In Oracle8i or later, you can create the following two temporary table: 1. Session-specific temporary table CREATE GLOBAL TEMPORARY <TABLE_NAME> (<column specification>) ON COMMIT PRESERVE ROWS; 2. Transaction-specific temporary table CREATE G

  • ORACLE stored in the temporary table created during 2010-10-12

    create procedure pro as str varchar2 (100); begin str: = 'CREATE GLOBAL TEMPORARY TABLE TABLENAME (COL1 VARCHAR2 (10), COL2 NUMBER) ON COMMIT PRESERVE ROWS'; execute immediate str; - use dynamic SQL statement to perform end; / stored procedure can no

  • [Transfer] Oracle temporary table 2010-10-19

    In Oracle8i or later, you can create two temporary tables: 1. Session-specific temporary table CREATE GLOBAL TEMPORARY <TABLE_NAME> (<column specification>) ON COMMIT PRESERVE ROWS; 2. Transaction-specific temporary table CREATE GLOBAL TEMPORA

  • Oracle uses a temporary table in the summary 2010-11-11

    1 Syntax In Oracle, you can create two temporary tables: 1) session-specific temporary table CREATE GLOBAL TEMPORARY () ON COMMIT PRESERVE ROWS; 2) transaction-specific temporary table CREATE GLOBAL TEMPORARY () ON COMMIT DELETE ROWS; CREATE GLOBAL T

  • Oracle database in the temporary table usage 2010-12-05

    This article introduces the Oracle database in the temporary table usage, we hope to help study and work. A: Grammar In Oracle, you can create two temporary tables: (1) session-specific temporary table CREATE GLOBAL TEMPORARY ( ) ON COMMIT PRESERVE R