Re: SQL-Query with many equal subselects

losthighway_at_metalogic.ch (Steve) wrote in message news:<9606de71.0304010354.28bbd9aa_at_posting.google.com>...
> I've searched the web for hours to find a solution for the following
> question, but nobody seems to have this problem except me ;-)
>
> I created a simple query that shows the problem. As you can see there
> are 2 main queries, both of them use an additional filter (SELECT id
> FROM T_profession WHERE id < 1000). It's quite unefficient to run this
> sub-query twice, so is it possible to place this query somewhere else
> and just run it once ? Making a view doesn't work as this subquery is
> being build at runtime within an application.
>
> Any help would be highly appreciated...
> Steve
>
>
> SELECT *
> FROM (SELECT 'Engineer', COUNT (*)
> FROM T_Profession
> WHERE description LIKE '%Engineer%'
> AND id IN (SELECT id FROM T_Profession WHERE id < 1000)),
> (SELECT 'Manager', COUNT (*)
> FROM T_Profession
> WHERE description LIKE '%Manager%'
> AND id IN (SELECT id FROM T_profession WHERE id < 1000))
>
> --------------------------------------------------------

It is NOT a problem in a correctly configured database as the data will be in buffer cache, and will be retrieved from buffer cache on subsequent selects and will not use physical I/O.

Regards

Sybrand Bakker
Senior Oracle DBA Received on Tue Apr 01 2003 - 15:28:19 CEST