Re: Optimizer problem?

On Thu, 9 Oct 1997 06:14:27 -0400, "Thierry Steenberghs" <steenbt_at_voicenet.com> wrote:

>Change your query to
>select count(pkey) from table;
>
>The count(*) doesn't give the optimizer any hint that it should use the
>index, but if you use the index key Oracle will use the index without beeing
>forced by hints.
>
>

The index will not be used just because you specified "count(pkey)". Use of the index is dictated by the use of a hint and/or the inclusion of a where clause in the query.

To illustrate, I created a table called temp which has a single column, pkey, defined as NUMBER(15). The table has a primary key index on the pkey column.

SQL> EXPLAIN PLAN

  2          SET STATEMENT_ID = 'test1'
  3          INTO plan_table
  4          FOR
  5             SELECT count(*) from temp;

Explained.

SQL>
SQL> SELECT LPAD(' ',2*(LEVEL-1))||operation operation, options, object_name, position

  2          FROM plan_table
  3          START WITH id = 0 AND statement_id = 'test1'
  4          CONNECT BY PRIOR id = parent_id AND
  5          statement_id = 'test1';

OPERATION            OPTIONS    OBJECT_NAME  POSITION
-------------------- ---------- ----------- ---------
SELECT STATEMENT
  SORT               AGGREGATE                      1
    TABLE ACCESS     FULL       TEMP                1

SQL>
SQL> EXPLAIN PLAN

  2          SET STATEMENT_ID = 'test1'
  3          INTO plan_table
  4          FOR
  5             SELECT count(pkey) from temp;

Explained.

SQL>
SQL> SELECT LPAD(' ',2*(LEVEL-1))||operation operation, options, object_name, position

  2          FROM plan_table
  3          START WITH id = 0 AND statement_id = 'test1'
  4          CONNECT BY PRIOR id = parent_id AND
  5          statement_id = 'test1';

OPERATION            OPTIONS    OBJECT_NAME  POSITION
-------------------- ---------- ----------- ---------
SELECT STATEMENT
  SORT               AGGREGATE                      1
    TABLE ACCESS     FULL       TEMP                1

SQL> As you can see, both have the same execution plan.

>Phil Tsao wrote in message <343AD21D.4C94E1B_at_technologist.com>...
>>Hi,
>>
>>I was wondering if any one has experienced the same problem.
>>I have a loop to add 10,000 rows to a table one by one then another loop
>>
>>to delete each row one by one. After doing so a few times, the
>>"select count(*) from table" starts doing table scan ( and physical
>>read)
>>every time. I thought the index (on primary key) was corrupted due to
>>the massive insert & delete and rebuilt it. It did not help.
>>The only way I can force using index is to use hint or the following:
>>
>> select count(*) from table where pkey > -9999;
>>
>>Is there anything you know that causes this to happen?
>>Thanks in advance.

Depending upon the current values of PCTUSED and PCTFREE, the blocks may or may not be available for reuse. This means the table may grow significantly after each repetition of the insert/delete loop. Each successive table scan will run proportionately longer as the high water mark moves.

>>
>>--
>>Phil Tsao
>>philt_at_technologist.com
>>
>>
>

Regards,

Jim Joslin - Principal Consultant (jjoslin_at_us.oracle.com) Oracle Consulting Services - Florida Technology Practice

The opinions expressed herein are my own and do not necessarily represent those of Oracle Corporation. Received on Wed Oct 15 1997 - 00:00:00 CDT