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:
Create table
t1.obclient> CREATE TABLE t1(c1 INT, c2 INT, c3 INT);Insert test data into table
t1.obclient> INSERT INTO t1 SELECT mod(level, 10), mod(level, 100), mod(level, 1000) FROM DUAL CONNECT BY LEVEL <= 1000;The result is as follows:
Query OK, 1000 rows affected Records: 1000 Duplicates: 0 Warnings: 0Collect the statistics information of table
T1of userTEST.obclient> CALL DBMS_STATS.GATHER_TABLE_STATS('TEST','T1');Query the column statistics information of table
T1of userTEST.obclient> SELECT NUM_DISTINCT, LOW_VALUE, HIGH_VALUE FROM SYS.DBA_TAB_COL_STATISTICS WHERE owner = 'TEST' AND table_name = 'T1';The result is as follows:
+--------------+-----------+------------+ | NUM_DISTINCT | LOW_VALUE | HIGH_VALUE | +--------------+-----------+------------+ | 10 | 0 | 9 | | 101 | 0 | 99 | | 1031 | 0 | 999 | +--------------+-----------+------------+ 3 rows in set
Create table
t1.obclient> CREATE TABLE t1(c1 INT, c2 INT, c3 INT);Insert test data into table
t1.Set a variable.
obclient> SET @i = 0;Insert data.
obclient> INSERT INTO t1 (c1, c2, c3) SELECT MOD(@i := @i + 1, 10), MOD(@i, 100), MOD(@i, 1000) FROM information_schema.tables a, information_schema.tables b, information_schema.tables c LIMIT 1000;The result is as follows:
Query OK, 1000 rows affected Records: 1000 Duplicates: 0 Warnings: 0Query the number of rows in table
t1.obclient> SELECT COUNT(*) FROM t1;The result is as follows:
+----------+ | COUNT(*) | +----------+ | 1000 | +----------+ 1 row in set
Collect the statistics information of table
t1in databasedb_test.obclient> CALL DBMS_STATS.GATHER_TABLE_STATS('db_test','t1');Query the column statistics information of table
t1in databasedb_test.obclient> SELECT NUM_DISTINCT, LOW_VALUE, HIGH_VALUE FROM oceanbase.DBA_TAB_COL_STATISTICS WHERE owner = 'db_test' AND table_name = 't1';The result is as follows:
+--------------+-----------+------------+ | NUM_DISTINCT | LOW_VALUE | HIGH_VALUE | +--------------+-----------+------------+ | 10 | 0 | 9 | | 103 | 0 | 99 | | 1012 | 0 | 999 | +--------------+-----------+------------+ 3 rows in set
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> EXPLAIN SELECT * FROM t1 WHERE c1 = 10;
The result is as follows:
+------------------------------------------------------------------------------------+
| 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> EXPLAIN SELECT * FROM t1 WHERE c1 = 10 AND c2 = 10;
The result is as follows:
+------------------------------------------------------------------------------------------+
| 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.