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

Home -> Community -> Usenet -> c.d.o.misc -> Re: O9i: How to create a matching index to a given query

Re: O9i: How to create a matching index to a given query

From: Andreas Mosmann <mosmann_at_expires-30-04-2007.news-group.org>
Date: Mon, 02 Apr 2007 11:57:31 +0200
Message-ID: <1175507851.39@user.newsoffice.de>


sybrandb schrieb am 02.04.2007 in
<1175500079.842304.118110_at_l77g2000hsb.googlegroups.com>:

> On Apr 2, 9:35 am, Andreas Mosmann <mosm..._at_expires-30-04-2007.news-
> group.org> wrote:

>> Hi ng,
>>
>> this is a general question about indexes and queries. I am glad about
>> any hint. I can not imagine that all experienced Oracle- Gurus only work
>> with try and error. There must be a strategie.
>>
>> At first: I actually work on Oracle 9.2.0.7 DB.
>>
>> If there is a query like
>>
>> SELECT <any_fields> FROM T1
>> JOIN T2 ON T2.F1=T1.F2
>> JOIN T3 ON T3.F1=T1.F3
>> WHERE T2.FX=<a value> and T3.FX=<a value>
>> GROUP BY T1.F4
>>
>> 1. What way is to go to find out what index should be created to make
>> this query faster?
>> 2. What (except hardware) does the performance depend on?
>> 3. Is there any place I can read about it? What search string I could
>> use for google search?
>>
>> Many thanks
>> Andreas Mosmann
>>
>> --
>> wenn email, dann AndreasMosmann <bei> web <punkt> de
> 1 read the explain plan, identify the driving table, and make sure the
> driving table is correct (ie assuming the driving table is not a look
> up table) and returns the smallest number of records. So no trial and
> error involved, but 'know thy application'
Which is the driving table of my query? How can I tell the CBO what driving table he has to use? Why should a driving table not be a lookup table (if I understand it correct)? Is it possible to explain with the example above?

> 2 Primarily on 'know thy application' and reading documentation, and > by avoiding putting everything on 1 single disk. To read Oracle documentation row by row I would need years. Is there a place I can read (my English is not the very best)?

> 3 The concept outlined above is explained in great detail in the 'SQL > Tuning book' by Dan Tow, published by O'Reilly. I bought it right now. Thanks.

> The books by Cary Millsap, Jonathan Lewis, and Tom Kyte have been
> recommended here so many times, I won't recommend them further. Anyone
> here can find their details by searching Google.
searching google is a fine thing but without useful keywords it is more interesting reading yellow pages.
> --
> Sybrand Bakker
> Senior Oracle DBA

Thank you very much
Andreas Mosmann

-- 
wenn email, dann AndreasMosmann <bei> web <punkt> de
Received on Mon Apr 02 2007 - 04:57:31 CDT

Original text of this message

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