Imedia query optimization

From: Ranganath K <ranganathk_at_subexgroup.com>
Date: Mon, 14 May 2001 02:51:03 -0700
Message-ID: <F001.0030262B.20010514030024@fatcity.com>

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:

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: ranganathk_at_subexgroup.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 Mon May 14 2001 - 04:51:03 CDT