![]() ![]() Let’s now collect some statistics on this table, including a histogram on the TEXT column so that the CBO knows the data is not even distributed and that the vast number of values of TEXT are ‘BOWIE’: SQL> create index bowie_i on bowie(text) OK, let’s now create an index on this column: So all rows have a TEXT value of ‘BOWIE’, except for just the one row which has a value of ‘ZIGGY’. SQL> insert into bowie values (1000001, ‘ZIGGY’) Let’s now add a new row, one that has a different value for the TEXT column: SQL> create table bowie as select rownum id, ‘BOWIE’ text from dual connect by level alter table bowie modify text not null The refusal of the CBO to consider an index with a NOT EQUAL condition can easily be illustrated.įirst, let’s create a table and populate a TEXT column with the same value, ‘BOWIE’: The reason possibly being such queries could be re-written to avoid the use of the NOT EQUAL condition and so its use is still suggesting a large selectivity. MYSQL NOT EQUAL CODEEven if 99.99% of rows match the value in the NOT EQUAL condition and there’s only a handful of remaining rows to actually be retrieved, the code path in the CBO is still followed and indexes are ignored regardless. When the CBO ignores indexes, it ignores indexes in all cases. What if most rows actually have the value specified in the NOT EQUAL condition and the remaining rows constitute a relatively small proportion of the remaining rows ? So the CBO doesn’t even bother trying and ignores all indexes that could potentially be used to retrieve the rows based on the NOT EQUAL condition.īut what if the data isn’t evenly distributed and the NOT EQUAL condition actually retrieves only a relatively small proportion of the rows. MYSQL NOT EQUAL FULLWhy bother going to all the overhead of calculating the cost of using an index to retrieve the vast majority of rows when a Full Table Scan is going to be the cheaper alternative in the vast majority of such cases. Therefore the CBO simply ignores indexes when costing a NOT EQUAL condition. It would generally be more efficient and less costly to simply perform a Full Table Scan if most rows are going to be returned anyways. However, we all know that typically, Oracle will not use an index if generally a relatively “high” percentage of rows are to be selected. In other words, we’re typically interested in the vast majority of possible values when we specify a NOT EQUAL condition. Means we want all the other possible values of TEXT, just not those with the specific value of ‘BOWIE’. We want most values but not if it’s this particular value.įor example, a condition where we state something such as: Typically when we have a condition where we just say NOT EQUAL, we’re basically suggesting we’re interested in the vast majority of possible values with the exception of the value specified in the NOT EQUAL condition. One of these little short cuts worth noting is how the CBO deals with NOT EQUAL (and NOT IN) conditions … However, these short cuts can sometimes be problematic if they’re not recognised and handled appropriately. ![]() ![]() Therefore, there are all sorts of short cuts and assumptions that are coded into the CBO to make its life a little easier. Database performance could be directly impacted if these trade-offs are not managed effectively. So there’s a trade-off between ensuring the CBO makes reasonable decisions while ensuring it makes its decisions in a timely and resource efficient manner. It’s also a vitally important piece of code because not only do the decisions need to be reasonably accurate so that it doesn’t generate inefficient execution plans but it needs to make these decisions in a reasonably efficient manner else it wastes resources and most importantly wastes time while it performs its calculations. The Cost Based Optimizer (CBO) is a rather complex piece of code that has to deal with countless different possible scenarios when trying to determine what the most optimal execution plan might be. Indexes and NOT Equal (Not Now John) AugPosted by Richard Foote in Index Access Path, NOT Equal, Oracle Cost Based Optimizer, Oracle Indexes, Performance Tuning. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |