最初に、country_idが'US'でcust_state_provinceが'CA'であるCUSTOMERS_OBE表の実際のカーディナリティまたは行数を確認します。 端末ウィンドウを開いて、次のコマンドを実行します。

CD<sql_files_directory>
sqlplus sh/sh
set echo on
@check_cardinality

check_cardinality.sqlコードは次のとおりです。

select count(*)
from customers_obe
where country_id = 'US' and cust_state_province = 'CA';

実際の行数が分かったので、表の統計を収集できます。 端末ウィンドウから、次のSQLスクリプトを実行します。

@gather_stats

gather_stats.sqlコードは次のとおりです。

exec dbms_stats.gather_table_stats(null,'customers_obe', -
      method_opt => 'for all columns size 1');

作成された列統計を確認します。 端末ウィンドウから、次のSQLスクリプトを実行します。

@review_stats

review_stats.sqlコードは次のとおりです。

select column_name, num_distinct, histogram
from user_tab_col_statistics where table_name = 'CUSTOMERS_OBE';

明確な値の数が、country_id列およびcust_state_province列の両方に示されていることが分かります。 これらの値の両方が正確と思われます。

この統計を前提として、オプティマイザの推定するクエリーの返す行数を確認します。 端末ウィンドウから、次のSQLスクリプトを実行します。

@explain_plan

explain_plan.sqlコードは次のとおりです。

explain plan for
select *
from customers_obe
where country_id = 'US' and cust_state_province = 'CA';

select plan_table_output
from table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));

基本的な統計では、オプティマイザは1行のみが返されると推定します。 この行数は正しくないため、正しい行数を決定できるより良い統計をオプティマイザに提供する必要があります。

偏りのある列のヒストグラムを収集します。 端末ウィンドウから、次のSQLスクリプトを実行します。

@gather_histogram

gather_histogram.sqlコードは次のとおりです。

exec dbms_stats.gather_table_stats(null,'customers_obe', -
      method_opt => 'for all columns size skewonly');

select column_name, num_distinct, histogram
from user_tab_col_statistics where table_name = 'CUSTOMERS_OBE';

EXPLAIN PLANを作成してオプティマイザの推定値を表示する準備ができました。 端末ウィンドウから、次のSQLスクリプトを実行します。

@explain_plan

explain_plan.sqlコードは次のとおりです。

explain plan for
select *
from customers_obe
where country_id = 'US' and cust_state_province = 'CA';

select plan_table_output
from table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));

各列のヒストグラムが作成されるため、推定は若干改善されています。 ただし、オプティマイザはまだ2列間の相関を認識していません。

拡張統計グループを作成します。 端末ウィンドウから、次のSQLスクリプトを実行します。

@create_extended_stats

create_extended_stats.sqlコードは次のとおりです。

select  dbms_stats.create_extended_stats(null,'customers_obe',
         '(country_id, cust_state_province)')
from dual;

create_extended_stats.sqlの出力は、作成される仮想列のシステム生成名です。

偏りのある列のヒストグラムを収集する際に実行したスクリプトを実行します。 端末ウィンドウから、次のSQLスクリプトを実行します。

@gather_histogram

gather_histogram.sqlコードは次のとおりです。

exec dbms_stats.gather_table_stats(null,'customers_obe', -
      method_opt => 'for all columns size skewonly');

select column_name, num_distinct, histogram
from user_tab_col_statistics where table_name = 'CUSTOMERS_OBE';

create_extended_statsプロシージャによって返されるシステム生成名の表に、新しい列が表示されます。

EXPLAIN PLANを再実行します。 端末ウィンドウから、次のスクリプトを実行します。

@explain_plan

explain_plan.sqlコードは次のとおりです。

explain plan for
select *
from customers_obe
where country_id = 'US' and cust_state_province = 'CA';

select plan_table_output
from table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));

オプティマイザでクエリーの正しい行数を取得する準備ができました。

country_idが'US'である行の総数を選択する関数を使用したクエリーを実行します。 クエリーに渡される値は小文字なので、LOWER関数がcountry_id列に設定されます。 端末ウィンドウから、次のスクリプトを実行します。

@get_count_lower

get_count_lower.sqlコードは次のとおりです。

select count(*)
from customers_obe
where lower(country_id) = 'us';

実際の行数が返されます。

EXPLAIN PLANを再実行して、オプティマイザによって推定されるクエリーの返す行数を表示する準備ができました。 端末ウィンドウから、次のスクリプトを実行します。

@explain_plan_lowercase

explain_plan_lowercase.sqlコードは次のとおりです。

explain plan for
select *
from customers_obe
where lower(country_id) = 'us';

select plan_table_output
from table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));

オプティマイザの取得する行数は、まだ正しくありません。

LOWER(country_id)式に拡張統計を作成すると、オプティマイザで正しい行数を取得できるようになります。 標準gather_table_statsプロシージャのmethod_opt引数で、収集する拡張機能や拡張統計を指定できます。 gather_table_statsプロシージャは、拡張統計が存在しない場合、自動的に拡張統計を作成します。 端末ウィンドウから、次のスクリプトを実行します。

@gather_stats_lower_col

gather_stats_lower_col.sqlコードは次のとおりです。

exec dbms_stats.gather_table_stats(null,'customers_obe', -
      method_opt =>  -
      'for all columns size skewonly for columns (lower(country_id))');

拡張統計が作成され、統計が収集されました。CUSTOMERS_OBE表の統計を確認することができます。 システム生成名の付いた新しい列が表示されています。 端末ウィンドウから、次のスクリプトを実行します。

@review_col_stats

review_col_stats.sqlコードは次のとおりです。

select column_name, num_distinct, histogram
from user_tab_col_statistics where table_name = 'CUSTOMERS_OBE';

EXPLAIN PLANを再実行して、オプティマイザの推定値を確認します。 端末ウィンドウから、次のスクリプトを実行します。

@explain_plan_lowercase

explain_plan_lowercase.sqlコードは次のとおりです。

explain plan for
select *
from customers_obe
where lower(country_id) = 'us';

select plan_table_output
from table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));

システム生成名から、仮想列定義を再生成することもできます。 端末ウィンドウから、次のコマンドを実行します。

select dbms_metadata.get_ddl('TABLE','CUSTOMERS_OBE') from dual;

SQL*Plusセッションから、次のコマンドを実行します。

DROP TABLE CUSTOMERS_OBE;