Selectivity refers to the ratio of the number of rows returned after passing through predicate conditions to the number of rows returned without passing through predicate conditions. The selectivity value ranges from 0 to 1, with a smaller value indicating better selectivity, and a value of 1 representing the worst selectivity. OceanBase Database's optimizer calculates predicate selectivity based on statistical information and selectivity calculation rules before proceeding with row estimation.
Here is an example:
OceanBase(TEST@TEST)>create table t1(c1 int, c2 int, c3 int);
Query OK, 0 rows affected (0.09 sec)
OceanBase(TEST@TEST)>insert into t1 select mod(level,10),mod(level,100),mod(level,1000) from dual connect by level<=1000;
Query OK, 1000 rows affected (0.03 sec)
Records: 1000 Duplicates: 0 Warnings: 0
OceanBase(TEST@TEST)>call dbms_stats.gather_table_stats('TEST','T1');
Query OK, 0 rows affected (0.48 sec)
OceanBase(TEST@TEST)>select NUM_DISTINCT,LOW_VALUE,HIGH_VALUE from sys.dba_tab_col_statistics where owner = 'TEST' and table_name='T1';
+--------------+-----------+------------+
| NUM_DISTINCT | LOW_VALUE | HIGH_VALUE |
+--------------+-----------+------------+
| 10 | 0 | 9 |
| 101 | 0 | 99 |
| 1031 | 0 | 999 |
+--------------+-----------+------------+
3 rows in set (0.19 sec)
After the statistical information is collected, you can query the corresponding view and find that the value of NDV in column c1 is 10. So, for query Q1 select * from t1 where c1 = 10, the selectivity of c1 = 10 is 1/10 = 0.1. Therefore, the estimated number of rows is 1000 * 1/10 = 100. This can be verified through the following execution plan for this query:
OceanBase(TEST@TEST)>explain select * from t1 where c1 = 10\G
*************************** 1. row ***************************
Query Plan: ===================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
-----------------------------------
|0 |TABLE SCAN|T1 |100 |48 |
===================================
Similarly, for query Q2 select * from t1 where c1 = 10 and c2 = 10, since the value of NDV in column c2 is 101, the selectivity of c2 = 10 is 1/101. The optimizer assumes that predicates are independent of each other, so the selectivity of c1 = 10 and c2 = 10 is 1/10 * 1/101 = 1/1010. Therefore, the estimated number of rows is 1000 * 1/1010 = 1. This can be verified through the following execution plan for this query:
OceanBase(TEST@TEST)>explain select * from t1 where c1 = 10 and c2 = 10\G
*************************** 1. row ***************************
Query Plan: ===================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
-----------------------------------
|0 |TABLE SCAN|T1 |1 |61 |
===================================
Currently, selectivity-based row estimation is the main method used by OceanBase Database's optimizer for estimating the number of rows. During plan generation, the predicates of relevant operators are calculated based on statistical information and their selectivity calculation rules to determine the corresponding selectivity. Ultimately, the number of rows is estimated based on the overall selectivity.