Explain
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
EXPLAIN PLAN FOR
select
l.channel,
l.service_id,
l.command_id,
l.package_id,
l.package_type,
count(l.id) as total_block,
sum(case WHEN l.command_type <> 0 and l.error_code =0 then l.fee else 0 END) as total_revenue,
count(case when l.error_code = 0 then 1 else null end) as total_charge_success,
count(case when l.command_type <> 0 then 1 else null end) as total_charge
from log_transaction l
where created_at between to_date('20160720','YYYYMMDD') and to_date('20160724','YYYYMMDD')
-- where trunc(created_at) = trunc(to_date('YYYYMMDD','20160723'))
group by CHANNEL, service_id, command_id,package_id,package_type;
Lệnh tạo bảng
Lệnh tạo bảng
CREATE TABLESPACE NAPTHE
DATAFILE
'/u01/app/oracle/oradata/ora15/NAPTHE_data01.dbf'
SIZE 5G AUTOEXTEND
ON NEXT 1G MAXSIZE UNLIMITED;
commit;
CREATE PROFILE APPS_PROFILES LIMIT
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL 3000
CONNECT_TIME 45
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL 1000
PRIVATE_SGA 15K
COMPOSITE_LIMIT 5000000;
CREATE USER NAPTHE PROFILE
APPS_PROFILES IDENTIFIED BY sysNap2013The
DEFAULT TABLESPACE NAPTHE
TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;
GRANT CONNECT TO NAPTHE;
GRANT RESOURCE TO NAPTHE;
commit;
