\uc815\ud655\ud55c\u00a0\ud1b5\uacc4\uc815\ubcf4\u00a0\uc218\uc9d1\uc740\u00a0Optimal Plan\uc744\u00a0\uc0dd\uc131\ud558\uae30\u00a0\uc704\ud55c\u00a0\ud544\uc218\u00a0\uc694\uac74\uc774\ub2e4. \uc624\ub77c\ud074\u00a0DBMS\uc5d0\uc11c\ub294\u00a0Cost-Based Opmizer \uc758\u00a0\uc644\ubcbd\ud55c\u00a0\uae30\ub2a5\uc744\u00a0\uc704\ud574\u00a0\ub2e4\uc591\ud55c\u00a0\ud1b5\uacc4\uc815\ubcf4\u00a0\uc218\uc9d1\ubc29\ubc95\uc744\u00a0\uad6c\uc0ac\ud560\u00a0\uc218\u00a0\uc788\ub2e4.<\/p>\n
\ub300\ud45c\uc801\uc778\u00a0\uc608\uac00\u00a0Histogram\uc73c\ub85c\u00a0Skew\ub41c\u00a0Data\uc5d0\u00a0\ub300\ud55c\u00a0\uc815\ud655\ud55c\u00a0\ud1b5\uacc4\uc218\uc9d1\uc774\u00a0\uac00\ub2a5\ud558\ub2e4.<\/p>\n
10g\uc5d0\uc11c\ub294\u00a0\ud1b5\uacc4\uc815\ubcf4\u00a0\uc218\uc9d1\uc744\u00a0\uc790\ub3d9\ud654\u00a0framework\uc744\u00a0\uc81c\uacf5\ud558\uc5ec\u00a0DBA\ub4e4\uc5d0\uac8c\u00a0\ud3b8\uc758\uc131\uc744\u00a0\uc81c\uacf5\ud558\uae30\ub3c4\u00a0\ud588\ub2e4.<\/p>\n
Oracle Database 11g\uc5d0\uc11c\ub294\u00a0\uc0c8\ub85c\uc6b4\u00a0\ud1b5\uacc4\uc815\ubcf4\u00a0\uc218\uc9d1\ubc29\uc2dd\uc744\u00a0\ucd94\uac00\ud558\uc5ec\u00a0\uace0\uac1d\uc758\u00a0\ub2e4\ud5a5\ud55c\u00a0Data \ud2b9\uc131\uc744\u00a0\ubc18\uc601\ud558\uac8c\u00a0\ub418\uc5c8\uace0\u00a0\ubcf4\ub2e4\u00a0\uc815\ud655\ud55c\u00a0\ud1b5\uacc4\uc815\ubcf4\ub97c\u00a0\uae30\ubc18\uc73c\ub85c\u00a0\ubcf4\ub2e4\u00a0\uc815\ud655\ud55c\u00a0Optimal Plan\uc758\u00a0\uc0dd\uc131\uc774\u00a0\uac00\ub2a5\ud558\uac8c\u00a0\ub418\uc5c8\ub2e4.<\/p>\n
<\/p>\n
\uc0c8\ub86d\uac8c\u00a0\ucd94\uac00\ub41c\u00a0\ud1b5\uacc4\uc815\ubcf4\u00a0\uc218\uc9d1\ubc29\uc2dd\uc740\u00a0\u201cExtended Statistics\u201d \uc640\u00a0\u201cFunction-Based Statistics\u201d \uc774\ub2e4.<\/strong><\/span><\/p>\n <\/p>\n Extended Statistics: \ud55c\u00a0\ud14c\uc774\ube14\u00a0\ub0b4\uc758\u00a0\uc5ec\ub7ec\u00a0column\uc744\u00a0\ud558\ub098\uc758\u00a0group\uc73c\ub85c\u00a0\ubb36\uc5b4\u00a0\ud1b5\uacc4\uc815\ubcf4\ub97c\u00a0\uc218\uc9d1\ud558\ub418\u00a0column\uc758 \uc0c1\ud638\u00a0\uc5f0\uad00\uc131\uae4c\uc9c0\u00a0\ud30c\uc545\ud558\uc5ec\u00a0\uc218\uc9d1\ud55c\ub2e4.<\/strong><\/span><\/p>\n Function-Based Statistics: \ud2b9\uc815\u00a0column\uc5d0\u00a0\uc801\uc6a9\ub41c\u00a0\ud568\uc218\ub97c\u00a0\uc801\uc6a9\ud55c\u00a0\uacb0\uacfc\uc5d0\u00a0\ub300\ud55c\u00a0\ud1b5\uacc4\uc815\ubcf4\ub97c\u00a0\uc218\uc9d1\ud55c\ub2e4.<\/strong><\/span><\/p>\n <\/p>\n \uc774\u00a0\uc7a5\uc5d0\uc11c\ub294\u00a011g \uc758\u00a0\ud5a5\uc0c1\ub41c\u00a0\ud1b5\uacc4\uc218\uc9d1\u00a0\ubc29\ubc95\uc744\u00a0\uc124\uba85\ud558\uace0\u00a0optimizer\uac00\u00a0\uc774\ub7ec\ud55c\u00a0\ud1b5\uacc4\uc815\ubcf4\ub97c\u00a0\uc5b4\ub5bb\uac8c\u00a0\ud65c\uc6a9\ud558\ub294\uc9c0\ub97c\u00a0\ud14c\uc2a4\ud2b8\ub97c\u00a0\ud1b5\ud558\uc5ec\u00a0\ud655\uc778\ud574\u00a0\ubcf4\ub3c4\ub85d\u00a0\ud55c\ub2e4.<\/p>\n <\/p>\n Extended Statistics<\/b><\/span><\/p>\n \uc608\ub97c\ub4e4\uc5b4, SH.customers\ub77c\ub294\u00a0\ud14c\uc774\ube14\uc5d0\u00a0cust_state_province \ub77c\ub294\u00a0\uceec\ub7fc\uacfc\u00a0country_id \ub77c\ub294\u00a0column\uc774\u00a0\uc788\ub2e4. \uceec\ub7fc\u00a0cust_state_province\uc758\u00a0selectivity\ub294\u00a00.005 \uc774\uace0\u00a0country_id selectivity\ub294\u00a00.1 \uc774\ub2e4.<\/p>\n cust_state_province \uc640\u00a0country_id\uac00\u00a0where\uc870\uac74\uc808\uc758\u00a0equality\uc640\u00a0and \uc870\uac74\uc73c\ub85c\u00a0\uc870\ud68c\ub41c\ub2e4\uba74\u00a0selectivity\ub294\u00a00.0005(=0.005 X 0.1) \uc774\ub2e4.<\/p>\n \ud558\uc9c0\ub9cc\u00a0cust_state_province\uac00\u00a0country_id\uc744\u00a0\uacb0\uc815\ud558\ub294\u00a0\uad00\uacc4\ub97c\u00a0\uac16\ub294\ub2e4\uba74, \uc774\ub4e4\uc758\u00a0selectivty\ub294\u00a00.0005\uac00\u00a0\uc544\ub2c8\ub2e4\u00a0.<\/p>\n \uc608\ub97c\ub4e4\uc5b4, \ubbf8\uad6d\uc774\ub77c\ub294\u00a0country_id(52780) \uc640\u00a0\uce98\ub9ac\ud3ec\ub2c8\uc544\ub77c\ub294\u00a0cust_state_province(\u2018CA\u2019) \ub97c\u00a0\uc870\ud68c\ud558\uba74,<\/p>\n <\/p>\n <\/p>\n \uc704\uc640 \uac19\uc740 \uacb0\uacfc\uac00 \ub098\uc628\ub2e4.\u00a0 \uc65c\ub0d0\ud558\uba74, cust_state_province\uac00 \uacb0\uc815\ub418\uba74 country_id\ub294 \uc790\ub3d9\uc801\uc73c\ub85c \uacb0\uc815\ub418\uae30 \ub54c\ubb38\uc774\ub2e4. \ub530\ub77c\uc11c 11g Optinizer\ub294 column\uac04\uc758 \uc0c1\ud638\uc5f0\uad00\uc131\uc5d0 \ub300\ud55c \ud1b5\uac8c\uc815\ubcf4\ub97c \uc774\uc6a9\ud558\uc5ec Optimal Plan\uc744 \uc138\uc6b8 \uc218 \uc788\ub2e4.<\/p>\n <\/p>\n Extended Statistics \uc7a5\uc810<\/strong><\/p>\n \uc0c1\ud638 \uc5f0\uad00\uc131\uc774 \uc788\ub294 column\ub4e4\uc774 AND, Equality \uc870\uac74\uc73c\ub85c \uc870\ud68c\ub420 \uacbd\uc6b0, \uc815\ud655\ud55c Selectivity \uc608\uce21 Extended Statistics<\/strong>\ub97c \uc774\uc6a9\ud558\uc5ec \ucd5c\uc801\uc758 SQL Plan \uc0dd\uc131 Application \uc131\ub2a5\ud5a5\uc0c1\uc5d0 \uae30\uc5ec<\/p>\n <\/p>\n <\/p>\n Extended Statistics Commands<\/strong><\/p>\n Column Group \uc0dd\uc131<\/p>\n Create_extended_statistics \ud568\uc218\ub97c \uc0ac\uc6a9\ud558\uc5ec column group \uc744 \uc0dd\uc131\ud55c\ub2e4. \uc774 \ud568\uc218\uc758 input parameter \ub294 \ub2e4\uc74c\uc758 \ud45c\uc640 \uac19\ub2e4.<\/p>\n <\/p>\n \uc608\ub97c\ub4e4\uc5b4, SH.customers\uc758 \uceec\ub7fc cust_state_province \uc640 country_id \ub97c group\uc73c\ub85c \ubb36\uc73c\ub824\uba74:<\/p>\n <\/p>\n declare Column Grop \uc870\ud68c<\/strong><\/p>\n Column group \uc774\ub984\uc740 show_extended_stats_name \ud568\uc218\ub97c \uc774\uc6a9\ud55c\ub2e4.<\/p>\n select sys.dbms_stats.show_extended_stats_name(‘sh’,’customers’,'(cust_state_province,country_id)’) col_group_name from dual; Column Group \uc81c\uac70<\/strong><\/p>\n Column group\uc744 \uc81c\uac70\ud558\uae30 \uc704\ud574\uc11c\ub294 drop_extended_stats \ud568\uc218\ub97c \uc774\uc6a9\ud55c\ub2e4.<\/p>\n <\/p>\n exec dbms_stats.drop_extended_stats(‘sh’,’customers’,'(cust_state_province,country_id)’);<\/p>\n Column Group \ubaa8\ub2c8\ud130\ub9c1<\/strong><\/p>\n Column group(multicolumn statistics)\uc758 \uc815\ubcf4\ub294 user_stats_extensions \ub97c \uc870\ud68c\ud558\uc5ec \uc5bb\ub294\ub2e4.<\/p>\n Select extension_name, extension from user_stat_extensions where table_name=’CUSTOMERS’;<\/p>\n EXTENSION_NAME EXTENSION Column group\uc5d0 \ub300\ud55c distinct value\uc640 histogram\uc774 \uc0ac\uc6a9\ub418\uc5c8\ub294\uc9c0\ub97c \uc870\ud68c\ud558\ub294 \ubc29\ubc95\uc740 \ub2e4\uc74c\uacfc \uac19\ub2e4.<\/p>\n select e.extension col_group, t.num_distinct, t.histogram COL_GROUP NUM_DISTINCT HISTOGRAM <\/p>\n Function-Based Statistics(Expression Statistics)<\/strong><\/span><\/p>\n 11g\uc5d0\uc11c\ub294 where \uc870\uac74\uc808\uc758 column\uc5d0 \ud568\uc218\uac00 \uc801\uc6a9\ub418\uc5c8\ub2e4 \ud558\ub354\ub77c\ub3c4, function-based \ud1b5\uacc4\uc815\ubcf4\ub97c \uc218\uc9d1\ud568\uc73c\ub85c\uc368 \uc815\ud655\ud55c selectivity\ub97c \uad6c\ud560 \uc218 \uc788\ub2e4.<\/p>\n \uc608\ub97c\ub4e4\uc5b4, where \uc808\uc5d0 lower(cust_state_province)=\u2019ca\u2019 \uc870\uac74\uc774 \uc788\uc744 \ub54c, cust_state_province\uc758 selectivity\uac00 0.005 \ub77c\uba74 lower(cust_state_province) selectivity\ub294 0.005\uac00 \uc544\ub2d0 \uac83\uc774\ub2e4.<\/p>\n \ud568\uc218\uac00 \uc801\uc6a9\ub41c column\uc758 \ubcf4\ub2e4 \uc7a5\ud655\ud55c selectivity\ub97c \uad6c\ud558\uae30 \uc704\ud574 Function-Based \ud1b5\uacc4\uc815\ubcf4\ub97c \uc218\uc9d1\ud55c\ub2e4.<\/p>\n Function_Based Statistics Commands<\/p>\n Expression Statistics \ubaa8\ub2c8\ud130\ub9c1<\/strong><\/p>\n Expression Statistics\uc758 \uc815\ubcf4\ub294 user_stats_extentions \ub97c \uc870\ud68c\ud558\uc5ec \uc5bb\ub294\ub2e4.<\/p>\n select e.extension col_group, t.num_distinct, t.histogram COL_GROUP NUM_DISTINCT HISTOGRAM Expression Statistics \uc81c\uac70<\/strong><\/p>\n Expression statistics \ub294 drop_extended_stats\ub97c \uc774\uc6a9\ud558\uc5ec \uc81c\uac70\ud55c\ub2e4.<\/p>\n exec dbms_stats.drop_extended_stats(null,’customers’,'(lower(country_id))’);<\/p>\n Selectivity & Cardinality<\/strong><\/p>\n \ucc38\uace0\ub85c SQL Plan\uc744 \uacb0\uc815\ud558\ub294\ub370 \uc0ac\uc6a9\ub418\ub294 selectivity\uc640 cardinality<\/span> \uac1c\ub150\uc5d0 \ub300\ud574 \uc124\uba85\ud55c\ub2e4.<\/p>\n <\/p>\n \u00a0SELECTIVITY<\/strong><\/span><\/em><\/p>\n \uc804\uccb4 \ub808\ucf54\ub4dc\uc218\uc640 \ud574\ub2f9 \ub808\ucf54\ub4dc \uc218\uc758 \ube44\uc728\uc774 selectivity\uc774\ub2e4.<\/p>\n Selectivity = \ud574\ub2f9 \ub808\ucf54\ub4dc \uc218 \/\uc804\uccb4 \ub808\ucf54\ub4dc\uc218<\/strong><\/p>\n \uc0ac\uc6d0\ud14c\uc774\ube14 1000 \uac1c \ub808\ud3ec\ub4dc \uc911 \ubd80\uc11c=\u2019\uc778\uc0ac\ud300\u2019 \uc774 \uadf8\uc911 10 \uac1c \ub77c\uba74 \uc120\ud0dd\ub3c4 \ub294 0.01 \uc774\ub2e4.<\/p>\n Selectivity\ub294 \ud589 \uc9d1\ud569\uc73c\ub85c\ubd80\ud130\uc758 \ud589\ub4e4\uc758 \uc77c\ubd80\ubd84\uc744 \ub098\ud0c0\ub0b8\ub2e4.<\/p>\n \ud589 \uc9d1\ud569\uc740 \uae30\ubcf8 \ud14c\uc774\ube14, \ubdf0, \uc870\uc778\uc774\ub098 GROUP BY\uc758 \uacb0\uacfc\uc77c \uc218\ub3c4 \uc788\uace0 \ud589 \uc9d1\ud569\uc5d0\uc11c \ud589\ub4e4\uc758 \ud2b9\uc815 \uc218\ub97c \uac78\ub7ec\ub0b4\ub294 \ud544\ud130\uc758 \uc5ed\ud560\uc744 \ud55c\ub2e4.<\/p>\n \uceec\ub7fc\uc5d0 \ub300\ud55c \ud788\uc2a4\ud1a0\uadf8\ub7a8(histogram)\uc774 \uc0ac\uc6a9\uac00\ub2a5 \ud558\ub2e4\uba74, \uc720\uc77c \uac12 \ub300\uc2e0 \uadf8\uac83\uc744 \uc0ac\uc6a9\ud55c\ub2e4.<\/p>\n \ud788\uc2a4\ud1a0\uadf8\ub7a8\uc740 \uceec\ub7fc\uc758 \ub2e4\ub978 \uac12\uc758 \ubd84\uc0b0\ub3c4\ub97c \uc800\uc7a5\ud574 \ub193\ub294\ub2e4. \ubd84\ud3ec\uac00 \ubd88\uade0\ud615\uc778 \uceec\ub7fc\uc5d0 \ud788\uc2a4\ud1a0\uadf8\ub7a8\uc744 \uc0ac\uc6a9\ud558\uba74, CBO \uac00 \ub354 \ub0ae\uc740 selectivity \ub97c \uacb0\uc815\ud558\ub294\ub370 \uc0c1\ub2f9\ud558\uac8c \ub3c4\uc6c0\uc744 \uc900\ub2e4.<\/p>\n <\/p>\n CARDINALITY<\/span><\/strong><\/em><\/p>\n Cardinality \ub294 \ud589 \uc9d1\ud569\uc5d0\uc11c \ud589\uc758 \uc218\ub97c \ub098\ud0c0\ub0b8\ub2e4. \uc5ec\uae30\uc5d0 \ud589 \uc9d1\ud569\uc740 \uae30\ubcf8 \ud14c\uc774\ube14, \ubdf0, \uc870\uc778\uc774\ub098 GROUP BY \uc758 \uacb0\uacfc\uc77c \uc218\ub3c4 \uc788\ub2e4.<\/p>\n \uc5b4\ub5a4 \ucffc\ub9ac\uc758 \uc218\ud589\uacb0\uacfc\ub85c \ub098\uc624\ub294 ROWS \ub85c cardinality \uc758 \uacc4\uc0b0\uc740<\/p>\n Cardinality = \uc804\uccb4\ub85c\uc6b0\uc218 * Selectivity<\/strong><\/p>\n \ub85c \uacb0\uc815\ub41c\ub2e4. \uc608\ub97c\ub4e4\uc5b4 \uc804\uccb4\ub85c\uc6b0\uc218\uac00 1000 \uc774\uace0 selecivity \uac00 0.01 \uc774\ub2e4\uba74 cardinality \ub294 10(=1000 * 0.01) \uc774\ub2e4.<\/p>\n <\/p>\n \uc608\uc81c<\/strong><\/span><\/p>\n SH.customers_obe\ub77c\ub294 \ud14c\uc774\ube14\uc5d0 \uc0c1\ud638\uc5f0\uad00\uc131\uc774 \uc788\ub294 \ub450\uac1c\uc758 column(country_id, cust_state_province)\uc744 equality \uc870\uac74\uc73c\ub85c \uc870\ud68c\ud560 \uacbd\uc6b0, \uc5b4\ub5a4 \ud1b5\uacc4\uc815\ubcf4\ub97c \uc81c\uacf5\ud574\uc57c optimizer\uac00 \uc815\ud655\ud55c cardinality\ub97c \uc608\uc0c1\ud558\ub294\uc9c0\ub97c \ud14c\uc2a4\ud2b8\ud574 \ubcf8\ub2e4.<\/p>\n Determining Single Column Statistics<\/p>\n \ud14c\uc774\ube14 SH.customers_obe\uc758 country_id \uac00 \u2018US\u2019\uc774\uace0 cust_state_province\uac00 \u2018CA\u2019\uc774 \uacbd\uc6b0\uc758 \uc2e4\uc81c cardinaltiry(\uc870\ud68c \uac74\uc218) \ud655\uc778\ud55c\ub2e4.<\/p>\n <\/p>\n <\/p>\n check_cardinality.sql<\/p>\n <\/p>\n check_cardinality \uc758 \uc870\ud68c\uacb0\uacfc\ub294 \ub2e4\uc74c\uacfc \uac19\uace0 \uc2e4\uc81c \uc870\ud68c\uac74\uc218\ub294 29\uc784\uc744 \ud655\uc778\ud55c\ub2e4.<\/p>\n <\/p>\n gather_stats.sql<\/p>\n <\/p>\n SH.customers_obe\uc758 \ud1b5\uacc4\uc815\ubcf4\ub97c \uc218\uc9d1\ud55c\ub2e4.<\/p>\n <\/p>\n review_stats.sql<\/p>\n <\/p>\n \ud1b5\uacc4\uc815\ubcf4\ub97c \uc870\ud68c\ud558\uc5ec cust_state_province \uc640 country_id\uc758 distinct value\ub97c \ud655\uc778\ud55c\ub2e4.<\/p>\n <\/p>\n explain_plan.sql<\/p>\n <\/p>\n Plan\uc744 \uc0dd\uc131\ud558\uc5ec optimizer\uac00 \uc608\uc0c1\ud558\ub294 \uc870\ud68c\uac74\uc218\ub97c \ud655\uc778\ud55c\ub2e4.<\/p>\n Opmizer\ub294 \uc870\ud68c\uac74\uc218\uac00 1\ub85c \uc608\uc0c1\ud558\uace0 \uc788\ub2e4. \uc774 \uc608\uc0c1\uac12\uc774 \ud2c0\ub9ac\ub2e4\ub294 \uac83\uc740 \ubbf8\ub9ac \uc870\ud68c\ud574 \ubcf8 \uc2e4\uc81c\uc870\ud68c\uac74\uc218\ub85c\ubd80\ud130 \uc54c \uc218 \uc788\ub2e4. \ub530\ub77c\uc11c opimizer\uac00 \uc880 \ub354 \uc815\ud655\ud55c \uc870\ud68c\uac74\uc218\ub97c \uc608\uc0c1\ud560 \uc218 \uc788\ub3c4\ub85d \ub354 \uc88b\uc740 \ud1b5\uacc4\uc815\ubcf4\ub97c \uc0dd\uc131\ud560 \ud544\uc694\uac00 \uc788\ub2e4.<\/p>\n Gathering Histograms on Skewed Columns<\/strong><\/p>\n 11g \uc774\uc804\uae4c\uc9c0\ub294 \uc0c1\ud638\uc5f0\uad00\ub41c column\ub4e4\uc758 selectivity\ub97c \uc815\ud655\ud788 \uc0dd\uc124\ud558\ub294 \ubc29\ubc95\uc740 \uc5c6\uc5c8\ub2e4.<\/p>\n \ub2e4\ub9cc, skew\ub41c data\uc5d0 \ub300\ud55c \uc815\ud655\ud55c selecivity\ub294 histogram\uc744 \ud1b5\ud558\uc5ec \uacc4\uc0b0\ud560 \uc218 \uc788\uc5c8\uc73c\ubbc0\ub85c SH.customers_obe\uc5d0 histogram\uc744 \uc0dd\uc131\ud574\uc11c opmizer\uac00 \uc5b4\ub5a4 \uc608\uc0c1\uc744 \ud558\ub294\uc9c0 \ud14c\uc2a4\ud2b8 \ud574 \ubcf8\ub2e4.<\/p>\n <\/p>\n gather_histogram.sql<\/p>\n <\/p>\n Histogram\uc744 \uc0dd\uc131\ud55c \ud6c4\uc758 \ud1b5\uacc4\uc815\ubcf4\ub97c \uc870\ud68c\ud55c\ub2e4.<\/p>\n <\/p>\n <\/p>\n SQL Plan\uc744 \uc0dd\uc131\ud558\uc5ec optimizer\uac00 \uc608\uc0c1\ud558\ub294 \uc870\ud68c\uac74\uc218\ub97c \uc870\uc0ac\ud55c\ub2e4.<\/p>\n <\/p>\n Histogram\uc744 \uc0dd\uc131\ud55c \uc774\ud6c4 \uc774\uc804\ubcf4\ub2e4\ub294 \uc870\ud68c\uac74\uc218\uc758 \uc608\uc0c1\uce58\uac00 \uc88b\uc544\uc9c0\uae34 \ud588\uc9c0\ub9cc opimizer\ub294 \uc5ec\uc804\ud788 \uc0c1\ud638\uc5f0\uad00\uc131\uc774 \uc788\ub294 column\ub4e4\uc758 \uad00\uacc4\ub294 \uc54c\uc9c0 \ubabb\ud558\ubbc0\ub85c \uc815\ud655\ud55c \uc870\ud68c\uac74\uc218\ub97c \uc608\uc0c1\ud558\uc9c0 \ubabb\ud588\ub2e4.<\/p>\n Creating Extended Statistics to Correlate Columns<\/strong><\/p>\n 11g\uc758 \uc2e0\uae30\ub2a5\uc778 extended statistics\ub97c \uc218\uc9d1\ud558\uc5ec optimizer\uac00 \uceec\ub7fc\uac04 \uc0c1\ud638\uad00\uacc4\ub97c \uc54c\uac8c \ud55c\ud6c4, \uc870\ud654\uac74\uc218\ub97c \uc5b4\ub5bb\uac8c \uc608\uc0c1\ud558\ub294\uc9c0 \ud14c\uc2a4\ud2b8\ud574 \ubcf8\ub2e4.<\/p>\n create_extended_stats.sql<\/p>\n <\/p>\n Country_id \uc640 cust_state_province\ub97c group\uc73c\ub85c \ud558\ub294 extended \ud1b5\uacc4\uc815\ubcf4\ub97c \uc218\uc9d1\ud55c\ub2e4.<\/p>\n <\/p>\n Histogram\uc744 \uc0dd\uc131\ud55c\ub2e4.<\/p>\n <\/p>\n SQL Plan\uc744 \uc0dd\uc131\ud558\uc5ec optimizer\uac00 \uc608\uc0c1\ud558\ub294 \uc870\ud68c\uac74\uc218\ub97c \uc870\uc0ac\ud55c\ub2e4.<\/p>\n \ud14c\uc2a4\ud2b8 \uacb0\uacfc\uc640 \uac19\uc774 optimizer\uac00 \uc815\ud655\ud55c \uc870\ud68c\uac74\uc218\ub97c \uc608\uc0c1\ud558\ub294 \uac83\uc744 \ud655\uc778\ud560 \uc218 \uc788\ub2e4.<\/p>\n <\/p>\n Creating Extended Statistics for a Function Used on a Column<\/strong><\/p>\n Column\uc5d0 \ud568\uc218\uac00 \uc801\uc6a9\ub41c \uacbd\uc6b0\uc5d0\ub3c4 extended statistics\ub97c \uc0dd\uc131\ud558\uc5ec optimizer\uac00 \uc815\ud655\ud55c cardinality\ub97c \uc608\uc0c1\ud558\ub294\ub97c \ud14c\uc2a4\ud2b8 \ud55c\ub2e4. \uc6b0\uc120, country_id\uc5d0 lower \ud568\uc218\ub97c \uc801\uc6a9\ud55c \uacbd\uc6b0\uc758 \uc2e4\uc81c cardinality\ub97c \uc870\ud68c\ud55c\ub2e4.<\/p>\n get_count_ower.sql<\/p>\n <\/p>\n \uc870\ud68c\uac74\uc218\uac00 165 \uc774\ub2e4.<\/p>\n <\/p>\n explain_plan_lowercase.sql<\/p>\n <\/p>\n SQL Plan\uc744 \uc0dd\uc131\ud558\uc5ec optimizer\uac00 \uc608\uc0c1\ud558\ub294 \uc870\ud68c\uac74\uc218\ub97c \uc870\uc0ac\ud55c\ub2e4.<\/p>\n \ud14c\uc2a4\ud2b8 \uacb0\uacfc\ucc98\ub7fc 6\uac74\uc774 \uc870\ud68c\ub420 \uac83\uc774\ub77c\uace0 \uc608\uc0c1\ud588\uc9c0\ub9cc \uc2e4\uc81c \uc870\ud68c\uac74\uc218\uc640\ub294 \ud070 \ucc28\uc774\uac00 \uc788\ub2e4.<\/p>\n \ub530\ub77c\uc11c lower(country_id) \uc5d0 \ub300\ud55c extended statistics\ub97c \uc218\uc9d1\ud55c\ub2e4.<\/p>\n gather_stats_lower_col.sql<\/p>\n <\/p>\n \ud14c\uc774\ube14\uc758 column \ud1b5\uacc4\uc815\ubcf4\ub97c \uc870\ud68c\ud574 \ubcf8\ub2e4.<\/p>\n <\/p>\n SQL Plan\uc744 \uc0dd\uc131\ud558\uc5ec optimizer\uac00 \uc608\uc0c1\ud558\ub294 \uc870\ud68c\uac74\uc218\ub97c \uc870\uc0ac\ud55c\ub2e4.<\/p>\n \ud568\uc218\uac00 \uc801\uc6a9\ub41c column\uc5d0\ub300\ud55c extended statistics\ub97c \uc218\uc9d1\ud55c \ud6c4 optimizer\uac00 \uc815\ud655\ud55c cardinality\ub97c \uc608\uc0c1\ud558\ub294 \uac83\uc744 \ud655\uc778\ud560 \uc218 \uc788\ub2e4.<\/p>\n Dbms_metadata\ud328\ud0a4\uc9c0\ub97c \uc0ac\uc6a9\ud558\uc5ec SH.customers_obe\uc5d0 \ub300\ud55c \uc815\uc758\ub97c \uc870\ud68c\ud574 \ubcf4\uba74, lower(country_id)\uc5d0 \ub300\ud55c system-generated virtual column\uc774 \uc815\uc758\ub418\uc5b4 \uc788\uc74c\uc774 \ud655\uc778\ub41c\ub2e4.<\/p>\n <\/p>\n \uc758\uacac<\/p>\n Oracle Database 11g\uc5d0\uc11c\ub294 \uc0c8\ub85c\uc6b4 \ud1b5\uacc4\uc815\ubcf4 \uc218\uc9d1\ubc29\uc2dd\uc744 \ucd94\uac00\ud558\uc5ec \uace0\uac1d\uc758 \ub2e4\ud5a5\ud55c Data \ud2b9\uc131\uc744 \ubc18\uc601\ud558\uac8c \ub418\uc5c8\uace0 \ubcf4\ub2e4 \uc815\ud655\ud55c \ud1b5\uacc4\uc815\ubcf4\ub97c \uae30\ubc18\uc73c\ub85c \ubcf4\ub2e4 \uc815\ud655\ud55c Optimal Plan\uc758 \uc0dd\uc131\uc774 \uac00\ub2a5\ud558\uac8c \ub418\uc5c8\ub2e4.<\/p>\n \uc0c8\ub86d\uac8c \ucd94\uac00\ub41c \ud1b5\uacc4\uc815\ubcf4 \uc218\uc9d1\ubc29\uc2dd\uc740 \u201cExtended Statistics\u201d \uc640 \u201cFunction-Based Statistics\u201d \uc774\ub2e4. Extended Statistics: \ud55c \ud14c\uc774\ube14 \ub0b4\uc758 \uc5ec\ub7ec column\uc744 \ud558\ub098\uc758 group\uc73c\ub85c \ubb36\uc5b4 \ud1b5\uacc4\uc815\ubcf4\ub97c \uc218\uc9d1\ud558\ub418 column\uc758 \uc0c1\ud638 \uc5f0\uad00\uc131\uae4c\uc9c0 \ud30c\uc545\ud558\uc5ec \uc218\uc9d1\ud55c\ub2e4.<\/p>\n Function-Based Statistics: \ud2b9\uc815 column\uc5d0 \uc801\uc6a9\ub41c \ud568\uc218\ub97c \uc801\uc6a9\ud55c \uacb0\uacfc\uc5d0 \ub300\ud55c \ud1b5\uacc4\uc815\ubcf4\ub97c \uc218\uc9d1\ud55c\ub2e4. 11g Optinizer\ub294 column\uac04\uc758 \uc0c1\ud638\uc5f0\uad00\uc131\uc5d0 \ub300\ud55c \ud1b5\uac8c\uc815\ubcf4\ub97c \uc774\uc6a9\ud558\uc5ec Optimal Plan\uc744 \uc138\uc6b8 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n <\/p>\n <\/p>\n <\/p>\n","protected":false},"excerpt":{"rendered":" \uc815\ud655\ud55c\u00a0\ud1b5\uacc4\uc815\ubcf4\u00a0\uc218\uc9d1\uc740\u00a0Optimal Plan\uc744\u00a0\uc0dd\uc131\ud558\uae30\u00a0\uc704\ud55c\u00a0\ud544\uc218\u00a0\uc694\uac74\uc774\ub2e4. \uc624\ub77c\ud074\u00a0DBMS\uc5d0\uc11c\ub294\u00a0Cost-Based Opmizer \uc758\u00a0\uc644\ubcbd\ud55c\u00a0\uae30\ub2a5\uc744\u00a0\uc704\ud574\u00a0\ub2e4\uc591\ud55c\u00a0\ud1b5\uacc4\uc815\ubcf4\u00a0\uc218\uc9d1\ubc29\ubc95\uc744\u00a0\uad6c\uc0ac\ud560\u00a0\uc218\u00a0\uc788\ub2e4. \ub300\ud45c\uc801\uc778\u00a0\uc608\uac00\u00a0Histogram\uc73c\ub85c\u00a0Skew\ub41c\u00a0Data\uc5d0\u00a0\ub300\ud55c\u00a0\uc815\ud655\ud55c\u00a0\ud1b5\uacc4\uc218\uc9d1\uc774\u00a0\uac00\ub2a5\ud558\ub2e4. 10g\uc5d0\uc11c\ub294\u00a0\ud1b5\uacc4\uc815\ubcf4\u00a0\uc218\uc9d1\uc744\u00a0\uc790\ub3d9\ud654\u00a0framework\uc744\u00a0\uc81c\uacf5\ud558\uc5ec\u00a0DBA\ub4e4\uc5d0\uac8c\u00a0\ud3b8\uc758\uc131\uc744\u00a0\uc81c\uacf5\ud558\uae30\ub3c4\u00a0\ud588\ub2e4. Oracle Database 11g\uc5d0\uc11c\ub294\u00a0\uc0c8\ub85c\uc6b4\u00a0\ud1b5\uacc4\uc815\ubcf4\u00a0\uc218\uc9d1\ubc29\uc2dd\uc744\u00a0\ucd94\uac00\ud558\uc5ec\u00a0\uace0\uac1d\uc758\u00a0\ub2e4\ud5a5\ud55c\u00a0Data \ud2b9\uc131\uc744\u00a0\ubc18\uc601\ud558\uac8c\u00a0\ub418\uc5c8\uace0\u00a0\ubcf4\ub2e4\u00a0\uc815\ud655\ud55c\u00a0\ud1b5\uacc4\uc815\ubcf4\ub97c\u00a0\uae30\ubc18\uc73c\ub85c\u00a0\ubcf4\ub2e4\u00a0\uc815\ud655\ud55c\u00a0Optimal Plan\uc758\u00a0\uc0dd\uc131\uc774\u00a0\uac00\ub2a5\ud558\uac8c\u00a0\ub418\uc5c8\ub2e4. \uc0c8\ub86d\uac8c\u00a0\ucd94\uac00\ub41c\u00a0\ud1b5\uacc4\uc815\ubcf4\u00a0\uc218\uc9d1\ubc29\uc2dd\uc740\u00a0\u201cExtended Statistics\u201d \uc640\u00a0\u201cFunction-Based Statistics\u201d \uc774\ub2e4. Extended Statistics: \ud55c\u00a0\ud14c\uc774\ube14\u00a0\ub0b4\uc758\u00a0\uc5ec\ub7ec\u00a0column\uc744\u00a0\ud558\ub098\uc758\u00a0group\uc73c\ub85c\u00a0\ubb36\uc5b4\u00a0\ud1b5\uacc4\uc815\ubcf4\ub97c\u00a0\uc218\uc9d1\ud558\ub418\u00a0column\uc758 \uc0c1\ud638\u00a0\uc5f0\uad00\uc131\uae4c\uc9c0\u00a0\ud30c\uc545\ud558\uc5ec\u00a0\uc218\uc9d1\ud55c\ub2e4. Function-Based Statistics: \ud2b9\uc815\u00a0column\uc5d0\u00a0\uc801\uc6a9\ub41c\u00a0\ud568\uc218\ub97c\u00a0\uc801\uc6a9\ud55c\u00a0\uacb0\uacfc\uc5d0\u00a0\ub300\ud55c\u00a0\ud1b5\uacc4\uc815\ubcf4\ub97c\u00a0\uc218\uc9d1\ud55c\ub2e4. \uc774\u00a0\uc7a5\uc5d0\uc11c\ub294\u00a011g \uc758\u00a0\ud5a5\uc0c1\ub41c\u00a0\ud1b5\uacc4\uc218\uc9d1\u00a0\ubc29\ubc95\uc744\u00a0\uc124\uba85\ud558\uace0\u00a0optimizer\uac00\u00a0\uc774\ub7ec\ud55c\u00a0\ud1b5\uacc4\uc815\ubcf4\ub97c\u00a0\uc5b4\ub5bb\uac8c\u00a0\ud65c\uc6a9\ud558\ub294\uc9c0\ub97c\u00a0\ud14c\uc2a4\ud2b8\ub97c\u00a0\ud1b5\ud558\uc5ec\u00a0\ud655\uc778\ud574\u00a0\ubcf4\ub3c4\ub85d\u00a0\ud55c\ub2e4. Extended Statistics \uc608\ub97c\ub4e4\uc5b4, SH.customers\ub77c\ub294\u00a0\ud14c\uc774\ube14\uc5d0\u00a0cust_state_province \ub77c\ub294\u00a0\uceec\ub7fc\uacfc\u00a0country_id \ub77c\ub294\u00a0column\uc774\u00a0\uc788\ub2e4. \uceec\ub7fc\u00a0cust_state_province\uc758\u00a0selectivity\ub294\u00a00.005 \uc774\uace0\u00a0country_id selectivity\ub294\u00a00.1 \uc774\ub2e4. cust_state_province \uc640\u00a0country_id\uac00\u00a0where\uc870\uac74\uc808\uc758\u00a0equality\uc640\u00a0and \uc870\uac74\uc73c\ub85c\u00a0\uc870\ud68c\ub41c\ub2e4\uba74\u00a0selectivity\ub294\u00a00.0005(=0.005 X 0.1) \uc774\ub2e4. \ud558\uc9c0\ub9cc\u00a0cust_state_province\uac00\u00a0country_id\uc744\u00a0\uacb0\uc815\ud558\ub294\u00a0\uad00\uacc4\ub97c\u00a0\uac16\ub294\ub2e4\uba74, \uc774\ub4e4\uc758\u00a0selectivty\ub294\u00a00.0005\uac00\u00a0\uc544\ub2c8\ub2e4\u00a0. \uc608\ub97c\ub4e4\uc5b4, \ubbf8\uad6d\uc774\ub77c\ub294\u00a0country_id(52780) \uc640\u00a0\uce98\ub9ac\ud3ec\ub2c8\uc544\ub77c\ub294\u00a0cust_state_province(\u2018CA\u2019) \ub97c\u00a0\uc870\ud68c\ud558\uba74, SQL> select […]<\/p>\n","protected":false},"author":1,"featured_media":3401,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[9],"tags":[977,978,162],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/2018\/02\/151.png?fit=655%2C437","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/3395"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3395"}],"version-history":[{"count":2,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/3395\/revisions"}],"predecessor-version":[{"id":3397,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/3395\/revisions\/3397"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/3401"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3395"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3395"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3395"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}SQL> select count(*) from sh.customers wehre cust_state_province=\u2019CA\u2019;\r\nCOUNT(*)\r\n----------\r\n3341\r\n\r\nSQL> select count(*) from sh.customers where cust_state_province=\u2019CA\u2019 and country_id=52780\r\nCOUNT(*)\r\n----------\r\n3341\r\n\r\nSQL> select count(*) from sh.customers where cust_state_province=\u2019CA\u2019 and country_id=52775\r\nCOUNT(*)\r\n----------\r\n0<\/pre>\n
\n\n
\n <\/td>\n Parameter<\/b><\/td>\n <\/td>\n <\/td>\n Description<\/b><\/td>\n <\/td>\n<\/tr>\n \n <\/td>\n Owner<\/td>\n <\/td>\n <\/td>\n <\/td>\n Schema owner. NULL indicates current schema.<\/td>\n<\/tr>\n \n <\/td>\n Tab_name<\/td>\n <\/td>\n <\/td>\n Name of the table to which the column group Is being added<\/td>\n<\/tr>\n \n <\/td>\n extension<\/td>\n <\/td>\n <\/td>\n <\/td>\n Columns in the column group<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\ncg_name varchar2(30);
\nbegin
\ncg_name := dbms_stats.create_extended_stats(null,’customers’,
\n‘(cust_state_province’,country_id)’);
\nend;
\n\/<\/p>\n
\nCOL_GROUP_NAME
\n—————-
\nSYS_STU#S#WF25Z#QAHIHE#MOFFMM<\/p>\n
\n————————————————————————-
\nSYS_STU#S#WF25Z#QAHIHE#MOFFMM_ (“CUST_STATE_PROVINCE”,”COUNTRY_ID”)<\/p>\n
\n2 from user_stat_extensions e, user_tab_col_statistics t
\n3 where e.extension_name=t.column_name
\n4 and t.table_name=’CUSTOMERS’;<\/p>\n
\n——————————————————————————-
\n(“COUNTRY_ID”,”CUST_STATE_PROVINCE”) 145 FREQUENCY<\/p>\n
\n2 from user_stat_extensions e, user_tab_col_statistics t
\n3 where e.extension_name=t.column_name
\n4 and t.table_name=’CUSTOMERS’;<\/p>\n
\n————————————————————————
\n(LOWER(“CUST_STATE_PROVINCE”)) 145 FREQUENCY<\/p>\n[oracle@obe11g multistats]$ imp sh\/sh file= customers_obe.dmp log=imp.log full=y\r\nImport: Release 11.1.0.5.0 - Beta on Mon Sep 10 16:04:56 2007\r\nCopyright (c) 1982, 2007, Oracle. All rights reserved.\r\nConnected to: Oracle Database 11g Enterprise Edition Release 11.1.0.5.0 - Beta\r\nWith the Partitioning, OLAP, Data Mining and Real Application Testing options\r\nExport file created by EXPORT:V11.01.00 via conventional path\r\nimport done in US7ASCII character set and AL16UTF16 NCHAR character set\r\nimport server uses AL32UTF8 character set (possible charset conversion)\r\n. importing SH's objects into SH\r\n. importing SH's objects into SH\r\n. . importing table \"CUSTOMERS_OBE\" 630 rows imported\r\nImport terminated successfully without warnings.\r\n[oracle@obe11g multistats]$<\/pre>\n
select count(*) from customers_obe\r\nwhere country_id = 'US' and cust_state_province = 'CA';<\/pre>\n
exec dbms_stats.gather_table_stats(null,'customers_obe', method_opt => 'for all columns size 1');<\/pre>\n
SQL> @gather_stats.sql\r\nPL\/SQL procedure successfully completed.\r\nSQL><\/pre>\n
select column_name, num_distinct, histogram from user_tab_col_statistics where table_name = 'CUSTOMERS_OBE';<\/pre>\n
SQL> @review_stats\r\nCOLUMN_NAME NUM_DISTINCT HISTOGRAM\r\n------------------------------ ------------ ---------------\r\nCUST_ID 630 NONE\r\nCUST_FIRST_NAME 450 NONE\r\nCUST_LAST_NAME 400 NONE\r\nCUST_GENDER 2 NONE\r\nCUST_YEAR_OF_BIRTH 66 NONE\r\nCUST_MARITAL_STATUS 2 NONE\r\nCUST_STREET_ADDRESS 630 NONE\r\nCUST_POSTAL_CODE 301 NONE\r\nCUST_CITY 300 NONE\r\nCUST_STATE_PROVINCE 120 NONE\r\nCOUNTRY_ID 19 NONE\r\nCOLUMN_NAME NUM_DISTINCT HISTOGRAM\r\n------------------------------ ------------ ---------------\r\nCUST_MAIN_PHONE_NUMBER 630 NONE\r\nCUST_INCOME_LEVEL 12 NONE\r\nCUST_CREDIT_LIMIT 8 NONE\r\nCUST_EMAIL 400 NONE\r\n15 rows selected.\r\nSQL><\/pre>\n
explain plan for\r\nselect *\r\nfrom customers_obe\r\nwhere country_id = 'US' and cust_state_province = 'CA';\r\nselect plan_table_output\r\nfrom table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));<\/pre>\nSQL> @explain_plan\r\nExplained.\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------\r\nPlan hash value: 520139036\r\n---------------------------------------------------\r\n| Id | Operation | Name | Rows |\r\n---------------------------------------------------\r\n| 0 | SELECT STATEMENT | | 1 |\r\n| 1 | TABLE ACCESS FULL| CUSTOMERS_OBE | 1 |\r\n---------------------------------------------------\r\n8 rows selected.\r\nSQL><\/pre>\n
exec dbms_stats.gather_table_stats(null,'customers_obe', method_opt => 'for all columns size skewonly');<\/pre>\n
SQL> @gather_histogram\r\nPL\/SQL procedure successfully completed.\r\nSQL> @review_stat\r\nCOLUMN_NAME NUM_DISTINCT HISTOGRAM\r\n------------------------------ ------------ ---------------\r\nCUST_ID 630 HEIGHT BALANCED\r\nCUST_FIRST_NAME 450 HEIGHT BALANCED\r\nCUST_LAST_NAME 400 HEIGHT BALANCED\r\nCUST_GENDER 2 FREQUENCY\r\nCUST_YEAR_OF_BIRTH 66 FREQUENCY\r\nCUST_MARITAL_STATUS 2 FREQUENCY\r\nCUST_STREET_ADDRESS 630 HEIGHT BALANCED\r\nCUST_POSTAL_CODE 301 HEIGHT BALANCED\r\nCUST_CITY 300 HEIGHT BALANCED\r\nCUST_STATE_PROVINCE 120 FREQUENCY\r\nCOUNTRY_ID 19 FREQUENCY\r\n\r\nCOLUMN_NAME NUM_DISTINCT HISTOGRAM\r\n------------------------------ ------------ ---------------\r\nCUST_MAIN_PHONE_NUMBER 630 HEIGHT BALANCED\r\nCUST_INCOME_LEVEL 12 FREQUENCY\r\nCUST_CREDIT_LIMIT 8 FREQUENCY\r\nCUST_EMAIL 400 HEIGHT BALANCED\r\n15 rows selected.\r\nSQL>\r\n<\/pre>\n
SQL> @explain_plan\r\nExplained.\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------\r\nPlan hash value: 520139036\r\n---------------------------------------------------\r\n| Id | Operation | Name | Rows |\r\n---------------------------------------------------\r\n| 0 | SELECT STATEMENT | | 8 |\r\n| 1 | TABLE ACCESS FULL| CUSTOMERS_OBE | 8 |\r\n---------------------------------------------------\r\n8 rows selected.\r\nSQL><\/pre>\n
select dbms_stats.create_extended_stats(null,'customers_obe', '(country_id, cust_state_province)') from dual;<\/pre>\n
SQL> @create_extended_stats.sql\r\nDBMS_STATS.CREATE_EXTENDED_STATS(NULL,'CUSTOMERS_OBE','(COUNTRY_ID,CUST_STATE_PR\r\n--------------------------------------------------------------------------------\r\nSYS_STUJGVLRVH5USVDU$XNV4_IR#4<\/pre>\n
SQL> @gather_histogram.sql\r\nPL\/SQL procedure successfully completed.\r\nSQL> @review_stats\r\nCOLUMN_NAME NUM_DISTINCT HISTOGRAM\r\n------------------------------ ------------ ---------------\r\nCUST_ID 630 HEIGHT BALANCED\r\nCUST_FIRST_NAME 450 HEIGHT BALANCED\r\nCUST_LAST_NAME 400 HEIGHT BALANCED\r\nCUST_GENDER 2 FREQUENCY\r\nCUST_YEAR_OF_BIRTH 66 FREQUENCY\r\nCUST_MARITAL_STATUS 2 FREQUENCY\r\nCUST_STREET_ADDRESS 630 HEIGHT BALANCED\r\nCUST_POSTAL_CODE 301 HEIGHT BALANCED\r\nCUST_CITY 300 HEIGHT BALANCED\r\nCUST_STATE_PROVINCE 120 FREQUENCY\r\nCOUNTRY_ID 19 FREQUENCY\r\nCOLUMN_NAME NUM_DISTINCT HISTOGRAM\r\n------------------------------ ------------ ---------------\r\nCUST_MAIN_PHONE_NUMBER 630 HEIGHT BALANCED\r\nCUST_INCOME_LEVEL 12 FREQUENCY\r\nCUST_CREDIT_LIMIT 8 FREQUENCY\r\nCUST_EMAIL 400 HEIGHT BALANCED\r\nSYS_STUJGVLRVH5USVDU$XNV4_IR#4 120 FREQUENCY\r\n16 rows selected.\r\nSQL><\/pre>\n
SQL> @explain_plan.sql\r\nExplained.\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------\r\nPlan hash value: 520139036\r\n---------------------------------------------------\r\n| Id | Operation | Name | Rows |\r\n---------------------------------------------------\r\n| 0 | SELECT STATEMENT | | 29 |\r\n| 1 | TABLE ACCESS FULL| CUSTOMERS_OBE | 29 |\r\n---------------------------------------------------\r\n8 rows selected.\r\nSQL><\/pre>\n
select count(*) from customers_obe where lower(country_id) = 'us';<\/pre>\n
SQL> @get_count_lower.sql\r\nCOUNT(*)\r\n----------\r\n165\r\nSQL><\/pre>\n
explain plan for\r\nselect *\r\nfrom customers_obe\r\nwhere lower(country_id) = 'us';\r\nselect plan_table_output\r\nfrom table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));<\/pre>\nSQL> @explain_plan_lowercase.sql\r\nExplained.\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------\r\nPlan hash value: 520139036\r\n---------------------------------------------------\r\n| Id | Operation | Name | Rows |\r\n---------------------------------------------------\r\n| 0 | SELECT STATEMENT | | 6 |\r\n| 1 | TABLE ACCESS FULL| CUSTOMERS_OBE | 6 |\r\n---------------------------------------------------\r\n8 rows selected.\r\nSQL><\/pre>\n
exec dbms_stats.gather_table_stats(null,'customers_obe', method_opt => 'for all columns size skewonly for columns (lower(country_id))');\r\n\r\nSQL> @gather_stats_gather_col.sql\r\n\r\nPL\/SQL procedure successfully completed.<\/pre>\n
SQL> @review_col_stats.sql\r\nCOLUMN_NAME NUM_DISTINCT HISTOGRAM\r\n------------------------------ ------------ ---------------\r\nCUST_ID 630 HEIGHT BALANCED\r\nCUST_FIRST_NAME 450 HEIGHT BALANCED\r\nCUST_LAST_NAME 400 HEIGHT BALANCED\r\nCUST_GENDER 2 FREQUENCY\r\nCUST_YEAR_OF_BIRTH 66 FREQUENCY\r\nCUST_MARITAL_STATUS 2 FREQUENCY\r\nCUST_STREET_ADDRESS 630 HEIGHT BALANCED\r\nCUST_POSTAL_CODE 301 HEIGHT BALANCED\r\nCUST_CITY 300 HEIGHT BALANCED\r\nCUST_STATE_PROVINCE 120 FREQUENCY\r\nCOUNTRY_ID 19 FREQUENCY\r\nCOLUMN_NAME NUM_DISTINCT HISTOGRAM\r\n------------------------------ ------------ ---------------\r\nCUST_MAIN_PHONE_NUMBER 630 HEIGHT BALANCED\r\nCUST_INCOME_LEVEL 12 FREQUENCY\r\nCUST_CREDIT_LIMIT 8 FREQUENCY\r\nCUST_EMAIL 400 HEIGHT BALANCED\r\nSYS_STUJGVLRVH5USVDU$XNV4_IR#4 120 FREQUENCY\r\nSYS_STUYYRO5KJCK7IDGUI37HEGCKQ 19 FREQUENCY\r\n17 rows selected.\r\nSQL><\/pre>\n
SQL> @explain_plan_lowercase.sql\r\nExplained.\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------\r\nPlan hash value: 520139036\r\n---------------------------------------------------\r\n| Id | Operation | Name | Rows |\r\n---------------------------------------------------\r\n| 0 | SELECT STATEMENT | | 165 |\r\n| 1 | TABLE ACCESS FULL| CUSTOMERS_OBE | 165 |\r\n---------------------------------------------------\r\n8 rows selected.\r\nSQL><\/pre>\n
SQL> select dbms_metadata.get_ddl('TABLE','CUSTOMERS_OBE') from dual;\r\nDBMS_METADATA.GET_DDL('TABLE','CUSTOMERS_OBE')\r\n--------------------------------------------------------------------------------\r\nCREATE TABLE \"SH\".\"CUSTOMERS_OBE\"\r\n( \"SYS_STUJGVLRVH5USVDU$XNV4_IR#4\" NUMB\r\nSQL><\/pre>\n