sql 查询优化
文章平均质量分 81
3
优惠券已抵扣
余额抵扣
还需支付
¥39.90
¥99.00
购买须知?
本专栏为图文内容,最终完结不会低于15篇文章。
订阅专栏,享有专栏所有文章阅读权限。
本专栏为虚拟商品,基于网络商品和虚拟商品的性质和特征,专栏一经购买无正当理由不予退款,不支持升级,敬请谅解。
scan724
这个作者很懒,什么都没留下…
展开
-
filter 优化
SELECT COUNT(*) FROM T18_TASKLIST t1 WHERE TASKTYPE || '-' || BUSINESSKEY || '-' || GRANULARITY NOT IN (SELECT TASKTYPE || '-' || BUSINESSKEY || '-' || GRANULARITY FROM T18_TASK_FA原创 2017-01-16 08:40:18 · 171 阅读 · 0 评论 -
with as 干掉谓词推入
SQL> explain plan for select count(1) 2 from v48_transaction_model m where 1 = 1 and m.trandate = date'2017-01-15' and 1 > 0 /*and m.dsacctname like :3*/ and 1 > 0 and 1 > 0 a原创 2017-01-16 14:10:03 · 462 阅读 · 0 评论 -
分区表 全局索引和本地索引 区别
SQL> create user test100 identified by test100 ;User created.SQL> grant dba to test100;Grant succeeded.ALL_IND_PARTITIONS 描述,对于每个index 分区访问到当前用户, 分区级别分区信息, 分区的存储参数相关的视图:DBA_IND_PARTITIO原创 2017-01-17 09:40:42 · 6874 阅读 · 0 评论 -
sql 字符串 数值型不走索引
SQL_ID 99tfs2tpapwqk, child number 0-------------------------------------select count(*) num from ( select a.*, b.client_name client_name2, a.err_msg err_msg2 from tbtranscfm a inner join tbclie原创 2017-03-07 16:28:08 · 984 阅读 · 0 评论 -
优化 集群因子概念
一. 官网说明 The index clustering factor measures row order in relation to an indexed value suchas employee last name. The more order that exists in rowstorage for this value, the lower t原创 2017-05-02 15:21:21 · 417 阅读 · 0 评论 -
集群因子 例子
测试:create table t1 as select trunc((rownum-1)/100) id, rpad(rownum,100) t_pad from dba_source where rownum<100000;create index t1_idx1 on t1(id);原创 2017-05-02 16:09:33 · 559 阅读 · 0 评论 -
彻底理解谓词推入的利弊
谓词推入(PUSHING PREDICATE):create or replace view vtest as select * from test1 where object_id>1000;SQL> explain plan for select /*+use_nl(test2 vtest) no_merge(vtest)*/ * from test2,vtestwhere test原创 2017-05-03 18:05:10 · 964 阅读 · 0 评论 -
分页语句模板
分页语句方法:1.rownum 2.row_number over(order by column) as 行号例子:create table page as select * from dba_objects;create index idx_page_1 on page(object_id,owner);BEGIN DBMS_STATS.GATHER_TABLE_STAT原创 2017-05-12 09:01:26 · 98 阅读 · 0 评论 -
利用组合索引优化
select /*+ parallel(d 16)*/ count(*) from t47_transaction_uh d --174027378 select /*+ parallel(d 16)*/ count(*) from t47_transaction_uh d where d.TX_DT BETWEEN DATE '2015-10-01' AND D原创 2017-05-25 11:34:02 · 435 阅读 · 0 评论 -
char和varchar2对比
-- Create tablecreate table TLCB_HARDWARE_INFO( sdate DATE, ip CHAR(20), operating_system VARCHAR2(30), operatingsystemrelease CHAR(10), hostname原创 2017-05-31 15:14:57 · 622 阅读 · 0 评论 -
Oracle 分区索引
-- Create tablecreate table TLCB_MON_LINUX( sdate DATE, ip VARCHAR2(20), processcpu CLOB, processmem CLOB, port CLOB, countprocess VARCHAR2(100), countport原创 2017-08-09 16:03:15 · 312 阅读 · 0 评论 -
update 嵌套优化
update ICR_BASEINFO IB set IB.a7109='4', IB.a4109=(select a4109 from icr_baseinfobak ibk where a7101=IB.a7101 and sessionid=(原创 2017-09-18 11:30:36 · 576 阅读 · 0 评论 -
sql 绑定变量问题
SQL> create table T_BUSINESS_DATA(user_id number(16),status varchar2(2),oper_date date);select * from T_BUSINESS_DATA t where 1=1 and t.APP_USER_ID=:userId and t.STATUS <> 1 order by t.operDate desc原创 2017-09-22 15:50:18 · 452 阅读 · 0 评论 -
FILTER 优化
PLAN_TABLE_OUTPUTPlan hash value: 2272901224 ------------------------------------------------------------------------------------------------| Id | Operation | Name原创 2017-12-08 10:06:27 · 295 阅读 · 0 评论 -
oracle range 分区访问
explain plan for select * from esb2_trans_log twhere t.trans_date >= to_date('2018-06-07 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and t.trans_date <= to_date('2018-06-07 23:59:59',...原创 2018-06-09 10:20:36 · 298 阅读 · 0 评论 -
where t.trans_date=date‘2018-06-07‘; 等价于 (“T“.“TRANS_DATE“=TO_DATE(‘ 2018-06-07 00:00:00‘, ‘syyyy-m
explain plan for select * from esb2_trans_log twhere t.trans_date >= to_date('2018-06-07 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and t.trans_date <= to_date('2018-06-07 23:59:59',...原创 2018-06-09 10:25:07 · 1390 阅读 · 0 评论 -
oracle 收集直方图不走索引
1 Plan hash value: 11388119812 3 ----------------------------------------------------------------------------------------------4 | Id | Operation | Name | Rows |...原创 2018-08-10 16:53:32 · 555 阅读 · 0 评论 -
sql当hints index fast full scan走不了 会走index range scan的情况
explain plan for select /*+INDEX_FFS(a t100_idx1)*/ owner from t100 a where a.owner='TLCBUSER';select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUTPlan hash value: 4259608525 -------...原创 2018-09-12 16:44:55 · 367 阅读 · 0 评论 -
index fast full scan
-- Create tablecreate table TEST( farendma VARCHAR2(4) default '' not null, mingxibh VARCHAR2(32) default '' not null, dkzhangh VARCHAR2(32) default '' not null, dkjiejuh VARCHAR2(35) defau...原创 2018-09-13 10:15:54 · 239 阅读 · 1 评论 -
sql加了hints sql_id就发生变化
v$sqlv$sql 列出统计信息在shared SQL areas 没有GROUP BY 字句,每行包含每个原始SQL文本的每个child v$sql 显示的统计信息是通常更新在查询执行尾端。v$sqlarea:v$sqlarea v$sqlarea 显示统计信息在恭喜SQL区域,每个SQL语句 一行测试1:先清空shared pool:SQL> ...原创 2018-09-13 18:25:54 · 308 阅读 · 0 评论 -
oracle 执行计划改变
1.sqlSQL> set linesize 200SQL> set pagesize 200SQL> select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx where dkjiejuh = '20151020000935' and farendma = '9999' ...原创 2018-09-14 17:29:15 · 1747 阅读 · 0 评论 -
python 传入类型错误 导致走index fast full scan
1.select esbflowno from esb2_trans_log where esbserviceflowno=:esbserviceflownoselect sql_id, sql_text, version_count, loads, hash_value, address, plan_has...原创 2018-09-20 09:28:03 · 236 阅读 · 0 评论 -
根据sql_id 查询执行计划
--根据sql_id 查看执行计划SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('SQL_ID',SQL_CHILD_NUMBER));select sql_id, sql_text, version_count, loads, hash_value, address, ...原创 2019-04-12 12:56:41 · 3814 阅读 · 0 评论 -
集群因子概念
SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) from test where object_id<90;COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))--------------------------------------------...原创 2019-05-05 14:33:21 · 244 阅读 · 0 评论 -
绑定变量窥探
绑定变量窥视cbo的一个附属功能,关闭他不会影响是用cbo还是rbo,就算关闭了绑定变量窥视,cbo还是会利用别的统计信息(num_distinct,density等)来评估cost和cardinality,只 是无法使用直方图信息而已: SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ FROM...原创 2019-05-05 16:21:51 · 192 阅读 · 0 评论 -
开启和关闭绑定变量窥探
绑定变量窥视cbo的一个附属功能,关闭他不会影响是用cbo还是rbo,就算关闭了绑定变量窥视,cbo还是会利用别的统计信息(num_distinct,density等)来评估cost和cardinality,只 是无法使用直方图信息而已: SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ FROM...原创 2019-05-14 12:47:54 · 648 阅读 · 0 评论 -
oracle 全表扫描返回无序
drop table t4;create table t4 as select * from t3 order by id asc;SQL> update t4 set id=666 where id=6;已更新 1 行。SQL> commit;提交完成。SQL> select * from t4; ID A1 A2---------- ...原创 2019-05-21 10:03:44 · 248 阅读 · 0 评论 -
搜集统计信息
为了讲解直方图,我收集统计信息的时候是 method_opt => 'for all columns size skewonly'正式的生产环境中,最好别用allcolumns方式收集直方图,因为all columns 几乎会对所有列都收集直方图信息method_opt => 'for all columns size skewonly'drop table p500 pu...原创 2019-05-16 09:33:43 · 300 阅读 · 0 评论 -
执行计划多版本查看
-- Create/Recreate indexes create unique index KLNL_DKKHMX_IDX1 on KLNL_DKKHMX (MINGXIBH, DKJIEJUH, FARENDMA) tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64...原创 2019-05-16 13:08:48 · 215 阅读 · 0 评论 -
嵌套循环支持等值和非等值连接
驱动表,外部表被驱动表 内部表 select /*+ LEADING(e) use_nl(e,d)*/ e.ename,e.job,d.dname from emp e,dept d where e.deptno=d.deptno and e.sal<2000;select e.*,d.* from emp e, dept d where e.deptno &l...原创 2019-05-30 11:11:30 · 195 阅读 · 0 评论 -
hash join 只支持等值连接
alter session set statistics_level=all; select /*+ use_hash(emp,dept) */ ename,job,sal ,dname,loc from emp,dept where emp.deptno=dept.deptno;SQL> select /*+ use_hash(emp,dept) */ ename,job,s...原创 2019-05-30 11:14:10 · 546 阅读 · 0 评论 -
什么情况下走sort merge join
SQL_ID cqsz37256v36j, child number 1-------------------------------------INSERT /*+append*/ INTO TMP_ACCT_AF NOLOGGING ( DATA_DATE , ACCT_NO , ACCT_ORD , ACCT_NO_PK , ACCT_BAL , D_CMP_BAL ,M_CMP_...原创 2019-05-30 14:10:30 · 410 阅读 · 0 评论 -
嵌套循环内连接 可以控制驱动表
嵌套循环 改变驱动表: select /*+ LEADING(e) use_nl(e,d)*/ e.ename,e.job,d.dname from emp e,dept d where e.deptno=d.deptno SQL> explain plan for select /*+ LEADING(e) use_nl(e,d)*/ e.ename,e.job,d.dn...原创 2019-05-31 10:25:42 · 324 阅读 · 0 评论 -
嵌套循环外连接 无法指定驱动表
嵌套循环 外连接改变驱动表: select /*+ LEADING(e) use_nl(e,d)*/ e.ename,e.job,d.dname from emp e left join dept d on e.deptno=d.deptno SQL> explain plan for select /*+ LEADING(e) use_nl(e,d)*/ e.enam...原创 2019-05-31 10:28:31 · 239 阅读 · 0 评论 -
hash join 内连接可以指定驱动表
hash join 内连接改变驱动表: select /*+ swap_join_inputs(e) use_hash(e,d)*/ e.ename,e.job,d.dname from emp e,dept d where e.deptno=d.deptno SQL> explain plan for select /*+ swap_join_inputs(e) use_...原创 2019-05-31 10:37:21 · 396 阅读 · 0 评论 -
hash join外连接可以指定驱动表
hash join 外连接改变驱动表: select /*+ swap_join_inputs(e) use_hash(e,d)*/ e.ename,e.job,d.dname from emp e left join dept d on e.deptno=d.deptno SQL> explain plan for select /*+ swap_join_inputs(...原创 2019-05-31 10:39:25 · 446 阅读 · 0 评论 -
SQL优化之FILTER
select count(1) over() as tcnt, rownum as rn, O.CustomerID, O.CustomerName, O.CustomerType, O.CertType, O.CertID, O.MFCustomerID, O.MCCustomer...原创 2019-06-26 16:44:22 · 2328 阅读 · 1 评论 -
子查询非嵌套(subquery unnesting) FILTER
SQL> set linesize 200SQL> set pagesize 200SQL> explain plan for select * from test1 where owner='SCOTT' or object_id in (select object_id from test2 where owner='SCOTT');已解释。SQL> se...原创 2019-07-02 19:46:35 · 337 阅读 · 0 评论 -
阻止子查询进行 Subquery Unnesting
hint NO_UNNEST 可以禁止CBO 进行 Subquery Unnesting SQL> set linesize 200SQL> set pagesize 200SQL> explain plan for select * from test1 where owner='SCOTT' and object_id in(select objec...原创 2019-07-03 08:57:27 · 151 阅读 · 0 评论 -
alter session set _unnest_subquery=false;
alter session set _unnest_subquery=false;不让它进行子查询改写select count(*) from test o where object_id>(select avg(object_id) from test i where i.object_type=o.object_type);SQL> set linesize 200...原创 2019-07-04 09:28:09 · 419 阅读 · 0 评论