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:
obclient [TEST]>create table t1(c1 int, c2 int, c3 int);
Query OK, 0 rows affected (0.09 sec)
obclient [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
obclient [TEST]>call dbms_stats.gather_table_stats('TEST','T1');
Query OK, 0 rows affected (0.48 sec)
obclient [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:
-- Q1
obclient [TEST]>explain select * from t1 where c1 = 10;
+------------------------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------------------------+
| =============================================== |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| ----------------------------------------------- |
| |0 |TABLE FULL SCAN|t1 |100 |60 | |
| =============================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t1.c1], [t1.c2], [t1.c3]), filter([t1.c1 = 10]), rowset=256 |
| access([t1.c1], [t1.c2], [t1.c3]), partitions(p0) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([t1.__pk_increment]), range(MIN ; MAX)always true |
+------------------------------------------------------------------------------------+
11 rows in set
Similarly, for query Q2: select * from t1 where c1 = 10 and c2 = 10, when the cardinality_estimation_model is set to partial, the selectivity of the two predicates in Q2 is 1/101 * sqrt(1/10), which is approximately 0.0031. The estimated number of rows is rounded up to 4. This can be verified by the following execution plan:
-- Q2
obclient [TEST]>explain select * from t1 where c1 = 10 and c2 = 10;
+------------------------------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------------------------------+
| =============================================== |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| ----------------------------------------------- |
| |0 |TABLE FULL SCAN|T1 |4 |63 | |
| =============================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([T1.C1], [T1.C2], [T1.C3]), filter([T1.C2 = 10], [T1.C1 = 10]), rowset=16 |
| access([T1.C1], [T1.C2], [T1.C3]), partitions(p0) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false,false], |
| range_key([T1.__pk_increment]), range(MIN ; MAX)always true |
+------------------------------------------------------------------------------------------+
11 rows in set (0.050 sec)
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.