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
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ranganath K
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).
