〇〇 发表于 2013-12-9 16:42 static/image/common/back.gif
别怕sql麻烦，关键是少用资源
不是麻烦，是这样开销返回更大，3个变量意味着排序3次，给你看看例子SQL> select ge.v_targ_id,sum(decode(sign(mileage*6400-time*24*250),-1,time,0))*24*3600 as total_time,
round(sum(decode(sign(mileage*6400-time*24*250),-1,mileage,0))*6400,2) as total_mileage
2 3 from ( select v_equp_id,(t2d_data_date - d_data_date) as time,
4 acos(round((sin(n_data_y) * sin(t2n_data_y) +cos(n_data_y) * cos(t2n_data_y) *cos(t2n_data_x - n_data_x)),12)) as mileage
5 from
6 (select ga.v_equp_id,
7 ga.d_data_date,
8 lead(d_data_date,1,d_data_date) over (order by v_equp_id, d_data_date) as t2d_data_date,
9 (ga.n_data_x * 3.14159265358979323846 / 180) as n_data_x,
(lead(n_data_x,1,n_data_x) over (order by d_data_date)) * 3.14159265358979323846 / 180 as t2n_data_x,
10 11 (ga.n_data_y * 3.14159265358979323846 / 180) as n_data_y,
12 (lead(n_data_y,1,n_data_y) over (order byv_equp_id, d_data_date)) * 3.14159265358979323846 / 180 as t2n_data_y
13 from gps_data ga
where ga.d_data_date >= to_date('2013-12-07','yyyy-mm-dd')
14 15 and ga.d_data_date <to_date('2013-12-08','yyyy-mm-dd')) ) t,gps_equp ge
16 where ge.v_equp_id = t.v_equp_id and t.time > 0
17 group by ge.v_targ_id;
191 rows selected.
Elapsed: 00:00:20.50
Execution Plan
----------------------------------------------------------
Plan hash value: 3945074772
------------------------------------------------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 312 | 26520 | |2862 (1)| 00:00:35 | | |
| 1 |HASH GROUP BY | | 312 | 26520 | |2862 (1)| 00:00:35 | | |
|*2 | HASH JOIN | | 312 | 26520 | |2861 (1)| 00:00:35 | | |
| 3 | VIEW | VW_GBC_5 | 330 | 13200 | |2724 (1)| 00:00:33 | | |
| 4 | HASH GROUP BY | | 330 | 21780 | |2724 (1)| 00:00:33 | | |
|*5 | VIEW | | 126K|8157K| |2724 (1)| 00:00:33 | | |
| 6 | WINDOW SORT | | 126K|4326K|5968K|2724 (1)| 00:00:33 | | |
| 7 | WINDOW SORT | | 126K|4326K|5968K|2724 (1)| 00:00:33 | | |
| 8 | PARTITION RANGE SINGLE| | 126K|4326K| | 369 (1)| 00:00:05 | 158 | 158 |
| 9 | TABLE ACCESS FULL | GPS_DATA | 126K|4326K| | 369 (1)| 00:00:05 | 158 | 158 |
|10 | TABLE ACCESS FULL | GPS_EQUP | 22853 |1004K| | 136 (0)| 00:00:02 | | |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("GE"."V_EQUP_ID"="ITEM_1")
5 - filter("T2D_DATA_DATE"-"D_DATA_DATE">0)
Statistics
----------------------------------------------------------
1recursive calls
1db block gets
1824consistent gets
0physical reads
0redo size
12761bytes sent via SQL*Net to client
655bytes received via SQL*Net from client
14SQL*Net roundtrips to/from client
2sorts (memory)
0sorts (disk)
191rows processed
看错了，是变小了
DBAPUB 发表于 2013-12-10 15:29 static/image/common/back.gif
看错了，是变小了
时间缩短了，autotracecost不准，
用select * from table(dbms_xplan.display_cursor('sql_id/hash_value', child_cursor_number, 'advanced'))，要指定SQL_ID和Child Cursor Number。
另外注意，当对并行执行的SQL使用GATHER_PLAN_STATISTICS hint时，FORMAT参数得是'ALLSTATS ALL'，不能是常规的'ALLSTATS LAST'。
本帖最后由 dingjun123 于 2013-12-10 15:44 编辑
貌似你改写后结果集都不一样了啊
竟然有三角函数，oracle应该不擅长这些的吧
本帖最后由 DBAPUB 于 2013-12-10 16:04 编辑
dingjun123 发表于 2013-12-10 15:44 static/image/common/back.gif
貌似你改写后结果集都不一样了啊
结果集是一样的，一个返回设备id，一个是对象id，设备比对象多。所以一个是300多，一个是200。我看的不是cost，是“1824consistent gets”，一致性读
jlandzpa 发表于 2013-12-10 15:53 static/image/common/back.gif
竟然有三角函数，oracle应该不擅长这些的吧
java、c++？
jlandzpa 发表于 2013-12-10 15:53 static/image/common/back.gif
竟然有三角函数，oracle应该不擅长这些的吧
刚看到您的徽章，这么元老的都出来啦。。。。
3.14159265358979323846 / 180
这些位数都对，但干嘛不用反三角函数表示？
pi/180=arctan(1)*4/180=>oracle写法： atan(1)*4/180或asin(1)*2/180也行
用了分析函数lead或lag后，mt就不需要了，t的order by 也不需要了，因为lead/lag自带你所需要的排序
jlandzpa 发表于 2013-12-10 15:53 static/image/common/back.gif
竟然有三角函数，oracle应该不擅长这些的吧
如果用低精度的浮点类型代替number,还是可以提高的
SQL> select sum(cos(cast(level/10 as binary_double)))from dual connect by level<=5e4;
SUM(COS(CAST(LEVEL/10ASBINARY_DOUBLE)))
---------------------------------------
-1.03E+001
已用时间:00: 00: 00.23
SQL> set num 20
SQL> /
SUM(COS(CAST(LEVEL/10ASBINARY_DOUBLE)))
---------------------------------------
-1.029409575842E+001
已用时间:00: 00: 00.21
SQL> select sum(cos(level/10)) from dual connect by level<=5e4;
SUM(COS(LEVEL/10))
--------------------
-10.2940957584186055
已用时间:00: 00: 01.19