-- 列サイズ長め（準備）
SCOTT@localhost:1521/freepdb1> @any_value.sql
1* DROP TABLE IF EXISTS any_value_table PURGE
表が削除されました。
経過: 00:00:00.20
1 CREATE TABLE any_value_table
2 (
3 ordered_date DATE NOT NULL
4 , order_id INTEGER NOT NULL
5 , product_id INTEGER NOT NULL
6 , product_name VARCHAR(2000) NOT NULL
7 , qty INTEGER NOT NULL
8 , CONSTRAINT pk_any_value_table PRIMARY KEY (order_id, product_id, ordered_date)
9* )
表が作成されました。
経過: 00:00:00.04
1 DECLARE
2 o_date DATE := SYSDATE;
3 BEGIN
4 FOR i IN 1..1000000 LOOP
5 INSERT INTO any_value_table
6 (ordered_date
7 , order_id
8 , product_id
9 , product_name
10 , qty
11 ) VALUES (o_date, i, 1, 'ITEM_1'||lpad('*',1600,'*'), 1);
12 IF mod(i,100) = 0 THEN commit; END IF;
13 END LOOP;
14* END;
PL/SQLプロシージャが正常に完了しました。
経過: 00:02:58.25
PL/SQLプロシージャが正常に完了しました。
経過: 00:00:42.26
SEGMENT_NAME GB
------------------------------ ----------
ANY_VALUE_TABLE 1.96582031
経過: 00:00:00.03
...中略...
-- ２回目
非集計列がGROUP BY句に定義もされず、集計関数も利用されていない場合は、エラーになることの確認！
SCOTT@localhost:1521/freepdb1> @any_value_test
1 SELECT
2 product_id
3 ,product_name
4 ,SUM(qty) AS total
5 FROM
6 any_value_table
7 GROUP BY
8* product_id
,product_name
*
行3でエラーが発生しました。:
ORA-00979: "PRODUCT_NAME": GROUP BY句に出現するか、集計関数で使用される必要があります
ヘルプ:
https://docs.oracle.com/error-help/db/ora-00979/
経過: 00:00:00.01
1 SELECT /*+ MONITOR */
2 product_id
3 ,product_name
4 ,SUM(qty) AS total
5 FROM
6 any_value_table
7 GROUP BY
8 product_id
9* , product_name
PRODUCT_ID PRODUCT_NAME TOTAL
---------- ----------------------------------------------------------------------------------------------------------- ----------
1 ITEM_1***************************************************************************************************** 1000000
***********************************************************************************************************
...中略...
***********************************************************************************************************
***********************************************************************************************************
*
経過: 00:00:00.58
...中略...
SQL Text
------------------------------
SELECT /*+ MONITOR */ product_id ,product_name
,SUM(qty) AS total FROM any_value_table
GROUP BY product_id , product_name
...中略...
Global Stats
================================================================
| Elapsed | Cpu | IO | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
================================================================
| 0.62 | 0.53 | 0.09 | 2 | 250K | 1981 | 2GB |
================================================================
SQL Plan Monitoring Details (Plan Hash Value=3772843140)
=====================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) |
=====================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +1 | 1 | 1 | | | | |
| 1 | HASH GROUP BY | | 1 | 69634 | 1 | +1 | 1 | 1 | | | 100.00 | Cpu (1) |
| 2 | TABLE ACCESS FULL | ANY_VALUE_TABLE | 1M | 69619 | 1 | +1 | 1 | 1M | 1981 | 2GB | | |
=====================================================================================================================================================
...中略...
SQL Text
------------------------------
SELECT /*+ MONITOR */ product_id ,MAX(product_name) AS product_name
,SUM(qty) AS total FROM any_value_table
GROUP BY product_id
...中略...
Global Stats
================================================================
| Elapsed | Cpu | IO | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
================================================================
| 0.74 | 0.65 | 0.09 | 2 | 250K | 1981 | 2GB |
================================================================
SQL Plan Monitoring Details (Plan Hash Value=3772843140)
=====================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) |
=====================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 1 | | | | |
| 1 | HASH GROUP BY | | 1 | 69634 | 1 | +0 | 1 | 1 | | | | |
| 2 | TABLE ACCESS FULL | ANY_VALUE_TABLE | 1M | 69619 | 1 | +0 | 1 | 1M | 1981 | 2GB | | |
=====================================================================================================================================================
...中略...
SQL Text
------------------------------
SELECT /*+ MONITOR */ product_id ,ANY_VALUE(product_name) AS product_name
,SUM(qty) AS total FROM any_value_table
GROUP BY product_id
...中略...
Global Stats
================================================================
| Elapsed | Cpu | IO | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
================================================================
| 0.33 | 0.25 | 0.09 | 2 | 250K | 1981 | 2GB |
================================================================
SQL Plan Monitoring Details (Plan Hash Value=3772843140)
=====================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) |
=====================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +1 | 1 | 1 | | | | |
| 1 | HASH GROUP BY | | 1 | 69634 | 1 | +1 | 1 | 1 | | | 100.00 | Cpu (1) |
| 2 | TABLE ACCESS FULL | ANY_VALUE_TABLE | 1M | 69619 | 1 | +1 | 1 | 1M | 1981 | 2GB | | |
=====================================================================================================================================================
...中略...
-- 列サイズ短めで件数が多い（準備）
SCOTT@localhost:1521/freepdb1> @any_value2.sql
1* DROP TABLE IF EXISTS any_value_table PURGE
表が削除されました。
経過: 00:00:00.13
1 CREATE TABLE any_value_table
2 (
3 ordered_date DATE NOT NULL
4 , order_id INTEGER NOT NULL
5 , product_id INTEGER NOT NULL
6 , product_name VARCHAR(2000) NOT NULL
7 , qty INTEGER NOT NULL
8 , CONSTRAINT pk_any_value_table PRIMARY KEY (order_id, product_id, ordered_date)
9* )
表が作成されました。
経過: 00:00:00.01
1 DECLARE
2 o_date DATE := SYSDATE;
3 BEGIN
4 FOR i IN 1..20000000 LOOP
5 INSERT INTO any_value_table
6 (ordered_date
7 , order_id
8 , product_id
9 , product_name
10 , qty
11 ) VALUES (o_date, i, 1, 'ITEM_1'||LPAD('*',30,'*'), 1);
12 IF mod(i,1000) = 0 THEN commit; END IF;
13 END LOOP;
14* END;
PL/SQLプロシージャが正常に完了しました。
経過: 00:12:27.16
PL/SQLプロシージャが正常に完了しました。
経過: 00:00:17.83
SEGMENT_NAME GB
------------------------------ ----------
ANY_VALUE_TABLE 1.3125
経過: 00:00:00.06
...中略...
-- ２回目
SCOTT@localhost:1521/freepdb1> @any_value_test2
1 SELECT /*+ monitor */
2 product_id
3 ,product_name
4 ,SUM(qty) AS total
5 FROM
6 any_value_table
7 GROUP BY
8 product_id
9* , product_name
PRODUCT_ID PRODUCT_NAME TOTAL
---------- ------------------------------------ ----------
1 ITEM_1****************************** 20000000
経過: 00:00:01.11
...中略...
SQL Text
------------------------------
SELECT /*+ monitor */ product_id ,product_name
,SUM(qty) AS total FROM any_value_table
GROUP BY product_id , product_name
...中略...
Global Stats
================================================================
| Elapsed | Cpu | IO | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
================================================================
| 1.15 | 1.10 | 0.06 | 2 | 171K | 1363 | 1GB |
================================================================
SQL Plan Monitoring Details (Plan Hash Value=3772843140)
=====================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) |
=====================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +1 | 1 | 1 | | | | |
| 1 | HASH GROUP BY | | 1 | 46860 | 1 | +1 | 1 | 1 | | | | |
| 2 | TABLE ACCESS FULL | ANY_VALUE_TABLE | 20M | 46510 | 1 | +1 | 1 | 20M | 1363 | 1GB | 100.00 | Cpu (1) |
=====================================================================================================================================================
...中略...
SQL Text
------------------------------
SELECT /*+ monitor */ product_id ,MAX(product_name) AS product_name
,SUM(qty) AS total FROM any_value_table
GROUP BY product_id
...中略...
Global Stats
================================================================
| Elapsed | Cpu | IO | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
================================================================
| 1.29 | 1.23 | 0.06 | 2 | 171K | 1363 | 1GB |
================================================================
SQL Plan Monitoring Details (Plan Hash Value=3772843140)
=====================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) |
=====================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +2 | 1 | 1 | | | | |
| 1 | HASH GROUP BY | | 1 | 46860 | 1 | +2 | 1 | 1 | | | | |
| 2 | TABLE ACCESS FULL | ANY_VALUE_TABLE | 20M | 46510 | 2 | +1 | 1 | 20M | 1363 | 1GB | 100.00 | Cpu (1) |
=====================================================================================================================================================
...中略...
SQL Text
------------------------------
SELECT /*+ monitor */ product_id ,ANY_VALUE(product_name) AS product_name
,SUM(qty) AS total FROM any_value_table
GROUP BY product_id
...中略...
Global Stats
================================================================
| Elapsed | Cpu | IO | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
================================================================
| 1.15 | 1.09 | 0.06 | 2 | 171K | 1363 | 1GB |
================================================================
SQL Plan Monitoring Details (Plan Hash Value=3772843140)
==========================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) |
==========================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +1 | 1 | 1 | | | | |
| 1 | HASH GROUP BY | | 1 | 46860 | 1 | +1 | 1 | 1 | | | | |
| 2 | TABLE ACCESS FULL | ANY_VALUE_TABLE | 20M | 46510 | 2 | +0 | 1 | 20M | 1363 | 1GB | 100.00 | direct path read (1) |
==========================================================================================================================================================
|
最近のコメント