/* You might be provided with a statement that has the wrong selectivity estimate and asked to correct it. Most of the time, even when having collected statistics you might get the wrong estimate, especially when having more than one column specified in the WHERE clause of the statement. */
난이도: ★★★☆☆
문제 1: dba_objects 뷰에 기반해 테이블 하나를 생성하라. statistics를 gather하고, 테이블을 쿼리한 후 투 컬럼 (object_name, object_type)를 사용해 행을 필터링한다. 그리고 execution plan를 얻고 estimated row count를 체크한다. 그리고, 두 컬럼에 대한 extended statistics을 생성하고 estimated row count를 체크해본다. (15분)
CREATE USER usr_multc_test IDENTIFIED BY oracle;
GRANT dba, unlimited tablespace TO usr_multc_test;
CONN usr_multc_test/oracle;
CREATE TABLE tbl_multicolumn_test
AS
SELECT *
FROM dba_objects
WHERE rownum <= 10;
INSERT INTO tbl_multicolumn_test
SELECT * FROM tbl_multicolumn_test;
/
/
/
/
SELECT object_name, COUNT(*)
FROM tbl_multicolumn_test
WHERE object_name = 'ICOL$'
AND object_type = 'TABLE'
GROUP BY object_name;
EXEC dbms_stats.gather_table_stats(NULL,'tbl_multicolumn_test',METHOD_OPT=>'FOR ALL COLUMNS SIZE 1');
SELECT column_name, num_distinct, histogram
FROM user_tab_col_statistics
WHERE table_name = 'TBL_MULTICOLUMN_TEST';
EXPLAIN PLAN FOR
SELECT * FROM tbl_multicolumn_test WHERE object_name='ICOL$' AND object_type='TABLE';
SELECT * FROM TABLE (dbms_xplan.display(null,null,'basic rows'));
EXEC dbms_stats.gather_table_stats(NULL,'tbl_multicolumn_test', method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY');
EXPLAIN PLAN FOR
SELECT * FROM tbl_multicolumn_test WHERE object_name='ICOL$' AND object_type='TABLE';
SELECT * FROM TABLE (dbms_xplan.display(null,null,'basic rows'));
SELECT dbms_stats.create_extended_stats(NULL,'tbl_multicolumn_test','(object_name,object_type)') stat
FROM dual;
EXPLAIN PLAN FOR
SELECT * FROM tbl_multicolumn_test WHERE object_name='ICOL$' AND object_type='TABLE';
SELECT * FROM TABLE (dbms_xplan.display(null,null,'basic rows'));
/* dbms_stats.gather_table_stats 프로시저 실시 시, 특정 칼럼에 함수를 지정하여 테이블 통계를 수집할 수 있다. */
EXEC DBMS_STATS.GATHER_TABLE_STATS(NULL,'tbl_multicolumn_test',METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY FOR COLUMNS LOWER(object_name))';
