In a number of posts about indexes I use a demo table with some indexes. Here is how it is created:<\/p>\n
drop table index_demo;\r\n\r\ncreate table index_demo as\r\n with engine as\r\n (select level l from dual connect by level<=1e5)\r\n select l n, round(l\/100) d, mod(l, 100) m, dbms_random.string('l',25) s from engine;\r\n\r\nprompt create indexes\r\ncreate unique index index_demo_u_n on index_demo(n);\r\ncreate index index_demo_n on index_demo(n);\r\ncreate index index_demo_d on index_demo(d);\r\ncreate index index_demo_m on index_demo(m);\r\ncreate index index_demo_s on index_demo(s);\r\n\r\nexec dbms_stats.gather_table_stats('MARTIJN', \r\n 'INDEX_DEMO',\r\n method_opt=>'for all columns size auto',\r\n cascade=>true);\r\n\r\n\r\n-- BAsic table statistics\r\n\r\n NUM_ROWS LAST_ANALYZED BLOCKS EMPTY_BLOCKS\r\n---------- ------------------- ---------- ------------\r\n 100000 2020-11-09 21:54:08 611 0\r\n\r\n-- Basic index statistics\r\nINDEX_NAME UNIQUENES BLEVEL LEAF_BLOCKS LAST_ANALYZED DISTINCT_KEYS CLUSTERING_FACTOR\r\n-------------------- --------- ---------- ----------- ------------------- ------------- -----------------\r\nINDEX_DEMO_S NONUNIQUE 1 516 2020-11-09 21:54:08 100000 99836\r\nINDEX_DEMO_U_N UNIQUE 1 208 2020-11-09 21:54:08 100000 593\r\nINDEX_DEMO_D NONUNIQUE 1 208 2020-11-09 21:54:08 1001 595\r\nINDEX_DEMO_M NONUNIQUE 1 196 2020-11-09 21:54:08 100 59268\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"In a number of posts about indexes I use a demo table with some indexes. Here is how it is created: drop table index_demo; create table index_demo as with engine as (select level l from dual connect by level<=1e5) select l n, round(l\/100) d, mod(l, 100) m, dbms_random.string(‘l’,25) s from engine; prompt create indexes create […]<\/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],"class_list":["post-158","post","type-post","status-publish","format-standard","hentry","category-index","category-oracle","tag-index","tag-oracle"],"_links":{"self":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/158","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=158"}],"version-history":[{"count":7,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/158\/revisions"}],"predecessor-version":[{"id":243,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/158\/revisions\/243"}],"wp:attachment":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=158"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=158"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=158"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}