Query sys.col$ table with sub query : sys.col « System Tables Views « Oracle PL / SQL






Query sys.col$ table with sub query

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

   
  








Related examples in the same category

1.Check column type with user-defined type in sys.col$
2.Join on and where clause
3.Join sys.col$ and user_objects