{"id":193,"date":"2020-11-02T21:40:03","date_gmt":"2020-11-02T21:40:03","guid":{"rendered":"https:\/\/maboc.nl\/?p=193"},"modified":"2020-11-03T16:17:47","modified_gmt":"2020-11-03T16:17:47","slug":"index-full-scan-min-max","status":"publish","type":"post","link":"https:\/\/maboc.nl\/?p=193","title":{"rendered":"INDEX FULL SCAN (MIN\/MAX)"},"content":{"rendered":"

Index index<\/a><\/p>\n

Another way an index can be used to access a piece of data: Index Full Scan (Min\/Max)<\/p>\n

If I’m only interested in the minimal or the maximum value of a column, and there is an index on that column, Oracle can use the Index Full Scan (Min\/Max). This access-method starts at (ofcourse) the root-block of the index and then decends to the leftmost leaf-block (for a minimal value) or the rightmost leaf-block (for a maximal value), retrieves the rowid for the smallest (leftmost) or largest (rightmost) value. And that’s it. It doen not even have to visit the table-blocks, because the value (of the column) is also stored in the index.
\nWhat does this look like in action? Well I use the same data-setup<\/a> I used the last few times. Let’s have a look at some explain plans:<\/p>\n

select min(n) from index_demo\r\n\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------------------------------------\r\nPlan hash value: 3365227721\r\n\r\n---------------------------------------------------------------------------------------------\r\n| Id  | Operation                  | Name           | Rows  | Bytes | Cost (%CPU)| Time     |\r\n---------------------------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT           |                |     1 |     5 |     2   (0)| 00:00:01 |\r\n|   1 |  SORT AGGREGATE            |                |     1 |     5 |            |          |\r\n|   2 |   INDEX FULL SCAN (MIN\/MAX)| INDEX_DEMO_U_N |     1 |     5 |     2   (0)| 00:00:01 |\r\n---------------------------------------------------------------------------------------------\r\n\r\n9 rows selected.\r\n<\/pre>\n
As you can see the Index Full Scan (Min\/Max) is used. Cost is low. Oracle only has to decend the leftmost path of the index. BLEVEL=1 (see data-setup<\/a>). So 1 root\/branch block + 1 leaf block = 2 blocks IO.<\/p>\n
That’s it.<\/p>\n
 <\/p>\n
Let’s see if that holds true for max(n)<\/p>\n
select max(n) from index_demo\r\n\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------------------------------------\r\nPlan hash value: 3365227721\r\n\r\n---------------------------------------------------------------------------------------------\r\n| Id  | Operation                  | Name           | Rows  | Bytes | Cost (%CPU)| Time     |\r\n---------------------------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT           |                |     1 |     5 |     2   (0)| 00:00:01 |\r\n|   1 |  SORT AGGREGATE            |                |     1 |     5 |            |          |\r\n|   2 |   INDEX FULL SCAN (MIN\/MAX)| INDEX_DEMO_U_N |     1 |     5 |     2   (0)| 00:00:01 |\r\n---------------------------------------------------------------------------------------------\r\n\r\n9 rows selected.\r\n\r\n<\/pre>\n
Yes…that works. Ofcourse you can not see it in the explain plan but now Oracle should decend by the rightmost branch-blocks.<\/p>\n
 <\/p>\n
OK….a little more advanced: A query with min(n) and max(n)<\/p>\n
select min(n), max(n) from index_demo\r\n\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------------------------------------\r\nPlan hash value: 3091733289\r\n\r\n---------------------------------------------------------------------------------\r\n| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |\r\n---------------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT   |            |     1 |     5 |   168   (1)| 00:00:01 |\r\n|   1 |  SORT AGGREGATE    |            |     1 |     5 |            |          |\r\n|   2 |   TABLE ACCESS FULL| INDEX_DEMO |   100K|   488K|   168   (1)| 00:00:01 |\r\n---------------------------------------------------------------------------------\r\n\r\n9 rows selected.\r\n\r\n<\/pre>\n
Ouch….that hurts….Oracle will not first go left, and then go right. That would be 4 blocks IO. NO…Oracle decides that it now should perform a Full Table Scan….pain….everywhere I feel pain.<\/p>\n
The index on column n does allow for nulls in the data. Let’s change that<\/p>\n
alter table index_demo modify n not null\r\n\r\nTable altered.\r\n\r\n<\/pre>\n
And try again<\/p>\n
select min(n), max(n) from index_demo\r\n\r\n\r\nExplained.\r\n\r\n\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------------------------------------\r\nPlan hash value: 1609178537\r\n\r\n----------------------------------------------------------------------------------------\r\n| Id  | Operation             | Name           | Rows  | Bytes | Cost (%CPU)| Time     |\r\n----------------------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT      |                |     1 |     5 |    58   (0)| 00:00:01 |\r\n|   1 |  SORT AGGREGATE       |                |     1 |     5 |            |          |\r\n|   2 |   INDEX FAST FULL SCAN| INDEX_DEMO_U_N |   100K|   488K|    58   (0)| 00:00:01 |\r\n----------------------------------------------------------------------------------------\r\n\r\n9 rows selected.\r\n\r\n\r\n<\/pre>\n
Well…it’s somewhat better. Oracle uses the Index Fast Full Scan. That is faster then the FTS (58 vs. 168), but not nearly as fast as what I expected. Now let me first enable NULL values again.<\/p>\n
alter table index_demo modify n null\r\n\r\nTable altered.\r\n\r\n<\/pre>\n
I came up with some alternatives for getting the min(n) and max(n) in one statement. Have a look at the results:<\/p>\n
Union all<\/p>\n
select min(n) \r\nfrom   index_demo \r\n   union all \r\nselect max(n) \r\nfrom   index_demo\r\n\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------------------------------------\r\nPlan hash value: 3364011568\r\n\r\n----------------------------------------------------------------------------------------------\r\n| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |\r\n----------------------------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT            |                |     2 |    10 |     4   (0)| 00:00:01 |\r\n|   1 |  UNION-ALL                  |                |       |       |            |          |\r\n|   2 |   SORT AGGREGATE            |                |     1 |     5 |            |          |\r\n|   3 |    INDEX FULL SCAN (MIN\/MAX)| INDEX_DEMO_U_N |     1 |     5 |     2   (0)| 00:00:01 |\r\n|   4 |   SORT AGGREGATE            |                |     1 |     5 |            |          |\r\n|   5 |    INDEX FULL SCAN (MIN\/MAX)| INDEX_DEMO_U_N |     1 |     5 |     2   (0)| 00:00:01 |\r\n----------------------------------------------------------------------------------------------\r\n\r\n12 rows selected.\r\n<\/pre>\n
I believe these are called Scalar Sub Querys:<\/p>\n
select (\r\n         select min(n) \r\n         from index_demo\r\n       ) min,\r\n       (\r\n         select max(n)\r\n         from   index_demo\r\n       ) max\r\nfrom   dual\r\n\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------------------------------------\r\nPlan hash value: 218373315\r\n\r\n---------------------------------------------------------------------------------------------\r\n| Id  | Operation                  | Name           | Rows  | Bytes | Cost (%CPU)| Time     |\r\n---------------------------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT           |                |     1 |       |     6   (0)| 00:00:01 |\r\n|   1 |  SORT AGGREGATE            |                |     1 |     5 |            |          |\r\n|   2 |   INDEX FULL SCAN (MIN\/MAX)| INDEX_DEMO_U_N |     1 |     5 |     2   (0)| 00:00:01 |\r\n|   3 |  SORT AGGREGATE            |                |     1 |     5 |            |          |\r\n|   4 |   INDEX FULL SCAN (MIN\/MAX)| INDEX_DEMO_U_N |     1 |     5 |     2   (0)| 00:00:01 |\r\n|   5 |  FAST DUAL                 |                |     1 |       |     2   (0)| 00:00:01 |\r\n---------------------------------------------------------------------------------------------\r\n\r\n12 rows selected.\r\n<\/pre>\n
Joining them as inline views<\/p>\n
select min.min, \r\n       max.max\r\nfrom   (\r\n         select min(n) min\r\n         from   index_demo\r\n       ) min,\r\n       (\r\n         select max(n) max \r\n         from   index_demo\r\n       ) max\r\n\r\n\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------------------------------------\r\nPlan hash value: 2289891000\r\n\r\n-----------------------------------------------------------------------------------------------\r\n| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |\r\n-----------------------------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT             |                |     1 |    26 |     4   (0)| 00:00:01 |\r\n|   1 |  NESTED LOOPS                |                |     1 |    26 |     4   (0)| 00:00:01 |\r\n|   2 |   VIEW                       |                |     1 |    13 |     2   (0)| 00:00:01 |\r\n|   3 |    SORT AGGREGATE            |                |     1 |     5 |            |          |\r\n|   4 |     INDEX FULL SCAN (MIN\/MAX)| INDEX_DEMO_U_N |     1 |     5 |     2   (0)| 00:00:01 |\r\n|   5 |   VIEW                       |                |     1 |    13 |     2   (0)| 00:00:01 |\r\n|   6 |    SORT AGGREGATE            |                |     1 |     5 |            |          |\r\n|   7 |     INDEX FULL SCAN (MIN\/MAX)| INDEX_DEMO_U_N |     1 |     5 |     2   (0)| 00:00:01 |\r\n-----------------------------------------------------------------------------------------------\r\n\r\n14 rows selected.\r\n<\/pre>\n
You see…there are ways around the problem….but to me these are workarounds. I would like Oracle to solve it in the engine \ud83d\ude42<\/p>\n
<\/pre>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
Index index Another way an index can be used to access a piece of data: Index Full Scan (Min\/Max) If I’m only interested in the minimal or the maximum value of a column, and there is an index on that column, Oracle can use the Index Full Scan (Min\/Max). This access-method starts at (ofcourse) 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,35,24],"class_list":["post-193","post","type-post","status-publish","format-standard","hentry","category-index","category-oracle","tag-index","tag-index-full-scan-min-max","tag-oracle"],"_links":{"self":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/193","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=193"}],"version-history":[{"count":16,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/193\/revisions"}],"predecessor-version":[{"id":212,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/193\/revisions\/212"}],"wp:attachment":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=193"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=193"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=193"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}