Analyze table with primary key and foreign key : Analyze « SQL Plus « Oracle PL / SQL






Analyze table with primary key and foreign key

  
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> analyze table emp compute statistics;

Table analyzed.

SQL> analyze table dept compute statistics;

Table analyzed.

SQL>
SQL> alter table emp  add constraint emp_pk      primary key(empno);

Table altered.

SQL> alter table dept add constraint dept_pk     primary key(deptno);

Table altered.

SQL> alter table emp  add constraint emp_fk_dept foreign key (deptno) references dept;

Table altered.

SQL>
SQL> set autotrace traceonly explain
SQL>
SQL> select * from emp, dept where emp.deptno = dept.deptno;

Execution Plan
----------------------------------------------------------
Plan hash value: 3487251775

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   700 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |    14 |   700 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL          | EMP     |    14 |   448 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    18 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | DEPT_PK |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

SQL>
SQL> analyze table emp compute statistics;

Table analyzed.

SQL> analyze table dept compute statistics;

Table analyzed.

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

Execution Plan
----------------------------------------------------------
Plan hash value: 3487251775

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   700 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |    14 |   700 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL          | EMP     |    14 |   448 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    18 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | DEPT_PK |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

SQL>
SQL> set autotrace off
SQL>
SQL> drop table emp cascade constraints;

Table dropped.

SQL> drop table dept cascade constraints;

Table dropped.

SQL>

   
  








Related examples in the same category

1.analyze materialized view
2.analyze index
3.Demonstrate IN versus EXISTS performance
4.analyze table tableName compute statistics for table, for all indexes, for all indexed columns;
5.ANALYZE TABLE employee COMPUTE STATISTICS
6.analyze index validate structure
7.analyze table compute statistics
8.analyze table compute statistics for all indexes
9.analyze table compute statistics for a table with two indexes
10.create or replace outline and analyze table compute statistics
11.Analyze a table with/without index
12.Anayze all objects in the APPOWNER schema.
13.Analyze index on varchar2 value and date type value
14.analyze and autotrace full outer join and union
15.analyze and autotrace single column key and multi-column key
16.analyze and autotrace table with primary key
17.analyze index t_idx validate structure
18.analyze table TABLENAME compute statistics;
19.analyze table after creating index
20.analyze table estimate statistics
21.analyze table students compute statistics
22.analyze table t compute statistics for table for columns id;
23.analyze table t compute statistics for table, for all indexes, for all indexed columns