1.找出两个表中的差异记录，比如：
select hx_stock_label.code,
hx_stock_label.affect,
hx_stock_label.label_name,
hx_stock_label.label_type,
hx_stock_label.specification
from hx_stock_label
minus
select zs_stock_label.code,
zs_stock_label.affect,
zs_stock_label.label_name,
zs_stock_label.label_type,
zs_stock_label.specification
from zs_stock_label;
2. 查询语句的执行计划
explain plan for
select * from zs_min_plate_0 where plate_id=19522290 and publish_at_ts between 1568681444 and 1568683444;
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2543244395
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 70 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| ZS_MIN_PLATE_0 | 1 | 70 | 7 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | ZS_PUBLISH_AT_TS_0 | 5 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("PLATE_ID"=19522290)
2 - access("PUBLISH_AT_TS">=1568681444 AND "PUBLISH_AT_TS"<=1568683444)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- 1 Sql Plan Directive used for this statement
其他示例：
explain plan for
select * from zs_min_plate_0 where plate_id=19522290
and publish_at in ('2019-09-17 14:59:00','2019-09-17 15:00:00');
select * from table(dbms_xplan.display);
Plan hash value: 3840775950
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 280 | 6 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| ZS_MIN_PLATE_0 | 4 | 280 | 6 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | UK_PXUIK5E3ORKFX81UQ29H8SOTN | 2 | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("PLATE_ID"=19522290 AND ("PUBLISH_AT"=TO_DATE(' 2019-09-17 14:59:00', 'syyyy-mm-dd
hh24:mi:ss') OR "PUBLISH_AT"=TO_DATE(' 2019-09-17 15:00:00', 'syyyy-mm-dd hh24:mi:ss')))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- 1 Sql Plan Directive used for this statement
explain plan for
select * from ( select zs.time_stamp as col_0_0_
from zs_large_stocks zs
order by zs.id desc ) where rownum <= 1;
select * from table(dbms_xplan.display);
Plan hash value: 3321757206
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 1 | 13 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| ZS_LARGE_STOCKS | 1430 | 14300 | 3 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN DESCENDING| SYS_C0015757 | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=1)
网友评论