| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Imedia query tuning
<SPAN
class=912384305-17052001>Hello,
Thanks
Lisa for your comment but.
<SPAN
class=912384305-17052001>
I try
to always <SPAN
class=912384305-17052001>use an index hint to return the rows in the required
order. This avoids a sort....
if
it's possible to use an index which can avoid both sort and table access - and
it often won't be possible - the this index will offer good performance both for
retrieval of all rows and the retreival of the first row.
<SPAN
class=912384305-17052001>
<SPAN
class=912384305-17052001>
<SPAN
class=912384305-17052001>Ranganath : What is the name of your index on
editor_choice ?
<SPAN
class=912384305-17052001>
<SPAN
class=912384305-17052001>Regards
<SPAN
class=912384305-17052001>Henrik
<FONT face=Tahoma
size=2>-----Original Message-----From: Koivu, Lisa
[mailto:lkoivu_at_qode.com]Sent: den 16 maj 2001 16:37To:
Multiple recipients of list ORACLE-LSubject: RE: Imedia query
tuning
Henrik, FYI
The ordered hint refers to the order in which the tables are
accessed in the query. It has nothing to do with an index.
Lisa Rutland Koivu
Oracle Database Administrator <FONT
size=2>Certified Self-Important Database Deity Slayer
of Unix Administrators Wanton Kickboxing
Goddess
lkoivu_at_qode.com
NeoMedia
2201 Second St., Suite 600 Fort
Myers, FL 33901, USA Phone: 941-337-3434
Fax: 941-337-3668 www.neom.com <<A
href="http://www.neom.com" target=_blank>http://www.neom.com>
www.paperclick.com <<A
href="http://www.paperclick.com"
target=_blank>http://www.paperclick.com> <FONT
size=2>www.qode.com <<A href="http://www.qode.com"
target=_blank>http://www.qode.com> <FONT
size=2> P a p e r C l i c k . c o m <<A
href="http://www.paperclick.com/home.htm"
target=_blank>http://www.paperclick.com/home.htm> <FONT
size=2>
Enter Your PaperClick Code Here! <FONT
size=2>
-----Original Message----- From:
Henrik Ekenberg [<A
href="mailto:henrik.ekenberg_at_anoto.com">mailto:henrik.ekenberg_at_anoto.com]
Sent: Wednesday, May 16, 2001 8:56 AM <FONT
size=2>To: Multiple recipients of list ORACLE-L <FONT
size=2>Subject: Re: Imedia query tuning
Hello,
I'm not a guru : I just try to help you. :->
Hint for Query 2 : Have you try to
hint so you run on the editor_choice index. If it <FONT
size=2>works. Can you remove the Order command. Because the index is
already ordered.
Regards Henrik E.
On Wed, 16 May 2001, Ranganath K wrote:
> Dear DBA Gurus, >
> I have the following two
queries along with execution plan and statistics. >
The first one is taking a long time to execute. The second query is
taking > a long time to execute when I use the
order by clause. Is there any way I > can
reduce the execution time as these queries will be used by a search
> engine? Any help in this regard will be greatly
appreciated. > > SQL>
select depth, count(*) a from category, site <FONT
size=2>> 2 where (site.fk_category in (select
pk_category_id from category > 3
where category.status = 0)) and site.status = 0 <FONT
size=2>> 4 and ((contains (title,'box') > 0) or
> 5 (contains (description, 'box') >
0)) > 6 and pk_category_id =
fk_category group by depth order by a desc; <FONT
size=2>> > 467 rows selected. <FONT size=2>> > Elapsed: 00:00:16.43 <FONT size=2>> > Execution Plan <FONT size=2>> ----------------------------------------------------------> 0 SELECT
6 BITMAP OR > 8 7
8
9
7
11
12
size=2>> 1 Card=1499 Bytes=38974) > <FONT size=2>> 15
size=2>> Card=149802 Bytes=301851030) > <FONT size=2>> 17
 |
 |