PL/SQL -> SQL -> SQL Queries

SQL Queries for Practice

Go Back

DBA Related Stuff 1

Indexes

SQL> cl scr 
 
SQL> SET VERIFY OFF 
SQL> cl scr 
 
SQL> DESC Emp 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 EMPNO                                     NOT NULL NUMBER(4) 
 ENAME                                              VARCHAR2(10) 
 JOB                                                VARCHAR2(9) 
 MGR                                                NUMBER(4) 
 HIREDATE                                           DATE 
 SAL                                                NUMBER(7,2) 
 COMM                                               NUMBER(7,2) 
 DEPTNO                                    NOT NULL NUMBER(2) 
 
SQL> CREATE INDEX EmpEmpnoIDX 
  2  ON Emp(Empno); 
ON Emp(Empno) 
       * 
ERROR at line 2: 
ORA-01408: such column list already indexed  
 
 
SQL> COLUMN Empno FORMAT 9999 
SQL> COLUMN Deptno FORMAT 99 
SQL> COLUMN MGR FORMAT 9999 
SQL> COLUMN Sal FORMAT 9999 
SQL> COLUMN Comm FORMAT 9999 
SQL> cl scr 
 
SQL> SELECT * FROM Emp; 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7839 KING       PRESIDENT       17-NOV-81  5000           10                    
 7698 BLAKE      MANAGER    7839 01-MAY-81  2850           30                    
 7782 CLARK      MANAGER    7839 09-JUN-81  2450           10                    
 7566 JONES      MANAGER    7839 02-APR-81  2975           20                    
 7654 MARTIN     SALESMAN   7698 28-SEP-81  1250  1400     30                    
 7499 ALLEN      SALESMAN   7698 20-FEB-81  1600   300     30                    
 7844 TURNER     SALESMAN   7698 08-SEP-81  1500     0     30                    
 7900 JAMES      CLERK      7698 03-DEC-81   950           30                    
 7521 WARD       SALESMAN   7698 22-FEB-81  1250   500     30                    
 7902 FORD       ANALYST    7566 03-DEC-81  3000           20                    
 7369 SMITH      CLERK      7902 17-DEC-80   800           20                    
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7788 SCOTT      ANALYST    7566 09-DEC-82  3000           20                    
 7876 ADAMS      CLERK      7788 12-JAN-83  1100           20                    
 7934 MILLER     CLERK      7782 23-JAN-82  1300           10                    
 
14 rows selected. 
 

SQL> SELECT * FROM Emp 
  2  WHERE Empno = 7566; 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7566 JONES      MANAGER    7839 02-APR-81  2975           20                    
 
SQL> cl scr 
 
SQL> SET AUTOTRACE ON EXPLAIN 
SQL> SELECT * 
  2  FROM Emp 
  3  WHERE Empno = 7566; 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7566 JONES      MANAGER    7839 02-APR-81  2975           20                    
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=87)           
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=1 Car           
          d=1 Bytes=87)                                                          
                                                                                 
   2    1     INDEX (UNIQUE SCAN) OF 'EMP_PRIMARY_KEY' (INDEX (UNIQUE)           
          ) (Cost=1 Card=1)                                                      
                                                                                 
 
 
 
SQL> SELECT * 
  2  FROM Emp 
  3  WHERE Ename = 'JONES'; 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7566 JONES      MANAGER    7839 02-APR-81  2975           20                    
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=87)           
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=1 Bytes=           
          87)                                                                    
                                                                                 
 
 
 
SQL> CREATE INDEX EmpEnameIDX 
  2  ON Emp(Ename); 
 
Index created. 
 
SQL> SELECT * 
  2  FROM Emp 

  3  WHERE Ename = 'JONES'; 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7566 JONES      MANAGER    7839 02-APR-81  2975           20                    
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=87)           
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Car           
          d=1 Bytes=87)                                                          
                                                                                 
   2    1     INDEX (RANGE SCAN) OF 'EMPENAMEIDX' (INDEX) (Cost=1 Card           
          =1)                                                                    
                                                                                 
 
 
 
SQL> SELECT * 
  2  FROM Emp 
  3  WHERE Empno = 7566; 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7566 JONES      MANAGER    7839 02-APR-81  2975           20                    
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=87)           
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=1 Car           
          d=1 Bytes=87)                                                          
                                                                                 
   2    1     INDEX (UNIQUE SCAN) OF 'EMP_PRIMARY_KEY' (INDEX (UNIQUE)           
          ) (Cost=1 Card=1)                                                      
                                                                                 
 
 
 
SQL> cl scr 
 
SQL> SELECT * 
  2  FROM Emp 
  3  WHERE Empno = 7566; 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7566 JONES      MANAGER    7839 02-APR-81  2975           20                    
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=87)           
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=1 Car           
          d=1 Bytes=87)                                                          

                                                                                 
   2    1     INDEX (UNIQUE SCAN) OF 'EMP_PRIMARY_KEY' (INDEX (UNIQUE)           
          ) (Cost=1 Card=1)                                                      
                                                                                 
 
 
 
SQL> SELECT * 
  2  FROM Emp 
  3  WHERE Empno > 7566; 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7839 KING       PRESIDENT       17-NOV-81  5000           10                    
 7698 BLAKE      MANAGER    7839 01-MAY-81  2850           30                    
 7782 CLARK      MANAGER    7839 09-JUN-81  2450           10                    
 7654 MARTIN     SALESMAN   7698 28-SEP-81  1250  1400     30                    
 7844 TURNER     SALESMAN   7698 08-SEP-81  1500     0     30                    
 7900 JAMES      CLERK      7698 03-DEC-81   950           30                    
 7902 FORD       ANALYST    7566 03-DEC-81  3000           20                    
 7788 SCOTT      ANALYST    7566 09-DEC-82  3000           20                    
 7876 ADAMS      CLERK      7788 12-JAN-83  1100           20                    
 7934 MILLER     CLERK      7782 23-JAN-82  1300           10                    
 
10 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=10 Bytes=87           
          0)                                                                     
                                                                                 
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=10 Bytes           
          =870)                                                                  
                                                                                 
 
 
 
SQL> SELECT * 
  2  FROM Emp 
  3  WHERE Empno < 7566; 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7499 ALLEN      SALESMAN   7698 20-FEB-81  1600   300     30                    
 7521 WARD       SALESMAN   7698 22-FEB-81  1250   500     30                    
 7369 SMITH      CLERK      7902 17-DEC-80   800           20                    
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=3 Bytes=261           
          )                                                                      
                                                                                 
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=3 Bytes=           
          261)                                                                   

                                                                                 
 
 
 
SQL> SELECT * 
  2   
SQL> SELECT * 
  2  FROM Emp 
  3  WHERE Empno <> 7566; 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7839 KING       PRESIDENT       17-NOV-81  5000           10                    
 7698 BLAKE      MANAGER    7839 01-MAY-81  2850           30                    
 7782 CLARK      MANAGER    7839 09-JUN-81  2450           10                    
 7654 MARTIN     SALESMAN   7698 28-SEP-81  1250  1400     30                    
 7499 ALLEN      SALESMAN   7698 20-FEB-81  1600   300     30                    
 7844 TURNER     SALESMAN   7698 08-SEP-81  1500     0     30                    
 7900 JAMES      CLERK      7698 03-DEC-81   950           30                    
 7521 WARD       SALESMAN   7698 22-FEB-81  1250   500     30                    
 7902 FORD       ANALYST    7566 03-DEC-81  3000           20                    
 7369 SMITH      CLERK      7902 17-DEC-80   800           20                    
 7788 SCOTT      ANALYST    7566 09-DEC-82  3000           20                    
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7876 ADAMS      CLERK      7788 12-JAN-83  1100           20                    
 7934 MILLER     CLERK      7782 23-JAN-82  1300           10                    
 
13 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=13 Bytes=11           
          31)                                                                    
                                                                                 
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=13 Bytes           
          =1131)                                                                 
                                                                                 
 
 
 
SQL> SELECT * 
  2  FROM Emp 
  3  WHERE Empno = 7566 OR Empno = 7839 OR Empno = 7782; 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7566 JONES      MANAGER    7839 02-APR-81  2975           20                    
 7782 CLARK      MANAGER    7839 09-JUN-81  2450           10                    
 7839 KING       PRESIDENT       17-NOV-81  5000           10                    
 
 
Execution Plan 
----------------------------------------------------------                       

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=3 Bytes=261           
          )                                                                      
                                                                                 
   1    0   INLIST ITERATOR                                                      
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 C           
          ard=3 Bytes=261)                                                       
                                                                                 
   3    2       INDEX (RANGE SCAN) OF 'EMP_PRIMARY_KEY' (INDEX (UNIQUE           
          )) (Cost=2 Card=1)                                                     
                                                                                 
 
 
 
SQL> SELECT * 
  2  FROM Emp 
  3  WHERE Empno IN(7566, 7839, 7782); 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7566 JONES      MANAGER    7839 02-APR-81  2975           20                    
 7782 CLARK      MANAGER    7839 09-JUN-81  2450           10                    
 7839 KING       PRESIDENT       17-NOV-81  5000           10                    
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=3 Bytes=261           
          )                                                                      
                                                                                 
   1    0   INLIST ITERATOR                                                      
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 C           
          ard=3 Bytes=261)                                                       
                                                                                 
   3    2       INDEX (RANGE SCAN) OF 'EMP_PRIMARY_KEY' (INDEX (UNIQUE           
          )) (Cost=2 Card=1)                                                     
                                                                                 
 
 
 
SQL> SELECT * 
  2  FROM Emp 
  3  WHERE Empno = 7566 AND Empno = 7654; 
 
no rows selected 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=87)           
   1    0   FILTER                                                               
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=1 C           
          ard=1 Bytes=87)                                                        
                                                                                 
   3    2       INDEX (UNIQUE SCAN) OF 'EMP_PRIMARY_KEY' (INDEX (UNIQU           
          E)) (Cost=1 Card=1)                                                    
                                                                                
 
SQL> SELECT * 
  2  FROM Emp 
  3  WHERE Empno = 7566 AND Ename = 'JONES'; 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7566 JONES      MANAGER    7839 02-APR-81  2975           20                    
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=87)           
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=1 Car           
          d=1 Bytes=87)                                                          
                                                                                 
   2    1     INDEX (RANGE SCAN) OF 'EMPENAMEIDX' (INDEX) (Cost=1 Card           
          =1)                                                                    
                                                                                 
 
 
 
SQL> SELECT * 
  2  FROM Emp 
  3  WHERE Empno = 7566 OR Ename = 'JONES'; 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7566 JONES      MANAGER    7839 02-APR-81  2975           20                    
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=87)           
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=3 Car           
          d=1 Bytes=87)                                                          
                                                                                 
   2    1     BITMAP CONVERSION (TO ROWIDS)                                      
   3    2       BITMAP OR                                                        
   4    3         BITMAP CONVERSION (FROM ROWIDS)                                
   5    4           INDEX (RANGE SCAN) OF 'EMP_PRIMARY_KEY' (INDEX (UN           
          IQUE)) (Cost=1)                                                        
                                                                                 
   6    3         BITMAP CONVERSION (FROM ROWIDS)                                
   7    6           INDEX (RANGE SCAN) OF 'EMPENAMEIDX' (INDEX) (Cost=           
          1)                                                                     
                                                                                 
 
 
 
SQL> SPOOL OFF 
SQL> cl scr 
 
SQL> CREATE INDEX EmpEnameJobIDX 

  2  AS 
  3   
SQL> CREATE INDEX EmpEnameJobIDX 
  2  ON Emp(Ename, Job); 
 
Index created. 
 
SQL> SELECT Ename, Sal, Deptno, Job, HireDate 
  2  FROM Emp 
  3  WHERE Ename = 'JONES'; 
 
ENAME             SAL     DEPTNO JOB       HIREDATE                              
---------- ---------- ---------- --------- ---------                             
JONES            2975         20 MANAGER   02-APR-81                             
 
SQL> SELECT Ename, Sal, Deptno, Job, HireDate 
  2  FROM Emp 
  3  WHERE Job = 'MANAGER'; 
 
ENAME             SAL     DEPTNO JOB       HIREDATE                              
---------- ---------- ---------- --------- ---------                             
BLAKE            2850         30 MANAGER   01-MAY-81                             
CLARK            2450         10 MANAGER   09-JUN-81                             
JONES            2975         20 MANAGER   02-APR-81                             
 
SQL> SET AUTOTRACE ON EXPLAIN 
SQL> SELECT Ename, Sal, Deptno, Job, HireDate 
  2  FROM Emp 
  3  WHERE Ename = 'JONES'; 
 
ENAME             SAL     DEPTNO JOB       HIREDATE                              
---------- ---------- ---------- --------- ---------                             
JONES            2975         20 MANAGER   02-APR-81                             
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=48)           
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Car           
          d=1 Bytes=48)                                                          
                                                                                 
   2    1     INDEX (RANGE SCAN) OF 'EMPENAMEJOBIDX' (INDEX) (Cost=1 C           
          ard=1)                                                                 
                                                                                 
 
 
 
SQL> SELECT Ename, Sal, Deptno, Job, HireDate 
  2  FROM Emp 
  3  WHERE Ename = 'JONES'; 
 
ENAME             SAL     DEPTNO JOB       HIREDATE                              
---------- ---------- ---------- --------- ---------                             
JONES            2975         20 MANAGER   02-APR-81                             
 
 

Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=48)           
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Car           
          d=1 Bytes=48)                                                          
                                                                                 
   2    1     INDEX (RANGE SCAN) OF 'EMPENAMEJOBIDX' (INDEX) (Cost=1 C           
          ard=1)                                                                 
                                                                                 
 
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Empno, Ename, Sal, Deptno, Job, HireDate 
  2  FROM Emp 
  3* WHERE Empno = 7566 
SQL> / 
 
     EMPNO ENAME             SAL     DEPTNO JOB       HIREDATE                   
---------- ---------- ---------- ---------- --------- ---------                  
      7566 JONES            2975         20 MANAGER   02-APR-81                  
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=61)           
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=1 Car           
          d=1 Bytes=61)                                                          
                                                                                 
   2    1     INDEX (UNIQUE SCAN) OF 'EMP_PRIMARY_KEY' (INDEX (UNIQUE)           
          ) (Cost=1 Card=1)                                                      
                                                                                 
 
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Empno, Ename, Sal, Deptno, Job, HireDate 
  2  FROM Emp 
  3* WHERE Empno = 7566 AND Ename = 'JONES' 
SQL> / 
 
     EMPNO ENAME             SAL     DEPTNO JOB       HIREDATE                   
---------- ---------- ---------- ---------- --------- ---------                  
      7566 JONES            2975         20 MANAGER   02-APR-81                  
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=61)           
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=1 Car           
          d=1 Bytes=61)                                                          
                                                                                 

   2    1     INDEX (RANGE SCAN) OF 'EMPENAMEJOBIDX' (INDEX) (Cost=1 C           
          ard=1)                                                                 
                                                                                 
 
 
 
SQL> SELECT Ename, Sal, Deptno, Job, HireDate 
  2  FROM Emp 
  3  WHERE Ename = 'JONES' AND Job = 'MANAGER'; 
 
ENAME             SAL     DEPTNO JOB       HIREDATE                              
---------- ---------- ---------- --------- ---------                             
JONES            2975         20 MANAGER   02-APR-81                             
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=48)           
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Car           
          d=1 Bytes=48)                                                          
                                                                                 
   2    1     INDEX (RANGE SCAN) OF 'EMPENAMEJOBIDX' (INDEX) (Cost=1 C           
          ard=1)                                                                 
                                                                                 
 
 
 
SQL> CREATE INDEX EmpEnameIDX 
  2  ON Emp(Ename); 
 
Index created. 
 
SQL> SELECT Ename, Sal, Deptno, Job, HireDate 
  2  FROM Emp 
  3  WHERE Ename = 'JONES' AND Job = 'MANAGER'; 
 
ENAME             SAL     DEPTNO JOB       HIREDATE                              
---------- ---------- ---------- --------- ---------                             
JONES            2975         20 MANAGER   02-APR-81                             
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=48)           
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Car           
          d=1 Bytes=48)                                                          
                                                                                 
   2    1     INDEX (RANGE SCAN) OF 'EMPENAMEJOBIDX' (INDEX) (Cost=1 C           
          ard=1)                                                                 
                                                                                 
 
 
 
SQL> SELECT Ename, Sal, Deptno, Job, HireDate 
  2  FROM Emp 
  3  WHERE Ename = 'JONES'; 

 
ENAME             SAL     DEPTNO JOB       HIREDATE                              
---------- ---------- ---------- --------- ---------                             
JONES            2975         20 MANAGER   02-APR-81                             
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=48)           
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Car           
          d=1 Bytes=48)                                                          
                                                                                 
   2    1     INDEX (RANGE SCAN) OF 'EMPENAMEIDX' (INDEX) (Cost=1 Card           
          =1)                                                                    
                                                                                 
 
 
 
SQL> SELECT Ename, Sal, Deptno, Job, HireDate 
  2  FROM Emp 
  3  WHERE Job = 'MANAGER'; 
 
ENAME             SAL     DEPTNO JOB       HIREDATE                              
---------- ---------- ---------- --------- ---------                             
BLAKE            2850         30 MANAGER   01-MAY-81                             
CLARK            2450         10 MANAGER   09-JUN-81                             
JONES            2975         20 MANAGER   02-APR-81                             
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=3 Bytes=144           
          )                                                                      
                                                                                 
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=3 Bytes=           
          144)                                                                   
                                                                                 
 
 
 
SQL> CREATE INDEX EmpJobEnameIDX 
  2  ON Emp(Job, Ename); 
 
Index created. 
 
SQL> SELECT Ename, Sal, Deptno, Job, HireDate 
  2  FROM Emp 
  3  WHERE Job = 'MANAGER'; 
 
ENAME             SAL     DEPTNO JOB       HIREDATE                              
---------- ---------- ---------- --------- ---------                             
BLAKE            2850         30 MANAGER   01-MAY-81                             
CLARK            2450         10 MANAGER   09-JUN-81                             
JONES            2975         20 MANAGER   02-APR-81                             
 
 

Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=3 Bytes=144           
          )                                                                      
                                                                                 
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Car           
          d=3 Bytes=144)                                                         
                                                                                 
   2    1     INDEX (RANGE SCAN) OF 'EMPJOBENAMEIDX' (INDEX) (Cost=1 C           
          ard=3)                                                                 
                                                                                 
 
 
 
SQL> CREATE INDEX EmpJobIDX 
  2  ON Emp(Job); 
 
Index created. 
 
SQL> SELECT Ename, Sal, Deptno, Job, HireDate 
  2  FROM Emp 
  3  WHERE Job = 'MANAGER'; 
 
ENAME             SAL     DEPTNO JOB       HIREDATE                              
---------- ---------- ---------- --------- ---------                             
BLAKE            2850         30 MANAGER   01-MAY-81                             
CLARK            2450         10 MANAGER   09-JUN-81                             
JONES            2975         20 MANAGER   02-APR-81                             
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=3 Bytes=144           
          )                                                                      
                                                                                 
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Car           
          d=3 Bytes=144)                                                         
                                                                                 
   2    1     INDEX (RANGE SCAN) OF 'EMPJOBENAMEIDX' (INDEX) (Cost=1 C           
          ard=3)                                                                 
                                                                                 
 
 
 
SQL> cl scr 
 
SQL> CREATE UNIQUE INDEX EmpEnameUNQIDX 
  2  ON Emp(Ename); 
ON Emp(Ename) 
       * 
ERROR at line 2: 
ORA-01408: such column list already indexed  
 
 
SQL> DROP INDEX EmpEnameIDX; 
 

Index dropped. 
 
SQL> CREATE UNIQUE INDEX EmpEnameUNQIDX 
  2  ON Emp(Ename); 
 
Index created. 
 
SQL> INSERT INTO Emp(Empno, Ename, Deptno, Job) 
  2  VALUES(1234, 'ADAMS', 30, 'CLERK'); 
INSERT INTO Emp(Empno, Ename, Deptno, Job) 
* 
ERROR at line 1: 
ORA-00001: unique constraint (SCOTT.EMPENAMEUNQIDX) violated  
 
 
SQL> DROP INDEX EmpEnameUNQIDX; 
 
Index dropped. 
 
SQL> cl scr 
 
SQL> CREATE UNIQUE INDEX EmpDeptnoUNQIDX 
  2  ON Emp(Deptno); 
ON Emp(Deptno) 
   * 
ERROR at line 2: 
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found  
 
 
SQL> cl scr 
 
SQL> ROLLBACK; 
 
Rollback complete. 
 
SQL> cl scr 
 
SQL> SELECT ROWID, Empno, Ename, Sal 
  2  FROM Emp; 
 
ROWID                   EMPNO ENAME             SAL                              
------------------ ---------- ---------- ----------                              
AAAMcCAAEAAAAHPAAA       7839 KING             5000                              
AAAMcCAAEAAAAHPAAB       7698 BLAKE            2850                              
AAAMcCAAEAAAAHPAAC       7782 CLARK            2450                              
AAAMcCAAEAAAAHPAAD       7566 JONES            2975                              
AAAMcCAAEAAAAHPAAE       7654 MARTIN           1250                              
AAAMcCAAEAAAAHPAAF       7499 ALLEN            1600                              
AAAMcCAAEAAAAHPAAG       7844 TURNER           1500                              
AAAMcCAAEAAAAHPAAH       7900 JAMES             950                              
AAAMcCAAEAAAAHPAAI       7521 WARD             1250                              
AAAMcCAAEAAAAHPAAJ       7902 FORD             3000                              
AAAMcCAAEAAAAHPAAK       7369 SMITH             800                              
 
ROWID                   EMPNO ENAME             SAL                              
------------------ ---------- ---------- ----------                              

AAAMcCAAEAAAAHPAAL       7788 SCOTT            3000                              
AAAMcCAAEAAAAHPAAM       7876 ADAMS            1100                              
AAAMcCAAEAAAAHPAAN       7934 MILLER           1300                              
 
14 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=56           
          0)                                                                     
                                                                                 
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes           
          =560)                                                                  
                                                                                 
 
 
 
SQL> cl scr 
 
SQL> SELECT ROWID, Empno, Ename, Deptno 
  2  FROM Emp; 
 
ROWID                   EMPNO ENAME          DEPTNO                              
------------------ ---------- ---------- ----------                              
AAAMcCAAEAAAAHPAAA       7839 KING               10                              
AAAMcCAAEAAAAHPAAB       7698 BLAKE              30                              
AAAMcCAAEAAAAHPAAC       7782 CLARK              10                              
AAAMcCAAEAAAAHPAAD       7566 JONES              20                              
AAAMcCAAEAAAAHPAAE       7654 MARTIN             30                              
AAAMcCAAEAAAAHPAAF       7499 ALLEN              30                              
AAAMcCAAEAAAAHPAAG       7844 TURNER             30                              
AAAMcCAAEAAAAHPAAH       7900 JAMES              30                              
AAAMcCAAEAAAAHPAAI       7521 WARD               30                              
AAAMcCAAEAAAAHPAAJ       7902 FORD               20                              
AAAMcCAAEAAAAHPAAK       7369 SMITH              20                              
 
ROWID                   EMPNO ENAME          DEPTNO                              
------------------ ---------- ---------- ----------                              
AAAMcCAAEAAAAHPAAL       7788 SCOTT              20                              
AAAMcCAAEAAAAHPAAM       7876 ADAMS              20                              
AAAMcCAAEAAAAHPAAN       7934 MILLER             10                              
 
14 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=56           
          0)                                                                     
                                                                                 
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes           
          =560)                                                                  
                                                                                 
 
 

 
SQL> cl scr 
 
SQL> SELECT Ename, Deptno, Sal, Comm, Sal + NVL(Comm) TotSal 
  2  FROM Emp 
  3  WHERE Sal + NVL(Comm) > 2000; 
SELECT Ename, Deptno, Sal, Comm, Sal + NVL(Comm) TotSal 
                                       * 
ERROR at line 1: 
ORA-00909: invalid number of arguments  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Deptno, Sal, Comm, Sal + NVL(Comm, 0) TotSal 
  2  FROM Emp 
  3* WHERE Sal + NVL(Comm, 0) > 2000 
SQL> / 
 
ENAME          DEPTNO        SAL       COMM     TOTSAL                           
---------- ---------- ---------- ---------- ----------                           
KING               10       5000                  5000                           
BLAKE              30       2850                  2850                           
CLARK              10       2450                  2450                           
JONES              20       2975                  2975                           
MARTIN             30       1250       1400       2650                           
FORD               20       3000                  3000                           
SCOTT              20       3000                  3000                           
 
7 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=7 Bytes=322           
          )                                                                      
                                                                                 
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=7 Bytes=           
          322)                                                                   
                                                                                 
 
 
 
SQL> CREATE INDEX EmpTotSalIDX 
  2  ON Emp(Sal + NVL(Comm, 0)); 
 
Index created. 
 
SQL> SELECT Ename, Deptno, Sal, Comm, Sal + NVL(Comm, 0) TotSal 
  2  FROM Emp 
  3  WHERE Sal + NVL(Comm, 0) > 2000; 
 
ENAME          DEPTNO        SAL       COMM     TOTSAL                           
---------- ---------- ---------- ---------- ----------                           
CLARK              10       2450                  2450                           

MARTIN             30       1250       1400       2650                           
BLAKE              30       2850                  2850                           
JONES              20       2975                  2975                           
FORD               20       3000                  3000                           
SCOTT              20       3000                  3000                           
KING               10       5000                  5000                           
 
7 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=7 Bytes=322           
          )                                                                      
                                                                                 
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Car           
          d=7 Bytes=322)                                                         
                                                                                 
   2    1     INDEX (RANGE SCAN) OF 'EMPTOTSALIDX' (INDEX) (Cost=1 Car           
          d=1)                                                                   
                                                                                 
 
 
 
SQL> SELECT Ename, Sal, Sal * 12 AnnSal 
  2  FROM Emp 
  3  WHERE Sal * 12 > 35000; 
 
ENAME             SAL     ANNSAL                                                 
---------- ---------- ----------                                                 
KING             5000      60000                                                 
JONES            2975      35700                                                 
FORD             3000      36000                                                 
SCOTT            3000      36000                                                 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=4 Bytes=80)           
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=4 Bytes=           
          80)                                                                    
                                                                                 
 
 
 
SQL> CREATE INDEX EmpAnnSalIDX 
  2  ON Emp(Sal * 12); 
 
Index created. 
 
SQL> SELECT Ename, Sal, Sal * 12 AnnSal 
  2  FROM Emp 
  3  WHERE Sal * 12 > 35000; 
 
ENAME             SAL     ANNSAL                                                 
---------- ---------- ----------                                                 

JONES            2975      35700                                                 
FORD             3000      36000                                                 
SCOTT            3000      36000                                                 
KING             5000      60000                                                 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=4 Bytes=80)           
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Car           
          d=4 Bytes=80)                                                          
                                                                                 
   2    1     INDEX (RANGE SCAN) OF 'EMPANNSALIDX' (INDEX) (Cost=1 Car           
          d=1)                                                                   
                                                                                 
 
 
 
SQL> SELECT Ename, Sal, Deptno, Job 
  2  FROM Emp 
  3  WHERE LOWER(Ename) = 'smith'; 
 
ENAME             SAL     DEPTNO JOB                                             
---------- ---------- ---------- ---------                                       
SMITH             800         20 CLERK                                           
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=39)           
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=1 Bytes=           
          39)                                                                    
                                                                                 
 
 
 
SQL> CREATE INDEX EmpEnameLIDX 
  2  ON Emp(LOWER(Ename)); 
 
Index created. 
 
SQL> CREATE INDEX EmpEnameUIDX 
  2  ON Emp(UPPER(Ename)); 
 
Index created. 
 
SQL> CREATE INDEX EmpEnameIIDX 
  2  ON Emp(INITCAP(Ename)); 
 
Index created. 
 
SQL> SELECT Ename, Sal, Deptno, Job 
  2  FROM Emp 
  3  WHERE LOWER(Ename) = 'smith'; 
 
ENAME             SAL     DEPTNO JOB                                             
---------- ---------- ---------- ---------                                       
SMITH             800         20 CLERK                                           
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=39)           
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Car           
          d=1 Bytes=39)                                                          
                                                                                 
   2    1     INDEX (RANGE SCAN) OF 'EMPENAMELIDX' (INDEX) (Cost=1 Car           
          d=1)                                                                   
                                                                                 
 
 
 
SQL> SELECT Ename, Sal, Deptno, Job 
  2  FROM Emp 
  3  WHERE INITCAP(Ename) = 'Smith'; 
 
ENAME             SAL     DEPTNO JOB                                             
---------- ---------- ---------- ---------                                       
SMITH             800         20 CLERK                                           
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=39)           
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Car           
          d=1 Bytes=39)                                                          
                                                                                 
   2    1     INDEX (RANGE SCAN) OF 'EMPENAMEIIDX' (INDEX) (Cost=1 Car           
          d=1)                                                                   
                                                                                 
 
 
 
SQL> SELECT Ename, Sal, Deptno, Job 
  2  FROM Emp 
  3  WHERE Ename = UPPER('smith'); 
 
ENAME             SAL     DEPTNO JOB                                             
---------- ---------- ---------- ---------                                       
SMITH             800         20 CLERK                                           
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=39)           
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Car           
          d=1 Bytes=39)                                                          
                                                                                 
   2    1     INDEX (RANGE SCAN) OF 'EMPENAMEJOBIDX' (INDEX) (Cost=1 C           
          ard=1)                                                                 
                                                                                 

SQL> SELECT Ename, HireDate, TRUNC(MONTHS_BETWEEN(SYSDATE, HireDate) / 12) 
EmpExp 
  2  FROM Emp 
  3  WHERE TRUNC(MONTHS_BETWEEN(SYSDATE, HireDate) / 12) > 28; 
 
ENAME      HIREDATE      EMPEXP                                                  
---------- --------- ----------                                                  
BLAKE      01-MAY-81         29                                                  
CLARK      09-JUN-81         29                                                  
JONES      02-APR-81         29                                                  
ALLEN      20-FEB-81         29                                                  
WARD       22-FEB-81         29                                                  
SMITH      17-DEC-80         29                                                  
 
6 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=6 Bytes=96)           
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=6 Bytes=           
          96)                                                                    
                                                                                 
 
 
 
SQL> CREATE INDEX EmpEmpExpIDX 
  2  ON Emp(TRUNC(MONTHS_BETWEEN(SYSDATE, HireDate) / 12)); 
ON Emp(TRUNC(MONTHS_BETWEEN(SYSDATE, HireDate) / 12)) 
                            * 
ERROR at line 2: 
ORA-01743: only pure functions can be indexed  
 
 
SQL> cl scr 
 
SQL> DESC USER_INDEXES 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 INDEX_NAME                                NOT NULL VARCHAR2(30) 
 INDEX_TYPE                                         VARCHAR2(27) 
 TABLE_OWNER                               NOT NULL VARCHAR2(30) 
 TABLE_NAME                                NOT NULL VARCHAR2(30) 
 TABLE_TYPE                                         VARCHAR2(11) 
 UNIQUENESS                                         VARCHAR2(9) 
 COMPRESSION                                        VARCHAR2(8) 
 PREFIX_LENGTH                                      NUMBER 
 TABLESPACE_NAME                                    VARCHAR2(30) 
 INI_TRANS                                          NUMBER 
 MAX_TRANS                                          NUMBER 
 INITIAL_EXTENT                                     NUMBER 
 NEXT_EXTENT                                        NUMBER 
 MIN_EXTENTS                                        NUMBER 
 MAX_EXTENTS                                        NUMBER 

 PCT_INCREASE                                       NUMBER 
 PCT_THRESHOLD                                      NUMBER 
 INCLUDE_COLUMN                                     NUMBER 
 FREELISTS                                          NUMBER 
 FREELIST_GROUPS                                    NUMBER 
 PCT_FREE                                           NUMBER 
 LOGGING                                            VARCHAR2(3) 
 BLEVEL                                             NUMBER 
 LEAF_BLOCKS                                        NUMBER 
 DISTINCT_KEYS                                      NUMBER 
 AVG_LEAF_BLOCKS_PER_KEY                            NUMBER 
 AVG_DATA_BLOCKS_PER_KEY                            NUMBER 
 CLUSTERING_FACTOR                                  NUMBER 
 STATUS                                             VARCHAR2(8) 
 NUM_ROWS                                           NUMBER 
 SAMPLE_SIZE                                        NUMBER 
 LAST_ANALYZED                                      DATE 
 DEGREE                                             VARCHAR2(40) 
 INSTANCES                                          VARCHAR2(40) 
 PARTITIONED                                        VARCHAR2(3) 
 TEMPORARY                                          VARCHAR2(1) 
 GENERATED                                          VARCHAR2(1) 
 SECONDARY                                          VARCHAR2(1) 
 BUFFER_POOL                                        VARCHAR2(7) 
 USER_STATS                                         VARCHAR2(3) 
 DURATION                                           VARCHAR2(15) 
 PCT_DIRECT_ACCESS                                  NUMBER 
 ITYP_OWNER                                         VARCHAR2(30) 
 ITYP_NAME                                          VARCHAR2(30) 
 PARAMETERS                                         VARCHAR2(1000) 
 GLOBAL_STATS                                       VARCHAR2(3) 
 DOMIDX_STATUS                                      VARCHAR2(12) 
 DOMIDX_OPSTATUS                                    VARCHAR2(6) 
 FUNCIDX_STATUS                                     VARCHAR2(8) 
 JOIN_INDEX                                         VARCHAR2(3) 
 IOT_REDUNDANT_PKEY_ELIM                            VARCHAR2(3) 
 DROPPED                                            VARCHAR2(3) 
 
SQL> DESC USER_IND_COLUMNS 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 INDEX_NAME                                         VARCHAR2(30) 
 TABLE_NAME                                         VARCHAR2(30) 
 COLUMN_NAME                                        VARCHAR2(4000) 
 COLUMN_POSITION                                    NUMBER 
 COLUMN_LENGTH                                      NUMBER 
 CHAR_LENGTH                                        NUMBER 
 DESCEND                                            VARCHAR2(4) 
 
SQL> SPOOL OFF 

Go Back