RE: Imedia query tuning

From: <Riyaj_Shamsudeen_at_i2.com>
Date: Wed, 16 May 2001 09:04:22 -0700
Message-ID: <F001.00304F98.20010516090222@fatcity.com>

May be you could rewrite your queries like this..

select /*+ ORDERED use_nl(b) index(a index_on_pk_category_id) index(b index_on_fk_category) */
depth, title, description, url, editor_choice
from category a,site b
where b.fk_category = a.pk_category_id and
a.status=0 and
b.status=0 and
((contains (title, 'box') > 0) or
(contains (description, 'box' ) > 0))
order by editor_choice desc;

Enter correct index name in the above index hints..This also assumes that # of rows with status=0 is probably very less (less than 10%). Sorry if this is a duplicate post..

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies www.i2.com

"Koivu, Lisa" <lkoivu_at_qode.com>
Sent by: root_at_fatcity.com
05/16/01 09:36 AM
Please respond to ORACLE-L

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:
Subject: 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

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-----
Sent: Wednesday, May 16, 2001 8:56 AM
To: Multiple recipients of list ORACLE-L

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 - 11:04:22 CDT