Ranganath,
I have found that intermedia queries really bite the biggie without a hint. Read up on the hints /*+ FIRST_ROWS DOMAIN_INDEX_NO_SORT */
There is a chapter in the doco on tuning intermedia queries that discusses the reasons why these hints "work".
HTH
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-----
From: Ranganath K [mailto:[EMAIL PROTECTED]]
Sent: Monday, May 14, 2001 7:00 AM
To: Multiple recipients of list ORACLE-L
Subject: Imedia query optimization
Dear DBA Gurus,
When I execute the below Intermedia search query which returns around 5
lakh records the query takes a long time to execute. How do I optimize the
query to run faster?
Below are the details of the table, query and the execution plan
below:
1) Table Description:
-----------------------------------------------
* Category Table:
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,
PRIMARY KEY (PK_CATEGORY_ID);
* Site Table:
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),
PRIMARY KEY (PK_SITE_ID);
2) Foreign Key Constraints:
-----------------------------------------------
* ALTER TABLE SITE ADD CONSTRAINT FKSITE
FOREIGN KEY (FK_CATEGORY)
REFERENCES SAMPLEVIEW.CATEGORY (PK_CATEGORY_ID) ;
3) Query
-----------------------------------------------
select depth from category, site where (site.fk_category in (select
pk_category_id from category where category.status = 0)) and site.status =
0 and ((contains (title,'escorts') > 0)or (contains (description,
'escorts') > 0)) and pk_category_id = fk_category group by depth;
4) Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=107477 Card=22470
Bytes=136572660)
1 0 SORT (GROUP BY) (Cost=107477 Card=22470 Bytes=136572660)
2 1 NESTED LOOPS (Cost=19 Card=22470 Bytes=136572660)
3 2 NESTED LOOPS (Cost=4 Card=15 Bytes=60945)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'SITE' (Cost=2 Card =1
Bytes=4037)
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
Card=149802 Bytes=301851030)
16 15 INDEX (UNIQUE SCAN) OF 'SYS_C001314' (UNIQUE)
Any help in this regard will be highly appreciated.
TIA and Warm 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).
