Hi List,
On a play-around sytem I do the following:
(This is a 11.2.0.4 RDBMS on linux x86 (32 bits))
drop table cbo_tab_1;
create table cbo_tab_1 (id number, sca number(10,0), clu number(10,0), null_col
number(10,0), filler char(500)) pctfree 99 pctused 1;
-- Following statement makes sure that ther actually are nulls in column
null_col
insert into cbo_tab_1 select level, mod(level,10), round(level/100),
decode(mod(level, 10), 0, null, level), 'filler' from dual connect by
level<=1000;
commit;
create unique index CBO_TAB_1_ID on CBO_TAB_1(id);
-- create index cbo_tab_1_id on cbo_tab_1(id);
create index cbo_tab_1_sca on cbo_tab_1(sca);
create index cbo_tab_1_clu on cbo_tab_1(clu);
create index cbo_tab_1_null_col on cbo_tab_1(null_col);
exec dbms_stats.gather_table_stats('MARTIJN','CBO_TAB_1', block_sample=>false,
cascade=>true, method_opt=>'for all columns size 1');
alter system flush shared_pool; --Since I try it a lot I need it to parse over
and over again.
explain plan for select count(null_col) from cbo_tab_1; --HERE IS THE SQL OF
WHICH I DON'T UNDERSTAND THE EXPLAIN PLAN
SQL> @xpln -- A script I created just to get a little more understanding of
explaining statement.
ID OPERATION OBJECT
ALIAS CARDINALITY COST OPTIMIZER
BYTES
---------- ----------------------------------------
---------------------------------------- -------------------- -----------
---------- --------------- ----------
--- 0 SELECT STATEMENT()
1 2 ALL_ROWS
4
--P 1 SORT(AGGREGATE)
1
4
--P 2 INDEX(FAST FULL SCAN) (INDEX)
MARTIJN.CBO_TAB_1_NULL_COL CBO_TAB_1@SEL$1 1000 2
ANALYZED 4000
ID PROJECTION
------
---------------------------------------------------------------------------
1 (#keys=0) COUNT("NULL_COL")[22]
2 "NULL_COL"[NUMBER,22]
SQL>
(if you find that suspect, I also added a more documented way to display
explain plans)
SQL> select * from table(dbms_xplan.display(format=>'all'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2862360477
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 2
(0)| 00:02:01 |
| 1 | SORT AGGREGATE | | 1 | 4 |
| |
| 2 | INDEX FAST FULL SCAN| CBO_TAB_1_NULL_COL | 1000 | 4000 | 2
(0)| 00:02:01 |
--------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / CBO_TAB_1@SEL$1
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT("NULL_COL")[22]
2 - "NULL_COL"[NUMBER,22]
SQL>
Now the thing that is troubling me is the cardinality estimate at id 2 (1000).
I would say that the CBO could esitmate 900 rows.
Where does the CBO get's that idea of 1000?
SQL> select TABLE_NAME, STATUS, PCT_FREE, PCT_USED, NUM_ROWS, BLOCKS,
EMPTY_BLOCKS, LAST_ANALYZED, AVG_ROW_LEN
from user_tables
where table_name='CBO_TAB_1';
TABLE_NAME STATUS PCT_FREE PCT_USED NUM_ROWS BLOCKS
EMPTY_BLOCKS LAST_ANALYZED AVG_ROW_LEN
--------------- -------- ---------- ---------- ---------- ----------
------------ -------------------- -----------
CBO_TAB_1 VALID 99 1000 1000
0 2013-NOV-17 22:27:10 515
SQL> select index_name, index_type, uniqueness, blevel, leaf_blocks,
distinct_keys, clustering_factor, num_rows, last_analyzed
from user_indexes
where table_name='CBO_TAB_1';
INDEX_NAME INDEX_TYPE UNIQUENES BLEVEL
LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS LAST_ANALYZED
------------------------------ --------------------------- --------- ----------
----------- ------------- ----------------- ---------- --------------------
CBO_TAB_1_ID NORMAL UNIQUE 1
2 1000 1000 1000 2013-NOV-17 22:27:10
CBO_TAB_1_SCA NORMAL NONUNIQUE 1
2 10 1000 1000 2013-NOV-17 22:27:10
CBO_TAB_1_CLU NORMAL NONUNIQUE 1
2 11 1000 1000 2013-NOV-17 22:27:10
CBO_TAB_1_NULL_COL NORMAL NONUNIQUE 1
2 900 900 900 2013-NOV-17 22:27:10
SQL>
As far as I'm concerned/understand the CBO chooses the right index
(CBO_TAB_1_NULL_COL). CBO_TAB_1_NULL_COL has 900 num rows, since the other 100
rows are null.
I would say that in this specific case it would be easy for the CBO to estimate
900 rows since it only visits this index (all information is in the index).
Now I'm thinking that I might oversee something very simple (or that i'm flat
out wrong), so would anyone be so kind as to point me in the right direction
for some guidance, or (better yet :-)) explain where my thinking is wrong.
Best Regards,
Martijn
Attachment:
pgp7Kr6LB62ZC.pgp
Description: PGP signature
