[교육] SQL초보에서실전전문가까지 ORACLE - 7

• DECENDING INDEX

CREATE INDEX  fidx_stud_no_name ON student (deptno DESC, name ASC);

• 함수기반 INDEX

CREATE INDEX  fidx_stud_no_name ON student (UPPER(ename));

• 실행계획보기

[oracle@localhost ~]$ cd $ORACLE_HOME/sqlplus/admin

[oracle@localhost admin]$ ls

glogin.sql  help  iplus  plustrce.sql  pupbld.sql

[oracle@localhost admin]$ pwd

/u01/app/oracle/product/10.2.0/db_1/sqlplus/admin

[oracle@localhost admin]$ ls -alrt plustrce.sql

-rw-r-----  1 oracle oinstall 801  3�›” 12  2002 plustrce.sql

[oracle@localhost admin]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 21 20:07:45 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> show user

USER is "SYS"

SQL> @plustrce.sql

SQL>

SQL> drop role plustrace;

drop role plustrace

          *

ERROR at line 1:

ORA-01919: role 'PLUSTRACE' does not exist

SQL> create role plustrace;

Role created.

SQL>

SQL> grant select on v_$sesstat to plustrace;

Grant succeeded.

SQL> grant select on v_$statname to plustrace;

Grant succeeded.

SQL> grant select on v_$mystat to plustrace;

Grant succeeded.

SQL> grant plustrace to dba with admin option;

Grant succeeded.

SQL>

SQL> set echo off

SQL>

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

[oracle@localhost admin]$ cd $ORACLE_HOME/rdbms/admin

[oracle@localhost admin]$ ls -alrt utilxplan

ls: utilxplan: 洹몃Ÿ� �ŒŒ�쇱대‚˜ �””��‰�†�由ш�€ �—†�Œ

[oracle@localhost admin]$ ls -alrt utilxplan.sql

ls: utilxplan.sql: 洹몃Ÿ� �ŒŒ�쇱대‚˜ �””��‰�†�由ш�€ �—†�Œ

[oracle@localhost admin]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 21 20:11:02 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> @utlxplan.sql

Table created.

SQL> grant plustrace to scott;  (scott 유저에게 해당 테이블 사용 권한을 줌)

Grant succeeded.

SQL> CREATE INDEX IDX_EMP_JOB ON EMP(JOB);

Index created.

SQL> set autotrace on

SQL> select * from emp where job='CLERK'

  2  ;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM  DEPTNO

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

      7369 SMITH      CLERK           7902 17-DEC-80           800       1000      20

      7876 ADAMS      CLERK           7788 23-MAY-87          1100       1000      20

      7900 JAMES      CLERK           7698 03-DEC-81           950       1000      30

      7934 MILLER     CLERK           7782 23-JAN-82          1300       1000      10

Execution Plan

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

Plan hash value: 3286489891

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

| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|  Time     |

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

|   0 | SELECT STATEMENT            |             |     3 |   111 |     3   (0)|  00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP         |     3 |   111 |     3   (0)|  00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_EMP_JOB |     3 |       |     1   (0)|  00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("JOB"='CLERK')

Statistics

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

          1  recursive calls

          0  db block gets

          4  consistent gets

          0  physical reads

          0  redo size

        989  bytes sent via SQL*Net to client

        385  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          4  rows processed

SQL> show parameter OPT

NAME                                 TYPE        VALUE

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

audit_sys_operations                 boolean     FALSE

filesystemio_options                 string      none

max_commit_propagation_delay         integer     0

object_cache_optimal_size            integer     102400

open_cursors                         integer     300

open_links                           integer     4

open_links_per_instance              integer     4

optimizer_dynamic_sampling           integer     2

optimizer_features_enable            string      10.2.0.1

optimizer_index_caching              integer     0

optimizer_index_cost_adj             integer     100

optimizer_mode                       string      ALL_ROWS    모든 Row 에 대해서 Cost 정보를 추적한다는 의미

optimizer_secure_view_merging        boolean     TRUE

plsql_optimize_level                 integer     2

read_only_open_delayed               boolean     FALSE

session_max_open_files               integer     10

• 동의어

• 계층적 질의문

오라클에서는 기본적으로 지원하는 문법임 이것을 잘 활용하면 좋을듯.

SELECT * FROM EMP WHERE JOB='CLERK';

create table dep (

deptno number(5),

dname varchar2(10),

pdeptno number(5));

delete from dep where deptno=10;

insert into dep values (10 ,'공과대학', NULL);

insert into dep values (100 ,'미디어학부', 10);

insert into dep values (101 ,'컴퓨공학과', 100);

insert into dep values (102,'멀티미학과', 100);

insert into dep values (200 ,'메카트학부', 10);

insert into dep values (201 ,'전자공학과', 200);

insert into dep values (202 ,'기계공학과', 200);

select * from dep;

SELECT deptno, dname, pdeptno 

FROM dep

WHERE dname != '멀티미학과'

START WITH pdeptno IS NULL

CONNECT BY PRIOR deptno = pdeptno;

SELECT deptno, dname, pdeptno 

FROM dep

START WITH pdeptno IS NULL

CONNECT BY PRIOR deptno = pdeptno;

dname != '멀티미학과'