Impact of Oracle10g bar chart information on execution plan

We know that Oracle optimizer is missing in Oracle Database 10 GBThe saving mode is changed from the previous choose to the current all_rows.

SQL> show parameter optimizer_mode

Name type value
-----------------------------------------------------------------------------
Optimizer_mode string all_rows

View the data distribution in the name column:

SQL> select name, count (*) from test group by name;

Name count (*)
------------------------------
Msptest 1
Tsptest 1034

There is an index in the name column of the test table:

SQL> select index_name, column_name from user_ind_columns where table_name = 'test ';

Index_name column_name
--------------------------------------------------------------
TT name

If no table analysis is performed, let's take a look at the execution plan:
SQL> set autotrace traceonly
SQL> select * from test where name = 'msptest ';

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = all_rows (cost = 1 card = 4 bytes = 84)
1 0 Table Access (by index rowid) of 'test' (cost = 1 card = 4 BYT
Es = 84)

2 1 index (range scan) of 'TT' (NON-UNIQUE) (cost = 1 card = 2)

Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
4 consistent gets
0 physical reads
0 redo size
564 bytes sent via SQL * Net to client
651 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
1 rows processed

SQL> select * from test where name = 'tsptest ';

1034 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = all_rows (cost = 1 card = 4 bytes = 84)
1 0 Table Access (by index rowid) of 'test' (cost = 1 card = 4 BYT
Es = 84)

2 1 index (range scan) of 'TT' (NON-UNIQUE) (cost = 1 card = 2)

Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
154 consistent gets
0 physical reads
0 redo size
25871 bytes sent via SQL * Net to client
1399 bytes encoded ed via SQL * Net From Client
70 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
1034 rows processed

The name column tsptest has a total of 1034 records. The efficiency of the full table is better than that of the index scan, but the index scan is used here. Obviously, the optimizer has made an incorrect choice.

Just analyze the table and try again:
SQL> analyze table test compute statistics;

Table analyzed.

SQL> select * from test where name = 'msptest ';

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = all_rows (cost = 2 card = 518 bytes = 7
252)

1 0 Table Access (full) of 'test' (cost = 2 card = 518 bytes = 7252)

Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
14 consistent gets
0 physical reads
0 redo size
564 bytes sent via SQL * Net to client
651 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
1 rows processed

A full table scan is performed for only one piece of data returned. This is an unreasonable execution plan. Because it only knows that the name column has two different values, but Oracle does not know how many records each different name has. Oracle by default, the distribution of these data is completely uniform, when name is used as the condition, Oracle considers that 1/2 of the total records will be returned.

SQL> select * from test where name = 'tsptest ';

1034 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = all_rows (cost = 2 card = 518 bytes = 7
252)

1 0 Table Access (full) of 'test' (cost = 2 card = 518 bytes = 7252)

Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
82 consistent gets
0 physical reads
0 redo size
25871 bytes sent via SQL * Net to client
1399 bytes encoded ed via SQL * Net From Client
70 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
1034 rows processed
 

Delete the statistics and make the same query After generating a column chart for table test:

SQL> analyze table test Delete statistics;

Table analyzed.

SQL> analyze table test compute statistics for table for all indexes for all indexed columns;

Table analyzed.

SQL> select * from test where name = 'msptest ';

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = all_rows (cost = 2 card = 1 bytes = 16)
1 0 Table Access (by index rowid) of 'test' (cost = 2 card = 1 BYT
Es = 16)

2 1 index (range scan) of 'TT' (NON-UNIQUE) (cost = 1 card = 1)

Statistics
----------------------------------------------------------
22 recursive cballs
0 dB block gets
5 consistent gets
0 physical reads
0 redo size
564 bytes sent via SQL * Net to client
651 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
1 rows processed

SQL> select * from test where name = 'tsptest ';

1034 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = all_rows (cost = 2 card = 1034 bytes =
16544)

1 0 Table Access (full) of 'test' (cost = 2 card = 1034 bytes = 1654
4)

 

Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
82 consistent gets
0 physical reads
0 redo size
25871 bytes sent via SQL * Net to client
1399 bytes encoded ed via SQL * Net From Client
70 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
1034 rows processed

It can be seen that after a bar chart is generated, Oracle selects an appropriate execution plan based on the actual distribution of data to improve performance.