Re: full table scan vs index path for 1block table

The table is 5 blocks big.
db_file_multiblock_read_count 4
will read 4 blocks. It will only not read 4 blocks if the table would have been smaller than 4 blocks.

Hth,

Sybrand Bakker, Oracle DBA

"Edward" <eshevtsov_at_flagship.ru> wrote in message news:8o0fov$287t$1_at_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
> ------------
> 1
>
> 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
> ------------
> 1
>
> 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 - 12:10:54 CDT