sql 查询优化
文章平均质量分 81
3
优惠券已抵扣
余额抵扣
还需支付
¥39.90
¥99.00
购买须知?
本专栏为图文内容,最终完结不会低于15篇文章。
订阅专栏,享有专栏所有文章阅读权限。
本专栏为虚拟商品,基于网络商品和虚拟商品的性质和特征,专栏一经购买无正当理由不予退款,不支持升级,敬请谅解。
scan724
这个作者很懒,什么都没留下…
展开
-
FILTER的执行次数和驱动表问题
drop table test1;create table test1 as select * from dba_objects where rownum<1000;drop table test2;create table test2 as select * from dba_objects where rownum<1000;insert into test1 select *原创 2014-05-08 13:35:12 · 706 阅读 · 0 评论 -
当分页语句遇到union all
SELECT * FROM (SELECT pubformdat0_.id id332_, pubformdat0_.domain_id domain2_332_, pubformdat0_.process_id process3_332_, pubformdat0_.entity_id entity4_332_,原创 2014-05-09 17:05:57 · 1718 阅读 · 0 评论 -
分区裁剪
explain plan for select /*+leading (T6) use_nl( T1 T6)*/ * from T_PM_ACCT_DTL_AF T1,(SELECT * FROM S_PM_MGR_DEPT_RELA A WHERE DEPT1_CODE <> '999999999原创 2014-04-10 09:05:24 · 825 阅读 · 0 评论 -
分页技术总结
create table page as select * from dba_objects; create index idx_page on page(object_id);create index idx_page_1 on page(owner,object_id);create index idx_page_2 on page(owner);create index idx_p原创 2014-05-11 13:35:00 · 619 阅读 · 0 评论 -
FILTER 执行次数
select count(*) from SAVJ_ATOMJOURBAK where ((list_flag='1' and prt_flag='0') and acct_no notin (select acct_no from savb_basicinfo1 where ((card_no is not null and base_acct_no is null) or (bo原创 2014-04-11 20:35:04 · 774 阅读 · 1 评论 -
再次遇到谓词推入
explain plan for with aa as (select a.agmt_id, sum(c.acct_bal) as card_bal, --借记卡期末存款余额 a.card_open_org, a.OPEN_DATE, -- 发卡日期 a.CARD_NEW_STATUS, --原创 2014-04-28 16:08:19 · 1391 阅读 · 3 评论 -
to_char函数引发的不走索引
SQL> conn cowork_czsh/cowork_czshConnected.SQL> set linesize 200SQL> set pagesize 200SQL> set autot traceSQL> select workitemhi0_.PERFORMER as col_0_0_ from WORKITEM_HISTORY workitemhi0_ where原创 2014-05-20 10:36:46 · 4993 阅读 · 2 评论 -
OR扩展
SQL> select substr(xx.acct_no,1,5) agent_org, xx.vou_kind,sum( xx.trans_amt) trans_amt from ( select * from dwf.F_EVT_SAVD_LIST原创 2014-05-21 17:50:04 · 797 阅读 · 0 评论 -
谓词推入一列
select * from mk_task_info_10032 b, (SELECT s.task_id, s.chn_type, s.contact_flag, s.accept_flag, s.fail_reason,原创 2014-06-04 19:05:16 · 622 阅读 · 0 评论 -
半链接和关联转换
select distinct dept.department_name,emp.employee_idfrom dept_test dept, emp_test emp where dept.department_id = emp.department_id;drop table emp_test;create table emp_test as select * from emp原创 2014-05-22 10:27:28 · 596 阅读 · 0 评论 -
hash join 驱动表和被驱动表不一定是全表扫描
explain plan for SELECT A.* FROM (SELECT /*+ use_hash(cd cv)*/ CD.*, nvl(CV.SUM_CI_BALANCE, 0) as SUM_CI_BALANCE, nvl(CV.SUM_LN_BALANCE, 0) as SUM_LN_BALANCE原创 2014-06-25 09:31:34 · 1753 阅读 · 0 评论 -
又见谓词推入
--------原始SQLselect e.* , aa.* , bb.* , ee.* , dd.* from b_m_sys_branch e left join --购买金额 (select nvl(c.SEC_ORG_ID, '其他') as SEC_ORG_ID,原创 2014-06-25 09:38:27 · 912 阅读 · 1 评论 -
反转索引
Reverse Key IndexesCreating a reverse key index, compared to a standard index, reverses the bytes of each column indexed (except the rowid) while keeping the column order. Such an arrangement can原创 2014-05-22 15:49:23 · 696 阅读 · 0 评论 -
sql改写or 改成union不等价数据变多
select count(*) from (SELECT A.* FROM (SELECT CD.*, nvl(CV.SUM_CI_BALANCE, 0) as SUM_CI_BALANCE, nvl(CV.SUM_LN_BALANCE, 0) as SUM_LN_BALANCE FROM OCRM_F_CI_CU原创 2014-06-25 11:27:41 · 2322 阅读 · 0 评论 -
with as +分析函数优化自链接
select para_id from dwf.f_Savc_Buscode b where b.Para_Id = (SELECT MIN(Para_Id) FROM Dwf.f_Savc_Buscode WHERE Bus_Code = b.Bus_Code);SQL> set linesize 2原创 2014-07-17 09:42:29 · 705 阅读 · 0 评论 -
子查询有OR无法展开,改写成union
SELECT A.* FROM (SELECT CD.*, nvl(CV.SUM_CI_BALANCE, 0) as SUM_CI_BALANCE, nvl(CV.SUM_LN_BALANCE, 0) as SUM_LN_BALANCE FROM OCRM_F_CI_CUST_DESC CD l原创 2014-06-12 22:23:27 · 704 阅读 · 0 评论 -
DBA_TABLES之BLOCKS AND EMPTY_BLOCKS
SQL> select owner,table_name,blocks,EMPTY_BLOCKS from dba_tables where EMPTY_BLOCKS <>0;OWNER TABLE_NAME BLOCKS EMPTY_BLOCKS------------------------原创 2014-07-19 07:27:08 · 859 阅读 · 0 评论 -
FILTER 哪里没展开改哪里
SELECT * FROM F_PTY_INDIV_TMP O WHERE EXISTS (SELECT 1 FROM F_PTY_INDIV F WHERE O.PTY_ID = F.PTY_ID AND O.CORP_ORG = F.CORP_ORG原创 2014-05-26 16:25:36 · 580 阅读 · 0 评论 -
返回10%的数据一定就能走索引?
select count(*) from dwf.f_agt_business_contract_h--1934179select count(*) from dwf.f_agt_business_contract_h t where t.end_dt=date'2999-12-31'--256314select (256314/1934179)*100 from dual;很多原创 2014-07-17 18:15:48 · 559 阅读 · 0 评论 -
单块读和多块读操作汇总
多块读的操作:全表扫描,index fast full scan ,merge,rowid range scan单块读:index unique scan ,index full scan ,index range scan,逻辑读原创 2014-05-27 10:51:59 · 1412 阅读 · 0 评论 -
查看统计信息是否过期
SQL> drop table test purge;Table dropped.SQL> create table test as select * from dba_objects;Table created.SQL> select owner, table_name name, object_type, stale_stats, last_analyzed from db原创 2014-07-01 12:16:20 · 1600 阅读 · 0 评论 -
Oracle 不走索引
explain plan for select * from ( select a.trans_org as org_id, --a.cust_magr, b.emp_name as cust_magr_name, a.cust_na原创 2014-07-01 18:15:18 · 924 阅读 · 0 评论 -
Oracle不等值链接
连接方式 驱动表 PGA 输出结果集 不等值连接 被驱动表扫描次数 嵌套循环 有(靠近关键字) 不消耗 驱动表5%以下 支持 等于驱动表返回行数 哈希连接 有(靠近关键字) 消耗 多 不支持 1 排序合并连接 无 消耗 多原创 2014-07-01 18:11:21 · 1387 阅读 · 0 评论 -
索引 split2
当往一个已经满了的索引块中插入新的索引条目时,将发生索引块的split,在9i下,分两种不同的情况进行split:(1)如果插入的索引键值不是最大的,将发生50-50的split,也就是说有block中一半的索引键值将被移到一个新的block中。就是说热点增长也始终在热点的右边,导致类似右手增长状况。但是由于不在最有边,将按照50:50 Split,导致接近50%的空间浪费。(2)如原创 2014-05-29 10:05:55 · 790 阅读 · 0 评论 -
TABLE ACCESS BY INDEX ROWID前面的星号
SQL> create table test as select * from dba_objects;Table created.select * from test where object_id=10 and OWNER='SYS';SQL> select count(*) from test where object_id=10; COUNT(*)--------原创 2014-07-02 20:54:56 · 1357 阅读 · 0 评论 -
虚拟机测试等值索引扫描成本
SQL> create index test_idx1 on test(owner);BEGINDBMS_STATS.GATHER_TABLE_STATS(ownname => 'TEST',tabname => 'TEST',estimate_percent => 100,method_opt => 'for all columns size 1',degree => DBMS_S原创 2014-07-23 22:37:17 · 667 阅读 · 0 评论 -
select count(*)和select * 区别
explain plan for select * from F_AGT_BUSINESS_CONTRACT_H a where a.end_dt=date'2999-12-31';select * from table(dbms_xplan.display());Plan hash value: 3544262987 -------------------原创 2014-07-24 10:59:49 · 1478 阅读 · 0 评论 -
关于group by
关于group by 排序问题10g 以前sort group by 10g 以后 hash group by原创 2014-05-29 16:57:22 · 690 阅读 · 0 评论 -
虚拟机测试全表扫描成本
BEGINDBMS_STATS.GATHER_TABLE_STATS(ownname => 'TEST',tabname => 'TEST',estimate_percent => 100,method_opt => 'for all columns size 1',degree => DBMS_STATS.AUTO_DEGREE,cascade=>TRUE);END;SQL>原创 2014-07-23 21:51:39 · 518 阅读 · 0 评论 -
半链接改写成关联
SQL> select sum(nvl(GetBusinessZongE('BusinessApprove', BA.SerialNo), 0)) as BusinessSum, sum(nvl(GetBusinessChangKou('BusinessApprove', BA.SerialNo), 0)) as ApproveSum from Business_Approve原创 2014-07-04 10:57:01 · 970 阅读 · 0 评论 -
模拟or 改写union数据变多情形
SQL> select * from test1; CUST_ID FR_ID CI_BALANCE LN_BALANCE MGR_ID---------- ---------- ---------- ---------- ---------- 10 11 0 0 100 10 11 12 12 101 10 40原创 2014-07-04 16:53:09 · 687 阅读 · 0 评论 -
自定义函数引发的性能问题
---执行1次select count(1) from LOAN_DUEBILLDATA bd left join LOAN_CONTRACTDATA bc on bd.RelativeSerialNo2 = bc.SerialNo where nvl(bd.ActualMaturity, bd.Maturity) <= '2014/08/06' and nvl(bd.A原创 2014-07-07 13:04:48 · 609 阅读 · 0 评论 -
DDL引发的对象invalidation
SQL> select * from v$sysstat a where a.STATISTIC#=339;STATISTIC# NAME CLASS VALUE STAT_ID---------- ---------------------------------------------------------------- ---------- -----原创 2014-07-06 19:07:34 · 594 阅读 · 0 评论 -
反连接走NL时无法改变驱动表
explain plan for SELECT BC."SERIALNO", BC."RELATIVESERIALNO", BC."ARTIFICIALNO", BC."OCCURDATE", BC."CUSTOMERID", BC."CUSTOMERNAME", BC."BUSINESSTYPE",原创 2014-07-07 15:48:14 · 863 阅读 · 0 评论 -
数据分布对访问方式的影响
select count(*) from F_EVT_SETR_IBPS_RCVSND XX where "XX"."SYS_DATE">=TO_DATE(' 2014-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "XX"."SYS_DATE"<=TO_DATE(' 2014-03-31 00:00:00',原创 2014-08-01 20:55:47 · 550 阅读 · 0 评论 -
巧妙的用IN替代关联后去从
SQL> explain plan for SELECT DISTINCT d.sender_id, 2 d.misc_notes, 3 TO_CHAR(d.creation_date, :"SYS_B_0") creation_date, 4 d.message 5 FROM lead l, 6 lead_document d 7 WHERE l.原创 2014-07-13 21:51:55 · 731 阅读 · 0 评论 -
利用OWI优化SQL
Plan hash value: 1608863478 ------------------------------------------------------------------------------------------------------------------------------------| Id | Operation原创 2014-08-06 11:21:58 · 658 阅读 · 0 评论 -
为什么主键分区必须加分区键?
为什么主键分区必须加分区键?SQL> alter table F_AGT_BUSINESS_CONTRACT_H add primary key (AGMT_ID, CORP_ORG, AGMT_MOD_CD, START_DT) USING INDEX LOCAL; 此分区表示以end_dt为分区键的如果主键没有加分区键,那么只能 保证每个分区内是唯一的,而无法保证所有分区的数据唯一。原创 2014-07-15 14:28:56 · 3370 阅读 · 2 评论 -
函数套函数优化
explain plan for SELECT date '2014-03-31', b.cust_no 客户编号, b.cust_name 客户名称, b.balance AS 贷款余额, b.changkou 授信敞口金额, (CASE WHEN原创 2014-08-07 16:52:55 · 509 阅读 · 0 评论 -
NL驱动表错误导致的性能问题
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-07 13:48:59 · 648 阅读 · 0 评论