{"id":156,"date":"2020-10-31T13:40:20","date_gmt":"2020-10-31T13:40:20","guid":{"rendered":"https:\/\/maboc.nl\/?p=156"},"modified":"2020-10-31T13:41:54","modified_gmt":"2020-10-31T13:41:54","slug":"index-unique-scan","status":"publish","type":"post","link":"https:\/\/maboc.nl\/?p=156","title":{"rendered":"Index Unique Scan"},"content":{"rendered":"

Main Post<\/a><\/p>\n

The Index Unique Scan is used when one unique piece of information (row) is needed. A primary key would be a perfect example. Since a primary key is 1) Not Null and 2) Unique. The uniqueness can be realized with a index…a unique index. This is actually a normal btree index, with the added rule that any value may appear only once in the index.<\/p>\n

The Index Unique Scan is probably the most cost effective way to get to a piece of data. Oracle will get very effectivly get to the entry in the index, and then knows that it only have to visit the one block that the index-entry is pointing to (because of the uniqueness).<\/p>\n

Let’s see the Index Unique Scan in action. The data used is described here<\/a>.<\/p>\n

Some statistics about the table and index:<\/p>\n

\r\n(2020-10-31 12:10:16) MARTIJN@_DUN > select table_name, \r\n                                            num_rows, \r\n                                            blocks\r\n                                     from   user_tables\r\n                                     where  table_name='INDEX_DEMO';\r\n\r\nTABLE_NAME                  NUM_ROWS     BLOCKS\r\n------------------------- ---------- ----------\r\nINDEX_DEMO                    100000        611\r\n\r\n(2020-10-31 12:16:36) MARTIJN@_DUN > select column_name, \r\n                                            data_type,\r\n                                            num_distinct,\r\n                                            num_nulls\r\n                                     from   user_tab_columns\r\n                                     where  table_name='INDEX_DEMO';\r\n\r\nCOLUMN_NAM DATA_TYPE  NUM_DISTINCT  NUM_NULLS\r\n---------- ---------- ------------ ----------\r\nN          NUMBER           100000          0\r\nD          NUMBER             1001          0\r\nM          NUMBER              100          0\r\nS          VARCHAR2          99840          0\r\n\r\n(2020-10-31 12:34:20) MARTIJN@_DUN > select index_name,\r\n                                            uniqueness,\r\n                                            blevel,\r\n                                            leaf_blocks,\r\n                                            clustering_factor,\r\n                                            distinct_keys,\r\n                                            num_rows\r\n                                     from   user_indexes\r\n                                     where  table_name='INDEX_DEMO';\r\n\r\nINDEX_NAME           UNIQUENES     BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR DISTINCT_KEYS   NUM_ROWS\r\n-------------------- --------- ---------- ----------- ----------------- ------------- ----------\r\nINDEX_DEMO_U_N       UNIQUE             1         208               593        100000     100000\r\nINDEX_DEMO_D         NONUNIQUE          1         208               595          1001     100000\r\nINDEX_DEMO_S         NONUNIQUE          1         516             99837         99840     100000\r\nINDEX_DEMO_M         NONUNIQUE          1         196             59268           100     100000\r\n\r\n(2020-10-31 14:16:08) MARTIJN@_DUN > select index_name, \r\n                                            column_name index_on, \r\n                                            column_position pos \r\n                                     from   user_ind_columns\r\n                                     where  table_name='INDEX_DEMO';\r\n  \r\nINDEX_NAME           INDEX_ON                    POS\r\n-------------------- -------------------- ----------\r\nINDEX_DEMO_M         M                             1\r\nINDEX_DEMO_D         D                             1\r\nINDEX_DEMO_S         S                             1\r\nINDEX_DEMO_U_N       N                             1\r\n<\/pre>\n
I can tell a lot about above statistics…but I won’t \ud83d\ude42
\nIt suffices to say that we can see there is a unique index on column N (index_demo_u_n). This one would be perfectly suitable for an index unique scan.<\/p>\n
Now select a single value out of column N (which has the unique index on it). My expectation is that a Index Unique Scan will be used:<\/p>\n
\r\n(2020-10-31 14:21:27) MARTIJN@_DUN > explain plan for select * from index_demo where n=100;\r\n(2020-10-31 14:23:26) MARTIJN@_DUN > select * from table(dbms_xplan.display());\r\n\r\nPLAN_TABLE_OUTPUT\r\n----------------------------------------------------------------------------------------------------\r\nPlan hash value: 882761573\r\n\r\n----------------------------------------------------------------------------------------------\r\n| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |\r\n----------------------------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT            |                |     1 |    38 |     2   (0)| 00:00:01 |\r\n|   1 |  TABLE ACCESS BY INDEX ROWID| INDEX_DEMO     |     1 |    38 |     2   (0)| 00:00:01 |\r\n|*  2 |   INDEX UNIQUE SCAN         | INDEX_DEMO_U_N |     1 |       |     1   (0)| 00:00:01 |\r\n----------------------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n   2 - access(\"N\"=100)\r\n(2020-10-31 14:23:52) MARTIJN@_DUN >\r\n<\/pre>\n
And my expectation is met: An Index Unique Scan is used to find a pointer to the rowid. And then the table is accessed with the found ROWID. The cost of the whole operation is 2. (I will get back on the calculation of the cost some other (far future) time.<\/p>\n
Just for fun I will drop this index and will search again just to see what cost is when a FTS (FUll Table Scan) is used:<\/p>\n
\r\n(2020-10-31 14:32:00) MARTIJN@_DUN > drop index index_demo_u_n;\r\n(2020-10-31 14:32:08) MARTIJN@_DUN > explain plan for select * from index_demo where n=100;\r\n(2020-10-31 14:32:16) MARTIJN@_DUN > select * from table(dbms_xplan.display());\r\n\r\nPLAN_TABLE_OUTPUT\r\n----------------------------------------------------------------------------------------------------\r\nPlan hash value: 1033748746\r\n\r\n--------------------------------------------------------------------------------\r\n| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |\r\n--------------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT  |            |     1 |    38 |   168   (1)| 00:00:01 |\r\n|*  1 |  TABLE ACCESS FULL| INDEX_DEMO |     1 |    38 |   168   (1)| 00:00:01 |\r\n--------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n   1 - filter(\"N\"=100)\r\n(2020-10-31 14:32:20) MARTIJN@_DUN >\r\n<\/pre>\n
There you have it, th cost for the query is 168, that is 134 times more expensive !!!!<\/p>\n
Let me conclude by saying…yes indeed, an Index Unique Scan is a very cost-effective way to get to your data.<\/p>\n","protected":false},"excerpt":{"rendered":"
Main Post The Index Unique Scan is used when one unique piece of information (row) is needed. A primary key would be a perfect example. Since a primary key is 1) Not Null and 2) Unique. The uniqueness can be realized with a index…a unique index. This is actually a normal btree index, with the […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[32,5],"tags":[8,24,33],"class_list":["post-156","post","type-post","status-publish","format-standard","hentry","category-index","category-oracle","tag-index","tag-oracle","tag-unique-index-scan"],"_links":{"self":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/156","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=156"}],"version-history":[{"count":16,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/156\/revisions"}],"predecessor-version":[{"id":178,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/156\/revisions\/178"}],"wp:attachment":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=156"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=156"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=156"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}