Thanks for the question, play.

Asked: August 27, 2001 - 11:25 pm UTC

Last updated: November 24, 2009 - 10:24 am UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Tom

I have observed that in a few queries of mine, I am retrieving columns which are part of the composite primary key, or the whole primary key.

then explain shows that this is doing a index scan and a table scan by rowid.

Now if I force the optimizer to use index_ffs, will it be more economical performance wise.

my optimizer right now is rbo, I will get the tables analyzed to do the above.

Want your suggestions, and examples.

Further, when I do a select empno from emp where rownum<2, it will return only one row to be

1.Will it do a full table scan
2.Will it make use of the index on empno
3.Will it return the row after it reads the first two rows, or does it read the whole table.
3.If for my key value there are multiple rows, and I want to return only one, what is the way apart from where rownum<2

Thanks

and Tom said...

Well, as with everything in life, it depends.

An INDEX_FFS (index fast full scan) will read the ENTIRE index structure. If you have a 2 gig index on a 200 gig table and you are only retrieving 20 rows from it -- reading the index as a data structure and NOT full scanning it will be faster.

an index ffs reads the entire index structure as if it were a table. You would only want to use it when you are getting MOST of the rows -- not just a few.

As for the rownum <2, the plan will say "full scan emp" but it will include a "count stopkey". Meaning it will start full scanning emp but quit the moment the rownum is exceeded.

It MIGHT use the index on emp, but maybe not ( with rownum < 2, it'll be easier to full scan the table rather then process an index structure).

It will not read the whole table.

as for "if for my key value there are >1 rows", you would have to give me some method to "identify" the row you want. If you don't care which row you get, rownum = 1 is as good as anything else.

Rating

(18 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Index_FFS and Full Table

A reader, February 07, 2004 - 11:11 am UTC

Hi Tom,

Do mean that Index_FFS can be slower than Full Table Scan? Why not faster given that Index_FFS reads less columns?

Could you give a solid example?

Thanks.

February 07, 2004 - 3:15 pm UTC

that is not what I said at all.

(but yes, it can be true for an index can in fact be larger than the table itself :)

In generaly, an INDEX_FFS will be faster than a TABLE FULL SCAN since in general the indexes are smaller than the tables they are on.

However, my point above was that an INDEX FULL SCAN or INDEX RANGE SCAN might be better (even though it uses single block IO) for certain classes of queries.

Index FFS

A reader, February 07, 2004 - 3:36 pm UTC

Hi Tom,

I have a query which joins a table multiple times. And I noticed that INDEX_FFS is being used on that table for all the joins. Will the index actually be scanned only once or multiple times?

February 07, 2004 - 3:58 pm UTC

i don't know. I cannot see your query plan from over here. My eyes are pretty good but they can read a screen more than a couple of feet away typically....

:)

(that means, the answer is

a) yes
b) no
c) maybe
d) all of the above <<<====

)

Diff. between FFS and RS

Jagjeet Singh, April 20, 2005 - 7:06 am UTC

Hi,

SQL> select tablespace_name,status from dba_Tablespaces ;

TABLESPACE_NAME                STATUS
------------------------------ ---------
....
TEST                           ONLINE
T                              ONLINE

5 rows selected.

SQL>
SQL> create table t tablespace TEST as select table_name from dict;

Table created.

SQL> create index ind_t on t(table_name) tablespace T;

Index created.


--- Table is in Test tablespace and Index in T tablespace ..


SQL>
SQL> alter tablespace test offline;

Tablespace altered.

SQL> set autot on exp



SQL> select count(table_name) from t where table_name >'A';

COUNT(TABLE_NAME)
-----------------
             1063

1 row selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     INDEX (RANGE SCAN) OF 'IND_T' (NON-UNIQUE)


I am getting result using range scan. It means, oracle is not reading
from table ( because tablespace is offline ) in range scan also.

Then what is diff. between Range Scan and Fast Full Scan.


Thanks,
Jagjeet Singh

April 20, 2005 - 7:09 am UTC

unless you see a "TABLE ...." step in there, correct. it does not need to read the table in this case.

see above, for what is the difference.

one does single block IO and reads the index keys in sort order.

the other does multi block IO and reads the index keys as it hits them, NOT in sort order.

Some Clarification

Muhammad Riaz Shahid, June 12, 2006 - 7:35 am UTC

Hello Tom,
Consider the following test case:

SQL> ed
Wrote file afiedt.buf

  1* create table t as select * from dba_objects
SQL> /

Table created.

SQL> insert into t select * from dba_objects;

41254 rows created.

SQL> commit;

Commit complete.

SQL> create index test_idx on t(object_id);

Index created.

SQL> analyze table t compute statistics;

Table analyzed.


SQL> set autot traceonly exp

SQL> ed
Wrote file afiedt.buf

  1  select DISTINCT object_id from t
  2* order by object_id
SQL> /

Execution Plan
----------------------------------------------------------
0   SELECT STATEMENT Optimizer=CHOOSE (Cost=779 Card=41212 Bytes=206060)

1  0   SORT (UNIQUE) (Cost=495 Card=41212 Bytes=206060)
2  1     TABLE ACCESS (FULL) OF 'T' (Cost=171 Card=82508 Bytes=412540)

Note: cpu costing is off

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
PL/SQL Release 9.2.0.5.0 - Production
CORE    9.2.0.6.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production

I was expecting optmizer will do an IFFS since all the required information is in index itself but unfortunately its not doing an IFFS. Could you please tell us why its not happening or i am missing something?

June 12, 2006 - 10:02 am UTC

ops$tkyte@ORA10GR2> create table t as select * from dba_objects where rownum = 1;
 
Table created.
 
ops$tkyte@ORA10GR2> desc t
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 OWNER                                             VARCHAR2(30)
 OBJECT_NAME                                       VARCHAR2(128)
 SUBOBJECT_NAME                                    VARCHAR2(30)
 OBJECT_ID                                         NUMBER
 DATA_OBJECT_ID                                    NUMBER
 OBJECT_TYPE                                       VARCHAR2(19)
 CREATED                                           DATE
 LAST_DDL_TIME                                     DATE
 TIMESTAMP                                         VARCHAR2(19)
 STATUS                                            VARCHAR2(7)
 TEMPORARY                                         VARCHAR2(1)
 GENERATED                                         VARCHAR2(1)
 SECONDARY                                         VARCHAR2(1)


because OBJECT_ID is nullable, and an index on OBJECT_ID by itself would not necessarily therefore point to every row.  (entirely null index keys are not entered into b*tree indexes)


hence, it cannot use the index, if you alter the table and modify object id to be NOT NULL, it would

Thanks for clarification

Muhammad Riaz Shahid, June 12, 2006 - 2:18 pm UTC

Thanks tom

Its cleared now.

nullable and index ffs

A reader, December 14, 2006 - 2:50 pm UTC

Hi

I dont get the idea why we cannot use index ffs for query such as (for ther emp table for example for test purposes we index COMM column)

select distinct comm from emp;

Aggregate Functions does not work with Nulls. I dont understand why in query above a index ffs would not work?

Imagine we have these values, 80, 30 and 40 and rest null, if we distinct using the index we still get 80, 30 and 40 correct? Null would not affect the result set.

December 15, 2006 - 8:32 am UTC

scott%ORA9IR2> set null xxxxx
scott%ORA9IR2> select distinct comm from emp;

COMM
----------
0
300
500
1400
xxxxx

scott%ORA9IR2>

aggregates do work with nulls.

aggregates do work with nulls.?!

A reader, December 28, 2006 - 9:39 am UTC

Hi

I do count(comm) from emp and it returns 5

select count(comm) from emp;

COUNT(COMM)
-----------
5

It does not count NULLs???

December 28, 2006 - 9:52 am UTC

count is defined to "count non-null occurrences of expression"

that does not mean it does not work with NULLs, it means nulls are not counted - huge huge difference.

indexes on tables

Reader, December 28, 2006 - 11:29 am UTC

we are in the process of creating a report on indexes created on tables in a schema. The purpose of this task is to determine if there is any missing indeesx after a release is applied so that we can compare before and after. I was wondering if you have any sql that could accomplish this.

Thanks

December 29, 2006 - 9:11 am UTC

select * from user_indexes;

dbms_metadata can generate them as well.

Actually, why don't you just have a query as part of your release install that runs very specific queries that looks for what you expect to be there?

clarification

Balu, December 29, 2006 - 3:31 am UTC

Dear Tom,

Can you pls tell me what is the diffrence between index full scan and index fast full scan when and at what situation it uses can you pls explain me with example.

December 29, 2006 - 9:47 am UTC

index full scan:

using single block IO, read down the left hand side of the index to the leaf block containing the "lowest values of the index key". The, continuing with single block IO, read the leaf blocks from left to right in order (leaf blocks point to the next leaf, we don't traverse a tree, we just read a linked list of sorted index keys on the leaves)

index fast full scan:

see original answer, just read the entire index structure (in any order) using multiblock IO. Ignore branch blocks, just process the leaf blocks as we hit them, in whatever order.

To: Balu

Michel Cadot, December 29, 2006 - 9:19 am UTC

Have a look at:
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#sthref1298

Michel

count(*) and index_ffs

abz, April 02, 2007 - 8:08 am UTC

IF
1-We have an index with a key with
atleast one non-null column.
2- The size of the index may or may not be smaller than the table.
Then
Can we say that index_ffs is always the fastest
way for the queries like SELECT COUNT(*) FROM tablea.?

because:-
1- There is no need of sorting here,
although the plan shows it, so there is no need of
index full scan.
2-FFS will only scan leaf blocks so even when the
overall size of index is larger than the table, it is
good to use FFS on that index because the total size
of leaf blocks might be smaller than the table size.

April 03, 2007 - 8:46 pm UTC

#2 says "may or may not be smaller than the table. "

so, we cannot say yes - obviously.

fast full scan does NOT scan just leaf blocks, it reads the entire index, all of it.

abz, April 05, 2007 - 9:31 am UTC

Ok, so if the index size is smaller or equal to the table,
then the above statement is correct?

See the plans below, the one with FAST FULL SCAN HINT does
complete within a minute or two and the one without
FAST FULL SCAN HINT is taken till now more than an hour,
and still not complete. Allthough the CBO is showing greater COST of index_ffs.
Why is CBO making wrong plan and costs.

1* select /*+ index_ffs(a xt075s1) */ count(*) from ci_bill_routing a
SQL> /

Execution Plan
----------------------------------------------------------

--------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27208 (2)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | INDEX FAST FULL SCAN| XT075S1 | 27M| 27208 (2)|
--------------------------------------------------------------

Note
-----
- 'PLAN_TABLE' is old version

SQL> select count(*) from ci_bill_routing;

Execution Plan
----------------------------------------------------------

---------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
---------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15510 (1)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | INDEX FULL SCAN| XT075S1 | 27M| 15510 (1)|
---------------------------------------------------------

Note
-----
- 'PLAN_TABLE' is old version

SQL> select name, value from v$parameter where name like '%multi%';

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
db_file_multiblock_read_count
128

1* select name, value from v$parameter where name like '%optimi%'
SQL> /

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
plsql_optimize_level
2

optimizer_features_enable
10.2.0.2

optimizer_mode
CHOOSE

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
optimizer_index_cost_adj
10

optimizer_index_caching
90

optimizer_dynamic_sampling
2

April 05, 2007 - 11:22 am UTC

if the index is smaller than the table, than an index fast full scan is LIKELY faster than a full table scan - yes.

you have told the optimizer that the index is almost entirely in the buffer cache (your optimizer_index_* parameters) and that single block IO is very cheap.

Hence, the fast full scan - assuming it was doing physical IO - did not look very appealing - single block IO from the buffer cache did.

ops$tkyte%ORA10GR2> create table t ( x int not null, y int );

Table created.

ops$tkyte%ORA10GR2> create index t_idx on t(x);

Index created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_stats.set_table_stats( user, 'T', numrows => 27000000, numblks => 27000000 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec dbms_stats.set_index_stats( user, 'T_IDX', numrows => 27000000, numlblks => 2700000 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> set linesize 1000
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @at
ops$tkyte%ORA10GR2> column PLAN_TABLE_OUTPUT format a72 truncate
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> alter session set optimizer_index_cost_adj =100;

Session altered.

ops$tkyte%ORA10GR2> alter session set optimizer_index_caching =0;

Session altered.

ops$tkyte%ORA10GR2> select /*+ index( t t_idx ) */ count(*) from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 995313729

------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |  2703K  (1)| 09:00:42 |
|   1 |  SORT AGGREGATE  |       |     1 |            |          |
|   2 |   INDEX FULL SCAN| T_IDX |    27M|  2703K  (1)| 09:00:42 |
------------------------------------------------------------------

ops$tkyte%ORA10GR2> select /*+ index_ffs( t t_idx ) */ count(*) from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 1058879072

-----------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |   593K  (1)| 01:58:46 |
|   1 |  SORT AGGREGATE       |       |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| T_IDX |    27M|   593K  (1)| 01:58:46 |
-----------------------------------------------------------------------

ops$tkyte%ORA10GR2> alter session set optimizer_index_cost_adj =10;

Session altered.

ops$tkyte%ORA10GR2> alter session set optimizer_index_caching =90;

Session altered.

ops$tkyte%ORA10GR2> select /*+ index( t t_idx ) */ count(*) from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 995313729

------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |   270K  (1)| 00:54:05 |
|   1 |  SORT AGGREGATE  |       |     1 |            |          |
|   2 |   INDEX FULL SCAN| T_IDX |    27M|   270K  (1)| 00:54:05 |
------------------------------------------------------------------

ops$tkyte%ORA10GR2> select /*+ index_ffs( t t_idx ) */ count(*) from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 1058879072

-----------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |   593K  (1)| 01:58:46 |
|   1 |  SORT AGGREGATE       |       |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| T_IDX |    27M|   593K  (1)| 01:58:46 |
-----------------------------------------------------------------------

ops$tkyte%ORA10GR2> set autotrace off

See the drop in cost for the index range scan? You made it really cheap looking, that is what these optimizer parameters do....

very nice

abz, April 05, 2007 - 1:50 pm UTC

I thought these parameters only tell about indexes not there access paths, I think, in the documentation its only
mentioned INDEX and there ACCESS PATHS is not mentioned
when these parameters are discussed.

April 05, 2007 - 1:59 pm UTC

not sure what you mean - the documentation does say what I said:

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams143.htm#sthref602
... by setting this parameter to a value between 0 and 100 to indicate the percentage of the index blocks the optimizer should assume are in the cache. .... over other indexes or full table scans....

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams144.htm#sthref607
Any other value makes the optimizer evaluate the access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as normal.

index ffs or full table scan

Raj, November 12, 2008 - 7:56 pm UTC

Tom
I have a table t1 as follows :
desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                      NOT NULL VARCHAR2(10)
 COL2                                               NUMBER(10)
 COL3                                               NUMBER(5)

2 indexes as follows :
create index i1 on t1(col1)
/
create index i2 on t1(col1,col2)
/
Index Stats :
select index_name, BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR, NUM_ROWS, LAST_ANALYZED
from all_indexes
where table_name = 'T1';
  2    3
INDEX_NAME                         BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR   NUM_ROWS LAST_ANALYZED
------------------------------ ---------- ----------- ------------- ----------------- ---------- ----------------
I1                                      1          62             2                53      21011 2008-11-10-22:00
I2                                      1          77         20091              1872      21011 2008-11-10-22:00


col1 has only 2 distinct values

when would 
select col1 from t1 where col1 <> 'A'
use index ffs ? i am just trying to see when the optimizer would choose between table full scan vs index ffs

SQL> set autotrace traceonly explain
SQL> select col1 from t1 where col1 <> 'P';

Execution Plan
----------------------------------------------------------
Plan hash value: 838529891

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 21011 |   102K|    17   (6)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   | 21011 |   102K|    17   (6)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("COL1"<>'P')


my db is
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for Solaris: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production
Parameters :
optimizer_features_enable            string                           10.2.0.2
optimizer_index_caching              integer                          0
optimizer_index_cost_adj             integer                          100
db_file_multiblock_read_count        integer                          21

Thanks

November 14, 2008 - 4:24 pm UTC

it is likely that the index (index key + rowid) is about the same size or maybe even *larger* than the table itself.

if the index where SMALLER than the table, it would cost less to fast full scan it than a full scan of the table - so that would be when it would do that

ops$tkyte%ORA10GR2> create table t ( col1 varchar2(10), col2 number(10), col3 number(5) );

Table created.

ops$tkyte%ORA10GR2> insert into t
  2  select substr( object_name, 1, 10 ), mod(object_id,10000000000), mod(object_id ,100000)
  3    from all_objects;

49808 rows created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create index t_idx on t(col1);

Index created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec show_space( 'T' );
Unformatted Blocks .....................              62
FS1 Blocks (0-25)  .....................               3
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               1
FS4 Blocks (75-100).....................               6<b>
Full Blocks        .....................             172
Total Blocks............................             256</b>
Total Bytes.............................       2,097,152
Total MBytes............................               2
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................               4
Last Used Ext BlockId...................          14,857
Last Used Block.........................             128

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec show_space( 'T_IDX', user, 'INDEX' );
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               1
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0<b>
Full Blocks        .....................             153
Total Blocks............................             256</b>
Total Bytes.............................       2,097,152
Total MBytes............................               2
Unused Blocks...........................              90
Unused Bytes............................         737,280
Last Used Ext FileId....................               4
Last Used Ext BlockId...................          15,113
Last Used Block.........................              38

PL/SQL procedure successfully completed.

the index would actually be larger here. more formatted blocks to full scan...

A reader, November 20, 2009 - 10:11 am UTC

Hello Tom,

could you please explain your above comment

<qoute>
"the index would actually be larger here. more formatted blocks to full scan... "
<qoute>

Thanks

November 23, 2009 - 4:04 pm UTC

I goofed, that example - the table was a *little larger* than the index, but so little that having the index in place for a fast full scan to avoid the table in this case would not make sense. The table and the index are just about the same size.

A reader, November 23, 2009 - 4:16 pm UTC

Nice reply

Do you mean that index have 103 (256 - 153) vs table have only 84(256-172)

that's make table scans costly is that correct?

November 23, 2009 - 5:06 pm UTC

No, the table:

ops$tkyte%ORA10GR2> exec show_space( 'T' );
Unformatted Blocks .....................              62
FS1 Blocks (0-25)  .....................               3
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               1
FS4 Blocks (75-100).....................               6
Full Blocks        .....................             172
Total Blocks............................             256
Total Bytes.............................       2,097,152
Total MBytes............................               2
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................               4
Last Used Ext BlockId...................          14,857
Last Used Block.........................             128

PL/SQL procedure successfully completed.

has 172 full blocks plus 6 almost full blocks plus one half empty block (179 blocks)
whereas the index:

ops$tkyte%ORA10GR2> exec show_space( 'T_IDX', user, 'INDEX' );
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               1
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................             153
Total Blocks............................             256
Total Bytes.............................       2,097,152
Total MBytes............................               2
Unused Blocks...........................              90
Unused Bytes............................         737,280
Last Used Ext FileId....................               4
Last Used Ext BlockId...................          15,113
Last Used Block.........................              38

has 153 full blocks and 1 less than half full - about 154 blocks.

As I said, I goofed, the index is slightly smaller than the table, but not hugely so - an index on a varchar2(10) PLUS the rowid would be about the same as as the table with a varchar2(10) and two number columns

my point was the index would be about the same size as the table itself, nothing to be gained by using the index for a full scan replacement.

A reader, November 23, 2009 - 9:49 pm UTC

Thanks Tom for your prompt reply

One more doubt sum of (unformat Block + fs1 + fs2 + fs3 + fs4 + full block) = Total block

I see discrepency of 12 block i.e. (62 + 3 + 0 + 1 + 6 + 172) = 244

(1) Is 12 block is use by bitmap to manage the free space I assumig the segemnt is define
in ASSM Tablespace ?

(2) Also unformat block is the blocks between LWM and HWM is that correct?

(3) unsed block is block above HWM is that correct?

Thanks

Regards

November 24, 2009 - 10:24 am UTC

(1) correct

(2) yes

(3) yes

A reader, November 24, 2009 - 2:47 pm UTC

Thanks for clarification!!!

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.

Breadcrumb

Question and Answer