SQL> -- Now it could use a Bitmap index even if the column contains NULLs !SQL> -- BUT ! The Count for the column excluded the NULLs !

So, we can have a COUNT of the rows returned from the WHERE clause (or all rows of the table if there is no WHERE clause) executed by :1. A FULL TABLE SCAN 2. An INDEX [FAST] FULL SCAN 3. A BITMAP Index

In any case, if you specify a Column as your COUNT target and the Column does contain a NULL in one or more rows, the rows with the NULL are excluded.On the other hand, if you specify a constaint (e.g. a "1") or a * as your COUNT target, no rows are excluded (other than rows that may have been excluded by filters specified in the WHERE clause).

Notice how I have NOT demonstrated a Primary Key index (or a Unique Index) being used for a Count. It is not necessary for an Index to be a Primary Key index for it to be usable by Oracle for this purpose. There are a number of Internet posts that suggest that Oracle can use a Primary Key to execute a count. The truth of the matter is that Oracle can use any Index if the index contains elements that are NOT NULL *OR* it can use a Bitmap Index because a Bitmap Index also captures NULLs.

Would anyone want to extent the test case to an Index Organized Table (an IOT) ?

SQL> -- the table has 1000 rows, as evidenced by the output aboveSQL> -- verify this :SQL> select count(*) from COUNT_ROWS_TBL;

COUNT(*)---------- 1000

SQL>SQL> -- First example : COUNT(column) doesn't count all the rowsSQL> -- if the column has a NULL in one or more rowsSQL>SQL> -- note how count(Column_1) excludes rows with a NULLSQL> select count(Column_1) from COUNT_ROWS_TBL ;

COUNT(COLUMN_1)--------------- 900

SQL> select count(Column_1) from COUNT_ROWS_TBL where Column_2 > 0;

COUNT(COLUMN_1)--------------- 900

SQL> -- this is the count of rows that the query for count(Column_1) excludesSQL> select count(*) from COUNT_ROWS_TBL where Column_1 is NULL;

COUNT(*)---------- 100

SQL> select count(Column_2) from COUNT_ROWS_TBL;

COUNT(COLUMN_2)--------------- 1000

SQL> select count(Column_2) from COUNT_ROWS_TBL where Column_2 > 0;

COUNT(COLUMN_2)--------------- 1000

SQL> select count(Column_3) from COUNT_ROWS_TBL;

COUNT(COLUMN_3)--------------- 1000

SQL> select count(Column_3) from COUNT_ROWS_TBL where Column_2 > 0;

COUNT(COLUMN_3)--------------- 1000

SQL>SQL>SQL> -- Second example : COUNT(1) does count all the rowsSQL> -- because 1 is a constant, not-NULL, valueSQL> select count(1) from COUNT_ROWS_TBL ;

COUNT(1)---------- 1000

SQL> select count(1) from COUNT_ROWS_TBL where Column_2 > 0;

COUNT(1)---------- 1000

SQL> select count(1) from COUNT_ROWS_TBL;

COUNT(1)---------- 1000

SQL> select count(1) from COUNT_ROWS_TBL where Column_2 > 0;

COUNT(1)---------- 1000

SQL> select count(1) from COUNT_ROWS_TBL;

COUNT(1)---------- 1000

SQL> select count(1) from COUNT_ROWS_TBL where Column_2 > 0;

COUNT(1)---------- 1000

SQL>SQL>SQL> -- Third example : COUNT(*) might use an IndexSQL> -- If the index is on a column that is guaranteed to not contain NULLsSQL>SQL> set autotrace onSQL> select count(*) from COUNT_ROWS_TBL;

SQL> -- note how Oracle chose to use the Index nowSQL> -- Remember : It will use the Index IF :SQL> --- a. The Indexed ColumnSQL> (or at least one column in a composite index)2 is a NOT NULL column3 --- b. The calculated "Cost" (determined by the Query optimizer)4 --- is lower for an Index [Fast] Full Scan than for a Full Table Scan5SQL>

So, a COUNT(column) and a COUNT(constant) and a COUNT(*) do not necessarily mean the same thing.

13 July, 2011

As I demonstrated in my previous blog post "ENABLE ROW MOVEMENT" the ALTER TABLE ... ENABLE ROW MOVEMENT is not just for supporting the ALTER TABLE ... SHRINK SPACE.

Furthermore, unlike ALTER TABLE ... SHRINK SPACE which requires that the Table be created in a Tablespace with Segment Space Management AUTO ("ASSM"), ENABLE ROW MOVEMENT can be done for a table in a Segment Space Management MANUAL ("MSSM") Tablespace as well.

12 July, 2011

Since the ALTER TABLE SHRINK command appeared and "ENABLE ROW MOVEMENT" has been presented as a requirement, some DBAs have been confused about what it means to enable row movement.

This does *NOT* cause Oracle to automatically move a row. However, a row may be moved as a result of action by the DBA (e.g. ALTER TABLE SHRINK) or a User / Application. The latter is the case where a row in a Partitioned Table has to move from one Partition to another because the Partition Key itself in that row has been updated.Note that updating the Partition Key (such that a row actually moves to another Partition) is frowned upon and is not enabled by default. In the rare case where your design has a flaw that rows have to move between Partitions, you need to ENABLE ROW MOVEMENT.

In the example below, a row has to move from the 'SG' Partition to the 'IN' Partition. This is disallowed by Oracle until and unless the DBA ENABLEs ROW MOVEMENT :

So it is not just ALTER TABLE SHRINK that the ENABLE ROW MOVEMENT is necessary for. Yet, it is likely a defect in your design if you have frequent movements of rows between Partitions. Remember that GLOBAL and LOCAL Indexes have to be updated....

You can see that the there was very little activity in the database. Less than 4 minutes of database time in 67minutes of elapsed time. There was less than 1 user call per second and 6 parses per second.So, there was *some* activity occurring (6 parses per second on machine with only 1 CPU/Core). But it didn't consume significant resources. 6 parses per second over 67minutes does account for the more than 24thousand parses.

Why the high activity on FILE$ ? There was a job that was periodically dropping a table, creating a new table, populating it with 400,000 rows, deleting them, doing a rollback and then a count. The job would, in a loop, after a noticeable "sleep" period, run another pass of drop-create-populate-delete-rollback-count.

In this case, the data dictionary queries/updates (FILE$, SYSAUTH$, UNDO$, SEG$, COL_USAGE$) were all because of the pattern of operations. (FILE$ to identify the file for the extent allocation, SEG$ to update the table segment information with each new extent added, SYSAUTH$ for the privileges, UNDO$ for the undo segment and COL_USAGE$ to update column usage on queries.

With respect to the second response about the data dictionary stats and object stats, these were not an issue here....

05 July, 2011

Occasionally, we come across questions about multiple channels and parallelism in RMAN.Although RMAN distributes the datafiles across the channels, it doesn't necessarily mean that each channel has the same I/O requirements and needs the same amount of time. One channel may be reading more data and writing a larger backup than another.

For example, in this database with 16 datafiles where data is not equally distributed across all the datafiles :

SQL> select file_id, sum(blocks) from dba_extents group by file_id order by 1;

So, Channel ORA_DISK_2 did a backup of 8 datafiles in 7seconds while Channel ORA_DISK_1 took 248 seconds to run a backup of 9 datafiles !

The next time you wonder why one RMAN channel takes a longer time than the other ..... ask yourself if the two channels have been given the same amount of "work" to do.

In my case, Channel ORA_DISK_2 had to backup data from 152 blocks only but Channel ORA_DISK_1 had to backup data from 551,104 blocks (*assuming* that there are no "empty" blocks that were formerly part of used extents but are now free extents) :

SQL> select file_id, sum(blocks) from dba_extents 2 where file_id in 3 (select file_id from dba_data_files 4 where (file_name like '%FLOW%' or file_name like '%APEX%') 5 ) 6 group by file_id 7 order by file_id 8 /

FILE_ID SUM(BLOCKS)---------- ----------- 13 152 -----------sum 152

SQL> select file_id, sum(blocks) from dba_extents 2 where file_id not in 3 (select file_id from dba_data_files 4 where (file_name like '%FLOW%' or file_name like '%APEX%') 5 ) 6 group by file_id 7 order by file_id 8 /