一、开发需求最近有一个开发需求。一般需要先使用主表，或者将主表关联几个子表来查询ID（主键）和一些主表字段。然后用这些ID在最多10个表中找到对应的记录。主表的记录数在2000万条左右，每个分表的记录数在100万以上，可能高达5000万条。主表中的一条数据可能对应子表中的多条数据。现在开发使用的逻辑是：1.使用条件查询主表或者主表和几个子表（不同场景）记录主表的ID值和其他一些满足条件的主表字段项状况。2.利用主表的ID值，与几个子表一起使用IN子句，查询子表中满足条件的记录。如果有多个子表，则执行多次SQL语句。这样做的缺点是（1）最多可能检测到100多个ID值，所以当分表使用IN子句时，很可能导致CBO选择全表扫描，虽然理论上，一条SQL可能不适用索引扫描效率一定高，CBO必须根据已有的统计信息选择cost值最低的执行计划，但是全表扫描的效率对于一张最高甚至最高的表可想而知最高值。（这里不是认真的，有可能使用SSD和Exadata硬件可以提高全表扫描的效率，这里只讨论一般存储条件下的可行方案）。另外，如果场景需要多个分表，SQL会执行多次。在一种情况下，SQL语句可能需要执行多次。综合需求，可能至少有以下改进方案1、用一条SQL完成以上需求。(1.1)主表和所有子表采用join关联方式。两表两表join，由于主分表是一对多的关系，很可能会因为笛卡尔积导致结果集变得非常大，应用过程会造成内存溢出错误。(1.2)使用unionall将子表关联为VIEW，再关联到主表。这是罗师傅推荐的方法，例如：SELECTA.ID,A.NAMEFROMT_ZHUBIAOA,(SELECTID,NAMEFROMT_ZIBIAO1UNIONALLSELECTID,NAMEFROMT_ZIBIAO2)BWHEREA.NAME='A'ANDA.ID=B.ID;和(1.1)不同的是，每个子表的检索是独立的indexuniquescan，所有子表关联为VIEW，与主表做嵌套循环连接。不过据了解，需求中每个子表的字段基本不一样，有的子表有几十个选择的字段。这样，使用这种UNIONALL需要同类型的搜索字段，开发拼接比较费力和不灵活。.2、将（1）的结果集存入临时表（temporarytable，不是应用程序自己处理的普通表），相当于一个临时结果集。分表每次关联这张临时表，都会查询两张表，这样可以避免IN值过多导致的检索效率低下。同时，由于两张表的关联字段是主键或外键（集合索引），可以利用索引扫描检索，完成事务级别控制的临时表。在这个事务之后，让Oracle自动清除数据，同时隔离会话之间的数据。3.(1)保持不变，只是(2)中的每个分表查询由应用程序控制。比如每30个IN值执行一次SQL语句，将一个分表查询拆分成若干个查询。优点是每次都可以使用外键索引扫描取回结果集，缺点是多执行了N次SQL语句。总结以上三种方案，(1)由于潜在的结果集过大和灵活性问题被否决，采用目前的方案(3)，因为它对开发的转化小，只需要besplit对于IN语句，如果检索效率高，测试结论满足非功能性需求，就会采用这种方式。如果不满足要求，将考虑解决方案（2）。就我个人而言，如果能满足要求，方案一最好。使用合适的索引来完成搜索减少了应用程序和数据库之间的交互次数，但是这个业务需求可能真的很复杂。在获取信息方面确实要求比较高。第二种是方案2，虽然分表的SQL执行次数不变，但是可以使用临时表，保证每次检索都可以利用索引快速定位，避免大表的全表扫描，临时表的特点表对应用程序几乎是透明的。方案三，唯一的好处是避免了大表的全表扫描，但是代价是更多的SQL交互。至于能否弥补性能上的差异，只能等性能测试结束后才能看到。如果您对以上需求有更好的解决方案，或者以上解决方案还有问题，欢迎指正！2、临时表介绍及实验在需要缓存中间结果集的场景下，可以考虑使用临时表，因为临时表中的数据在session级别是私有的，每个session只能看到和修改自己的数据。当会话结束时，表中的数据会自动删除，不需要应用程序操作。CREATEGLOBALTEMPORARYTABLE语法用于创建临时表，ONCOMMIT子句决定表数据是事务级别还是会话级别，默认是事务级别。可以在临时表上创建索引、视图或触发器。ONCOMMIT子句的两个参数的区别在于：临时表中的数据默认存放在default临时表空间中，创建过程中可以指定其他临时表空间。临时表的数据和索引在定义时不会分配段，只有在使用INSERT(CTAS)插入语句时才会分配段空间。创建事务级临时表：SQL>createglobaltemporarytabletest(idnumber,namevarchar2(10))oncommitdeleterows；查看表属性，TEMPORARY指定为Y，说明是临时表，没有tablespace_name参数值，说明不是存放在普通表空间中。SQL>selecttable_name,tablespace_name,temporaryfromdba_tableswhereowner='BISAL';TABLE_NAMETABLESPACE_NAMETEM--------------------------------------TESTYsession1执行：SQL>insertintotestvalues(1,'a');SQL>select*fromtest;IDNAME-----1asession2执行：SQL>select*fromtest;norowsselected说明临时表数据会话级隔离，session1执行：SQL>commit;SQL>select*fromtest;norowsselected执行commit结束事务，Oracle会自动删除临时表中的数据。创建会话级临时表：SQL>createglobaltemporarytabletest(idnumber,namevarchar2(10))oncommitpreserverows；表属性相同：SQL>selecttable_name,tablespace_name,temporaryfromdba_tableswhereowner='BISAL';TABLE_NAMETABLESPACE_NAMETEM-----------------------------------TESTYsession1执行：SQL>insertintotestvalues(1,'a');SQL>select*fromtest;IDNAME------1asession2执行：SQL>select*fromtest;norowsselectedsession1执行：SQL>commit;SQL>select*fromtest;IDNAME------1a执行commit后，数据没有被删除。退出当前会话，重新登录，发现数据被删除了：SQL>select*fromtest;norowsselectedSummary临时表的使用其实很简单，除了和普通的建表语句有些语法上的区别外，可以看做是普通的forapplicationstable，但是还有一些细节需要注意：1.临时表默认使用default临时表空间。如果应用有很多排序等需要消耗临时表空间的场景，临时表的使用频率很高，那么为了避免相互影响，可以考虑为临时表建立一个独立的临时表空间。2.如果使用会话级临时表，应用使用连接池，需要保证应用使用同一个会话完成一个事务，避免违反临时表使用规则。
