sql 查询优化
文章平均质量分 81
3
优惠券已抵扣
余额抵扣
还需支付
¥39.90
¥99.00
购买须知?
本专栏为图文内容,最终完结不会低于15篇文章。
订阅专栏,享有专栏所有文章阅读权限。
本专栏为虚拟商品,基于网络商品和虚拟商品的性质和特征,专栏一经购买无正当理由不予退款,不支持升级,敬请谅解。
scan724
这个作者很懒,什么都没留下…
展开
-
VIEW PUSHED PREDICATE 原理
SELECT *FROM (SELECT DISTINCT e.n_event_id,e.n_parent_id,e.v_event_num,em.n_req_type_1,em.n_req_type_2,em.v_title,em.v_description,e.n_priority,cb.n_time_limit,cb.n_status,e.n_process_way,原创 2014-09-18 11:30:28 · 1562 阅读 · 0 评论 -
正常的谓词推入效率
explain plan for with zz as (select t.br_org_name, t.sd_org_name,原创 2014-08-08 13:11:53 · 699 阅读 · 0 评论 -
利用FILTER特性优化SQL
SELECT A.CARDOFBANK, A.CARD_NO, to_char(A.TRANS_DATE, 'YYYY-MM-DD') , decode(A.TRANS_CODE, '111019', 0, A.TRANS_AMT), A.FEE_AMT ,原创 2014-08-11 09:14:28 · 885 阅读 · 0 评论 -
谓词推入演示
create or replace view v_emp as select emp.empno,emp.ename,emp.deptno from emp,deptwhere emp.deptno=dept.deptno;SQL> select * from t10; ID NAME---------- ---------- 1 a 2 b 3 c 7902原创 2014-09-19 20:24:09 · 637 阅读 · 0 评论 -
查看哪些索引没有被使用
select distinct object_name from (select object_name from dba_hist_sql_plan where object_owner = 'DWF' union all select object_name from v$sql_plan where object_ow原创 2014-08-11 16:24:00 · 820 阅读 · 0 评论 -
分页语句优化
技巧3:分页语句优化分页语句,一般都有order by column desc/asc分页语句的优化技巧:1.分页SQL要想快最好走索引,根据order by asc/desc ,用hint index_asc/index_desc 强制它走索引INDEX FULL SCAN DESCENDING/INDEX RANGE SCAN DESCENDING2.不要让Oracle原创 2014-08-12 16:59:07 · 487 阅读 · 0 评论 -
错误的选择了HASH JOIN!
explain plan for selecta.open_org as 机构号,a.org_name as 机构名,b.cur_cd as 币种,sum(b.acct_bal)/10000 as 余额,count(*) as 户数from( select /*+ leading(y) use_nl(x y)*/ x.open_org , x.agmt_id , x.cust_n原创 2014-08-13 11:01:23 · 607 阅读 · 0 评论 -
分页技术COUNT STOPKEY和SORT ORDER BY
COUNT STOPKEY :条件有rownum的时候出现扫描表,到前n行停止SORT ORDER BY STOPKEYSORT ORDER BY分页语句有SORT ORDER BY STOPKEY和SORT ORDER BY 表示重新对数据进行排序。原创 2014-08-13 13:31:09 · 2026 阅读 · 0 评论 -
FILTER再来一例
explain plan for SELECT a.trans_org as 机构代码FROM (select x.trans_org, x.ext_acct_no, x.acct_seqno, y.agmt_id, x.cust_n原创 2014-08-13 11:37:24 · 502 阅读 · 0 评论 -
分页SQL走全表扫描导致TEMP耗尽
.查看SQL信息:select * from (select a.cert_type, a.cert_no, a.cust_name, e.oper_no, to_char(c.vou_start_no + instr原创 2014-08-13 17:54:16 · 1350 阅读 · 0 评论 -
多表关联的分页SQL经典案例
explain plan for select * from (select a.cert_type, a.cert_no, a.cust_name, e.oper_no, to_char(c.vou_start_no + instr(c.vou_status, '0'原创 2014-08-13 15:59:32 · 2270 阅读 · 0 评论 -
索引访问中的access和filter
explain plan for select * from comr_clerk_vou c where c.vou_type = '11' and c.sub_vou_type = '0' and c.vou_status like '%0%'; select * from table(dbms_xplan.display());原创 2014-08-14 10:14:04 · 994 阅读 · 0 评论 -
分页语句的深入研究
select * from (select a.cert_type, a.cert_no, a.cust_name, e.oper_no, to_char(c.vou_start_no + instr(c.vou_sta原创 2014-08-18 10:34:51 · 505 阅读 · 0 评论 -
hash join驱动表问题
explain plan for select c.oper_no, a.passbook_no, g.acct_no from auto_savb_acct g, auto_savb_acct a, (select * from comc_branch where substr(ctrl_bit,7,1)='0' )b, au原创 2014-09-02 10:05:56 · 884 阅读 · 0 评论 -
索引和NULL值
索引和NULL值create table test10(id int,name char(10))beginfor i in 1 .. 100000loopinsert into test10 values(i,'a'||i);commit;end loop;end;create index test10_idx1 on test10(id);BEGIN DBMS原创 2014-10-24 19:16:17 · 557 阅读 · 0 评论 -
强制让SQL走谓词推入
SELECT * FROM STORESUM WHERE (ORG_ID IN (SELECT EP_LOC.ORG_ID FROM EP_LOC, EP_USER_LOC WHERE EP_LOC.LOC_ID = EP_USER_LOC.LOC_ID AND EP原创 2014-11-11 18:08:32 · 1033 阅读 · 0 评论 -
NULL值会影响SQL查询结果
SQL> delete from test10 where id is null;已删除 1 行。SQL> commit;提交完成。SQL> select * from (select /*+ index_desc(test10 test10_idx1) */ id from test10 where rownum < 2原创 2014-10-24 20:32:38 · 848 阅读 · 0 评论 -
绑定变量值长度不一致,mismatch问题
111原创 2014-11-12 22:14:19 · 972 阅读 · 0 评论 -
经典的标量子查询
explain plan for ( select 'C10927' as YXJGDM, --银行机构代码\ '20110213000003' as JRXKZH, --金融许可证号 a.SIGN_ORG as NBJGH, --内部机构号 case when substr(b.product_name,原创 2014-10-30 16:42:49 · 1461 阅读 · 0 评论 -
利用分析函数改写解析
SQL> SELECT acct_no, trans_amt, set_date, opp_acct_no, dc_flag, seqno, MAX(seqno) over(PARTITION BY acct_no, trans_amt, set_date, opp_acct_no, dc_原创 2014-11-19 10:01:43 · 797 阅读 · 0 评论 -
全局索引 truncate有数据的分区,索引失效,没数据的分区,索引不失效
查看索引状态:SQL> select index_name, index_type, partitioned, status, global_stats from dba_indexes where table_name = 'F_AGT_BUSINESS_CONTRACT_H'; 2 3 4 INDEX_NAME INDEX_TYPE PAR原创 2014-10-21 14:16:37 · 1114 阅读 · 0 评论 -
INDEX FAST FULL SCAN 也无法开并行
SQL> drop table t1 purge ;表已删除。SQL> create table t1 as select * from dba_objects;表已创建。SQL> insert into t1 select * from t1;已创建72665行。SQL> /已创建145330行。SQL> commit;提交完成。SQL> create i原创 2014-11-05 16:57:03 · 709 阅读 · 0 评论 -
index range scan 单块读 无法走并行
SQL> create table t1 as select * from dba_objects;SQL> create index i1_idx on t1(owner);BEGIN DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TEST', tabname原创 2014-11-05 14:27:59 · 668 阅读 · 0 评论 -
分区索引truncate某个分区,分区索引不失效
SQL> select owner,index_name,index_type,partitioned,status from dba_indexes where table_name='TEST1' and owner='DWF';OWNER INDEX_NAME INDEX_TYPE PAR STATUS---------------------原创 2014-10-21 14:21:44 · 1759 阅读 · 0 评论 -
index range scan和index fast full scan
SQL> create index idx_1 on t1(object_id);Index created.SQL> set linesie 200SP2-0158: unknown SET option "linesie"SQL> select max(object_id) from t1;MAX(OBJECT_ID)-------------- 175920SQL>原创 2014-11-07 08:38:45 · 1167 阅读 · 0 评论 -
With as 必须跟select
update b set b.object_name=(select a.object_name from a where a.object_id=b.object_id);create table a as (select * from (select * from dba_objects order by object_id) where rownum<100)create tabl原创 2014-12-26 10:44:37 · 2426 阅读 · 0 评论 -
11g 搜集直方图导致不走索引
SELECT messagein0_.proccnt AS col_0_0_ FROM mbfe.messagein messagein0_ WHERE messagein0_.msgID='ID:414d5120514d453333313020202020204c224a3c200c49cb';1条记录SQL_ID c1pvjs5wx132x, CHILD NUMBER 0----原创 2015-02-04 19:33:51 · 678 阅读 · 0 评论 -
搜集直方图repeat和skewonly
SQL> set linesize 200SQL> select a.column_name, b.num_rows, a.num_distinct Cardinality, round(a.num_distinct / b.num_rows * 100, 2) selectiv原创 2015-02-12 09:43:59 · 942 阅读 · 0 评论 -
全关联优化
explain plan for select count(*) from (SELECT 20141001 AS data_date ,NVL(T1.ACCT_NO, T2.ACCT_NO) AS acct_no ,NVL(T1.ACCT_ORD, T2.ACCT_ORD) AS acct_ord原创 2015-02-28 12:41:12 · 958 阅读 · 0 评论 -
分页语句 where 条件列和Order by 列 引导列的问题
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------原创 2015-03-10 12:24:38 · 1071 阅读 · 0 评论 -
组合索引适用的情况
假设一个表有下面的规则:CREATE TABLE test ( id INT NOT NULL, last_name CHAR(30) NOT NULL, first_name CHAR(30) NOT NULL, PRIMARY KEY (id), INDEX name (last_name,first_name) );name原创 2015-08-25 17:58:19 · 878 阅读 · 0 评论 -
被驱动表 拼接列无法走索引
create table t100(id int,name varchar(10));create table t200(id int,name varchar(10));begin for i in 1..10 loop insert into t100 values(i,i||'a'); end loop; end;begin原创 2015-11-14 14:42:28 · 1029 阅读 · 0 评论 -
10g中注意子查询里主表谓词过滤的位置
在10g中当主查询的谓词信息,被错误的放入子查询中,会导致子查询无法展开explain plan for UPDATE DWF.F_PTY_INDIV O SET END_DT = TO_DATE('2012-12-31', 'YYYY-MM-DD') WHERE EXISTS (SELECT 1 FROM DWF.F_PTY_INDIV F WH原创 2014-05-26 08:53:11 · 636 阅读 · 0 评论 -
FILTER NESTLOOP 中驱动表问题
FILTER 内连接 不能修改驱动表NEST LOOP 内连接可以修改驱动表,外连接不行 HASH 可以原创 2015-11-14 14:06:58 · 1145 阅读 · 0 评论 -
排序过程
如果排序需要的内存大于sort_area_size, 那么ORACLE就会把排序操作分成2个块, 典型的ONE-PASS Sort run 1第一块排序 在内存里排序 排完后把排序结果放到TEMP表空间 Sort run 2 先临时放到temp segment里,等到第一块排序完后,把第一块排序的结果放到临时表空间,在把第2块读到排序区进行排序,排序结果放到temp原创 2014-03-08 18:26:50 · 691 阅读 · 0 评论 -
创建本地索引和全局索引
LOCALITY:表明分区索引是LOCAL的还是GLOBAL的。--创建分区索引:create index IDX_MGR_BASE_MGR on T_PM_MGR_BASE (MGR_CODE) LOCAL parallel 16 nologging ;SQL> select * from T_PM_MGR_BASE where data_date=20131012 and原创 2013-12-10 12:55:46 · 2118 阅读 · 0 评论 -
分区表 主键全局索引和分区索引区别
SQL> create table pdba (agmt_id number,corp_org char(10),agmt_mod_cd char(10),a1 number,a2 number,a3 number,a4 number, start_date date) partition by range (start_date )(partition p1 values less than原创 2017-01-02 20:07:23 · 3949 阅读 · 0 评论 -
SORT ORDER BY STOPKEY
select * from ( select rownum as rn ,a.* from ( select * from t100 a order by object_id desc )a where rownum=1SQL> SQL> select count(*) from t100; COUNT(*)---------- 3020896 BE原创 2017-01-13 19:11:46 · 1573 阅读 · 1 评论 -
分页SQL创建索引规则
SQL> select * from dba_indexes where table_name='T100' and owner='SYSTEM';SQL> 未选定行SQL> select * from (select rownum as rn, a.* from (select * from t100 a原创 2017-01-14 16:48:56 · 459 阅读 · 0 评论 -
分页语句创建索引技巧
SQL> select * from (select rownum as rn, a.* from (select * from t100 a where object_id > 1500 and owner = 'SYSTEM'原创 2017-01-14 18:41:37 · 891 阅读 · 0 评论