Oracle: How global information and partition information affects execution plans on partitioned tables

In a recent study of the Dbms_stats package, the following is a description of the execution plan based on the difference in granularity values, and we specify several parameters for the table or index statistics through the Dbms_stats package, which is global, Partition and Subpartition. I'll use a number of examples to illustrate how global information and partitioning information affect execution plans.

My environment:

Os:oracle Enterprise Linux 5.5 64Bit

DB type:oracle Restart

DB version:11.2.0.3

– Create an Experimental object partition table and pour data into it

Luocs@maa> CREATE TABLE Ltb3 (ID number,

2 name VARCHAR2 (100),

3 type VARCHAR2 (100)

4) partition by hash (ID)

5 Partitions 5

6/

Table created.

luocs@maa> INSERT INTO LTB3 select object_id, object_name, object_type from All_objects;

21111 rows created.

Luocs@maa> commit;

Commit complete.

Luocs@maa> CREATE index inx_ltb3_local on LTB3 (ID) local;

Index created.

--There is no statistics for the table or table partition after the initialization, but the newly created local index partition collects statistics

Luocs@maa> Col partition_name for A30

Luocs@maa> Select Partition_name, Num_rows, last_analyzed from user_tab_partitions where table_name= ' LTB3 ';

Partition_name num_rows last_analyzed

------------------------------ ---------- -----------------------

Sys_p101

sys_p102

sys_p103

sys_p104

sys_p105

Luocs@maa> Select Partition_name, STATUS, Num_rows, last_analyzed from user_ind_partitions where Index_name= ' INX_ Ltb3_local ';

Partition_name STATUS num_rows last_analyzed

------------------------------ ---------------- ---------- -----------------------

sys_p106 USABLE 2653 19-jan-2013 22:30:32

sys_p107 USABLE 5234 19-jan-2013 22:30:32

sys_p108 USABLE 5414 19-jan-2013 22:30:32

sys_p109 USABLE 5232 19-jan-2013 22:30:32

sys_p110 USABLE 2578 19-jan-2013 22:30:32

Luocs@maa> Select Num_rows, Avg_row_len, last_analyzed from User_tables where table_name= ' LTB3 ';

Num_rows Avg_row_len last_analyzed

---------- ----------- -----------------------

Luocs@maa> Col index_name for A25

Luocs@maa> Select Index_name, Num_rows, STATUS, last_analyzed from user_indexes where table_name= ' LTB3 ';

Index_name num_rows STATUS last_analyzed

------------------------- ---------- ---------------- -----------------------

Inx_ltb3_local 21111 N/a 19-jan-2013 22:30:32

--Manual analysis, global analysis by default

luocs@maa> exec dbms_stats.gather_table_stats (user, ' ltb3 ', cascade=>true);

Pl/sql procedure successfully completed.

Luocs@maa> Select Partition_name, Num_rows, last_analyzed from user_tab_partitions where table_name= ' LTB3 ';

Partition_name num_rows last_analyzed

------------------------------ ---------- -----------------------

sys_p101 2653 19-jan-2013 22:57:13

sys_p102 5234 19-jan-2013 22:57:13

sys_p103 5414 19-jan-2013 22:57:13

sys_p104 5232 19-jan-2013 22:57:13

sys_p105 2578 19-jan-2013 22:57:13

Luocs@maa> Select Partition_name, STATUS, Num_rows, last_analyzed from user_ind_partitions where Index_name= ' INX_ Ltb3_local ';

Partition_name STATUS num_rows last_analyzed

------------------------------ ---------------- ---------- -----------------------

sys_p106 USABLE 2653 19-jan-2013 22:57:13

sys_p107 USABLE 5234 19-jan-2013 22:57:13

sys_p108 USABLE 5414 19-jan-2013 22:57:13

sys_p109 USABLE 5232 19-jan-2013 22:57:13

sys_p110 USABLE 2578 19-jan-2013 22:57:13

Luocs@maa> Select Num_rows, Avg_row_len, last_analyzed from User_tables where table_name= ' LTB3 ';

Num_rows Avg_row_len last_analyzed

---------- ----------- -----------------------

21111 19-jan-2013 22:57:13

Luocs@maa> Select Index_name, Num_rows, STATUS, last_analyzed from user_indexes where table_name= ' LTB3 ';

Index_name num_rows STATUS last_analyzed

------------------------- ---------- ---------------- -----------------------

Inx_ltb3_local 21111 N/a 19-jan-2013 22:57:13