| 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
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
Certified Self-Important Database Deity
Slayer of Unix Administrators
Wanton Kickboxing Goddess
lkoivu_at_qode.com
NeoMedia
www.neom.com <http://www.neom.com> www.paperclick.com <http://www.paperclick.com> www.qode.com <http://www.qode.com>
Enter Your PaperClick Code Here!
-----Original Message-----
From: Henrik Ekenberg [mailto:henrik.ekenberg_at_anoto.com]
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 :henrik.ekenberg_at_anoto.com Fax +46 (0)8 406 79 50 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Henrik Ekenberg INET: henrik.ekenberg_at_anoto.com 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: ListGuru_at_fatcity.com (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).Received on Wed May 16 2001 - 10:20:26 CDT
 |
 |