-----Original Message-----
From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]
Sent: den 16 maj 2001 16:37
To: Multiple recipients of list ORACLE-L
Subject: RE: Imedia query tuningHenrik, 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
Certified Self-Important Database Deity
Slayer of Unix Administrators
Wanton Kickboxing Goddess[EMAIL PROTECTED]
NeoMedia
2201 Second St., Suite 600
Fort Myers, FL 33901, USA
Phone: 941-337-3434
Fax: 941-337-3668
www.neom.com <http://www.neom.com>
www.paperclick.com <http://www.paperclick.com>
www.qode.com <http://www.qode.com>
P a p e r C l i c k . c o m <http://www.paperclick.com/home.htm>
Enter Your PaperClick Code Here!
-----Original Message-----
From: Henrik Ekenberg [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 16, 2001 8:56 AM
To: Multiple recipients of list ORACLE-L
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
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
> 2 where (site.fk_category in (select pk_category_id from category
> 3 where category.status = 0)) and site.status = 0
> 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;
>
> 467 rows selected.
>
> Elapsed: 00:00:16.43
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=214936 Card=22470 By
> tes=136572660)
>
> 1 0 SORT (ORDER BY) (Cost=214936 Card=22470 Bytes=136572660)
> 2 1 SORT (GROUP BY) (Cost=214936 Card=22470 Bytes=136572660)
> 3 2 NESTED LOOPS (Cost=19 Card=22470 Bytes=136572660)
> 4 3 NESTED LOOPS (Cost=4 Card=15 Bytes=60945)
> 5 4 TABLE ACCESS (BY INDEX ROWID) OF 'SITE' (Cost=2 Ca
> rd=1 Bytes=4037)
>
> 6 5 BITMAP CONVERSION (TO ROWIDS)
> 7 6 BITMAP OR
> 8 7 BITMAP CONVERSION (FROM ROWIDS)
> 9 8 SORT (ORDER BY)
> 10 9 DOMAIN INDEX OF 'SITE2TITLEIDEX' (Cost=1
> )
>
> 11 7 BITMAP CONVERSION (FROM ROWIDS)
> 12 11 SORT (ORDER BY)
> 13 12 DOMAIN INDEX OF 'SITE1DESCIDX' (Cost=1)
> 14 4 TABLE ACCESS (BY INDEX ROWID) OF 'CATEGORY' (Cost=
> 1 Card=1499 Bytes=38974)
>
> 15 14 INDEX (UNIQUE SCAN) OF 'SYS_C001314' (UNIQUE)
> 16 3 TABLE ACCESS (BY INDEX ROWID) OF 'CATEGORY' (Cost=1
> Card=149802 Bytes=301851030)
>
> 17 16 INDEX (UNIQUE SCAN) OF 'SYS_C001314' (UNIQUE)
>
> Statistics
> ----------------------------------------------------------
> 786 recursive calls
> 40 db block gets
> 5919 consistent gets
> 1389 physical reads
> 0 redo size
> 1829532 bytes sent via SQL*Net to client
> 69737 bytes received via SQL*Net from client
> 920 SQL*Net roundtrips to/from client
> 13 sorts (memory)
> 0 sorts (disk)
> 467 rows processed
>
> SQL> select depth, title, description, url, editor_choice from category,site
> 2 where (site.fk_category in (select pk_category_id from category
> 3 where category.status = 0)) and site.status = 0
> 4 and site.fk_category = category.pk_category_id
> 5 and ((contains (title, 'box') > 0) or
> 6 (contains (description, 'box' ) > 0)) order by editor_choice desc;
>
> 552 rows selected.
>
> Elapsed: 00:00:16.94
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=142941 Card=22470 By
> tes=181714890)
>
> 1 0 SORT (ORDER BY) (Cost=142941 Card=22470 Bytes=181714890)
> 2 1 NESTED LOOPS (Cost=19 Card=22470 Bytes=181714890)
> 3 2 NESTED LOOPS (Cost=4 Card=15 Bytes=91080)
> 4 3 TABLE ACCESS (BY INDEX ROWID) OF 'SITE' (Cost=2 Card
> =1 Bytes=6046)
>
> 5 4 BITMAP CONVERSION (TO ROWIDS)
> 6 5 BITMAP OR
> 7 6 BITMAP CONVERSION (FROM ROWIDS)
> 8 7 SORT (ORDER BY)
> 9 8 DOMAIN INDEX OF 'SITE2TITLEIDEX' (Cost=1)
> 10 6 BITMAP CONVERSION (FROM ROWIDS)
> 11 10 SORT (ORDER BY)
> 12 11 DOMAIN INDEX OF 'SITE1DESCIDX' (Cost=1)
> 13 3 TABLE ACCESS (BY INDEX ROWID) OF 'CATEGORY' (Cost=1
> Card=1499 Bytes=38974)
>
> 14 13 INDEX (UNIQUE SCAN) OF 'SYS_C001314' (UNIQUE)
> 15 2 TABLE ACCESS (BY INDEX ROWID) OF 'CATEGORY' (Cost=1 Ca
> rd=149802 Bytes=301851030)
>
> 16 15 INDEX (UNIQUE SCAN) OF 'SYS_C001314' (UNIQUE)
>
> Statistics
> ----------------------------------------------------------
> 781 recursive calls
> 66 db block gets
> 6930 consistent gets
> 1708 physical reads
> 0 redo size
> 2244834 bytes sent via SQL*Net to client
> 252240 bytes received via SQL*Net from client
> 2265 SQL*Net roundtrips to/from client
> 11 sorts (memory)
> 1 sorts (disk)
> 552 rows processed
>
> TIA and Regards,
>
> Ranganath
>
>
>--
---------------------------------------------------------------------------
Henrik Ekenberg Anoto AB
Direct +46 (0)8 410 78 577 Vasagatan 7
Mobile +46 (0)733 478 577 111 20 Stockholm, Sweden
www.anoto.com Switchb. +46 (0)8 406 79 00
E-mail :[EMAIL PROTECTED] Fax +46 (0)8 406 79 50
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Henrik Ekenberg
INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Title: RE: Imedia query tuning
Hello,
Thanks
Lisa for your comment but.
I try
to always 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.
Ranganath : What is the name of your index on
editor_choice ?
Regards
Henrik
- Imedia query tuning Ranganath K
- Imedia query tuning Ranganath K
- RE: Imedia query tuning Daemen, Remco
- Re: Imedia query tuning Henrik Ekenberg
- RE: Imedia query tuning Koivu, Lisa
- RE: Imedia query tuning Riyaj_Shamsudeen
- Henrik Ekenberg