Oracle PL/SQL/System Tables Views/sys.col

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

Check column type with user-defined type in sys.col$

   <source lang="sql">
 

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> create or replace type emp_type

 2    as object
 3    (empno       number(4),
 4     ename       varchar2(10),
 5     job         varchar2(9),
 6     mgr         number(4),
 7     hiredate    date,
 8     sal         number(7, 2),
 9     comm        number(7, 2)
10    );
11  /

Type created. SQL> SQL> create or replace type emp_tab_type as table of emp_type;

 2  /

Type created. SQL> create table dept_and_emp

 2    (deptno number(2) primary key,
 3     dname     varchar2(14),
 4     loc       varchar2(13),
 5     emps      emp_tab_type
 6    )
 7    nested table emps store as emps_nt;

Table created. SQL> SQL> SQL> insert into dept_and_emp

 2    select dept.*,
 3       CAST( multiset( select empno, ename, job, mgr, hiredate, sal, comm from emp ) AS emp_tab_type )
 4      from dept
 5  /

4 rows created. SQL> SQL> SQL> select name

 2      from sys.col$
 3     where obj# = ( select object_id
 4                     from user_objects
 5                     where object_name = "DEPT_AND_EMP" )
 6  /

NAME


DEPTNO DNAME EMPS LOC SYS_NC0000400005$ 5 rows selected. SQL> SQL> SQL> drop table emp cascade constraints; Table dropped. SQL> drop table dept cascade constraints; Table dropped. SQL> drop table dept_and_emp; Table dropped. SQL> drop type emp_tab_type; Type dropped. SQL> drop type emp_type; Type dropped. SQL> SQL> --

 </source>
   
  


Join on and where clause

   <source lang="sql">

SQL> CREATE TABLE emp

 2  (
 3     cid                NUMBER,
 4     address    VARCHAR2(40)  ,
 5     postal_code       VARCHAR2(10)  ,
 6     city              VARCHAR2(30) ,
 7     customer_state    VARCHAR2(40)
 8  );

Table created. SQL> SQL> CREATE TABLE sales(

 2    product_id               NUMBER(6),
 3    cid               NUMBER,
 4    time_id               DATE,
 5    channel_id            CHAR(1),
 6    promo_id              NUMBER(6),
 7    sold         NUMBER(3),
 8    amount                NUMBER(10,2),
 9    cost                  NUMBER(10,2)
10  );

Table created. SQL> SQL> select c.cid, c.customer_state, s.sold

 2  from sales s join emp c
 3  on s.cid = c.cid
 4  where product_id = 117;

no rows selected SQL> SQL> SQL> drop table sales; Table dropped. SQL> drop table emp; Table dropped.

 </source>
   
  


Join sys.col$ and user_objects

   <source lang="sql">

SQL> SQL> create or replace type address_type as object

 2  ( city    varchar2(30),
 3    street  varchar2(30),
 4    state   varchar2(2),
 5    zip     number
 6  )
 7  /

SQL> SQL> create or replace type person_type as object

 2  ( name             varchar2(30),
 3    dob              date,
 4    home_address     address_type,
 5    work_address     address_type
 6  )
 7  /

Type created. SQL> create table people of person_type

 2  /

Table created. SQL> desc people

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
NAME                                               VARCHAR2(30)
DOB                                                DATE
HOME_ADDRESS                                       ADDRESS_TYPE
WORK_ADDRESS                                       ADDRESS_TYPE

SQL> SQL> SQL> select * from people; no rows selected SQL> SQL> select name, p.home_address.city from people p; no rows selected SQL> SQL> select name, segcollength from sys.col$

 2  where obj# = ( select object_id from user_objects where object_name = "PEOPLE" )
 3  /

NAME SEGCOLLENGTH


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

SYS_NC_OID$ 16 SYS_NC_ROWINFO$ 1 NAME 30 DOB 7 HOME_ADDRESS 1 SYS_NC00006$ 30 SYS_NC00007$ 30 SYS_NC00008$ 2 SYS_NC00009$ 22 WORK_ADDRESS 1 SYS_NC00011$ 30 SYS_NC00012$ 30 SYS_NC00013$ 2 SYS_NC00014$ 22 14 rows selected. SQL> SQL> drop table people; Table dropped. SQL> SQL>

 </source>
   
  


Query sys.col$ table with sub query

   <source lang="sql">
 

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> SQL> create index upper_name_idx

 2  on emp(upper(ename));

Index created. SQL> SQL> SQL> select * from sys.col$ where obj#= (select object_id from dba_objects where object_name="EMP"); 9 rows selected.

Execution Plan


Plan hash value: 876618436


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


| 0 | SELECT STATEMENT | | 13 | 741 | 22 (0)| 00:00:01 | | 1 | TABLE ACCESS CLUSTER | COL$ | 13 | 741 | 2 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 (0)| 00:00:01 | | 3 | VIEW | DBA_OBJECTS | 2 | 158 | 20 (0)| 00:00:01 | | 4 | UNION-ALL | | | | | | |* 5 | FILTER | | | | | | | 6 | NESTED LOOPS | | 2 | 154 | 19 (0)| 00:00:01 | | 7 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 2 | 148 | 17 (0)| 00:00:01 | |* 8 | INDEX SKIP SCAN | I_OBJ2 | 2 | | 16 (0)| 00:00:01 | | 9 | TABLE ACCESS CLUSTER | USER$ | 1 | 3 | 1 (0)| 00:00:01 | |* 10 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 | |* 11 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 8 | 2 (0)| 00:00:01 | |* 12 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 | | 13 | NESTED LOOPS | | 1 | 82 | 1 (0)| 00:00:01 | |* 14 | INDEX SKIP SCAN | I_LINK1 | 1 | 79 | 0 (0)| 00:00:01 | | 15 | TABLE ACCESS CLUSTER | USER$ | 1 | 3 | 1 (0)| 00:00:01 | |* 16 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 |


Predicate Information (identified by operation id):


  2 - access("OBJ#"= (SELECT /*+ */ "OBJECT_ID" FROM  ( (SELECT /*+ */ "U"."NAME"
             "OWNER","O"."NAME" "OBJECT_NAME","O"."SUBNAME" "SUBOBJECT_NAME","O"."OBJ#"
             "OBJECT_ID","O"."DATAOBJ#" "DATA_OBJECT_ID",DECODE("O"."TYPE#",0,"NEXT
             OBJECT",1,"INDEX",2,"TABLE",3,"CLUSTER",4,"VIEW",5,"SYNONYM",6,"SEQUENCE",7,"PROCEDURE",8
             ,"FUNCTION",9,"PACKAGE",11,"PACKAGE BODY",12,"TRIGGER",13,"TYPE",14,"TYPE
             BODY",19,"TABLE PARTITION",20,"INDEX PARTITION",21,"LOB",22,"LIBRARY",23,"DIRECTORY",24,"
             QUEUE",28,"JAVA SOURCE",29,"JAVA CLASS",30,"JAVA
             RESOURCE",32,"INDEXTYPE",33,"OPERATOR",34,"TABLE SUBPARTITION",35,"INDEX
             SUBPARTITION",40,"LOB PARTITION",41,"LOB SUBPARTITION",42,NVL( (SELECT /*+ */ DISTINCT
             "REWRITE EQUIVALENCE" FROM SYS."SUM$" "S" WHERE "S"."OBJ#"=:B1 AND
             BITAND("S"."XPFLAGS",8388608)=8388608),"MATERIALIZED
             VIEW"),43,"DIMENSION",44,"CONTEXT",46,"RULE SET",47,"RESOURCE PLAN",48,"CONSUMER
             GROUP",51,"SUBSCRIPTION",52,"LOCATION",55,"XML SCHEMA",56,"JAVA DATA",57,"SECURITY
             PROFILE",59,"RULE",60,"CAPTURE",61,"APPLY",62,"EVALUATION
             CONTEXT",66,"JOB",67,"PROGRAM",68,"JOB CLASS",69,"WINDOW",72,"WINDOW
             GROUP",74,"SCHEDULE",79,"CHAIN",81,"FILE GROUP","UNDEFINED") "OBJECT_TYPE","O"."CTIME"
             "CREATED","O"."MTIME" "LAST_DDL_TIME",TO_CHAR(INTERNAL_FUNCTION("O"."STIME"),"YYYY-MM-DD:
             HH24:MI:SS") "TIMESTAMP",DECODE("O"."STATUS",0,"N/A",1,"VALID","INVALID")
             "STATUS",DECODE(BITAND("O"."FLAGS",2),0,"N",2,"Y","N")
             "TEMPORARY",DECODE(BITAND("O"."FLAGS",4),0,"N",4,"Y","N")
             "GENERATED",DECODE(BITAND("O"."FLAGS",16),0,"N",16,"Y","N") "SECONDARY" FROM
             "SYS"."USER$" "U","SYS"."OBJ$" "SYS_ALIAS_2" WHERE ("O"."TYPE#"<>1 AND "O"."TYPE#"<>10
             OR "O"."TYPE#"=1 AND  (SELECT /*+ */ 1 FROM "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B2 AND
             ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR
             "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1) AND "O"."NAME"="EMP" AND "O"."NAME"<>"_NEXT_OBJECT"
             AND "O"."NAME"<>"_default_auditing_options_" AND "O"."LINKNAME" IS NULL AND
             "O"."OWNER#"="U"."USER#") UNION ALL  (SELECT /*+ */ "U"."NAME" "OWNER","L"."NAME"
             "OBJECT_NAME",NULL "SUBOBJECT_NAME",TO_NUMBER(NULL) "OBJECT_ID",TO_NUMBER(NULL)
             "DATA_OBJECT_ID","DATABASE LINK" "OBJECT_TYPE","L"."CTIME" "CREATED",TO_DATE(NULL)
             "LAST_DDL_TIME",NULL "TIMESTAMP","VALID" "STATUS","N" "TEMPORARY","N" "GENERATED","N"
             "SECONDARY" FROM "SYS"."USER$" "U","SYS"."LINK$" "L" WHERE "L"."NAME"="EMP" AND
             "L"."OWNER#"="U"."USER#")) "DBA_OBJECTS"))
  5 - filter("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND  (SELECT /*+ */ 1
             FROM "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR
             "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1)
  8 - access("O"."NAME"="EMP" AND "O"."LINKNAME" IS NULL)
      filter("O"."NAME"="EMP" AND "O"."NAME"<>"_NEXT_OBJECT" AND
             "O"."NAME"<>"_default_auditing_options_" AND "O"."LINKNAME" IS NULL)
 10 - access("O"."OWNER#"="U"."USER#")
 11 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
             "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
 12 - access("I"."OBJ#"=:B1)
 14 - access("L"."NAME"="EMP")
      filter("L"."NAME"="EMP")
 16 - access("L"."OWNER#"="U"."USER#")

Statistics


         0  recursive calls
         0  db block gets
        35  consistent gets
         0  physical reads
         0  redo size
      3472  bytes sent via SQL*Net to client
       615  bytes received via SQL*Net from client
        11  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         9  rows processed

SQL> SQL> drop table emp; Table dropped. SQL> SQL> SQL> --

 </source>