Re: ODBC SQLColums is very slow
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Thu, 22 Jan 2009 10:49:48 +0100
Message-ID: <4978413e$0$199$e4fe514c_at_news.xs4all.nl>
Gerard H. Pille schreef:
> Shakespeare schreef:
>
>
> There is another solution that I found on the web.
>
> In the schema of the user running the query, create tables all_synonyms
> and all_tab_columns, ...
>
> Fill them with data and add the necessary indexes.
>
>
> Barbatruc.
Date: Thu, 22 Jan 2009 10:49:48 +0100
Message-ID: <4978413e$0$199$e4fe514c_at_news.xs4all.nl>
Gerard H. Pille schreef:
> Shakespeare schreef:
>> >> >> I ran the second query with the join (with actual tablenames) with sql >> plus on one of our production databases; takes more than a second as >> well. If I persist running the same query on the same table, it tunes >> down to 0,6 seconds. So it seems it's a slow query anyway.... so >> replacing ODBC with anything else won't help much. It's the second >> part of the union that is slow. The first part runs within 0.1 second. >> Joining all_tab_columns with all_synonyms seems a bad idea.... >> >> Shakespeare
>
>
> There is another solution that I found on the web.
>
> In the schema of the user running the query, create tables all_synonyms
> and all_tab_columns, ...
>
> Fill them with data and add the necessary indexes.
>
>
> Barbatruc.
Or create a synonym ALL_SYNONYMS for DBA_SYNONYMS. (Sounds like a wonderful META operation). And pray ALL_SYNONYMS isn't used anywhere else... DBA_SYNONYMS contains all the rows of ALL_SYNONYMS plus some more (so ALL is not ALL at all).
It's ALL_SYNONYMS that is bugging the query. See one of my other posts in this discussion. Filling tables is not such a good idea, because you would have to update them every time a synonym or table is created....
Shakespeare Received on Thu Jan 22 2009 - 03:49:48 CST