Skip to Main Content
  • Questions
  • Query index information is very slow

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: August 18, 2016 - 8:39 pm UTC

Last updated: August 19, 2016 - 4:26 am UTC

Version: 10

Viewed 1000+ times

You Asked

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

and Connor said...

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



Is this answer out of date? If it is, please let us know via a Comment