RE: Imedia query tuning

From: Daemen, Remco <R.Daemen_at_facent.nl>
Date: Wed, 16 May 2001 05:23:03 -0700
Message-ID: <F001.00304B51.20010516052526@fatcity.com>

Hi Ranganath,

Do you realy need the subquery ? Try replacing the "... in (...)" condition by "category.status=0". You already have a condition which states that fk_category must equal pk_category_id.

If it's still slow: how many rows are there in each table ?

Remco

-----Oorspronkelijk bericht-----
Van: Ranganath K [mailto:ranganathk_at_subexgroup.com] Verzonden: woensdag 16 mei 2001 13:56
Aan: Multiple recipients of list ORACLE-L Onderwerp: 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.

To create category table

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,
PRIMARY KEY ( PK_CATEGORY_ID ) ); 2. To create site table.

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),
  PRIMARY KEY ( PK_SITE_ID )); ALTER TABLE SITE ADD CONSTRAINT FKSITE
FOREIGN KEY (FK_CATEGORY)
  REFERENCES VCPLNEW.CATEGORY (PK_CATEGORY_ID) ; 3. creating an index after inserting the data(Datebase Updation) in both the tables.

Execute this script to create a preference.

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 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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Daemen, Remco
INET: R.Daemen_at_facent.nl

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------