一个跑步出来的sql
select aa.cashier_shop_no,aa.merch_id,count(aa.card_no) card_no_num from(select /*+use_hash(a,b,c)*/ b.cashier_shop_no, b.merch_id,c.card_no,sum((case when c.p_code='7686' or c.p_code='7646' then 0- case when c.txn_amt >c.earning_amt then trunc(c.txn_amt,1) else trunc(c.earning_amt,1) end elsecase when c.txn_amt >c.earning_amt then trunc(c.txn_amt,1) else trunc(c.earning_amt,1) end end)) txn_amtfrom tb_bill_test a ,tb_merch b,tb_trans cwhere a.nbr_group='15' and a.cust_id=b.cashier_shop_noand b.merch_id=c.merch_id and c.txn_date between '20150901' and '20150930'and c.p_code in ('7687','7647','7686','7646')group by b.cashier_shop_no, b.merch_id,c.card_nohaving sum((case when c.p_code='7686' or c.p_code='7646' then 0- case when c.txn_amt >c.earning_amt then trunc(c.txn_amt,1) else trunc(c.earning_amt,1) end elsecase when c.txn_amt >c.earning_amt then trunc(c.txn_amt,1) else trunc(c.earning_amt,1) end end))>20) aagroup by aa.cashier_shop_no,aa.merch_id;
with ... as /*+ */ a,b 先关联
TXN_DATE, MERCH_ID 做的 global 索引
要知道 加了 hint 为啥 不走 hint ,看10053原始文件
二:执行计划,怎么阅读执行计划
看到rows就行,
clob 是一行一行执行的,
全自动化,查看表大小。
先
col segment_name format a30
col segment_type format a30
select owner, segment_name,segment_type, sum(bytes / 1024 / 1024) "Size(Mb)"
from dba_segments
where owner in (select /*+ no_unnest */ object_owner from plan_table)
and segment_name in (select /*+ no_unnest */ object_name from plan_table)
group by owner,segment_type, segment_name
UNION
----table in the index
select owner, '*'||segment_name ,segment_type, sum(bytes / 1024 / 1024) "Size(Mb)"
from dba_segments
where owner in (select table_owner
from dba_indexes
where owner in (select /*+ no_unnest */ object_owner from plan_table)
and index_name in (select /*+ no_unnest */ object_name from plan_table))
and segment_name in (select /*+ no_unnest */ table_name
from dba_indexes
where owner in (select /*+ no_unnest */ object_owner from plan_table)
and index_name in (select /*+ no_unnest */ object_name from plan_table))
group by owner,segment_type, segment_name
order by 3,4;
select * from test where id<1000; ---返回 999条
select * from test where owner='SB'; ---返回 6w条
select object_id from test where owner='SB'; ---返回 6w条