Oracle PL/SQL Tutorial/System Packages/DBMS STATS

Материал из SQL эксперт
Перейти к: навигация, поиск

Check table status with SYS.DBMS_STATS.gather_table_stats after adding index

   <source lang="sql">

SQL> SQL> CREATE TABLE employee_evaluation (

 2  id            number,
 3  title         varchar2(100),
 4  written_date  date );

Table created. SQL> SQL> SQL> SQL> INSERT INTO employee_evaluation(id,title,written_date)VALUES(100,"SQL",to_date("19700101", "YYYYMMDD") ); 1 row created. SQL> INSERT INTO employee_evaluation(id,title,written_date)VALUES(100,"Java",to_date("19900101", "YYYYMMDD") ); 1 row created. SQL> INSERT INTO employee_evaluation(id,title,written_date)VALUES(200,"C++",to_date("20030101", "YYYYMMDD") ); 1 row created. SQL> INSERT INTO employee_evaluation(id,title,written_date)VALUES(200,"C",to_date("20000101", "YYYYMMDD") ); 1 row created. SQL> INSERT INTO employee_evaluation(id,title,written_date)VALUES(200,"Oracle",to_date("20020101", "YYYYMMDD") ); 1 row created. SQL> INSERT INTO employee_evaluation(id,title,written_date)VALUES(200,"Theory",to_date("20050101", "YYYYMMDD") ); 1 row created. SQL> INSERT INTO employee_evaluation(id,title,written_date)values(300,"Third",to_date("20000101", "YYYYMMDD")

 2  INSERT INTO employee_evaluation(id,title,written_date)values(300,"Data",to_date("20020101", "YYYYMMDD")
 3

SQL> SQL> CREATE INDEX employee_evaluation_k1

 2  on           employee_evaluation (
 3  title );

Index created. SQL> SQL>EXEC SYS.DBMS_STATS.gather_table_stats(USER, UPPER("employee_evaluation")); SQL> SQL> SQL> drop table employee_evaluation; Table dropped. SQL> SQL></source>


Create a procedure to collect statistics on all my objects

   <source lang="sql">

SQL> SQL> SQL> create table my_stats_table

 2  (object_name  varchar2(30),
 3   gather_date  date default sysdate);

Table created. SQL> SQL> SQL> create or replace procedure gather_my_stats as

 2    olist  dbms_stats.objectTab;
 3  begin
 4    dbms_stats.gather_schema_stats(user,
 5            cascade=>TRUE,
 6            method_opt=> "AUTO",
 7            options=>"GATHER AUTO",
 8            objlist=>olist);
 9
10    if olist.COUNT > 0 then
11       FOR x in 1..olist.COUNT LOOP
12           insert into my_stats_table (object_name)
13            values(olist(x).objname);
14       END LOOP;
15    end if;
16  end;
17  /

Procedure created. SQL> SQL> SQL> select object_name, to_char(gather_Date, "mm/dd/yyyy hh:mi")

 2  from my_Stats_table;

no rows selected SQL> SQL> drop table my_stats_table;

SQL></source>


dbms_stats.drop_stat_table, dbms_stats.create_stat_table,dbms_stats.delete_system_stats

   <source lang="sql">

SQL> exec dbms_stats.drop_stat_table( user, "SYSTEM_STATS" ); SQL> exec dbms_stats.create_stat_table( user, "SYSTEM_STATS" ); SQL> exec dbms_stats.delete_system_stats; SQL> SQL> SQL></source>


dbms_stats.gather_schema_stats

   <source lang="sql">

SQL> SQL> declare

 2    olist  dbms_stats.objectTab;
 3  begin
 4    dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> "FOR ALL INDEXED COLUMNS",options=>"GATHER STALE",objlist=>olist);
 5
 6    dbms_output.put_line("Objects Analyzed: " || olist.COUNT );
 7    if olist.COUNT > 0 then
 8       FOR x in 1..olist.COUNT LOOP
 9           dbms_output.put_line("Object Name: " || olist(x).objname );
10       END LOOP;
11    end if;
12
13  end;
14  /

Objects Analyzed: 2 Object Name: MYSTATS Object Name: P1 PL/SQL procedure successfully completed. SQL> SQL></source>


dbms_stats.gather_table_stats

   <source lang="sql">

SQL> create table t as select * from all_objects where rownum < 20; Table created. SQL> SQL> exec dbms_stats.gather_table_stats( user, "T" ); PL/SQL procedure successfully completed. SQL> SQL> alter session set workarea_size_policy=manual; Session altered. SQL> alter session set sort_area_size = 65536; Session altered. SQL> set termout off SQL> select * from t where rownum < 20 order by 1, 2, 3, 4 ; OWNER OBJECT_NAME


------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE


---------- -------------- -------------------

CREATED LAST_DDL_ TIMESTAMP STATUS T G S


--------- ------------------- ------- - - -

SYS CON$

                                      28             28 TABLE

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS C_COBJ#

                                      29             29 CLUSTER

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N OWNER OBJECT_NAME


------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE


---------- -------------- -------------------

CREATED LAST_DDL_ TIMESTAMP STATUS T G S


--------- ------------------- ------- - - -

SYS FILE$

                                      17             17 TABLE

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS ICOL$

                                      20              2 TABLE

OWNER OBJECT_NAME


------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE


---------- -------------- -------------------

CREATED LAST_DDL_ TIMESTAMP STATUS T G S


--------- ------------------- ------- - - -

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS IND$

                                      19              2 TABLE

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CDEF2 OWNER OBJECT_NAME


------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE


---------- -------------- -------------------

CREATED LAST_DDL_ TIMESTAMP STATUS T G S


--------- ------------------- ------- - - -
                                      51             51 INDEX

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CDEF4

                                      53             53 INDEX

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N

OWNER OBJECT_NAME


------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE


---------- -------------- -------------------

CREATED LAST_DDL_ TIMESTAMP STATUS T G S


--------- ------------------- ------- - - -

SYS I_CON1

                                      48             48 INDEX

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_FILE#_BLOCK#

                                       9              9 INDEX

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N OWNER OBJECT_NAME


------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE


---------- -------------- -------------------

CREATED LAST_DDL_ TIMESTAMP STATUS T G S


--------- ------------------- ------- - - -

SYS I_FILE1

                                      41             41 INDEX

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_IND1

                                      39             39 INDEX

OWNER OBJECT_NAME


------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE


---------- -------------- -------------------

CREATED LAST_DDL_ TIMESTAMP STATUS T G S


--------- ------------------- ------- - - -

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_OBJ#

                                       3              3 INDEX

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_OBJ3 OWNER OBJECT_NAME


------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE


---------- -------------- -------------------

CREATED LAST_DDL_ TIMESTAMP STATUS T G S


--------- ------------------- ------- - - -
                                      38             38 INDEX

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_PROXY_ROLE_DATA$_1

                                      26             26 INDEX

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N

OWNER OBJECT_NAME


------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE


---------- -------------- -------------------

CREATED LAST_DDL_ TIMESTAMP STATUS T G S


--------- ------------------- ------- - - -

SYS I_TS#

                                       7              7 INDEX

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_USER1

                                      44             44 INDEX

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N OWNER OBJECT_NAME


------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE


---------- -------------- -------------------

CREATED LAST_DDL_ TIMESTAMP STATUS T G S


--------- ------------------- ------- - - -

SYS PROXY_ROLE_DATA$

                                      25             25 TABLE

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS UET$

                                      13              8 TABLE

OWNER OBJECT_NAME


------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE


---------- -------------- -------------------

CREATED LAST_DDL_ TIMESTAMP STATUS T G S


--------- ------------------- ------- - - -

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS UNDO$

                                      15             15 TABLE

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N

19 rows selected. SQL> SQL> set termout on SQL> alter session set sort_area_size=1048576; Session altered. SQL> set termout off SQL> select * from t where rownum < 20 order by 1, 2, 3, 4; OWNER OBJECT_NAME


------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE


---------- -------------- -------------------

CREATED LAST_DDL_ TIMESTAMP STATUS T G S


--------- ------------------- ------- - - -

SYS CON$

                                      28             28 TABLE

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS C_COBJ#

                                      29             29 CLUSTER

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N OWNER OBJECT_NAME


------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE


---------- -------------- -------------------

CREATED LAST_DDL_ TIMESTAMP STATUS T G S


--------- ------------------- ------- - - -

SYS FILE$

                                      17             17 TABLE

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS ICOL$

                                      20              2 TABLE

OWNER OBJECT_NAME


------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE


---------- -------------- -------------------

CREATED LAST_DDL_ TIMESTAMP STATUS T G S


--------- ------------------- ------- - - -

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS IND$

                                      19              2 TABLE

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CDEF2 OWNER OBJECT_NAME


------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE


---------- -------------- -------------------

CREATED LAST_DDL_ TIMESTAMP STATUS T G S


--------- ------------------- ------- - - -
                                      51             51 INDEX

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CDEF4

                                      53             53 INDEX

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N

OWNER OBJECT_NAME


------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE


---------- -------------- -------------------

CREATED LAST_DDL_ TIMESTAMP STATUS T G S


--------- ------------------- ------- - - -

SYS I_CON1

                                      48             48 INDEX

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_FILE#_BLOCK#

                                       9              9 INDEX

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N OWNER OBJECT_NAME


------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE


---------- -------------- -------------------

CREATED LAST_DDL_ TIMESTAMP STATUS T G S


--------- ------------------- ------- - - -

SYS I_FILE1

                                      41             41 INDEX

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_IND1

                                      39             39 INDEX

OWNER OBJECT_NAME


------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE


---------- -------------- -------------------

CREATED LAST_DDL_ TIMESTAMP STATUS T G S


--------- ------------------- ------- - - -

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_OBJ#

                                       3              3 INDEX

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_OBJ3 OWNER OBJECT_NAME


------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE


---------- -------------- -------------------

CREATED LAST_DDL_ TIMESTAMP STATUS T G S


--------- ------------------- ------- - - -
                                      38             38 INDEX

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_PROXY_ROLE_DATA$_1

                                      26             26 INDEX

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N

OWNER OBJECT_NAME


------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE


---------- -------------- -------------------

CREATED LAST_DDL_ TIMESTAMP STATUS T G S


--------- ------------------- ------- - - -

SYS I_TS#

                                       7              7 INDEX

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_USER1

                                      44             44 INDEX

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N OWNER OBJECT_NAME


------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE


---------- -------------- -------------------

CREATED LAST_DDL_ TIMESTAMP STATUS T G S


--------- ------------------- ------- - - -

SYS PROXY_ROLE_DATA$

                                      25             25 TABLE

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS UET$

                                      13              8 TABLE

OWNER OBJECT_NAME


------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE


---------- -------------- -------------------

CREATED LAST_DDL_ TIMESTAMP STATUS T G S


--------- ------------------- ------- - - -

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS UNDO$

                                      15             15 TABLE

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N

19 rows selected. SQL> set termout on SQL> alter session set sort_area_size=1073741820; Session altered. SQL> set termout off SQL> select * from t where rownum < 20 order by 1, 2, 3, 4; OWNER OBJECT_NAME


------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE


---------- -------------- -------------------

CREATED LAST_DDL_ TIMESTAMP STATUS T G S


--------- ------------------- ------- - - -

SYS CON$

                                      28             28 TABLE

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS C_COBJ#

                                      29             29 CLUSTER

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N OWNER OBJECT_NAME


------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE


---------- -------------- -------------------

CREATED LAST_DDL_ TIMESTAMP STATUS T G S


--------- ------------------- ------- - - -

SYS FILE$

                                      17             17 TABLE

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS ICOL$

                                      20              2 TABLE

OWNER OBJECT_NAME


------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE


---------- -------------- -------------------

CREATED LAST_DDL_ TIMESTAMP STATUS T G S


--------- ------------------- ------- - - -

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS IND$

                                      19              2 TABLE

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CDEF2 OWNER OBJECT_NAME


------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE


---------- -------------- -------------------

CREATED LAST_DDL_ TIMESTAMP STATUS T G S


--------- ------------------- ------- - - -
                                      51             51 INDEX

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CDEF4

                                      53             53 INDEX

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N

OWNER OBJECT_NAME


------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE


---------- -------------- -------------------

CREATED LAST_DDL_ TIMESTAMP STATUS T G S


--------- ------------------- ------- - - -

SYS I_CON1

                                      48             48 INDEX

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_FILE#_BLOCK#

                                       9              9 INDEX

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N OWNER OBJECT_NAME


------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE


---------- -------------- -------------------

CREATED LAST_DDL_ TIMESTAMP STATUS T G S


--------- ------------------- ------- - - -

SYS I_FILE1

                                      41             41 INDEX

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_IND1

                                      39             39 INDEX

OWNER OBJECT_NAME


------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE


---------- -------------- -------------------

CREATED LAST_DDL_ TIMESTAMP STATUS T G S


--------- ------------------- ------- - - -

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_OBJ#

                                       3              3 INDEX

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_OBJ3 OWNER OBJECT_NAME


------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE


---------- -------------- -------------------

CREATED LAST_DDL_ TIMESTAMP STATUS T G S


--------- ------------------- ------- - - -
                                      38             38 INDEX

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_PROXY_ROLE_DATA$_1

                                      26             26 INDEX

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N

OWNER OBJECT_NAME


------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE


---------- -------------- -------------------

CREATED LAST_DDL_ TIMESTAMP STATUS T G S


--------- ------------------- ------- - - -

SYS I_TS#

                                       7              7 INDEX

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_USER1

                                      44             44 INDEX

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N OWNER OBJECT_NAME


------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE


---------- -------------- -------------------

CREATED LAST_DDL_ TIMESTAMP STATUS T G S


--------- ------------------- ------- - - -

SYS PROXY_ROLE_DATA$

                                      25             25 TABLE

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS UET$

                                      13              8 TABLE

OWNER OBJECT_NAME


------------------------------

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE


---------- -------------- -------------------

CREATED LAST_DDL_ TIMESTAMP STATUS T G S


--------- ------------------- ------- - - -

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS UNDO$

                                      15             15 TABLE

07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N

19 rows selected. SQL> set termout on SQL> SQL> drop table t; Table dropped. SQL> SQL></source>


dbms_stats.import_system_stats

   <source lang="sql">

SQL> alter system flush shared_pool; SQL> SQL> alter session set optimizer_index_cost_adj=100; SQL> SQL> alter session set optimizer_index_caching=0; SQL> SQL> begin

 2     dbms_stats.import_system_stats( stattab => "SYSTEM_STATS", statid => "OLTP", statown => user );
 3  end;
 4  /</source>
   
  

dbms_stats.objectTab

   <source lang="sql">

SQL> SQL> declare

 2    olist  dbms_stats.objectTab;
 3  begin
 4    dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> "FOR ALL INDEXED COLUMNS",options=>"GATHER STALE",objlist=>olist);
 5
 6    dbms_output.put_line("Objects Analyzed: " || olist.COUNT );
 7    if olist.COUNT > 0 then
 8       FOR x in 1..olist.COUNT LOOP
 9           dbms_output.put_line("Object Name: " || olist(x).objname );
10       END LOOP;
11    end if;
12
13  end;
14  /

Objects Analyzed: 2 Object Name: MYSTATS Object Name: P1 PL/SQL procedure successfully completed. SQL> SQL></source>


dbms_stats.set_table_stats( user, "EMP", numrows => 100000 )

   <source lang="sql">

SQL> CREATE TABLE EMP(

 2      EMPNO NUMBER(4) NOT NULL,
 3      ENAME VARCHAR2(10),
 4      JOB VARCHAR2(9),
 5      MGR NUMBER(4),
 6      HIREDATE DATE,
 7      SAL NUMBER(7, 2),
 8      COMM NUMBER(7, 2),
 9      DEPTNO NUMBER(2)
10  );

SQL> SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); SQL> SQL> CREATE TABLE DEPT(

 2      DEPTNO NUMBER(2),
 3      DNAME VARCHAR2(14),
 4      LOC VARCHAR2(13)
 5  );

SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); SQL> SQL> create materialized view emp_dept

 2  build immediate
 3  refresh on demand
 4  enable query rewrite
 5  as
 6  select dept.deptno, dept.dname, count (*)
 7    from emp, dept
 8   where emp.deptno = dept.deptno
 9   group by dept.deptno, dept.dname
10  /

SQL> SQL> begin

 2      dbms_stats.set_table_stats( user, "EMP", numrows => 100000 );
 3      dbms_stats.set_table_stats( user, "DEPT", numrows =>  1000 );
 4  end;
 5  /

SQL> SQL> SQL> drop table emp; SQL> SQL> drop table dept;</source>


dbms_stats.set_table_stats( user, "myTable1", numrows => 10000000, numblks => 1000000 )

   <source lang="sql">

SQL> SQL> SQL> create table myTable1

 2  as
 3  select *
 4    from all_users
 5   where 1=0;

SQL> SQL> create index myTable1_username_idx on myTable1(username);

SQL> SQL> create table myTable2

 2  as
 3  select *
 4    from all_objects
 5   created;

SQL> SQL> exec dbms_stats.set_table_stats( user, "myTable1", numrows => 10000000, numblks => 1000000 ); PL/SQL procedure successfully completed. SQL> SQL> exec dbms_stats.set_table_stats( user, "myTable2", numrows => 10000, numblks => 1000 ); PL/SQL procedure successfully completed. SQL> SQL> set autotrace traceonly explain SQL> select myTable1.username, sum(myTable2.object_id)

 2    from myTable1, myTable2
 3   where myTable1.username = myTable2.owner (+)
 4   group by myTable1.username
 5  /

SQL> SQL> set autotrace off SQL> SQL> drop table myTable1; SQL> drop table myTable2; Table dropped.</source>


execute SYS.DBMS_STATS.gather_table_stats(USER, "employee_job");

   <source lang="sql">

SQL> SQL> SQL> create table employee_job (

 2  id                             number                         not null,
 3  worker_id                      number                         not null,
 4  logical_workplace_id           number                         not null,
 5  active_date                    date          default SYSDATE  not null,
 6  inactive_date                  date )
 7  tablespace USERS pctfree 20
 8  storage (initial 10K next 10K pctincrease 0);

Table created. SQL> SQL> execute SYS.DBMS_STATS.gather_table_stats(USER, "employee_job"); PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table employee_job; Table dropped.</source>