full table scan vs index path for 1block table

From: Edward <eshevtsov_at_flagship.ru>
Date: Wed, 23 Aug 2000 16:23:58 +0400
Message-ID: <8o0fov$287t$1@josh.sovintel.ru>

Hello

Could anyone explain the following case in detail.

Oracle 8.1.6, using CBO

I have a small table tax_values. This is lookup table. There're no updates, deletes or index buildings at the moment !

SQL> desc tax_values;

 Name                                                  Null?    Type
 ----------------------------------------------------- -------- -----------
 TAX_GROUP_ID                                          NOT NULL NUMBER(10)
 TAX_CATEGORY_ID                                       NOT NULL NUMBER(10)
 TAX_CHAIN_ID                                          NOT NULL NUMBER(10)

Primary key is defined on (tax_group_id, tax_category_id)

SQL> select *
2 from tax_values;

TAX_GROUP_ID TAX_CATEGORY_ID TAX_CHAIN_ID ------------ --------------- ------------

           1               1            1
           2               1            3
           3               1            2
           1               2            1
           2               2            3
           3               2            2

6 rows selected.

SQL> analyze table tax_values compute statistics;
SQL>
SQL> select table_name, blocks

2 from user_tables
3 where lower(table_name) = 'tax_values';

TABLE_NAME                        BLOCKS
------------------------------ ---------
TAX_VALUES                             1

High Water Mark's statistics are :

SQL> @tab\findHWM
**

High Water Mark Information for TABLE BQC.TAX_VALUES **
Total number of blocks in the segment: 10
Total number of bytes in the segment: 40960
Number of blocks which are not used: 8
Number of bytes which are not used: 32768
File ID of the last extent which contains data: 10
Block ID of the last extent which contains data: 110606
Last block within this extent which contains data: 2
**
SQL> SQL> set autotrace on SQL> SQL> SELECT
- of course, this is second run 2 FROM TAX_VALUES 3 WHERE TAX_GROUP_ID = 1 AND TAX_CATEGORY_ID = 1;

TAX_CHAIN_ID

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=6) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TAX_VALUES' (Cost=1 Card=1 Bytes=6)

2 1 INDEX (UNIQUE SCAN) OF 'PK_TAX_VAL' (UNIQUE) Statistics

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

SQL> ed
Wrote file afiedt.buf

1 SELECT /*+ FULL(TAX_VALUES) */ TAX_CHAIN_ID 2 FROM TAX_VALUES
3* WHERE TAX_GROUP_ID = 1 AND TAX_CATEGORY_ID = 1 SQL> / TAX_CHAIN_ID

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=6) 1 0 TABLE ACCESS (FULL) OF 'TAX_VALUES' (Cost=1 Card=1 Bytes=6)

Statistics

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

SQL>
SQL> col name format a30

SQL> col value format a10
SQL> select name, value
2 from v$parameter
3 where name like 'db_file_multi%';

NAME                           VALUE
------------------------------ ----------

db_file_multiblock_read_count 4

The long prehistory :-), so let me a couple of questions. The only difference between two plans is
4 db block gets for second plan (by full table scan). What do they mean in this case? Why? Are they from recursive level (perhaps data dictionary)? But autotrace shows there are no recursive calls. And why the CBO sorts in the both cases?

I thought that the fastest way to get data from 1block table is full table scan. But the reality is not the same.

Please, make copy to me directly

Thanks in advance
Edward Received on Wed Aug 23 2000 - 07:23:58 CDT