в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T10:01:19Z

1 версия

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

==Check index column and contraints for just created table==



<source lang="sql">

SQL>
SQL> create table movie(
2 movie_no integer constraint movie_pk primary key using index (create index movie_pk_idx on movie(movie_no) )
3 ,title varchar2(40)
4 ,rating char(4)
5 ,type varchar2(20)
6 ,star varchar2(40)
7 ,qty number
8 ,price number
9 );
Table created.
SQL>
SQL> select index_name, table_name, column_name from user_ind_columns where table_name = "MOVIE";
MOVIE_PK_IDX MOVIE
MOVIE_NO

1 row selected.
SQL>
SQL> select constraint_name, table_name, column_name from user_cons_columns where table_name = "MOVIE";
MOVIE_PK MOVIE
MOVIE_NO

1 row selected.
SQL>
SQL> drop table movie cascade constraints;
Table dropped.

</source>

==Decode column position==



<source lang="sql">

SQL>
SQL>
SQL> column table_name format a15 word_wrapped
SQL> column index_name format a25 word_wrapped
SQL> column columns format a40
SQL>
SQL> break on table_name skip 1
SQL>
SQL> select table_name,
2 index_name,
3 rtrim(
4 max( decode( column_position, 1, column_name, "" ) )||","||
5 max( decode( column_position, 2, column_name, "" ) )||","||
6 max( decode( column_position, 3, column_name, "" ) )||","||
7 max( decode( column_position, 4, column_name, "" ) )||","||
8 max( decode( column_position, 5, column_name, "" ) )||","||
9 max( decode( column_position, 6, column_name, "" ) )||","||
10 max( decode( column_position, 7, column_name, "" ) )||","||
11 max( decode( column_position, 8, column_name, "" ) )||","||
12 max( decode( column_position, 9, column_name, "" ) )||","||
13 max( decode( column_position, 10, column_name, "" ) )||","||
14 max( decode( column_position, 11, column_name, "" ) )||","||
15 max( decode( column_position, 12, column_name, "" ) )||","||
16 max( decode( column_position, 13, column_name, "" ) )||","||
17 max( decode( column_position, 14, column_name, "" ) )||","||
18 max( decode( column_position, 15, column_name, "" ) )||","||
19 max( decode( column_position, 16, column_name, "" ) ), "," )
20 columns
21 from user_ind_columns
22 where rownum < 50
23 group by table_name,index_name
24 /
TABLE_NAME INDEX_NAME COLUMNS
--------------- ------------------------- ----------------------------------------
EMP_CHANGES SYS_C005784 MSGID
UPPER_ENAME SYS_IOT_TOP_15816 X$ENAME,X$RID
DEPT_AND_EMP SYS_C005826 DEPTNO
SYS_C005827 EMPS
AQ$_EMP_CHANGES SYS_IOT_TOP_16251 MSGID,SUBSCRIBER#,NAME,ADDRESS#
_G
TABLE_NAME INDEX_NAME COLUMNS
--------------- ------------------------- ----------------------------------------

AQ$_EMP_CHANGES SYS_IOT_TOP_16249 MSGID,SUBSCRIBER#,NAME,ADDRESS#
_H

AQ$_EMP_CHANGES SYS_IOT_TOP_16254 SUBSCRIBER#,NAME,QUEUE#,MSG_PRIORITY,MSG
_I _ENQ_TIME,MSG_STEP_NO,MSG_CHAIN_NO,MSG_L
OCAL_ORDER_NO,MSGID

TABLE_NAME INDEX_NAME COLUMNS
--------------- ------------------------- ----------------------------------------
AQ$_EMP_CHANGES SYS_C005787 SUBSCRIBER_ID
_S

AQ$_EMP_CHANGES SYS_IOT_TOP_16247 NEXT_DATE,TXN_ID,MSGID
_T

COMPILE_SCHEMA_ COMPILE_SCHEMA_TMP_PK OBJECT_NAME,OBJECT_TYPE
TMP

10 rows selected.
SQL>

</source>

==Query user_indexes and user_ind_columns table==



<source lang="sql">

SQL>
SQL>
SQL> SELECT dic.table_name, dic.index_name,
2 DECODE(di.uniqueness, "UNIQUE", "YES", "NO")uniqueness,
3 dic.column_position, dic.column_name
4 FROM user_indexes di, user_ind_columns dic
5 WHERE dic.table_name = di.table_name
6 AND dic.index_name = di.index_name
7 and rownum < 50
8 ORDER BY dic.table_name, dic.index_name,
9 di.uniqueness desc, dic.column_position;
no rows selected
SQL>

</source>

==user_indexes join user_ind_columns ic using (table_name,index_name)==



<source lang="sql">

SQL>
SQL>
SQL>
SQL> col table_name format a15
SQL> col index_name format a15
SQL> col unq format a9
SQL> col column_name format a15
SQL>
SQL> set linesize 80
SQL>
SQL> break on table_name skip 1 -
> on index_name -
> on unq -
> on status
SQL>
SQL> select table_name
2 , index_name
3 , i.uniqueness as unq
4 , i.status
5 , ic.column_name
6 from user_indexes i join
7 user_ind_columns ic using (table_name,index_name)
8 where table_name in ("EMP","DEPARTMENTS")
9 order by table_name
10 , index_name
11 , unq
12 , ic.column_position;
no rows selected
SQL>
SQL> --clear breaks
SQL>

</source>

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

Oracle PL/SQL/System Tables Views/user ind columns - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия