SQL> SELECT /*+ FIRST_ROWS */ a FROM t; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=2 Card=82 Bytes=574) 1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=82 Bytes=574)At 10g, it's been renamed to "Rows" :
SQL> SELECT * FROM t; Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 82 | 574 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| T | 82 | 574 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------This is the number of rows that Oracle expects that step in the plan to evaluate. For a stricter definition of cardinality, see this Wikipedia entry, or see this AskTom thread.
Object Type | Estimated Cardinality |
Heap Table | 82 |
Global Temporary Table | 8168 |
Index-Organized Table | 1 |
System Generated Materialized View (such as the output of the TABLE operator) |
8168 |
SQL> alter session set optimizer_dynamic_sampling = 0; Session altered.Example 1 : A heap table (and the FIRST_ROWS hint to force the CBO, although you won't need this at 10g) :
SQL> SELECT /*+ FIRST_ROWS */ a FROM t1; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=2 Card=82 Bytes=574) 1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=82 Bytes=574)or, at 10g :
SQL> SELECT * FROM t; Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 82 | 574 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| T | 82 | 574 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------Example 2 : an index-organised table (IOT) (note, use of IOTs automatically forces the CBO, so no need for any hint) :
SQL> select * from iot; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=7) 1 0 INDEX (FAST FULL SCAN) OF 'SYS_IOT_TOP_62307' (UNIQUE) (Cost=5 Card=1 Bytes=7)and, again at 10g :
SQL> select * from iot; Execution Plan ---------------------------------------------------------- Plan hash value: 977529495 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 7 | 9 (0)| 00:00:01 | | 1 | INDEX FAST FULL SCAN| SYS_IOT_TOP_13657 | 1 | 7 | 9 (0)| 00:00:01 | ------------------------------------------------------------------------------------------
Estimated Cardinality = CEIL(( Number of Blocks * ( Block Size - Cache Layer )) / Average Row Length )This formula looks like a bizarre way of working out cardinality, but think about it. The formula for working out the size of a table is :
Table Size = Number of rows * Row sizeA bit of algebra later gives us that
Number of rows (cardinality) = Table size / Row sizeBut since not every row is the same size, we can only work out the average row size, making the number of rows an approximation, therefore :
Estimated Cardinality = Size of table in bytes / Average size of a row in bytes.The "size of the table" is the number of blocks used by the table multiplied by the size of a block (i.e. the block size minus any "block overhead" (known as the "cache layer").
SQL> SELECT /*+ FIRST_ROWS */ a 2 FROM t1 3 WHERE rownum <= 4; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=2 Card=4 Bytes=28) 1 0 COUNT (STOPKEY) 2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=82 Bytes=574)Which, although doesn't change the cardinality of the table scan step, DOES affect the cardinality of the SQL statement.