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.
For 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 statistics for user
TEST's tableT1.obclient> CALL DBMS_STATS.GATHER_TABLE_STATS('TEST','T1');View column statistics for user
TEST's tableT1.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 the 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: 0View the row count of
t1.obclient> SELECT COUNT(*) FROM t1;The result is as follows:
+----------+ | COUNT(*) | +----------+ | 1000 | +----------+ 1 row in set
Collect statistics for database
db_test's tablet1.obclient> CALL DBMS_STATS.GATHER_TABLE_STATS('db_test','t1');View column statistics for database
db_test's tablet1.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 NDV of 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:
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 cardinality_estimation_model is partial, the selectivity of the two predicates in Q2 is 1/101 * sqrt( 1/10), approximately 0.0031. The estimated number of rows is rounded up to 4. This can be verified through 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
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.