Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Query on multiple tablespace

Re: Query on multiple tablespace

From: Stewart Burnett <Stewart_at_burnettms.nospam.demon.co.uk>
Date: 1998/12/02
Message-ID: <743sjq$jgh$1@hagen.cloud9.co.uk>#1/1

Using two tablespaces shouldn't cause a problem unless they are in different databases, in fact it may be faster if the tablespaces can be located on different discs.

Your problem is to do with how Oracle's optimiser decides to execute the query, you should get an execution plan (See EXPLAIN PLAN in the SQL Language Reference).

You could also try this;

generate some statistics for the tables and switch on the cost based optimiser;

ANALYSE TABLE Binaries COMPUTE STATISTICS; ANALYSE TABLE Images COMPUTE STATISTICS; ALTER SESSION SET OPTIMIZER_GOAL = ALL_ROWS; and try the query again.

If this works you can either set your whole system to use this optimiser goal or compare the explain plan from before and after these changes and use a hint to make the optimiser use the correct execution plan.

Isabelle Fernandez wrote in message <36653E1B.8A42AA18_at_focus-fr.com>...
>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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US