в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T10:01:17Z

1 версия

Новая страница

==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>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL/System Tables Views/sys.col - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия