"_ Sort_elimination_cost_ratio", one of the CBO Parameters"

"_ Sort_elimination_cost_ratio" [SQL] create table t1 as select * from dba_objects where object_id is not null; alter table t1 add constraint t1_pk primary key (object_id ); create index t1_ind on t1 (object_type); alter session set optimizer_mode = first_rows; alter session set "_ sort_elimination_cost_ratio" = 0; the preceding script creates a table whose primary key is object_id, the table has a common index object_type. set autotrace traceonlyselect * 2 fr Om t1 3 where object_type = 'table' 4 order by 5 object_id 6; 2060 rows selected. execution Plan -------------------------------------------------------- 0 select statement ptimizer = FIRST_ROWS (Cost = 276 Card = 423 Bytes = 36378) 1 0 table access (by index rowid) OF 't1' (Cost = 276 Card = 423 Bytes = 36378) 2 1 INDEX (full scan) OF 'T1 _ pK' (UNIQUE) (Cost = 23 Card = 10986) the default execution plan follows the index full scan of the primary key index. calculate The resulting cost is 276. select/* + no_index (t1, t1_pk) */2*3 from t1 4 where object_type = 'table' 5 order by 6 object_id 7; 2060 rows selected. execution Plan defaults 0 select statement ptimizer = FIRST_ROWS (Cost = 46 Card = 423 Bytes = 36378) 1 0 SORT (order by) (Cost = 46 Card = 423 Bytes = 36378) 2 1 table access (by index rowid) OF 't1' (Cost = 32 Card = 423 Bytes = 36378) 3 2 INDEX (range scan) OF 'T1 _ IND '(NON-UNIQUE) (Cost = 1 Card = 423) by adding a hint to the statement, the query plan follows the index RANGE scan. at this time, the cost is 46. select 276/46 from dual; 276/46 ---------- 6 the cost of the default query plan (INDEX pk full scan) is six times that of the object_type INDEX query plan. We can set the value of _ sort_elimination_cost_ratio to be less than 6. To be precise, All integers smaller than 6 and greater than 0 can be used. Alter session set "_ sort_elimination_cost_ratio" = 5; Session altered. select 2*3 from t1 4 where object_type = 'table' 5 order by 6 object_id 7; 2060 rows selected. execution Plan defaults 0 select statement ptimizer = FIRST_ROWS (Cost = 46 Card = 423 Bytes = 36378) 1 0 SORT (order by) (Cost = 46 Card = 423 Bytes = 36378) 2 1 table access (by index rowid)' T1 '(Cost = 32 Card = 423 Bytes = 36378) 3 2 INDEX (range scan) OF 'T1 _ IND' (NON-UNIQUE) (Cost = 1 Card = 423) we can see that the default execution plan has gone through the range scan of the object_type index. if the value of _ sort_elimination_cost_ratio is set to greater than 6, the query plan still uses the index full scan of the primary key column. alter session set "_ sort_elimination_cost_ratio" = 6/7 (as long as the value is 6); Session altered. select * from t1where object_type = 'table' order by object_id; 2060 rows selected. execution Plan ----------- --------------------------------------------- 0 select statement ptimizer = FIRST_ROWS (Cost = 276 Card = 423 Bytes = 36378) 1 0 table access (by index rowid) OF 't1' (Cost = 276 Card = 423 Bytes = 36378) 2 1 INDEX (full scan) OF 'T1 _ pK' (UNIQUE) (Cost = 23 Card = 10986) we can see the meaning of _ sort_elimination_cost_ratio. If you do not choose the cost of sorting/the cost of sorting> _ sort_elimination_cost_ratio. Then the execution plan is sorted. If the cost of sorting is not taken or the cost of sorting is <_ sort_elimination_cost_ratio. Then the execution plan is not sorted. _ Sort_elimination_cost_ratio = 0 is a special value, indicating that sorting is reduced at any time, even if the cost of sorting is infinite. If you want to cancel this feature, set this parameter value to 1.