Oracle PL/SQL/System Tables Views/sys.col
Содержание
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>