Re: 7.3.3 Query performance problem

Not sure Kevin, I am trying to reproduce your problem and am getting the following. I would try to force the index using hints to see if that helps.

Later, Jon.

SQL> describe benefit

 Name                            Null?    Type
 ------------------------------- -------- ----
 BENEFITID                       NOT NULL NUMBER(20)
 ACLID                                    NUMBER(20)
 LIBRARY                                  NUMBER(20)
 BASEBENEFIT                              NUMBER(20)
 MAPPEDTO                                 NUMBER(20)
 BENEFITSET                               NUMBER(20)
 PRODUCTTEMPLATE                          NUMBER(20)
 PLAN                                     NUMBER(20)
 NAME                                     VARCHAR2(254)
 DESCRIPTION                              VARCHAR2(254)
 UNITTYPE                                 NUMBER(16)
 DISPLAYORDER                             NUMBER(16)
 BENEFITTYPE                              NUMBER(16)
 COMMENTSTRING                            VARCHAR2(254)
 CREATOR                                  VARCHAR2(254)
 PLANSELECTOR                             NUMBER(1)
 CATEGORY                                 VARCHAR2(254)
 SHAREDVALUES                             NUMBER(1)
 FACTORTYPE                               NUMBER(16)
 NETWORKCOVERAGE                          NUMBER(16)
 CERTDESC                                 VARCHAR2(254)
 REQUIRED                                 NUMBER(1)
 VAL                                      FLOAT(126)
 VALHIGH                                  FLOAT(126)
 FACTOR                                   FLOAT(126)
 ISVARIABLE                               NUMBER(1)
 DISTRIBUTED                              NUMBER(1)

SQL> run
  1 select count(aclid)
  2 from benefit
  3* where description='e'

COUNT(ACLID)

           0

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 SORT (AGGREGATE)

   2    1     TABLE ACCESS (BY ROWID) OF 'BENEFIT'
   3    2       INDEX (RANGE SCAN) OF 'BENEFITTEST' (NON-UNIQ

Statistics

          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        188  bytes sent via SQL*Net to client
        292  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> edit
Wrote file afiedt.buf

  1 select aclid
  2 from benefit
  3* where description='e'
SQL> run
  1 select aclid
  2 from benefit
  3* where description='e'

no rows selected

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 TABLE ACCESS (BY ROWID) OF 'BENEFIT'    2 1 INDEX (RANGE SCAN) OF 'BENEFITTEST' (NON-UNIQUE Statistics

          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        167  bytes sent via SQL*Net to client
        285  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> -----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Fri Sep 18 1998 - 12:53:20 CDT