Shulou(Shulou.com)05/31 Report--

This article mainly introduces "the analysis of predicate cross-boundary problem in database". In the daily operation, I believe that many people have doubts about the problem of predicate cross-boundary analysis in the database. The editor has consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts of "predicate cross-boundary problem analysis in database"! Next, please follow the editor to study!

In the development environment, when you encounter a predicate out of bounds problem, simulate this SQL, as shown below, where A_ID is the foreign key of the table test and there is an index

SELECT 1 FROM test WHERE A_ID = 6052138 AND IS_VALID = 1

The amount of data in this table is about 100000.

SQL > select count (*) from test; COUNT (*)-99044

Check out 10053 of the SQL select 1

* * BASE STATISTICAL INFORMATION***Table Stats:: Table: TEST Alias: TEST # Rows: 265702 # Blks: 13157 AvgRowLen: 180.00 ChainCnt: 0.00Index Stats:: Index: IDX_TEST_01 Col#: 2 LVLS: 2 # LB: 1777 # DK: 119696 LB/K: 1.00 DB/K: 1.00 CLUF: 118505.00 Index: IDX_TEST_02 Col#: 3 LVLS: 2 # LB: 2339 # DK: 381 LB/K: 6.00 DB/K: 272.00 CLUF: 103794.00 Index: IDX_TEST_03 Col#: 7 LVLS: 2 # LB: 786 # DK: 2292 LB/K: 1.00 DB/K: 36.00 CLUF: 82804.00 Index: PK_ TEST_ID Col#: 1 LVLS: 2 # LB: 1652 # DK: 265702 LB/K: 1.00 DB/K: 1.00 CLUF: 238444.00Access path analysis for TEST**SINGLE TABLE ACCESS PATH Single Table Cardinality Estimation for TEST [TEST] Column (# 2): A_ID (AvgLen: 6 NDV: 119696 Nulls: 0 Density : 0.000008 Min: 5586857 Max: 5726449 Column (# 60): IS_VALID (AvgLen: 3 NDV: 1 Nulls: 0 Density: 0.000002 Min: 1 Max: 1 Histogram: Freq # Bkts: 1 UncompBkts: 10049 EndPtVals: 1Using prorated density: 0.000002 of col # 2 as selectvity of out-of-range/non-existent value pred Table: TEST Alias: TEST Card: Original: 265702.000000 Rounded: 1 Computed: 0.5 Non Adjusted: 0.50 Access Path: TableScan Cost: 3577.48 Resp: 3577.48 Degree: 0 Cost_io: 3565.00 Cost_cpu: 460365831 Resp_io: 3565.00 Resp_cpu: 460365831 Using prorated density: 0.000002 of col # 2 as selectvity of out-of-range/non-existent value pred Access Path: index (AllEqRange) Index: IDX_TEST_01 resc_io: 4.00 resc_cpu: 30301 ix_sel: 0.000002 ix_sel_with_filters: 0.000002 Cost: 4.00 Resp: 4. 00 Degree: 1 Best:: AccessPath: IndexRange Index: IDX_TEST_01 Cost: 4.00 Degree: 1 Resp: 4.00 Card: 0.50 Bytes: 0**...CBRID: TEST @ SEL$1 TableLookup allocation-Failure -: disabled by parameter

When you see the hint, the column # 2, that is, A_ID, uses 0.000002 as the selection rate for values that are out of range and do not exist, that is, the selection rate is estimated, not actually calculated, in other words, there may be a deviation in the calculation of the execution cost.

Using prorated density: 0.000002 of col # 2 as selectvity of out-of-range/non-existent value pred

Let's look at this table, the actual storage of the A_ID field, to see if there is what he called "out of range."

SQL > select min (A_ID), max (A_ID) from TEST;MIN (A_ID) MAX (A_ID)-6006992 6052756

The above results show that the value range of A_ID is 6006992-6052756, while in trace, the min and max marked A_ID are 5586857-5726449. Therefore, this SQL appears the legendary "predicate out of bounds".

Min: 5586857 Max: 5726449

How did you get min and max in trace? He reads the dba_tab_col_statistics view, which is obtained by conversion.

SQL > select table_name, column_name, utl_raw.cast_to_number (low_value) low, 2 utl_raw.cast_to_number (high_value) hight 3 from dba_tab_col_statistics 4 WHERE table_name='TEST' AND column_name='A_ID' 5 and owner='BISAL' TABLE_NAME COLUMN_NAME LOW HIGHT---- TEST A_ID 5586857 5726449

But fortunately, although there is the problem of predicate out of bounds, there is no deviation in the calculation of the cost value, which does not cause CBO to choose the wrong execution plan. I think it is relatively simple and has something to do with the predicate condition of this SQL. There are only two optional execution plans.

SELECT / * + gather_plan_statistics*/ 1 FROM test WHERE A_ID = 6052138 AND IS_VALID = 1select * from table (dbms_xplan.display_cursor (null,null,'ALLSTATS LAST')) Plan hash value: 1000423460-| Id | | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |-| -- | 0 | SELECT STATEMENT | | 1 | | 2 | 00 TEST 00.01 | 6 | | * 1 | TABLE ACCESS BY INDEX ROWID | TEST | 1 | 1 | 2 | 00 | 00.01 | 6 | | * 2 | INDEX RANGE SCAN | | IDX_TEST_01 | 1 | 1 | 2 | 00GRV 00.01 | 4 |-| -Predicate Information (identified by operation id):-1-filter ("IS_VALID" = 1) 2-access ("A_ID" = 6052138)

Therefore, in this case, although there is a "predicate out of bounds", there will be errors in the calculation of the COST, but it does not affect the choice of the execution plan. If it is a SQL with complex predicates, including the possibility of multiple execution plans, there is a high probability of "predicate crossing the bounds", choosing the wrong execution plan and causing performance problems.

The solution is to re-collect statistics to make the COST calculation closer to reality, avoid using default values, and let CBO make the right choice.

At this point, the study on "the analysis of predicate cross-boundary problems in the database" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.