alter session set OPTIMIZER_MODE = RULE

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

Table created.

SQL>
SQL> INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK',    7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7521, 'WARD',  'SALESMAN', 7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER',  7839, TO_DATE('2-APR-1981',  'DD-MON-YYYY'), 2975, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);

1 row created.

SQL> INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);

1 row created.

SQL> INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

1 row created.

SQL>
SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) );

Table created.

SQL>
SQL> INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');

1 row created.

SQL> INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');

1 row created.

SQL> INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');

1 row created.

SQL> INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

1 row created.

SQL>
SQL>
SQL> begin
  2           dbms_stats.set_table_stats
  3           ( user, 'EMP', numrows => 10000000, numblks => 1000000 );
  4           dbms_stats.set_table_stats
  5           ( user, 'DEPT', numrows => 1000000, numblks => 100000 );
  6      end;
  7      /

PL/SQL procedure successfully completed.

SQL>
SQL> select * from emp, dept where emp.deptno = dept.deptno;

     EMPNO Employee Name JOB              MGR HIREDATE      Salary       COMM     DEPTNO     DEPTNO DNAME          LOC
---------- ------------- --------- ---------- --------- ---------- ---------- ---------- ---------- -------------- -------------
      7369 SMITH         CLERK           7902 17-DEC-80    $800.00                    20         20 RESEARCH       DALLAS
      7499 ALLEN         SALESMAN        7698 20-FEB-81  $1,600.00        300         30         30 SALES          CHICAGO
      7521 WARD          SALESMAN        7698 22-FEB-81  $1,250.00        500         30         30 SALES          CHICAGO
      7566 JONES         MANAGER         7839 02-APR-81  $2,975.00                    20         20 RESEARCH       DALLAS
      7654 MARTIN        SALESMAN        7698 28-SEP-81  $1,250.00       1400         30         30 SALES          CHICAGO
      7698 BLAKE         MANAGER         7839 01-MAY-81  $2,850.00                    30         30 SALES          CHICAGO
      7782 CLARK         MANAGER         7839 09-JUN-81  $2,450.00                    10         10 ACCOUNTING     NEW YORK

     EMPNO Employee Name JOB              MGR HIREDATE      Salary       COMM     DEPTNO     DEPTNO DNAME          LOC
---------- ------------- --------- ---------- --------- ---------- ---------- ---------- ---------- -------------- -------------
      7788 SCOTT         ANALYST         7566 09-DEC-82  $3,000.00                    20         20 RESEARCH       DALLAS
      7839 KING          PRESIDENT            17-NOV-81  $5,000.00                    10         10 ACCOUNTING     NEW YORK
      7844 TURNER        SALESMAN        7698 08-SEP-81  $1,500.00          0         30         30 SALES          CHICAGO
      7876 ADAMS         CLERK           7788 12-JAN-83  $1,100.00                    20         20 RESEARCH       DALLAS
      7900 JAMES         CLERK           7698 03-DEC-81    $950.00                    30         30 SALES          CHICAGO
      7902 FORD          ANALYST         7566 03-DEC-81  $3,000.00                    20         20 RESEARCH       DALLAS
      7934 MILLER        CLERK           7782 23-JAN-82  $1,300.00                    10         10 ACCOUNTING     NEW YORK

14 rows selected.

SQL>
SQL> alter session set OPTIMIZER_MODE = RULE;

Session altered.

SQL>
SQL> select * from emp, dept where emp.deptno = dept.deptno;

     EMPNO Employee Name JOB              MGR HIREDATE      Salary       COMM     DEPTNO     DEPTNO DNAME          LOC
---------- ------------- --------- ---------- --------- ---------- ---------- ---------- ---------- -------------- -------------
      7782 CLARK         MANAGER         7839 09-JUN-81  $2,450.00                    10         10 ACCOUNTING     NEW YORK
      7839 KING          PRESIDENT            17-NOV-81  $5,000.00                    10         10 ACCOUNTING     NEW YORK
      7934 MILLER        CLERK           7782 23-JAN-82  $1,300.00                    10         10 ACCOUNTING     NEW YORK
      7566 JONES         MANAGER         7839 02-APR-81  $2,975.00                    20         20 RESEARCH       DALLAS
      7902 FORD          ANALYST         7566 03-DEC-81  $3,000.00                    20         20 RESEARCH       DALLAS
      7876 ADAMS         CLERK           7788 12-JAN-83  $1,100.00                    20         20 RESEARCH       DALLAS
      7369 SMITH         CLERK           7902 17-DEC-80    $800.00                    20         20 RESEARCH       DALLAS

     EMPNO Employee Name JOB              MGR HIREDATE      Salary       COMM     DEPTNO     DEPTNO DNAME          LOC
---------- ------------- --------- ---------- --------- ---------- ---------- ---------- ---------- -------------- -------------
      7788 SCOTT         ANALYST         7566 09-DEC-82  $3,000.00                    20         20 RESEARCH       DALLAS
      7521 WARD          SALESMAN        7698 22-FEB-81  $1,250.00        500         30         30 SALES          CHICAGO
      7844 TURNER        SALESMAN        7698 08-SEP-81  $1,500.00          0         30         30 SALES          CHICAGO
      7499 ALLEN         SALESMAN        7698 20-FEB-81  $1,600.00        300         30         30 SALES          CHICAGO
      7900 JAMES         CLERK           7698 03-DEC-81    $950.00                    30         30 SALES          CHICAGO
      7698 BLAKE         MANAGER         7839 01-MAY-81  $2,850.00                    30         30 SALES          CHICAGO
      7654 MARTIN        SALESMAN        7698 28-SEP-81  $1,250.00       1400         30         30 SALES          CHICAGO

14 rows selected.

SQL>
SQL> drop table emp cascade constraints;

Table dropped.

SQL>
SQL> drop table dept cascade constraints;

Table dropped.

SQL>
SQL>

SQL>

Related examples in the same category

1.	alter the session with the ALTER SESSION statement and set the session's time zone forward
2.	alter session set nls_date_format = 'dd-MON-yyyy hh24:mi:ss';
3.	Adjust your session time zone to -08:00, display the contents of your time table.
4.	alter session set cursor_sharing = force
5.	alter session set use_stored_outlines
6.	Event-based call and exception tracing
7.	event-based call and exception tracing.
8.	SQL trace
9.	uses dynamic SQL to issue an ALTER SESSION statement.
10.	ALTER SESSION SET QUERY_REWRITE_ENABLED
11.	alter session set sql_trace=true
12.	sql_trace a stored procedure
13.	Set the following session parameters to enable query rewrite:
14.	If your session time zone is not US/Central (-06:00), alter your session to Central time:
15.	alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'
16.	Alter time_zone
17.	sessiontimezone, current_timestamp
18.	demonstrates the use of DBMS_SQL to execute an ALTER SESSION statement.
19.	interaction between ALTER SESSION and autonomous transactions.
20.	ALTER SESSION SET SQL_TRACE = TRUE
21.	ALTER SESSION SET optimizer_dynamic_sampling
22.	alter session set NLS_DATE_FORMAT='DD-MM-YYYY'
23.	alter session set current_schema=Smart
24.	alter session set db_file_multiblock_read_count
25.	alter session set hash_area_size
26.	alter session set nls_date_format='dd-mm-yyyy', nls_language=Dutch, nls_currency='Eur'
27.	alter session set optimizer_index_cost_adj, alter session set optimizer_index_caching
28.	alter session set optimizer_max_permutations=80000
29.	alter session set optimizer_mode=all_rows
30.	alter session set optimizer_mode=first_rows
31.	alter session set query_rewrite_integrity=enforced
32.	alter session set sort_area_size = 102400000
33.	alter session set workarea_size_policy=manual
34.	alter system flush shared_pool
35.	alter system quiesce restricted
36.	alter system resume
37.	alter system suspend
38.	set optimizer_features_enable = '8.1.5' scope = spfile
39.	show filestat
40.	show parameter optimizer_features
41.	show verify
42.	If your session time zone is not US/Central Standard Time (-06:00), alter your session to Central Standard time:

alter session set OPTIMIZER_MODE = RULE : Session variable « SQL Plus « Oracle PL / SQL

Related examples in the same category