Re: Query on multiple tablespace

Without a few more details like number of entries in each table, it is tricky giving
suggestions here, BUT one common problem I have seen is stale statistics for the
Oracle optimizer to use. Try doing an ANALYZE on both tables before the query.
The optimizer is obviously picking the wrong order.

(NOTE: since ANALYZE is Oracle specific, I dropped theory from the newsgroups.)

Isabelle Fernandez wrote:

> Hi !
>
> I have created a "nice" database with
> - almost all tables in the same tablespace (lets call it
> MainTableSpace
> and one table is the Images table)
> - one table called Binaries in a separate tablespace
> (lets call it LongRawTableSpace).
> Tables in the MainTableSpace contain essentially date, strings, ....
> The Binaries table contains one long raw with some flags.
>
> I would like to send a query which retrieve common data plus one flag
> from the Binaries table.
> I generate a query like that :
>
> SELECT Images.Name, Images.BinaryEntry, Binaries.Flag
> FROM Images, Binaries
> WHERE Images.BinaryEntry = Binaries.Identifier
> AND Images.Identifier = 10
>
> This query is very very long to execute. If I divide it into
> two separated queries, it is ok and runs very quickly.
>
> SELECT Images.Name, Images.BinaryEntry
> FROM Images
> WHERE Images.Identifier = 10
>
> SELECT Binaries.Flag
> FROM Binaries
> WHERE Binaries.Identifier = ... /* Value get by the previous
> request */
>
> /* REM ********************************************
> Binaries.Identifier, Images.Identifier are primary keys
> Images.BinaryEntry is a foreign key to the Binaries table
> Images.BinaryEntry has a unique constraint
> ***************************************************/
>
> I suppose long time request is due to the separation into two
> different tablespaces.
> How can I have quick result with only one query ?
>
> Thanks
> Cheers
> Isabelle.
>
> --
> * Isabelle Fernandez
> * Product Division, Database engineer
> * FOCUS Imaging, Les Genets, 449 Route des Cretes, 06560 Valbonne
> Sophia Antipolis
> * Mailto:isabelle_at_focus-fr.com
Received on Wed Dec 02 1998 - 00:00:00 CST