Common table index, partition table local index and partition table's global index partitioning efficiency comparison test

First create a large table:

CREATE TABLE Big_table
As
Select RowNum ID, a.*
From All_objects A
where 1=0
/
ALTER TABLE big_table nologging;

Declare
L_CNT number;
L_rows Number: = &1;
Begin
Insert/*+ Append * *
Into big_table
Select RowNum, a.*
From All_objects A;

L_CNT: = Sql%rowcount;

Commit

while (L_cnt < l_rows)
Loop
Insert/*+ APPEND */into big_table
Select Rownum+l_cnt,
OWNER, object_name, Subobject_name,
OBJECT_ID, data_object_id,
Object_type, CREATED, Last_ddl_time,
TIMESTAMP, STATUS, temporary,
Generated, secondary
From big_table
where RowNum <= l_rows-l_cnt;
L_CNT: = l_cnt + sql%rowcount;
Commit
End Loop;
End
/

ALTER TABLE big_table ADD constraint
BIG_TABLE_PK primary KEY (ID)
/

Begin
Dbms_stats.gather_table_stats
(Ownname => User,
TabName => ' big_table ',
Method_opt => ' For all indexed columns ',
Cascade => TRUE);
End
/

In this test I created 2 million lines of records.

Then create a hash partition table:

CREATE TABLE big_table_hashed nologging
Partition by hash (object_id) partitions 10
As
SELECT * from Big_table;

To create an index on the owner field for two sheets:

Create INDEX big_idx1 on big_table (owner);
Create INDEX big_hash_idx1 on big_table_hashed (owner) local;

Analyze table Big_table compute statistics for table;
Analyze table Big_table Compute statistics for all indexes;
Analyze table Big_table Compute statistics for all indexed columns;
Analyze table big_table_hashed compute statistics for table;
Analyze table big_table_hashed Compute statistics for all indexes;
Analyze table big_table_hashed Compute statistics for all indexed columns;

Please note: This index is not a partition key.

Use the following test script to compare the two efficiencies:

Variable own varchar2 (100);
Declare
Dumy Big_table_hashed%rowtype;
Begin
For I in 1..100
Loop
For x in (select distinct owner from big_table_hashed)
Loop
: Own: = X.owner;
SELECT * Into Dumy from big_table_hashed where owner =: own and rownum = 1;
End Loop;
End Loop;
End
/

Variable own varchar2 (100);
Declare
Dumy Big_table%rowtype;
Begin
For I in 1..100
Loop
For x in (select distinct owner from big_table)
Loop
: Own: = X.owner;
SELECT * Into Dumy from big_table where owner =: own and rownum = 1;
End Loop;
End Loop;
End
/
Use the Tkprof tool to view the results of efficiency comparisons:

Hash partition table:

SELECT *
From
big_table_hashed WHERE OWNER =: B1 and rownum =1

Call count CPU Elapsed disk query current rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2100 0.32 0.40 0 0 0 0
Fetch 2100 5.54 6.56 3720 1154600 0 2100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Total 4201 5.87 6.97 3720 1154600 0 2100

Normal table:

SELECT *
From
big_table WHERE OWNER =: B1 and rownum =1

Call count CPU Elapsed disk query current rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2100 0.39 0.41 0 0 0 0
Fetch 2100 0.09 0.11 0 8402 0 2100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Total 4201 0.48 0.52 0 8402 0 2100

You can see that the query efficiency of the partitioned table is quite poor because the index is not a partition key and you must use the index query

Traverse all partitions, so it is inefficient.

Deletes the index of the original hash partition table,

Create the following partitioning index:

Create INDEX big_hash_idx1 on big_table_hashed (owner)
Global partition by range (owner)
(Partition values less than (' F '),
Partition values less than (' M '),
Partition values less than (' T '),
Partition values less than (MAXVALUE)
);

Run the above test script again, using the Tkprof tool for comparison, the results are as follows:

For a hash partition table:

SELECT *
From
big_table_hashed WHERE OWNER =: B1 and rownum =1

Call count CPU Elapsed disk query current rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2100 0.46 1.58 0 0 0 0
Fetch 2100 0.09 0.14 0 7600 0 2100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Total 4201 0.56 1.72 0 7600 0 2100

Normal table:

SELECT *
From
big_table WHERE OWNER =: B1 and rownum =1

Call count CPU Elapsed disk query current rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2100 0.46 0.38 0 0 0 0
Fetch 2100 0.06 0.11 0 8402 0 2100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Total 4201 0.53 0.50 0 8402 0 2100

This time, the query efficiency of the hash partitioned table is much better because the global index is partitioned, but even so,

There is no normal index for normal tables fast.

Test conclusion: If you are using a partitioned table, if you create an index that is not a partitioning key, it is recommended that you use a global index partition or the query will be inefficient.

Reference documents:

Oracle Efficient Design