| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Imedia query tuning
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. I am also sending the table details. 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
Here are the table details.
CREATE TABLE CATEGORY
PK_CATEGORY_ID NUMBER NOT NULL, PARENT_CATEGORY NUMBER NOT NULL, NAME VARCHAR2 (1000) NOT NULL, DEPTH VARCHAR2 (4000) NOT NULL, STATUS NUMBER NOT NULL,UPDATED_DATETIME DATE,
CREATE TABLE SITE
PK_SITE_ID NUMBER NOT NULL, FK_CATEGORY NUMBER NOT NULL, TITLE CLOB, URL VARCHAR2 (4000) NOT NULL, DESCRIPTION CLOB, STATUS NUMBER NOT NULL, PAGE_HITS NUMBER NOT NULL,EDITOR_CHOICE VARCHAR2 (10),
begin
ctx_ddl.create_preference('sitelexer', 'BASIC_LEXER');
ctx_ddl.set_attribute('sitelexer', 'printjoins', '_-''');
ctx_ddl.set_attribute('sitelexer', 'endjoins', '%');
ctx_ddl.set_attribute ( 'sitelexer', 'index_text', 'YES');
ctx_ddl.set_attribute ( 'sitelexer', 'mixed_case', 'NO');
end;
b) Execute this script to create an indexes.
CREATE INDEX site1descidx ON
site(description)
indextype is ctxsys.context
parameters ( 'LEXER sitelexer' );
CREATE INDEX site2titleidex ON
site(title)
indextype is ctxsys.context
parameters ( 'LEXER sitelexer' );
4.Deleting a preference
begin
ctx_ddl.drop_preference('sitelexer');
end
Thanks in advance and Regards,
Ranganath
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: ranganathk_at_subexgroup.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing ListsReceived on Wed May 16 2001 - 06:11:57 CDT
--------------------------------------------------------------------
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).
 |
 |