Designing an index is not done through \u201ctrial and error\u201d. This kind of strategy seems to doom many engineered indexes to imprecision, explosion, and locking issues. Designing a precise index very often tends to be a \u201ckill two birds with one stone\u201d<\/i> strategy.\u00a0 In this article I am going to show you how to design a single index with which I intend to cover a where clause predicate on column c1<\/i> and avoid a sorting cost of an order by operation on column c2<\/i>.<\/p>\nThe Quiz<\/h2>\n
What is the best index you would have come up with to cover the following query?<\/p>\n
An order by<\/i><\/b> operation is problematic when we want to display a large volume of data in a sorted order. Or when a query generates a small number of ordered rows but is executed a huge number of times. Let\u2019s suppose that we are in one of those kinds of situations and we are asked to optimize the above query with a single index.<\/p>\n Results presented in this article have been done on 11.2.0.3.0 with a 32K block size. I have also conducted the same experiment in 11.2.0.3.0 and 12.0.1.0.1 with 8K block size. Although timing and number of generated buffers are not identical I came to approximately the same conclusion about the efficiency of the designed indexes.<\/p>\n To answer this question we need to observe carefully the predicate part which, in this case, is composed of a where clause on the\u00a0c1<\/i><\/b>\u00a0column coupled with an\u00a0order by<\/i><\/b>\u00a0operation requested on a different column (c2<\/i><\/b>).<\/p>\n Our\u00a0main goal here would be, obviously, to help the CBO achieve a sorted result set without the overhead of obeying the requested\u00a0order by\u00a0<\/i><\/b>operation.<\/p>\n We all know that rows are inserted in a precise order in an index. This is why\u00a0the CBO is able\u00a0to take advantage of these index ordered entries to satisfy the user requested sort.<\/p>\n This is what I am going to illustrate with the following\u00a0example implemented on 11.2.0.3.0<\/p>\n I\u2019ve created a simple table on which I have collected statistics without histograms:<\/p>\n And, as far as I have set the global preference\u00a0approximate_ndv<\/i><\/b>\u00a0to true:<\/p>\n I left the sampling decision in the hands of Oracle:<\/p>\n As such, collecting statistics will be performed very quickly and with a far better\u00a0precision when compared to the sampling I would have manually entered.<\/p>\n You might have already noticed that I managed to create a skewed id2 column on which I haven\u2019t collected histograms:<\/p>\n Put this on hold, we will come back to it later in this article.<\/p>\n Now, I am going to execute the following query and display its corresponding execution plan:<\/p>\n Four immediate points can be derived from the above execution plan:<\/p>\n I have highlighted the E-Rows and the A-Rows information of operation ID = 1<\/b> to show how the CBO has completely messed up its initial estimations. This is mainly due to the skewed column id2 on which I haven\u2019t collected any histograms.<\/p>\n Back to our\u00a0initial goal which is to engineer and index covering the predicate part on column\u00a0id2<\/i><\/b>\u00a0and avoiding the\u00a0order by<\/i><\/b>\u00a0operation on column\u00a0id1<\/i><\/b>. Here below the first index I came up with:<\/p>\n In passing, spot how I\u2019ve managed to name my index (t1_ind_id1_fbi<\/i>) so that it immediately indicates its type (a function-based index because of the desc<\/i> clause I have added in its definition).<\/p>\n Re-executing the original query gives us the following execution plan:<\/p>\n This execution plan highlights six points:<\/p>\n This was a typical example on how we can design an index so that we can achieve two goals with one index:<\/p>\n However, I was still wondering what if I had created the following normal b-tree index instead of the above Will this new index allow the CBO to avoid the order by operation?<\/p>\n Yes it does. The b-tree index ( The above engineered two types of composite indexes paved the way to the following question:<\/p>\n Since both indexes, the function-based index (T1_IND_ID1_FBI) and the b-tree one (T1_IND_ID1_NI), achieved the initial desire of discarding and sorting with approximatively the same amount of time and energy (in this case of course), which type of index would you prefer?<\/i><\/p>\n I have two reasons to prefer the normal b-tree index in this particular situation. The first one is that with the b-tree index I can eventually \u201ckill three<\/span> birds with one stone\u201d. <\/i>A very plausible situation is the presence of a foreign key (id2, id1) on the t1 table. In this case, with the b-tree index I have created above I would have been able to:<\/p>\n The function-based index ( The second reason to prefer the b-tree index to the function-based one (in this case) is due to the hidden column which goes hand-in-hand with the function-based index creation:<\/p>\n I wrote above that the function-based index (id2, id1 desc) doesn\u2019t start with the plausible foreign key column (id2, id1. Here is the proof:<\/p>\n Thanks to the desc<\/i> clause in the index definition, the internal index columns expression looks now as (id2<\/i>, SYS_NC00005$) instead of (id2, id1 desc)<\/i>. And this is the main reason why this index doesn\u2019t cover the foreign key (id2, id1)<\/i> lock and deadlock threat.<\/p>\n To take advantage of the function-based index, we very often need an extra step to collect statistics on the hidden column by means of the following call to the dbms_stats package:<\/p>\n Having exposed my b-tree index preference motivations (in this particular case), I still have a supplementary question:<\/p>\n What, if instead of a composite index on (id2, id1) I created only one single column index on (id2)? \u00a0Will the CBO be able to use the primary key index on (id1) in its attempt to avoid the order by operation and the second one on (id2) in its attempt to cover the where clause on id2?<\/i><\/p>\n Note how the CBO is unable to combine simultaneously the t1_ind_id2 (id2) index to filter t1 table using the predicate id2 = 42 and the t1_pk<\/i> primary key index to avoid the sort operation. Using this pair of indexes, the initial high number of Logical I\/O (36232) is back.<\/p>\n Even though the CBO visited the primary index and walks its leaf block in the key order (INDEX FULL SCAN ) without any sorting, it has no way to merge the ordered rowids of operation (ID =2) with those filtered from the t1 table access by rowid at operation (ID = 1).<\/p>\n There exists, nevertheless, a way to avoid the order by operation with the pair of indexes we have at our disposal. We need to re-write the query to give the CBO another possible path as shown below:<\/p>\n I built two “with subqueries<\/i>“, one to get the ordered rowids by id1 ( And here is the execution plan I got in 11.2.0.3.0:<\/p>\n The two ”with subqueries<\/i>\u201d have been hash joined together using their corresponding rowid as a join column. The probe table gives the rowids satisfying the id2 = 42<\/i> where clause using the corresponding index on (id2) while the build table gives the rowids ordered by id1 using the primary key index on (id1). The Logical I\/O using this refactored query dropped to 1518 from 32232 original buffer gets.<\/p>\n When observing operation 5 in the above plan I asked myself: Why, despite the CBO wrongly estimating to buffer 500,000 rows at this step, does it nevertheless decide to use an index range scan to honour this operation? In passing with both 11.2.0.3.0 and 12.0.1.1 8K block size, the same operation has been honored via a table full scan. I kept this question open for another investigation.<\/p>\n Finally, whether this query rewrite represents a good strategy or not depends on your specific case and what you will be confronted to. I have to confess that there are cases (particularly under first_rows<\/i><\/b> mode) where the CBO prefers an INDEX FULL SCAN access to avoid an order by operation whatever the cost of that INDEX FULL SCAN operation is.<\/p>\n Because of the data skew of the id2 column, the CBO has been wrongly estimating, in almost all the above execution plans whatever the index it has used, the cardinality of the operations that filter on the id2 column. One solution would be to gather adequate histograms on this column. But being in an index design path, I would prefer to tackle this issue using a virtual column which I will be adequately indexing. Something like this:<\/p>\n I added a virtual column on t1 table which will hold the value 42 when it is filled up with 42 and null when filled up with a different value:<\/p>\n The next step is to create a new composite index using the virtual column coupled with the id1 column on which we want a sort:<\/p>\n And to collect fresh statistics for the virtual column and its associated index:<\/p>\n Now that I have put the scenario in place I am going to realize it<\/p>\n Note how, with the help of the virtual column and its corresponding index, the CBO is doing a perfect estimation which ultimately leads almost always to an optimal execution plan.<\/p>\n I have managed to create the What if in this case, I opted for a single index on derived_id2 column? Will I take advantage of the small size of that index?<\/i><\/p>\n There is a reduction of the number of consistent gets using this new index that is worth checking before taking the final index design decision.<\/p>\n Engineering an index should be dictated first by the query predicate part (where clause, group by and order by). Look carefully to the column you will use as the leading edge of the index. They should be the ones on which an equality predicate is applied. You should also have a \u201ckill two birds with one stone\u201d<\/i> design strategy as far as with one index you can cover multiple queries, avoid redundant indexes<\/a> and cover the foreign key lock threat. Do not forget the benefit an indexed virtual column could have on helping the CBO make good guesses (estimations) and producing attractive small indexes.<\/p>\n If a switch in the column order is still able to guaranty the precision and the use of the index then start your index with the column having the lowest number of distinct values. As such you can efficiently compress your index and give a CBO an extra possible path represented by the index skip scan<\/b>.<\/p>\n","protected":false},"excerpt":{"rendered":" Designing an index is not done through \u201ctrial and error\u201d. This kind of strategy seems to doom many engineered indexes to imprecision, explosion, and locking issues. Designing a precise index very often tends to be a \u201ckill two birds with one stone\u201d strategy.\u00a0 In this article I am going to show you how to design a single index with whic…<\/p>\n","protected":false},"author":305852,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[4263],"coauthors":[],"class_list":["post-73184","post","type-post","status-publish","format-standard","hentry","category-oracle-databases","tag-index"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73184","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/305852"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73184"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73184\/revisions"}],"predecessor-version":[{"id":91672,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73184\/revisions\/91672"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73184"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73184"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73184"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73184"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}select * from t1 where c1 =:a order by c2;<\/code><\/p>\nA word of caution<\/h3>\n
The answer<\/h2>\n
create table t1\r\nas select\r\n rownum\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 id1,\r\n case\r\n when mod(rownum,1000)= 0\r\n then 42\r\n else 1\r\n end\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 id2,\r\n lpad(rownum,10,'0')\u00a0\u00a0\u00a0\u00a0\u00a0 small_vc,\r\n rpad('x',1000)\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0padding\r\nfrom dual\r\nconnect by level <= 1e6;\r\n\r\nalter table t1 add constraint t1_pk primary key (id1);\r\n\r\nBEGIN\r\n dbms_stats.gather_table_stats\r\n (user\r\n ,'t1'\r\n ,method_opt \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0=> 'FOR ALL COLUMNS SIZE 1'\r\n ,estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE\r\n ,cascade\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 => true);\r\nEND;\r\n\/<\/pre>\n\n
(method_opt => 'FOR ALL COLUMNS SIZE 1')<\/code><\/li>\n<\/ul>\nSQL> select dbms_stats.get_prefs ('approximate_ndv') ndv from dual;\r\nNDV\r\n----\r\nTRUE<\/pre>\n\n
(estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE)<\/code><\/li>\n<\/ul>\nSQL> select id2, count(1) from t1 group by id2;\r\n\r\n ID2\u00a0\u00a0 COUNT(1) \r\n---------- ----------\r\n 1\u00a0\u00a0\u00a0\u00a0 999000\r\n 42\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1000\r\n\r\nSQL> select\r\n column_name\r\n ,num_distinct\r\n ,histogram\r\n from user_tab_col_statistics\r\n where table_name = 'T1'\r\n and column_name\u00a0 = 'ID2';\r\n\r\nCOLUMN_NAME\u00a0 NUM_DISTINCT HISTOGRAM\r\n------------ ------------ ----------\r\nID2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NONE<\/pre>\n
SQL> select * from t1 where id2 = 42 order by id1 desc;\r\n-----------------------------------------------------------------------------------------------\r\n| Id\u00a0 | Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Name\u00a0 | Starts | E-Rows | A-Rows |\u00a0\u00a0 A-Time\u00a0\u00a0 | Buffers |\r\n----------------------------------------------------------------------------------------------\r\n|\u00a0\u00a0 0 | SELECT STATEMENT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0 1000 |00:00:00.52 |\u00a0\u00a0 36232 |\r\n|*\u00a0 1 |\u00a0 TABLE ACCESS BY INDEX ROWID| T1\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0 500K<\/span>|\u00a0\u00a0 1000 <\/span>|00:00:00.52 |\u00a0\u00a0 36232 |\r\n|\u00a0\u00a0 2 |\u00a0\u00a0 INDEX FULL SCAN DESCENDING| T1_PK |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0 1000K|\u00a0\u00a0 1000K|00:00:00.15 |\u00a0\u00a0\u00a0\u00a0 515 |\r\n-----------------------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n 1 - filter(\"ID2\"=42)<\/pre>\n\n
SQL> create index t1_ind_id1_fbi on t1(id2,id1 desc);<\/pre>\n
SQL> select index_name, index_type\r\n from user_indexes\r\n where table_name = 'T1';\r\n\r\nINDEX_NAME\u00a0\u00a0\u00a0\u00a0 INDEX_TYPE\r\n-------------- ---------------------\r\nT1_IND_ID1_FBI\u00a0FUNCTION-BASED NORMAL\r\nT1_PK \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NORMAL<\/pre>\n
SQL> select * from t1 where id2 = 42 order by id1 desc;\r\n\r\n--------------------------------------------------------------------------------------------------------\r\n| Id\u00a0 | Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Starts | E-Rows | A-Rows |\u00a0\u00a0 A-Time\u00a0\u00a0 | Buffers |\r\n--------------------------------------------------------------------------------------------------------\r\n|\u00a0\u00a0 0 | SELECT STATEMENT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0 1000 |00:00:00.01 |\u00a0\u00a0\u00a0 1005 |\r\n|\u00a0\u00a0 1 |\u00a0 TABLE ACCESS BY INDEX ROWID| T1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0 500K<\/span>|\u00a0\u00a0 1000<\/span> |00:00:00.01 |\u00a0\u00a0\u00a0 1005<\/span> |\r\n|*\u00a0 2 |\u00a0\u00a0 INDEX RANGE SCAN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | T1_IND_ID1_FBI |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0 500K<\/span>|\u00a0\u00a0 1000<\/span> |00:00:00.01 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5 |\r\n--------------------------------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n 2 - access(\"ID2\"=42)<\/pre>\n\n
\n
T1_IND_ID1_FBI<\/code> function-based one?<\/p>\nSQL> create index t1_ind_id1_ni on t1(id2, id1); -- there is no desc for id1 column\r\n -- \u201cni\u201d stands for normal index<\/pre>\n
SQL> select * from t1 where id2 = 42 order by id1 desc;\r\n\r\n--------------------------------------------------------------------------------------------------------\r\n| Id\u00a0 | Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Starts | E-Rows | A-Rows |\u00a0\u00a0 A-Time\u00a0\u00a0 | Buffers |\r\n--------------------------------------------------------------------------------------------------------\r\n|\u00a0\u00a0 0 | SELECT STATEMENT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0 1000 |00:00:00.01 |\u00a0\u00a0\u00a0 1005 |\r\n|\u00a0\u00a0 1 |\u00a0 TABLE ACCESS BY INDEX ROWID | T1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0 500K|\u00a0\u00a0 1000 |00:00:00.01 |\u00a0\u00a0\u00a0 1005 |\r\n|*\u00a0 2 |\u00a0\u00a0 INDEX RANGE SCAN DESCENDING<\/span>| T1_IND_ID1_NI |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0 500K|\u00a0\u00a0 1000 |00:00:00.01 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5 |\r\n--------------------------------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n2 - access(\"ID2\"=42)<\/pre>\nT1_IND_ID1_NI<\/code>) is also able to allow the CBO to avoid the order by operation by scanning the index in descending order as shown by operation 2 in the immediate preceding execution plan. This has been made possible thanks to the existence of two pointers in the index leaf blocks (blocks where the real indexed values are located). Each leaf block (except the extreme ones) has a pointer backward and a pointer forward to its immediate neighbour block so that Oracle can traverse the index structure in both directions, ascending and descending.<\/p>\n\n
id2, id1 desc<\/code>) is not able to cover the t1 table foreign key threat<\/a> because it doesn\u2019t start with the couple of foreign key columns (id2,id1).<\/p>\nSQL> SELECT\r\n column_name\r\n FROM\r\n user_tab_cols\r\n WHERE table_name = 'T1'\r\n AND HIDDEN_COLUMN = 'YES';\r\n\r\nCOLUMN_NAME\r\n-------------\r\nSYS_NC00005$<\/pre>\n
SQL> select index_name, column_name, column_position, descend\r\n from user_ind_columns\r\n where index_name = 'T1_IND_ID1_FBI';\r\n\r\nINDEX_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COLUMN_NAME\u00a0\u00a0\u00a0\u00a0 COLUMN_POSITION DESCEND\r\n----------------- --------------- --------------- ----------\r\nT1_IND_ID1_FBI\u00a0\u00a0 \u00a0ID2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 ASC\r\nT1_IND_ID1_FBI\u00a0\u00a0\u00a0 SYS_NC00005$\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2 DESC<\/pre>\n
BEGIN\r\n dbms_stats.gather_table_stats\r\n (user\r\n ,'t1'\r\n ,method_opt \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0=> 'FOR ALL HIDDEN<\/span> COLUMNS SIZE 1'\r\n ,estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE\r\n ,cascade\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 => true);\r\n END;\r\n\/<\/pre>\nSQL> drop index T1_IND_ID1_NI;\r\nSQL> drop index T1_IND_ID1_FBI;\r\n\r\nSQL> create index t1_ind_id2 on t1(id2);\r\n\r\nSQL> select index_name\r\n ,column_name\r\n ,column_position\r\n from\r\n user_ind_columns\r\n where table_name = 'T1';\r\n\r\nINDEX_NAME COLUMN_NAM COLUMN_POSITION\r\n---------- ---------- ---------------\r\nT1_PK\u00a0\u00a0\u00a0\u00a0\u00a0 ID1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\r\nT1_IND_ID2 ID2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\r\n\r\nSQL> select * from t1 where id2 = 42 order by id1 desc;\r\n\r\n-----------------------------------------------------------------------------------------------\r\n| Id\u00a0 | Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Name\u00a0 | Starts | E-Rows | A-Rows |\u00a0\u00a0 A-Time\u00a0\u00a0 | Buffers |\r\n-----------------------------------------------------------------------------------------------\r\n|\u00a0\u00a0 0 | SELECT STATEMENT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0 1000 |00:00:00.51 |\u00a0\u00a0 36232 |\r\n|*\u00a0 1 |\u00a0 TABLE ACCESS BY INDEX ROWID| T1\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0 500K|\u00a0\u00a0 1000 |00:00:00.51 |\u00a0\u00a0 36232 |\r\n|\u00a0\u00a0 2 |\u00a0\u00a0 INDEX FULL SCAN DESCENDING| T1_PK |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0 1000K|\u00a0\u00a0 1000K|00:00:00.15 |\u00a0\u00a0\u00a0\u00a0 515 |\r\n-----------------------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n1 - filter(\"ID2\"=42)<\/pre>\n
SQL> with\r\n got_my_id2_rowid as\r\n (select\u00a0rowid, t1.* from t1 where id2 = 42)\r\n ,got_my_id1_rowid as\r\n (select \/*+ no_merge *\/ rowid from t1 order by id1)\r\n select\r\n t1.id1\r\n ,t1.id2\r\n ,t1.small_vc\r\n ,t1.padding\r\n from\r\n got_my_id2_rowid t1\r\n , got_my_id1_rowid t2\r\n where\r\n t1.rowid = t2.rowid;<\/pre>\n
got_my_id1_rowid<\/code>) and the other \u00a0to get the t1 table rowids to satisfy the predicate part where id2=42 (got_my_id2_rowid). I have also added the\u00a0no_merge<\/i>\u00a0hint in the\u00a0got_my_id1_rowid<\/code><\/i>\u00a0factored subquery so that it will not be merged with the remaining one.<\/p>\n-----------------------------------------------------------------------------------------------------\r\n| Id\u00a0 | Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Starts | E-Rows | A-Rows |\u00a0\u00a0 A-Time\u00a0\u00a0 | Buffers |\r\n-----------------------------------------------------------------------------------------------------\r\n|\u00a0\u00a0 0 | SELECT STATEMENT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0 1000 |00:00:00.66 |\u00a0\u00a0\u00a0 1518<\/span> |\r\n|*\u00a0 1 |\u00a0 HASH JOIN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0 500K|\u00a0\u00a0 1000 |00:00:00.66 |\u00a0\u00a0\u00a0 1518 |\r\n| \u00a0\u00a02 |\u00a0\u00a0 VIEW\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0 1000K|\u00a0\u00a0 1000K|00:00:00.22 |\u00a0\u00a0\u00a0\u00a0 513 |\r\n|\u00a0\u00a0 3 |\u00a0\u00a0\u00a0 INDEX FULL SCAN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | T1_PK\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0 1000K|\u00a0\u00a0 1000K|00:00:00.10 |\u00a0\u00a0\u00a0\u00a0 513 |\r\n|\u00a0\u00a0 4 |\u00a0\u00a0 TABLE ACCESS BY INDEX ROWID| T1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0 \u00a0\u00a0\u00a01 |\u00a0\u00a0\u00a0 500K|\u00a0\u00a0 1000 |00:00:00.01 |\u00a0\u00a0\u00a0 1005 |\r\n|*\u00a0 5 |\u00a0\u00a0\u00a0 INDEX RANGE SCAN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | T1_IND_ID2 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0 500K|\u00a0\u00a0 1000 |00:00:00.01 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5 |\r\n-----------------------------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n\r\n---------------------------------------------------\r\n1 - access(\"T1\".ROWID=\"T2\".ROWID)\r\n5 - access(\"ID2\"=42)<\/pre>\nTackling the CBO wrong estimation<\/h3>\n
SQL> alter table t1 add derived_id2 number generated always as (case when id2 = 42 then 42 else null end) virtual;<\/code><\/p>\nSQL> desc t1\r\n Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Null?\u00a0\u00a0\u00a0 Type\r\n ------------------------------- -------- -------------------\r\n1\u00a0\u00a0\u00a0\u00a0\u00a0 ID1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL NUMBER\r\n2\u00a0\u00a0\u00a0\u00a0\u00a0 ID2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NUMBER\r\n3\u00a0\u00a0\u00a0\u00a0\u00a0 SMALL_VC\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VARCHAR2(10 CHAR)\r\n4\u00a0\u00a0\u00a0\u00a0\u00a0 PADDING\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VARCHAR2(1000 CHAR)\r\n5\u00a0\u00a0\u00a0\u00a0\u00a0 DERIVED_ID2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NUMBER<\/pre>\n
SQL> create index t1_derived_id2_ind on t1(derived_id2, id1) compress;<\/code><\/p>\nSQL> begin\r\n dbms_stats.gather_table_stats\r\n (user,\r\n 't1',\r\n method_opt => 'for columns derived_id2 size 1',\r\n cascade => true,\r\n no_invalidate => false\r\n );\r\n end;\r\n\/<\/pre>\n
SQL> select * from t1 where derived_id2 = 42 order by id1 desc;\r\n\r\n------------------------------------------------------------------------------------------------\r\n| Id\u00a0 | Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Starts | E-Rows | A-Rows | Buffers |\r\n------------------------------------------------------------------------------------------------\r\n|\u00a0\u00a0 0 | SELECT STATEMENT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0 1000 |\u00a0\u00a0\u00a0 1004 |\r\n|\u00a0\u00a0 1 |\u00a0 TABLE ACCESS BY INDEX ROWID | T1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0 1000<\/span> |\u00a0\u00a0 1000<\/span> |\u00a0\u00a0\u00a0 1004 |\r\n|*\u00a0 2 |\u00a0\u00a0 INDEX RANGE SCAN DESCENDING| T1_DERIVED_ID2_IND |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0 1000<\/span> |\u00a0\u00a0 1000<\/span> |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4<\/span> |\r\n------------------------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n 2 - access(\"DERIVED_ID2\"=42)<\/pre>\nThe last observation<\/h2>\n
DERIVED_ID2<\/code> virtual column so that it holds only my ID2<\/code> critical value (42) which I needed to cover. All other id2 values are seen by this new virtual column as null. However when I\u2019ve created the composite T1_DERIVED_ID2_IND(DERIVED_ID2,ID1)<\/code> index, the \u201cnull\u201d id2 values have been inserted in this index because they are protected by the id1 not null column. The immediate consequence is that I lost the advantage of not indexing the huge number of \u201cnull\u201d DERIVED_ID2<\/code> column. This paved again the path for my last question:<\/p>\nSQL> create index t1_derived_id2_ind_bis on t1(derived_id2);\r\n\r\nSQL> SELECT\r\n index_name,\r\n blevel,\r\n leaf_blocks,\r\n distinct_keys,\r\n num_rows,\r\n clustering_factor\r\n FROM user_indexes\r\n WHERE table_name = 'T1';\r\n\r\nINDEX_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0BLEVEL\u00a0 \u00a0LEAF_BLOCKS \u00a0DISTINCT_KEYS NUM_ROWS\u00a0 \u00a0\u00a0CLUSTERING_FACTOR\r\n--------------------- -------- ---------- ----------- ---------- ----------------\r\nT1_PK\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 512\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 1000000\u00a0\u00a0\u00a0\u00a0\u00a0 1000000\u00a0\u00a0\u00a0 \u00a0 35715\r\nT1_DERIVED_ID2_IND\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 785\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1000000\u00a0\u00a0\u00a0\u00a0\u00a0 1000000\u00a0\u00a0\u00a0\u00a0 36715\r\nT1_DERIVED_ID2_IND_BIS 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 1000\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 1000\r\n\r\nSQL> alter index T1_DERIVED_ID2_IND invisible;\r\n\r\nSQL> select * from t1 where derived_id2 = 42 order by id1 desc;\r\n\r\n---------------------------------------------------------------------------------------------------\r\n| Id\u00a0 | Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Starts | E-Rows | A-Rows | Buffers|\r\n---------------------------------------------------------------------------------------------------\r\n|\u00a0\u00a0 0 | SELECT STATEMENT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0 1000 |\u00a0\u00a0\u00a0 1001|\r\n|\u00a0\u00a0 1 |\u00a0 SORT ORDER BY\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0 1000 |\u00a0\u00a0 1000 |\u00a0\u00a0\u00a0 1001|\r\n|\u00a0\u00a0 2 |\u00a0\u00a0 TABLE ACCESS BY INDEX ROWID| T1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0 1000 |\u00a0\u00a0 1000 |\u00a0 \u00a0\u00a01001|\r\n|*\u00a0 3 |\u00a0\u00a0\u00a0 INDEX RANGE SCAN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | T1_DERIVED_ID2_IND_BIS |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0 1000 |\u00a0\u00a0 1000 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1<\/span>|\r\n---------------------------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n3 - access(\"DERIVED_ID2\"=42)<\/pre>\nConclusion<\/h2>\n