A field does not go through Index analysis.

Table t_busi_main_presend2 has an index t_busi_main_presend2_shstatus in the shstatus field. shstatus char (1) defaults '0', with only two values '0' and '1 '. The initial value is '0', the value is '1' after processing, and the number of data records in the table is several hundred thousand. Most of the data records are '1'. Currently, 8 data records are '0 '.

Now, after the index is created (the index type is normal and bitmap is not supported, it will be locked), the following statement turns out to be a full table!

select * from t_busi_main_presend2  where shstatus='0'

So, I:

1. continue executing the analysis table again:

analyze table t_busi_main_presend2 compute statistics;

2. Re-indexing:

alter index T_BUSI_MAIN_PRESEND2_SHSTATUS rebuild;

3. Empty Shar pool or not:

alter system flush shared_pool

If none of these three methods works, delete the table statistics first, and then ignore the analysis table:

 begin  DBMS_STATS.delete_table_stats(ownname => 'YDSOFT_TEST',tabname => 'T_MT');  end;

After I did this myself, I went through the index.

In addition, F5 cannot be used in PL/SQL to see the statistics of the execution plan,

Select * from table (dbms_xplan.display_cursor ('SQL _ id').
SQL _id can be retrieved from V $ SQL or viewed in em.

 

After expert guidance, there are several solutions:

2. General feasible methods:

1. Because the field 1 has many values and the 0 value is very small

① Delete the statistics of the previous index first

② Sampling and collecting statistical information by 1% of the number of tables

After sampling:

If the value is 1, the entire table is used. If the value is 0, the index is used ('ydsoft _ test' is the owner. Note: If you collect 1%, the following estimate_percent => 1 cannot be followed by %. Otherwise

ORA-06550: 2nd rows, 114th columns:
PLS-00103: The symbol "%" appears when one of the following is required:
), * & =-+ </> At in is
MoD remainder not REM <an exponent (**)> <> or! = Or ~ ==<=
<> And or like like2 _ like4 _ likec _ between | Multiset
Member submultiset _
The symbol "%" is ignored.

). This is the correct result:

Parameter description:

'Ydsoft _ test' is the owner

Cascade = true

Estimate_percent => 1 the index is also analyzed. The percentage here is about small, the better. The middle reason is too complicated. I don't quite understand the question of "Bucket" (below.
Method_opt is table association analysis.

The first sentence is the index deletion statistics.

SQL> begin  2  DBMS_STATS.delete_index_stats(ownname => 'YDSOFT_TEST',indname => 'T_BUSI_MAIN_PRESEND2_SHSTATUS');  3  end;  4  /

beginDBMS_STATS.GATHER_TABLE_STATS('YDSOFT_TEST', 'T_BUSI_MAIN_PRESEND2', DEGREE=>3, CASCADE=>TRUE,ESTIMATE_PERCENT=>1 ,no_invalidate => false);end;

2. This method is not practical in the Automatic Execution System, but it can still be used for manual operations.

In the PL/SQL explain Plan window, select the optimizer target (select/Rule/the first line/all rows), and select the index respectively, finally, determine the rule/the first row to take the index. In this case, add/* + rule */to force the rule to take the index.

select  /*+rule*/* from t_busi_main_presend2  where shstatus='0'update /*+rule*/t_busi_main_presend2 set shstatus='1' where shstatus='0' and id<>'504808'

Notes, about sampling volume:

The larger the sample size, because there are too many different values.
If the value is too large, the smaller the value, the better, you can regard it as the space of a plant. If you have three values, you only need two buckets. For example, if the value is 1, accounts for 90% of the total, which is only 10% for one person. Of course, the index is adopted.
Of course, the bucket here does not store data. Only the data size is stored, that is, when the insertion plan is generated, the system first checks whether there is a hint and then checks the table size, looking at the data histogram to determine the scale of the target data is actually the data distribution, such as the proportion of 1 value, data Dispersion
This is sharp with Io.
Related
Histogram, one is the data proportion, and the other is an indicator of Data dispersion.

Height not viewed yet

 

The larger the number, the faster it is. Instead of table analysis, it is a full table sampling scan. This is not a table analysis case.

Select * From tablename sample (95) order by rowid DESC;
Note: The larger the number following the sample statement, the faster the scanning speed.
The preceding statement can quickly scan the last inserted data from a large table, which is very effective. [In fact, this is also based on the results of Table Analysis and filtering]