| Incorrect cardinalty estimate when using a local non-prefixed unique index [message #609467] |
Fri, 07 March 2014 03:08  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
This is my test case:drop table parts;
create table parts(c1 number,c2 number) partition by range (c2)
(partition p1 values less than (10),partition p2 values less than (20));
create unique index ui on parts(c1,c2) local;
alter table parts add constraint uk primary key(c1);
explain plan for select * from parts where c1=5;
select * from table(dbms_xplan.display);
Never mind whether this is sensible, I am trying to understand what the CBO is doing, I'm not trying to tune anything.
My plan is,PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 104709894
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 52 | 0 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL| | 2 | 52 | 0 (0)| 00:00:01 | 1 | 2 |
|* 2 | INDEX RANGE SCAN | UI | 2 | 52 | 0 (0)| 00:00:01 | 1 | 2 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=5)
Of course Oracle has to search both index partitions, but given that I am using an equality predicate on the primary key I expected the estimated cardinalty to be 1, not 2.
Same result with a global index:
drop table parts;
create table parts(c1 number,c2 number) partition by range (c2)
(partition p1 values less than (10),partition p2 values less than (20));
create unique index ui on parts(c1,c2) global;
alter table parts add constraint uk primary key(c1);
|* 1 | INDEX RANGE SCAN| UI | 2 | 52 | 0 (0)| 00:00:01 |
This is of course largely a thought experiment, though if one had thousands of partitions it could be important. But then, with thousands of partitions a local non-prefixed index would be pretty stupid. Any ideas why the estimate is wrong?
|
|
|
|
|
|
| Re: Incorrect cardinalty estimate when using a local non-prefixed unique index [message #609473 is a reply to message #609471] |
Fri, 07 March 2014 03:23  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
It gets worse:orclz> alter table parts add
2 partition p3 values less than (30),
3 partition p4 values less than (40);
Table altered.
orclz> explain plan for select * from parts where c1=5;
Explained.
orclz> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 104709894
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 78 | 0 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL| | 3 | 78 | 0 (0)| 00:00:01 | 1 | 4 |
|* 2 | INDEX RANGE SCAN | UI | 3 | 78 | 0 (0)| 00:00:01 | 1 | 4 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=5)
14 rows selected.
orclz> |
|
|
|
|
|
| Re: Incorrect cardinalty estimate when using a local non-prefixed unique index [message #609499 is a reply to message #609485] |
Fri, 07 March 2014 12:26 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Well, now I am getting a completely bizarre effect. This is my new test case, it generates a hundred partitions and inserts a row into each to create the segments:drop table parts;
create table parts(c1 number, c2 number)
partition by range (c2) interval (10)
(partition p1 values less than (10));
insert into parts select rownum*10,rownum*10 from dual connect by level <100;
create unique index ui on parts(c1,c2) local;
alter table parts add constraint uk primary key(c1);
Now I look at an exec plan:orclz> explain plan for select * from parts where c1=5;
Explained.
orclz> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
Plan hash value: 1546562866
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 1 (0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| PARTS | 1 | 26 | 1 (0)| 00:00:01 | ROWID | ROWID |
|* 2 | INDEX UNIQUE SCAN | UK | 1 | | 0 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=5)
14 rows selected.
orclz> |
|
|
|
| Re: Incorrect cardinalty estimate when using a local non-prefixed unique index [message #609500 is a reply to message #609499] |
Fri, 07 March 2014 12:37 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Just one more thing: if Oracle does use the local index, the estimates are astonishing. Considering that the index contains all the projected columns and that the index does have statistics (remember that indexes are analyzed automatically on creation) this is potentially a bit worrying:orclz>
orclz> alter index uk invisible;
Index altered.
orclz> explain plan for select * from parts where c1=5;
Explained.
orclz> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 104709894
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3723 | 96798 | 1 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL| | 3723 | 96798 | 1 (0)| 00:00:01 | 1 |1048575|
|* 2 | INDEX RANGE SCAN | UI | 3723 | 96798 | 1 (0)| 00:00:01 | 1 |1048575|
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=5)
14 rows selected.
orclz> select num_rows from user_indexes where index_name='UI';
NUM_ROWS
----------
99
orclz> |
|
|
|
|
|
| Re: Incorrect cardinalty estimate when using a local non-prefixed unique index [message #609912 is a reply to message #609499] |
Thu, 13 March 2014 20:04 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
John Watson wrote on Sat, 08 March 2014 05:26 The constraint is not using my pre-created index. It has created its own, global, index. What the heck is going on to cause that?
I think that's expected from a non-prefixed index. Thinking about it, if you insert a new value of C1 then you don't want Oracle searching every local partition to see if it already exists.
In fact, there is an excellent article here from a quite reputable source describing the behaviour.
What's more weird is that it didn't choose the global index in the earlier example.
I supect the optimizer is a bit lazy. Whilst it will sometimes use contraint information to modify a cardinality estimate from an index, I'm betting it only does so if that index is used to support the unique/PK constraint. This should be fairly safe under normal circumstances: if the index is prefixed then it will do a single partition range scan and get the cardinality right. If the index in not prefixed, then it shouldn't have to mess with the cardinality estimate because that index should never have been chosen in favour of the gloabl unique index.
I'll make a further guess: I reckon that the Unique vs non-unique plans came up with the same cost because the table was empty. I seem to remember reading (but I might be making this up) that 12c only creates index (and table?) segments when it needs them - until then they just sit dormant in the data dictionary. If that's right (it's a big "if", because my memory flakes on this stuff a lot, and I'm too lazy to look it up), then the IO cost of both plans would be 0 and Oracle would select one of them at random.
You could test my theory (and I grant that I could test it too if I weren't so damned lazy) by taking your original test case and inserting a single row in each partition before creating the index. If I'm right, I would expect the opimizer to choose the gloabl index.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
