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" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED] 05/16/01 09:36 AM
|
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> 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
[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-----
Hello,
I'm not a guru : I just try to help you. :->
Hint for Query 2 : Regards
On Wed, 16 May 2001, Ranganath K wrote:
> Dear DBA Gurus, --
-- Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
Sent: Wednesday, May 16, 2001 8:56 AM
To: Multiple recipients of list ORACLE-L
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.
Henrik E.
>
> 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]
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).
