hua6884858

a和b表都分别有350W条数据,在a表和b表都以username为主键;

这个查询是找出存在在a表但不存在在b表的username

select username,mobilephone from a where username not in (select username from b );

Statistics
----------------------------------------------------------
403 recursive calls
91 db block gets
10804761 consistent gets
30631 physical reads
13748 redo size
184 bytes sent via SQL*Net to client
224 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

结果a和b都走了"INDEX FAST FULL SCAN"的索引扫描,速度奇慢,花了12094秒(3个多小时,因为这个是作为一个job在晚上3点钟运行的,这是在user_jobs里显示的时间)

select /*+ no_index(a) */ username,mobilephone from a where username not in (select /*+ no_index(b) */ username from b );

Statistics
----------------------------------------------------------
480 recursive calls
93 db block gets
80524 consistent gets
24934 physical reads
30000 redo size
184 bytes sent via SQL*Net to client
224 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

加上了no_index之后,运行时间为00:00:14.82秒,运行效率得到了大大提高!!("consistent gets"也的到很大幅度的降低)

这时候可以体现出全表扫描比索引扫描快!!

	/**构架获取关联产品的SQL语句
	 * 
	 * @param sql
	 * @param oParam
	 * @param fb
	 */
	private void buildRelateproductSQL(StringBuffer sql,List oParam,FormBody fb) {
		String userId = fb.getString("USERID");
		String focus = fb.getString("FOCUS");
		sql.append("select r1.*,r3.bssproductcode,r3.productname from tf_andcustrela r1,");
		sql.append("td_product r2, td_product_bss r3,");
		sql.append("(select * from tf_custinfoweb t where t.upuserid = ? and t.state = 'E') r4 ");
		oParam.add(userId);
		sql.append("where r1.userid = r4.userid and r1.productid = r2.productid and r1.productid = r3.productid ");
		sql.append("and r3.state = 'E' and r2.state = 'E' and r1.state = 'E' ");
		if(!ExtString.isEmpty(focus)) {
			sql.append("and r1.phonenum like '%' || ? || '%' ");
			oParam.add(focus);
		}
	}

/**构架获取关联产品的SQL语句
	 * 
	 * @param sql
	 * @param oParam
	 * @param fb
	 */
	private void buildRelateproductSQL(StringBuffer sql,List oParam,FormBody fb) {
		String userId = fb.getString("USERID");
		String focus = fb.getString("FOCUS");
		sql.append("select r1.*,r3.bssproductcode,r3.productname from ");
		sql.append("(Select /*+index(t,INX_DT_USERID)*/ * From Tf_Andcustrela t ");
		sql.append("Where Exists (Select * From Tf_Custinfoweb T2 Where T2.Upuserid = ? And T2.State = 'E' And t.Userid = Userid) ");
		oParam.add(userId);
		sql.append("And state='E') R1, ");
		sql.append("(Select * From Td_Product T1 Where T1.Upproductid = 0) R2, ");
		sql.append("Td_Product_Bss R3 ");
		sql.append("Where R1.Productid = R2.Productid ");
		if(!ExtString.isEmpty(focus)) {
			sql.append("and R1.phonenum like '%' || ? || '%' ");
			oParam.add(focus);
		}
		sql.append("And R1.Productid = R3.Productid ");
		sql.append("And R1.Citycode = R3.Citycode ");
		sql.append("And R3.State = 'E' ");
		sql.append("And R2.State = 'E' ");
	}