Performace of Metadata Queries

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for Oracle in Delphi and C++Builder
Post Reply
tac
Posts: 1
Joined: Thu 09 Sep 2010 11:48

Performace of Metadata Queries

Post by tac » Thu 09 Sep 2010 11:55

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
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Post by AlexP » Tue 28 Sep 2010 12:04

Hello,

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

bork
Devart Team
Posts: 643
Joined: Fri 12 Mar 2010 07:55

Post by bork » Tue 12 Oct 2010 08:45

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.

Post Reply