Mega Code Archive
Query sys col$ table with sub query
<![CDATA[
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>
--
]]>