Preface

Oracle recommends partitioning a single table if it exceeds 2G. The benefit of not more than 30w rows of partitioned data for creating partitions for large tables is obvious. This example comes with its own sample under oracle 11g and partitioning demonstration under sh user based on sales. The table has 91w data

select count(1) from  sales t;

We use sales table data to rebuild tables and partition them, comparing the efficiency of partitioning and non-partitioning, and comparing the efficiency of partitioning local index and bitmap index after partitioning.

1. Create tables containing primary partition and subpartition

sales_part_test
Are partitioned according to the time_id field

create table sales_part_test
(
  prod_id       NUMBER not null,
  cust_id       NUMBER not null,
  time_id       DATE not null,
  channel_id    NUMBER not null,
  promo_id      NUMBER not null,
  quantity_sold NUMBER(10,2) not null,
  amount_sold   NUMBER(10,2) not null
)
partition by range(time_id) subpartition  by range  (time_id)  --Specify both primary and subpartition partitions:Scope partitioning, by column time_id Scope Division
( 
 partition sales_part_1998 values less than (TO_DATE('1999-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
 tablespace   EXAMPLE
 (
   subpartition sales_part_1998_01 values less than ( TO_DATE('1998-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,--Specify primary partition sales_part_1998 Sub partition sales_part_1998_01 Note that the subpartition names of each primary partition cannot be the same
   subpartition sales_part_1998_02 values less than ( TO_DATE('1998-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
   subpartition sales_part_1998_03 values less than ( TO_DATE('1998-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
   subpartition sales_part_1998_04 values less than ( TO_DATE('1998-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
   subpartition sales_part_1998_05 values less than ( TO_DATE('1998-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
   subpartition sales_part_1998_06 values less than ( TO_DATE('1998-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
   subpartition sales_part_1998_07 values less than ( TO_DATE('1998-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
   subpartition sales_part_1998_08 values less than ( TO_DATE('1998-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
   subpartition sales_part_1998_09 values less than ( TO_DATE('1998-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
   subpartition sales_part_1998_10 values less than ( TO_DATE('1998-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
   subpartition sales_part_1998_11 values less than ( TO_DATE('1998-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
   subpartition sales_part_1998_12 values less than ( TO_DATE('1999-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE 

 )
 ,                 
 partition sales_part_1999 values less than (TO_DATE('2000-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
 tablespace EXAMPLE
 (
   subpartition sales_part_1999_01 values less than ( TO_DATE('1999-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
   subpartition sales_part_1999_02 values less than ( TO_DATE('1999-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
   subpartition sales_part_1999_03 values less than ( TO_DATE('1999-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
   subpartition sales_part_1999_04 values less than ( TO_DATE('1999-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
   subpartition sales_part_1999_05 values less than ( TO_DATE('1999-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
   subpartition sales_part_1999_06 values less than ( TO_DATE('1999-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
   subpartition sales_part_1999_07 values less than ( TO_DATE('1999-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
   subpartition sales_part_1999_08 values less than ( TO_DATE('1999-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
   subpartition sales_part_1999_09 values less than ( TO_DATE('1999-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
   subpartition sales_part_1999_10 values less than ( TO_DATE('1999-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
   subpartition sales_part_1999_11 values less than ( TO_DATE('1999-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
   subpartition sales_part_1999_12 values less than ( TO_DATE('2000-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE 
 ),
 partition sales_part_2000 values less than (TO_DATE('2001-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
 tablespace EXAMPLE
 (
   subpartition sales_part_2000_01 values less than ( TO_DATE('2000-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
   subpartition sales_part_2000_02 values less than ( TO_DATE('2000-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
   subpartition sales_part_2000_03 values less than ( TO_DATE('2000-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
   subpartition sales_part_2000_04 values less than ( TO_DATE('2000-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
   subpartition sales_part_2000_05 values less than ( TO_DATE('2000-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
   subpartition sales_part_2000_06 values less than ( TO_DATE('2000-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
   subpartition sales_part_2000_07 values less than ( TO_DATE('2000-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
   subpartition sales_part_2000_08 values less than ( TO_DATE('2000-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
   subpartition sales_part_2000_09 values less than ( TO_DATE('2000-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
   subpartition sales_part_2000_10 values less than ( TO_DATE('2000-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
   subpartition sales_part_2000_11 values less than ( TO_DATE('2000-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
   subpartition sales_part_2000_12 values less than ( TO_DATE('2001-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE 
  ),
 partition sales_part_2001 values less than (TO_DATE('2002-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
 tablespace EXAMPLE (
   subpartition sales_part_2001_01 values less than ( TO_DATE('2001-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
   subpartition sales_part_2001_02 values less than ( TO_DATE('2001-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
   subpartition sales_part_2001_03 values less than ( TO_DATE('2001-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
   subpartition sales_part_2001_04 values less than ( TO_DATE('2001-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
   subpartition sales_part_2001_05 values less than ( TO_DATE('2001-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
   subpartition sales_part_2001_06 values less than ( TO_DATE('2001-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
   subpartition sales_part_2001_07 values less than ( TO_DATE('2001-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
   subpartition sales_part_2001_08 values less than ( TO_DATE('2001-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
   subpartition sales_part_2001_09 values less than ( TO_DATE('2001-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
   subpartition sales_part_2001_10 values less than ( TO_DATE('2001-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
   subpartition sales_part_2001_11 values less than ( TO_DATE('2001-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
   subpartition sales_part_2001_12 values less than ( TO_DATE('2002-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE 
 )
 )

2. Creating Partition Local Index

create index idx_sales_sales_part_test  on sales_part_test (time_id)local;

3. Insert data

insert into   sales_part_test select  t.* from  sales  t;

4. View the execution plan (here is the explanation of the execution plan)

select  t.* from  sales_part_test t where t.time_id<=date'1998-05-01'

You can see that the first primary partition was accessed by the index range scan method through the local index idx_sales_sales_part_test we created.
All subpartitions of sales_part_1998,
After obtaining the data, we can use PARTITION RANGE ITERATOR (partition scope iteration) or get 1-5 sub-partitions of sales_part_1998 as the main partition

5. Create a non-partitioned table with the same field data in the partitioned table above and create an index

create table  sales_nopart as select  t.* from  sales t;
create index idx_sales_nopart   on sales_nopart (time_id) ;

6. Look at the execution plan for non-partitioned tables

You can see that the execution plan did not choose index scanning, but chose all scanning, and the cost was 1243 times the partition table, and the time consumed was about 15 times.
There are only 38 consistency readings for partition tables, but there are no physical readings. The logical or consistent readings for non-partition tables are 8227 and the physical readings are 4166. The performance is different.

7. Scan partitioned and non-partitioned tables indexed

Analysis of both tables

exec dbms_stats.gather_table_stats('sh','SALES_PART_TEST');
exec dbms_stats.gather_table_stats('sh','SALES_NOPART');

Look at the execution plan for the following statements

select  t.* from  sales_part_test t where t.time_id=date'1998-05-01'

The implementation plan of the partition table is as follows:

Non-partitioned table execution plan

select  t.* from  sales_nopart t where t.time_id=date'1998-05-01'

From the above results, we can see that both partitioned and non-partitioned tables have been scanned by index range, but the logical reading cost of non-partitioned tables is 58 and 42 is still higher than that of non-partitioned tables.
The cost of 54 logical reads and 33 partition tables is higher.

9. Creating Bitmap Index in Partitions

Seeing that we are not satisfied, we find that there are too many time_id duplicate values in the partition. We can try to create bitmap index instead of b-tree index.

drop  index idx_sales_sales_part_test;
create bitmap index idx_sales_sales_part_test  on sales_part_test (time_id)local;
--Note that bitmap indexes must be created as local indexes

10. The execution plan of the same statement again

--First analysis table
     exec dbms_stats.gather_table_stats('sh','SALES_PART_TEST');
     select  t.* from  SALES_PART_TEST t where t.time_id=date'1998-05-01'

From the results, we can see that after creating bitmap index, oracle chose bitmap index for unique value scanning, and only scanned a fifth sub-partition. The cost was only 4, and only 36 logical reads were used. Is this the best choice?
But... When we execute the following statement, will the execution plan be cheaper than the b-tree index?

12. When bitmap indexes contain execution plans for index columns in predicates'<','<=','>','>='.

select  t.* from  SALES_PART_TEST t where t.time_id<=date'1998-05-01'

As a result, we were shocked. At this time, the database did not choose to index, but chose to scan the whole table for the main partition sales_part_1998.
Scanning is the same for all other partitions, but the total logical reading is more than 10 times that of the 38 B-tree indexes. So there is no absolute optimum for the database, which is relative. Here we will have a question: Why not go to bitmap index?
Bitmap index column in predicate and predicate is "="
What needs to be distinguished is that for B-tree indexes, any query on non-unique index columns will be scanned by index range, as confirmed by the results of step 4 and step 7.

13. Execution plan when bitmap index columns need to be retrieved

The list of columns to be retrieved can be retrieved from the bitmap index of one of the columns to obtain the range scan of the index of the walking bitmap here.
Let's look at the execution plan again by changing the above sql * to t.cust_id,t.time_id,t.prod_id

select t.cust_id,t.time_id,t.prod_id from  SALES_PART_TEST t where t.time_id <=date'1998-05-01'

You can see that the execution plan did indeed select Bitmap Index Scan, although it still scans all the sub-partitions of the main partition sales_part_1998.
But we can see that there are only 122 logical reads, which is less than four times the B-tree index range scan, and it is much better than the full table scan.
Bitmap index should be carefully used. Bitmap index is suitable for data warehouse. Bitmap index is especially unsuitable for online systems with DML operations.

summary

If you encounter a problem, you should dig it to the bottom and solve your doubts layer by layer. At the beginning of this paper, you just want to verify that partition query is better than non-partition query for tables with large amount of data.
It is more efficient, but the efficiency comparison between B-tree index and bitmap index in partition table is extended later. At the same time, the index scanning is also reviewed.
Conditions, but also learned that there is no absolute best, need to look at the problem dialectically.