Oracle partitioning, indexing, testing (2)

Oracle partition, index, test (2) this select operation, each table is 0.1 billion, and the indexes of all three tables are disable. First test compression.

SQL code -- sales_data bitmap is not suitable for alter table sales_data1 compress; --- view SELECT table_name, partition_name, compression FROM user_tab_partitions; SELECT table_name, partition_name, compression FROM user_tables; --- compress alter table SALES_DATA1 move partition SALES_200901 COMPRESS statement; alter table SALES_DATA1 move partition into COMPRESS statement; alter table SALES_DATA1 move partition into COMPRESS statement; alter table SALES_DATA1 move partition into COMPRESS statement; alter table SALES_DATA1 move partition SALES_200905 compress parallel; some statement SQL code -- View SELECT FILE_NAME, TABLESPACE_NAME, BYTES, AUTOEXTENSIBLE, MAXBYTES, INCREMENT_BY FROM DBA_DATA_FILES; --- table space usage select. tablespace_name,. bytes/1024/1024 "Sum MB", (. bytes-b.bytes)/1024/1024 "used MB", B. bytes/1024/1024 "free MB", round (. bytes-b.bytes)/. bytes) * 100,2) "percent_used" from (select tablespace_name, sum (bytes) bytes from dba_data_files group by tablespace_name) a, (select tablespace_name, sum (bytes) bytes, max (bytes) largest from dba_free_space group by tablespace_name) B where. tablespace_name = B. tablespace_name order by (. bytes-b.bytes)/. bytes) desc --- whether the tablespace is auto-incrementing select file_id, file_name, tablespace_name, autoextensible, increment_by from dba_data_files order by file_id desc; --- the table size Select Segment_Name, Sum (bytes) /1024/1024 comparison of Segment_Name SUM (BYTES)/1024/1024 BYTES ---------------------- SALES_DATA 3676.625 SALES_DATA1 1643.625 SALES_DATA2 3717 after compression From User_Extents Group By SEGMENT_NAME is much smaller; SQL code for collecting information: execute dbms_stats.gather_table_stats (ownname => 'toby', tabname => 'sales _ data', degree => 4); execute distribute (ownname => 'toby ', tabname => 'sales _ DATA1 ', degree => 4); execute dbms_stats.gather_table_stats (ownname => 'toby', tabname => 'sales _ DATA2', degree => 4 ); join to see the difference between compression and non-compression SQL code-for compression 35,167 ms elapsed select city, sum (sales_amount) from sales_data inner join city using (city_id) where sales_date between to_date ('1970-01-1 ', 'yyyy-mm-dd') and to_date ('1970-02-28', 'yyyy-mm-dd') group by city; --- compress 21,549 ms select city, sum (sales_amount) from sales_data1 inner join city using (city_id) where sales_date between to_date ('2017-01-1 ', 'yyyy-mm-dd ') and to_date ('1970-02-28 ', 'yyyy-mm-dd') group by city; 35 seconds to 21 seconds to view the execution plan Statistics resume 267 recursive cballs 234762 consistent gets direct 234762 physical reads direct 0 recovery blocks read 0 redo buffer allocation retries Statistics limit 357 recursive cballs 104407 consistent gets direct 104407 physical reads direct 0 recovery blocks read 0 redo buffer allocation retries reads about two times different. ---------- test the SQL code alter table sales_data NOPARALLEL; alter table city NOPARALLEL; select city, sum (sales_amount) from sales_data inner join city using (city_id) where sales_date between to_date ('1970-01-1 ', 'yyyy-mm-dd') and to_date ('1970-02-28', 'yyyy-mm-dd') group by city; 42,734 ms elapsedPlan hash value: 3773866511 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop | latency | 0 | select statement | 23 | 805 | 58734 (13) | 00:11:45 | 1 | sort group by | 23 | 805 | 58734 (13) | 00:11:45 | * 2 | hash join | 50M | 1679M | 53390 (4) | 00:10:41 | 3 | table access full | CITY | 23 | 437 | 3 (0) | 00:00:01 | 4 | partition range iterator | 50M | 767M | 52923 (3) | 00:10:36 | 1 | 2 | * 5 | table access full | SALES_DATA | 50M | 767M | 52923 (3) | 00:10:36 | 1 | 2 | compare a 34-second comparison of 42-second compression is 21-to-42-second index creation of SALES_DATA SQL code -- partition index placed in the corresponding tablespace create index index_sales_data_partition on sales_data (sales_date) local (partition sales_200901 tablespace partition, partition sales_200904 tablespace partition, partition sales_200905 tablespace partition, partition tablespace partition, partition tablespace partition, partition tablespace partition, partition tablespace partition, partition sales_200910 tablespace partition, partition sales_200911 tablespace partition, partition sales_200912 tablespace partition, partition tablespace partition, partition sales_201002 tablespace partition ); --- bitmap create bitmap index index_sales_data_sales_type on sales_data (sales_type) local; -- bitmap connects to create bitmap index index_sales_data_city sales_data (city. city_id) from sales_data, city where sales_data.city_id = city. city_id local; run SQLSql code select city, sum (sales_amount) from sales_data inner join city using (city_id) where sales_date = to_date ('2017-01-1 ', 'yyyy-mm-dd') or sales_date = to_date ('2017-03-5 ', 'yyyy-mm-dd') group by city; 2009 ms elapsedPlan hash value: 303492610 Bytes -------- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop | TQ | IN-OUT | PQ Distrib | route -------- | 0 | select statement | 23 | 805 | 14650 (3) | 00:02:56 | 1 | px coordinator | 2 | px send qc (RANDOM) |: TQ10002 | 23 | 805 | 14650 (3) | 00:02:56 | Q1, 02 | P-> S | QC (RAND) | 3 | sort group by | 23 | 805 | 14650 (3) | 00:02:56 | Q1, 02 | PCWP | 4 | px receive | 23 | 805 | 14650 (3) | 00:02:56 | Q1, 02 | PCWP | 5 | px send hash |: TQ10001 | 23 | 805 | 14650 (3) | 00:02:56 | Q1, 01 | P-> P | HASH | 6 | sort group by | 23 | 805 | 14650 (3) | 00:02:56 | Q1, 01 | PCWP | * 7 | hash join | 1820K | 60M | 14609 (3) | 00:02:56 | Q1, 01 | PCWP | 8 | px receive | 23 | 437 | 1 (0) | 00:00:01 | Q1, 01 | PCWP | 9 | px send broadcast |: TQ10000 | 23 | 437 | 1 (0) | 00:00:01 | Q1, 00 | P-> P | BROADCAST | 10 | px block iterator | 23 | 437 | 1 (0) | 00:00:01 | Q1, 00 | PCWC | 11 | table access full | CITY | 23 | 437 | 1 (0) | 00:00:01 | Q1, 00 | PCWP | 12 | px block iterator | 1820K | 27M | 14604 (3) | 00:02:56 | KEY (I) | Q1, 01 | PCWC | * 13 | table access full | SALES_DATA | 1820K | 27M | 14604 (3) | 00:02:56 | KEY (I) | Q1, 01 | PCWP | certificate -------- Predicate Information (identified by operation id): ------------------------------------------------- 7-access ("SALES_DATA ". "CITY_ID" = "CITY ". "CITY_ID") 13-filter ("SALES_DATA ". "SALES_DATE" = TO_DATE ('1970-01-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss') OR "SALES_DATA ". "SALES_DATE" = TO_DATE ('2017-03-05 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss ')) statistics limit 40 recursive cballs 234602 consistent gets direct 234602 physical reads direct 0 recovery blocks read 0 redo buffer allocation retries used 22 seconds without using the partition index force using the index SQL code select/* + index (sales_data INDEX_SALES_DATA_PARTITION) */city, sum (sales_amount) from sales_data inner join city using (city_id) where sales_date = to_date ('2017-01-1 ', 'yyyy-mm-dd ') or sales_date = to_date ('1970-03-5 ', 'yyyy-mm-dd') group by city; 2009 ms elapsedPlan hash value: 1538767871 Bytes ---------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop | TQ | IN-OUT | PQ Distrib | hour -------------------------------------------- | 0 | select statement | 23 | 805 | 240 K (1) | 00:48:12 | 1 | px coordinator | 2 | px send qc (RANDOM) |: TQ10003 | 23 | 805 | 240 K (1) | 00:48:12 | Q1, 03 | P-> S | QC (RAND) | 3 | sort group by | 23 | 805 | 240 K (1) | 00:48:12 | Q1, 03 | PCWP | 4 | px receive | 23 | 805 | 240 K (1) | 00:48:12 | Q1, 03 | PCWP | 5 | px send hash |: TQ10002 | 23 | 805 | 240 K (1) | 00:48:12 | Q1, 02 | P-> P | HASH | 6 | sort group by | 23 | 805 | 240 K (1) | 00:48:12 | Q1, 02 | PCWP | * 7 | hash join | 1820K | 60M | 240 K (1) | 00:48:11 | Q1, 02 | PCWP | 8 | px receive | 23 | 437 | 1 (0) | 00:00:01 | Q1, 02 | PCWP | 9 | px send hash |: TQ10001 | 23 | 437 | 1 (0) | 00:00:01 | Q1, 01 | P-> P | HASH | 10 | px block iterator | 23 | 437 | 1 (0) | 00:00:01 | Q1, 01 | PCWC | 11 | table access full | CITY | 23 | 437 | 1 (0) | 00:00:01 | Q1, 01 | PCWP | 12 | buffer sort | Q1, 02 | PCWC | 13 | px receive | 1820K | 27M | 240 K (1) | 00:48:11 | Q1, 02 | PCWP | 14 | px send hash |: TQ10000 | 1820K | 27M | 240 K (1) | 00:48:11 | S-> P | HASH | 15 | inlist iterator | 16 | partition range iterator | 1820K | 27M | 240 K (1) | 00:48:11 | KEY (I) | 17 | TABLE ACCESS BY LOCAL INDEX ROWID | SALES_DATA | 1820K | 27M | 240 K (1) | 00:48:11 | KEY (I) | * 18 | index range scan | INDEX_SALES_DATA_PARTITION | 1831K | 4884 (1) | 00:00:59 | KEY (I) | KEY (I) | certificate -------------------------------------------- Predicate Information (identified by operation id): found 7-access ("SALES_DATA ". "CITY_ID" = "CITY ". "CITY_ID") 18-access ("SALES_DATA ". "SALES_DATE" = TO_DATE ('1970-01-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss') OR "SALES_DATA ". "SALES_DATE" = TO_DATE ('2017-03-05 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss ')) statistics ----------------------------------------------------------- 76 recursive cballs 0 consistent gets direct 3644 physical reads direct 0 recovery blocks read 0 redo buffer allocation retries

 

It took 50 seconds... it seems that we still need to use oralce's own optimizer... it took half of the time to use it.