Oracle 优化
zhangxiaobo5152
追求 常清静
展开
-
in 里的小表做驱动表
select /*+ use_nl(T@SEL$2,T@SEL$1) leading(T@SEL$2) */ code,name,idcode,address,room,indate from scotte.T_S_ROOM t where code in (select code from scotte.T_S_ROOM t where name='张三' and indate ='1转载 2015-12-24 16:57:50 · 433 阅读 · 0 评论 -
hint 索引示例
hint index(table_name index_name)SQL调优 对比索引和非索引扫描hint index_ffs(table_name index_name)常用于统计索引列键值的个数,如count(object_id),跟全表扫描很像,但效率要比全表扫描要高很多,FAST FULL SCNhint index_ss(table_name index_na转载 2016-01-12 15:38:04 · 601 阅读 · 0 评论 -
索引的升序 降序
/*+ INDEX_ASC(TABLE INDEX_NAME) */表明对表选择索引升序的扫描方法.SELECT /*+INDEX_ASC(EMPMS PK_EMPMS) */ * FROM EMPMS WHERE DPT_NO='SCOTT';/*+ INDEX_DESC(TABLE INDEX_NAME) */表明对表选择索引降序的扫描方法.SELECT /*+IN转载 2016-01-12 16:55:59 · 5789 阅读 · 0 评论 -
怎么在大表中找到null的几条数据?
我们假设temp_zhangxb_2015110501a 是千万级的大表,而cust_id中有几个null值,且cust_id 中不会存在'1'的值,那么我们这么做:create index ind_custid2 on temp_zhangxb_2015110501a(nvl(cust_id,'1'));select /*+ index(t ind_custid2)*/* from原创 2016-01-13 10:06:10 · 320 阅读 · 0 评论 -
重复记录的处理
方案1delete from temp_zhangxb_2015110501a a where a.cust_id in (select a.cust_id from temp_zhangxb_2015110501a a group by a.cust_id having count(cust_id) > 1) and rowid not in (select min(rowid) from原创 2016-01-13 10:39:45 · 249 阅读 · 0 评论 -
sql输出执行计划范例
explain plan forselect count(*) from temp_zhangxb_2015110501a a where a.cust_id is null;select * from table(dbms_xplan.display());原创 2016-01-13 17:31:16 · 324 阅读 · 0 评论