sqlplus / as sysdba
alter pluggable database all open;
alter session set container=ORCLPDB;

売上伝票 t_sales
伝票番号 sales_num number(10),
売上日 sales_dtm date,
納品日 delivery_dtm date,
得意先コード customer_cd char(5),
社員コード employee_cd char(3),
発注書番号 purchase_order_num number(10),
備考 sales_note

create table t_sales(
sales_num number(10),
sales_dtm date,
delivery_dtm date,
customer_cd char(5),
employee_cd char(3),
purchase_order_num number(10),
sales_note varchar2(1024),
CONSTRAINT t_sales_pkey PRIMARY KEY(sales_num)
)
TABLESPACE users;

売上伝票明細 t_sales_detail
伝票明細番号 sales_detail_num number(10),
伝票番号 sales_num number(10),
商品コード item_cd char(10),
数量 qty number(10),
単価 unit_amt number(10.3),
備考 sales_detail_note

create table t_sales_detail(
sales_detail_num number(10),
sales_num number(10),
item_cd char(10),
qty number(10),
unit_amt number(10,3),
sales_detail_note varchar2(1024),
CONSTRAINT t_sales_detail_pkey PRIMARY KEY(sales_num,sales_detail_num)
)
TABLESPACE users;

–select DBMS_RANDOM.VALUE(1,9999999999) into dennum from dual;
–select dennum from dual;
–DBMS_OUTPUT.PUT_LINE( ‘dennum:’ || dennum );

TRUNCATE TABLE t_sales;
TRUNCATE TABLE t_sales_detail;
SET TIMING ON
SET PAGES 50000
SET SERVEROUTPUT ON
DECLARE
cnt NUMBER(10):=0;
detail_cnt NUMBER(10):=0;
dennum NUMBER(10):=0;
BEGIN
— 売上テーブルのループ
LOOP
BEGIN
— 売上伝票番号採番
SELECT DBMS_RANDOM.VALUE(1,9999999999) INTO dennum FROM dual;
cnt := cnt + 1;
— 売上伝票行数設定
EXIT WHEN cnt = 2500001;
INSERT INTO t_sales values(dennum, sysdate-1, null, ‘3000’, 100, null, null);
— 売上明細テーブルのループ
LOOP
BEGIN
detail_cnt := detail_cnt + 1;
— 売上明細行数設定
EXIT WHEN detail_cnt = 4;
INSERT INTO t_sales_detail VALUES(detail_cnt, dennum, ‘1000000001’, 100, 1000, null);
END;
END LOOP;
detail_cnt := 0;
IF cnt >= 5000 THEN
COMMIT;
END IF;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN –ORA-00001
NULL;
WHEN OTHERS THEN
–DBMS_OUTPUT.PUT_LINE( ‘Error:’ || ERROR );
RAISE;
END;
END LOOP;
END;
/
COMMIT;
set timing off
select count(*) from t_sales;
select count(*) from t_sales_detail;

SET TIMING ON

select sales_num from (
select sales_num from t_sales where rownum<=1250000 ) where rownum=1; select * from t_sales where sales_num=5277201537; select count(*) from t_sales where sales_num>=5277201537;
select count(*) from t_sales where sales_num<=5277201537; select * from t_sales where sales_num=5277201537; select distinct p.* from t_sales p inner JOIN t_sales_detail c on p.sales_num = c.sales_num where p.sales_num=5277201537; SELECT p.sales_num, p.sales_dtm, p.delivery_dtm, p.customer_cd, p.employee_cd, p.purchase_order_num, p.sales_note, c.sales_detail_num, c.item_cd, c.qty, c.unit_amt, c.sales_detail_note FROM t_sales p INNER JOIN t_sales_detail c ON p.sales_num = c.sales_num WHERE p.sales_num=5277201537 ORDER BY p.sales_num, c.sales_detail_num; select count(*) from t_sales where rownum<=5277201537; # インデックスが使用されているか確認の実行例 alter index t_sales_pkey monitoring usage; alter index t_sales_detail_pkey monitoring usage; SELECT * from v$object_usage; select * from t_sales order by 1 offset 10 rows fetch first 20 rows only; #サンプルデータ #sample