改写优化
文章平均质量分 70
jgmydsai
道森oracle讲师,课程:sql高级查询改写
展开
-
用列转行改写多次关联
网友发来语句(如下),问语句有什么问题。这个语句本身没什么问题,关联多次是为了取不同列对应的值。SQL> explain plan for 2 SELECT gcc.segment1, 3 ffv1.DESCRIPTION, 4 gcc.segment3, 5 ffv3.DESCRIPTION, 6 gcc.se原创 2013-12-26 12:51:14 · 1217 阅读 · 0 评论 -
返回最大值所在行的几种方式比较
本例用dba_objects建表,因表大小不一样,与你的结果可能也不一样,如要参考时需要实测建立测试表如下SQL> create table test1 as select * from dba_objects;Table createdSQL> create index idx_test1 on test1(owner,object_name);Index created原创 2014-01-16 14:57:31 · 3208 阅读 · 0 评论 -
用分析函数去掉标量子查询
原语句如下(里面字段及表名称替换过的)SELECT A.CODE AS CODE, A.M_CODE AS M_CODE, A.STKTYPE AS F_STYPE, A.E_YEAR AS E_YEAR, B.SNAME AS SNAME, A.C_DATE AS C_DATE, TO_CHAR(SYSDAT原创 2014-03-04 13:55:13 · 1445 阅读 · 0 评论 -
left join 变 inner join
建一新表我们来看下SQL> EXPLAIN PLAN FOR select * from l left join r on l.v=r.v inner join l t3 on r.v=t3.v;ExplainedExecuted in 0.016 secondsSQL> select * from table(dbms_xplan.display);PLAN_TABLE原创 2014-05-28 21:28:58 · 2571 阅读 · 0 评论 -
update 改为 merge
有个同学问过不看执行能不能优化,有些语句确实不需要看plan原创 2014-07-11 16:43:32 · 1714 阅读 · 0 评论 -
分页语句的快与慢
今天又有人在问分页语句的问题:为什么最后的分页会慢。这儿我们通过执行计划来分析一下,首先建立测试用表。drop table test1 purge;create table test1 as select * from dba_objects;SQL> select count(*) from test1; COUNT(*)---------- 87028原创 2013-12-24 15:05:11 · 1412 阅读 · 0 评论 -
COUNT STOPKEY 及分页方式比较
先看下planselect * from table(dbms_xplan.display_cursor(sql_id =>null,cursor_child_no =>0,format =>'ALL ALLSTATS LAST NOTE ADVANCED -projection'));PLAN_TABLE_OUTPUT----------------------------------原创 2013-11-27 16:48:16 · 2228 阅读 · 0 评论 -
一个简单的优化改写
网友 ^… 10:53:48 SELECT COUNT(1) FROM S WHERE S.CLASS_ID IN (210, 310, 510) AND NOT EXISTS (SELECT c1 FROM tmp WHERE c1 = S.s1 OR c1原创 2014-08-04 11:12:11 · 837 阅读 · 0 评论 -
枚举上月的日期
有学生提这个需求,这个太好实现了,肯定是没好好听课SELECT last_m + (LEVEL - 1) AS d FROM (SELECT add_months(trunc(SYSDATE, 'mm'), -1) last_m, trunc(SYSDATE, 'mm') m FROM dual)CONNECT BY LEVEL <= m原创 2014-10-15 17:26:53 · 888 阅读 · 0 评论 -
用bypass_ujvc改驱动表
有一群友需要改update语句中的驱动表UPDATE p SET gflag = 1 WHERE EXISTS (SELECT 1 FROM t WHERE t.region = :b2 AND t.cycle = :b1 AND t.acctid = p.acctid)因为t表返回数据较少,所以他想原创 2014-12-02 17:50:48 · 1113 阅读 · 0 评论 -
批量insert示例
DROP TABLE test1 PURGE/DROP TABLE test2 PURGE/CREATE TABLE test1 AS SELECT * FROM Dba_Objects/CREATE TABLE test2 AS SELECT * FROM test1 WHERE 1=2/DECLARE v_limit INT := 10000; v_原创 2013-12-20 12:21:50 · 1050 阅读 · 0 评论 -
function在查询语句里的运行次数
SQL> SELECT pkg_test.get_id AS a, pkg_test.get_id AS b, pkg_test.get_id AS c from dual; A B C---------- ---------- ---------- 1 2 31 row selected原创 2014-02-25 15:28:16 · 1550 阅读 · 0 评论 -
Oracle查询优化改写技巧与案例上架了
应邀写了本sql改写的书,终于上架了,怀着忐忑的心情祝贺下。http://item.jd.com/11587404.html原创 2014-12-09 13:42:07 · 3717 阅读 · 8 评论 -
用分析函数优化标量子查询
以下语句表名及部分字段作过替换处理SELECT ii.*, CASE WHEN (SELECT COUNT(1) FROM ii ii WHERE ii.id > 0 AND ii.itemtranflag = 2原创 2013-11-12 14:48:29 · 1504 阅读 · 0 评论 -
一个通过dblink查询的优化案例(去掉filter)
原语句如下EXPLAIN PLAN FOR SELECT a.*, b.rkids FROM (SELECT gys.khbh, gys.khmc, wz.wzzbm, wz.wzmbm, wz.wzmc, wz.wzgg,原创 2013-11-19 16:46:37 · 3067 阅读 · 0 评论 -
行转列的变种
有群友提出以下问题,如下表所示,每个job显示一行,同类job的姓名分列显示,没有的显示为空。JOB N1 N2 N3 N4--------- ---------- ---------- ---------- ----------ANALYST SCOTT FORD CLERK原创 2013-11-18 10:24:23 · 721 阅读 · 0 评论 -
用正则表达式提取clob里的文本格式记录集
表结构如下SQL> desc test;Name Type Nullable Default Comments ---- ---- -------- ------- -------- C1 CLOB Y SQL> select count(*) from test; COUNT(*)---------- 1字段中内容为SU.SYSTEM_USER_原创 2013-11-19 11:12:21 · 1550 阅读 · 0 评论 -
分页语句去掉distinct由hash改为nl
/*我们知道当数据量大时,如果进行分页查询,plan最好走nl。该环境是根据原sql模拟的案例.因为构建的环境数据量不大,在执行速度上看不出来,大家只看plan就可以了*/DROP TABLE t_objects PURGE;DROP TABLE t_columns PURGE;DROP TABLE t_tables PURGE;DROP TABLE t_users原创 2013-11-07 11:32:59 · 1159 阅读 · 0 评论 -
一次分析函数的应用
下面是一个网友的问题问:表test(yyyymmdd,price)按yyyymmdd--yyyymmdd+3,sum(price)做统计,sql怎么写?例如:20131101--20131103 10020131102--20131104 10520131103--20131105 95于是生成环境及语句如下:SQL> DROP TABLE TEST原创 2013-11-07 20:09:57 · 933 阅读 · 0 评论 -
用with改写优化sql
网友发来一案例,sql如下,表名及部分字段名作了处理,这个sql要跑20分钟。WITH SAWITH0 AS (select distinct T_A.T_A_SHORT_NAME as c1, cast(substr(T_B.CAL_MONTH_NAME, - (2)) as INTEGER) as c2, T_B原创 2013-11-09 11:22:55 · 1329 阅读 · 0 评论 -
用with改写优化sql之二
这是另一个网友发的sql,这个有点儿坑SYS@netdb1(netdb61)>select l_target.*, rownum as rn 2 FROM (select l0.l0_id as l0Id, 3 l0.date1 as createDate, 4原创 2013-11-09 11:51:18 · 1173 阅读 · 0 评论 -
Exists如些多焦
看下面的sql,头晕不。居然有如此多的exists,而且是关联的同一个表。SELECT COUNT(1) num FROM (SELECT t1.* FROM t t1 WHERE 1 = 1 AND t1.type = 0 AND (t1.status IN (1, 10, 11, 12, 100原创 2013-11-12 15:55:08 · 1100 阅读 · 0 评论 -
烦人的标量加rownum = 1
根据sql构建表如下DROP TABLE r;CREATE TABLE r AS SELECT 1 AS singerid,1 AS songid FROM dual;DROP TABLE s1;CREATE TABLE s1 AS SELECT 1 AS areaid,'1' AS singername,1 AS singerid,1 AS songid FROM dual;D原创 2013-11-12 16:01:34 · 1673 阅读 · 0 评论 -
not in、not exists 及用join改写
/*我们接着用上次建的两个表。数据如下*/SQL> select * from l;STR V------ -left_1 1left_2 2left_3 3left_4 4 SQL> select * from r;STR V------- -right_3 3right_4 4right_5 5right_6 6 /原创 2013-11-13 10:59:29 · 5335 阅读 · 0 评论 -
坑人的sql(MERGE改写UPDATE)
原sql如下,是一个procedureBEGIN UPDATE A SET A.NCHAR1 = (SELECT DISTINCT B.NUM1 FROM B WHERE B.COL6 = A.COL2 AND A.NCHAR2 = B.COL7) WHERE TRIM(A.NCH原创 2013-11-09 15:09:48 · 1386 阅读 · 0 评论 -
一次关于分页语句的改写优化
一网友在群内发来语句(语句内容已做处理)SELECT * FROM (SELECT /*这儿有d0的30多列*/ d0.* FROM d0 d0 WHERE d0.document_id IN (SELECT d1.document_id FROM d0原创 2013-11-21 09:57:20 · 1421 阅读 · 0 评论 -
UPDATE、ROW_NUMBER 与 MERGE
环境如下,建一测试表CREATE TABLE emp2 AS SELECT * FROM emp;alter table emp2 add constraints pk_emp primary key(empno);有人想达到如下目的/*UPDATE emp2 SET empno = 201311070000000000 + ROW_NUMBER() OVER(ORDER BY原创 2013-11-07 17:39:12 · 1335 阅读 · 0 评论 -
12C标量子查询中的BUG
在12C中,cbo会自动对标量子查询改写了:create table test as select * frm dba_objects;create table test3 as select * from test;select (select max(DATA_OBJECT_ID) from test b where b.object_id=a.object_id ) from t原创 2015-08-21 19:35:06 · 1208 阅读 · 0 评论