Hello,
I do a
SELECT AIC.INDEX_NAME AS IndexName,
CASE ALC.CONSTRAINT_TYPE
WHEN 'P' THEN 'T'
ELSE 'F'
END AS IsPrimary,
CASE ALC.CONSTRAINT_TYPE
WHEN 'U' THEN 'T'
ELSE 'F'
END AS IsUnique,
AIC.COLUMN_NAME AS ColumnName
FROM ALL_IND_COLUMNS AIC
LEFT JOIN ALL_CONSTRAINTS ALC ON AIC.INDEX_NAME = ALC.CONSTRAINT_NAME
AND AIC.TABLE_NAME = ALC.TABLE_NAME
AND AIC.TABLE_OWNER = ALC.OWNER
WHERE UPPER(AIC.TABLE_NAME) = UPPER('TEST')
to query index information about a table.
I have to query 250 tables.
The above SQL takes about 3-5 seconds per table.
Is there a way to speed it up?
Greetings
Stefan
If you *know* you need to query 250 tables, perhaps an option is to take a single pass through the table and get *all* of the relevant rows, eg
SQL> create global temporary table ALL_OF_IT
2 on commit preserve rows as
3 SELECT AIC.TABLE_NAME, AIC.INDEX_NAME AS IndexName,
4 CASE ALC.CONSTRAINT_TYPE
5 WHEN 'P' THEN 'T'
6 ELSE 'F'
7 END AS IsPrimary,
8 CASE ALC.CONSTRAINT_TYPE
9 WHEN 'U' THEN 'T'
10 ELSE 'F'
11 END AS IsUnique,
12 AIC.COLUMN_NAME AS ColumnName
13 FROM ( select * from ALL_IND_COLUMNS where table_owner = 'MCDONAC' ) AIC
14 LEFT JOIN ( select * from ALL_CONSTRAINTS where owner = 'MCDONAC' ) ALC ON AIC.INDEX_NAME = ALC.CONSTRAINT_NAME
15 AND AIC.TABLE_NAME = ALC.TABLE_NAME
16 AND AIC.TABLE_OWNER = ALC.OWNER;
Table created.
Elapsed: 00:00:12.84
So that cost me 12 seconds...but now queries for each table are nearly instantaneous
SQL>
SQL> select * from ALL_OF_IT
2 where TABLE_NAME = UPPER('PARENT');
TABLE_NAME
------------------------------
INDEXNAME I
-------------------------------------------------------------------------------------------------------------------------------- -
I
-
COLUMNNAME
----------------------------------------------------------------------------------------------------------------------------------
PARENT
SYS_C0020656 T
F
F1
Elapsed: 00:00:00.00