show pdbs
alter pluggable database ORCLPDB open;
alter pluggable database ORCLPDB save state;
alter session set container=cdb$root;
alter session set container=ORCLPDB;
set pages 50000
select username from dba_users order by 1;
alter user test12 identified by test12;
alter user hr account unlock;
alter user hr identified by hr;
conn test12/test12@ORCLPDB
conn hr/hr@ORCLPDB
select table_name from user_tables;
REGIONS
COUNTRIES
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
select count(*) from REGIONS;
select count(*) from COUNTRIES;
select count(*) from LOCATIONS;
select count(*) from DEPARTMENTS;
select count(*) from JOBS;
select count(*) from EMPLOYEES;
select count(*) from JOB_HISTORY;
select index_name from user_indexes;
— オートトレース取得環境をつくる
alter session set container=ORCLPDB;
@$ORACLE_HOME/sqlplus/admin/plustrce.sql
grant plustrace to hr;
conn hr/hr@ORCLPDB
set autotrace on
####################################################################################
# — ダミーデータ作成
# set mark csv on
# select * from EMPLOYEES;
#
# HR.EMP_EMP_ID_PK
#
# set serveroutput on
#
# DECLARE
# n_cnt NUMBER(5) := 0;
# n_num NUMBER(10) :=1009;
#
# BEGIN
# FOR n_cnt in 1..10000 LOOP
# n_num := n_num + 1;
#
# insert into EMPLOYEES
# values(
# n_num,
# ‘Dummy-Firsr’,
# ‘Dummy-Last’,
# ‘E-Mail-‘||TO_CHAR(n_num),
# ‘515.123.8181’,
# TO_DATE(‘20240125′,’yyyymmdd’),
# ‘AC_ACCOUNT’,
# 8300,
# 0.0,
# 205,
# 110
# );
#
# END LOOP;
# END;
# /
#
# commit;
#
# set mark csv on
# select * from EMPLOYEES;
#
# SQL> desc EMPLOYEES
# 名前 NULL? 型
# —————————————– ——– —————————-
# EMPLOYEE_ID NOT NULL NUMBER(6)
# FIRST_NAME VARCHAR2(20)
# LAST_NAME NOT NULL VARCHAR2(25)
# EMAIL NOT NULL VARCHAR2(25)
# PHONE_NUMBER VARCHAR2(20)
# HIRE_DATE NOT NULL DATE
# JOB_ID NOT NULL VARCHAR2(10)
# SALARY NUMBER(8,2)
# COMMISSION_PCT NUMBER(2,2)
# MANAGER_ID NUMBER(6)
# DEPARTMENT_ID NUMBER(4)
#
#
# insert into EMPLOYEES
# values(
# 10800,
# ‘Dummy-Firsr’,
# ‘Dummy-Last’,
# ‘E-Mail-10800’,
# ‘515.123.8181’,
# TO_DATE(‘20240115′,’yyyymmdd’),
# ‘AC_ACCOUNT’,
# 8400,
# 0.0,
# 205,
# 110
# );
#
#
# select * from EMPLOYEES where EMPLOYEE_ID=100;
####################################################################################
drop table smpl_sales;
create table smpl_sales(
sales_id number(10),
parson_id number(5),
sales_date date,
sales_update date,
sales_create date
);
alter table smpl_sales add constraint smpl_sales_pkey primary key (sales_id);
set serveroutput on
DECLARE
n_cnt NUMBER(5) := 0;
n_id NUMBER(3) := 0;
BEGIN
FOR n_cnt in 1..1000000 LOOP
n_id := 100;
insert into smpl_sales
values(
n_cnt,
n_id,
sysdate,
sysdate,
sysdate
);
END LOOP;
END;
/
commit;
insert into smpl_sales values(3001,200,sysdate,sysdate,sysdate);
exec dbms_stats.gather_table_stats(‘hr’,’smpl_sales’);
select * from smpl_sales;
SALES_ID,PARSON_ID
set autotrace on
set autotrace on explain
set autotrace on statistics
set autotrace traceonly
select * from smpl_sales where SALES_ID=1001;
select * from smpl_sales where SALES_ID!=1001;
select * from smpl_sales where
SALES_ID=1001;
“———————————————————————————————–”
“| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |”
“———————————————————————————————–”
“| 0 | SELECT STATEMENT | | 1 | 32 | 3 (0)| 00:00:01 |”
“| 1 | TABLE ACCESS BY INDEX ROWID| SMPL_SALES | 1 | 32 | 3 (0)| 00:00:01 |”
“|* 2 | INDEX UNIQUE SCAN | SMPL_SALES_PKEY | 1 | | 2 (0)| 00:00:01 |”
“———————————————————————————————–”
select * from smpl_sales where
SALES_ID=1001
AND SALES_ID=1002;
“————————————————————————————————”
“| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |”
“————————————————————————————————”
“| 0 | SELECT STATEMENT | | 1 | 32 | 0 (0)| |”
“|* 1 | FILTER | | | | | |”
“| 2 | TABLE ACCESS BY INDEX ROWID| SMPL_SALES | 1 | 32 | 3 (0)| 00:00:01 |”
“|* 3 | INDEX UNIQUE SCAN | SMPL_SALES_PKEY | 1 | | 2 (0)| 00:00:01 |”
“————————————————————————————————”
select * from smpl_sales where
SALES_ID=1001
OR SALES_ID=1002;
“————————————————————————————————”
“| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |”
“————————————————————————————————”
“| 0 | SELECT STATEMENT | | 2 | 64 | 5 (0)| 00:00:01 |”
“| 1 | INLIST ITERATOR | | | | | |”
“| 2 | TABLE ACCESS BY INDEX ROWID| SMPL_SALES | 2 | 64 | 5 (0)| 00:00:01 |”
“|* 3 | INDEX UNIQUE SCAN | SMPL_SALES_PKEY | 2 | | 4 (0)| 00:00:01 |”
“————————————————————————————————”
select * from smpl_sales where
SALES_ID!=1001
OR SALES_ID=1002;
“——————————————————————————–”
“| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |”
“——————————————————————————–”
“| 0 | SELECT STATEMENT | | 999K| 30M| 1410 (1)| 00:00:01 |”
“|* 1 | TABLE ACCESS FULL| SMPL_SALES | 999K| 30M| 1410 (1)| 00:00:01 |”
“——————————————————————————–”
#ダミースキーマ
#ダミーデータ
#テストスキーマ
#テストデータ
#サンプルスキーマ