RE: null values
Date: Wed, 20 Nov 2013 13:09:18 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DCABE0_at_exmbx05.thus.corp>
Having nothing better to do in an airport lounge I decided to have a closer look at this - and realised something that I hadn't consciously noticed before - the optimizer doesn't seem to pay any attention to the number of index entries in an index; note, for example this index description for your null_col index from a 10053 trace file created in 11.2.0.4:
Index: CBO_TAB_1_NULL_COL Col#: 4
LVLS: 1 #LB: 2 #DK: 900 LB/K: 1.00 DB/K: 1.00 CLUF: 900.00
There is no echo of the "num_rows" statistic for the index.
In fact, the only time you would need this statistic would, I think, be for the specific case you've supplied - when you want to count the number of non-null entries without including a predicate of the form "is not null".
Regards
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Martijn Bos [maboc_at_maboc.nl] Sent: 17 November 2013 21:50
To: Oracle-L
Subject: null values
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> _at_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_at_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_at_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_ANALYZEDSQL> 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).
------------------------------ --------------------------- --------- ---------- ----------- ------------- ----------------- ---------- --------------------
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
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--
http://www.freelists.org/webpage/oracle-l
Received on Wed Nov 20 2013 - 14:09:18 CET