Performace of Metadata Queries

[tac]

Oracle dbexpress Driver v4.40.16, ORACLE DATABASE Version 9.2

The driver uses the following statement to get metadata form ORACLE systables.

select ROWNUM RECNO, U.* From
( select * From ( select CATALOG_NAME, SCHEMA_NAME, TABLE_NAME, INDEX_NAME, COLUMN_NAME, COLUMN_POSITION, Max(PKEY_NAME) PKEY_NAME, Sum(INDEX_TYPE) INDEX_TYPE, SORT_ORDER, FILTER from
( SELECT '' CATALOG_NAME, INDEX_OWNER SCHEMA_NAME, I.TABLE_NAME, I.INDEX_NAME, COLUMN_NAME, COLUMN_POSITION, '' PKEY_NAME, DECODE(I.UNIQUENESS, 'UNIQUE', 2, 1 ) INDEX_TYPE, 'A' SORT_ORDER, '' FILTER
FROM ALL_IND_COLUMNS B, ALL_INDEXES I
WHERE I.TABLE_NAME LIKE 'MY_TABLE' AND I.OWNER= 'MY_OWNER' AND I.INDEX_NAME = B.INDEX_NAME AND I.OWNER = B.INDEX_OWNER UNION SELECT '' CATALOG_NAME, I.OWNER SCHEMA_NAME, I.TABLE_NAME, I.CONSTRAINT_NAME INDEX_NAME, COLUMN_NAME, COLUMN_POSITION, I.CONSTRAINT_NAME PKEY_NAME, 4 INDEX_TYPE, 'A' SORT_ORDER, '' FILTER FROM ALL_IND_COLUMNS B, SYS.ALL_CONSTRAINTS I WHERE I.TABLE_NAME LIKE 'MY_TABLE' AND I.OWNER= 'MY_OWNER' AND (I.CONSTRAINT_NAME = B.INDEX_NAME) AND (I.OWNER = B.INDEX_OWNER) AND (I.CONSTRAINT_TYPE = 'P')
) group by CATALOG_NAME, SCHEMA_NAME, TABLE_NAME, INDEX_NAME, COLUMN_NAME, SORT_ORDER, FILTER, COLUMN_POSITION )
) U

This statement is better then the borland methode but however has a poorly performance on
ORACLE systems with many schemata and many tables.
It needs about 2 seconds.
Is it planned for future releases to increase performace for geting metadata?

best regards

Torsten Achatz

[AlexP]

Hello,

Thanks for the information.
We will investigate the possibility to change this sql statement in one of the next builds/versions.

[bork]

Hello

We have improved the performance of this query. This fix will be included in the next dbExpress driver for Oracle build. But in a large database information about indexes cannot be received very quickly.