Histograms on "larger" columns

UPDATE :  These figures (the 254 distinct values, the 32 characters/bytes limit etc) were in tests on Release 10.2
Release 12.1 has relaxed some of the limits and has new Histogram types.

We rely on Oracle's collection of Histograms to denote data skew. However, if we give it some thought, it should be fairly obvious that generation of such histograms is a resource intensive task. The kernel has to identify all the distinct values for a column and, for each distinct value, count the number of occurrences of each such value.
If there are very many distinct values (more than 254) Oracle actually creates what it calls a Height Balanced Histogram, instead of a Frequency Histogram. (If the DBA explicitly specifies a number of buckets for column statistics and Oracle determines that the distinct count is more than such number, then, too, Oracle creates a Height Balanced Histogram).

Thus, it is fairly understood that
a. The number of distinct values
and
b. The number of rows in the table or sample
would determine the "effort" required to create a Histogram.

However, we sometimes forget that how "large" the column is -- in terms of how many characters (or bytes) are present in each occurrence [row] -- is also important. It would require more time and effort to compute the number of distinct values for a column with a length of 36 characters then it would for, say, a column with a length of 6 characters only. Sorting 36-character elements requires more memory/temp space and CPU (and I/O) then sorting 6-character elements. As also would be the effort to count the number of occurrences of each distinct value. (There may well be "word-size" limitations of the algorithms used as well !)

Therefore, when it comes to deriving a Histogram on such "large" columns, Oracle has to draw the line somewhere.

If the first 6 characters (actually, bytes !) of a column are the same, Oracle doesn't actually place the column values in ENDPOINT_VALUE in the %_TAB_HISTOGRAMS table. The values are placed in ENDPOINT_ACTUAL_VALUE !

However, if the first 32 characters (actually, bytes !) are the same, Oracle doesn't compare the rest of the characters in that row. Effectively, no Histogram is computed for a column where the first 32 characters (bytes) are the same.


Here I conduct a small test with columns of different values with 'N' leading characters being the same.

I first create a 10,000 row table with 4 columns and 10 distinct values in each column (with exactly 1,000 rows for each value) such that :

a. In Column A_SMALLER_COLUMN, only the first 2 characters are always the same and the third character varies
b. In Column B_LARGER_COLUMN, the first 6 characters are always the same
c. In Column C_GREATEST_COLUMN, the first 32 characters are always the same
d. In Column FIRST_FIVE_SAME, the first 5 characters the always the same

SQL> create table TEST_HSTGRM
2  (
3  a_smaller_column  varchar2(5),
4  b_larger_column varchar2(20),
5  c_greatest_column varchar2(60),
6  first_five_same varchar2(8)
7  );

Table created.

SQL>
SQL> declare
2  num_rows number;
3
4  begin
5  for num_rows in 1..10000
6  loop
7   insert into TEST_HSTGRM
8   values (
9           'A_'||mod(num_rows,10)||'_X',
10           'Large_'||mod(num_rows,10)||'_XY',
11           'C123_ABCDEFGHIJKLMNOPQRSTUVWXYZ_'||mod(num_rows,10)||'_XYZ',
12           'Five_'||mod(num_rows,10));
13  end loop;
14  end;
15  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> exec dbms_stats.gather_table_stats('','TEST_HSTGRM',estimate_percent=>100,-
> method_opt=>'FOR ALL COLUMNS SIZE 254');

PL/SQL procedure successfully completed. 

I next see what column statistic Oracle has collected :

SQL> col column_name format a18
SQL> col endpoint_value format 9999999999999999999999999999999999999
SQL> col endpoint_actual_value format a30
SQL>
SQL> select column_name, sample_size, num_distinct, num_buckets, histogram,
2  low_value, high_value
3  from user_tab_columns
4  where table_name = 'TEST_HSTGRM';

COLUMN_NAME        SAMPLE_SIZE NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------------ ----------- ------------ ----------- ---------------
LOW_VALUE
----------------------------------------------------------------
HIGH_VALUE
----------------------------------------------------------------
A_SMALLER_COLUMN         10000           10          10 FREQUENCY
415F305F58
415F395F58

B_LARGER_COLUMN          10000           10          10 FREQUENCY
4C617267655F305F5859
4C617267655F395F5859

C_GREATEST_COLUMN        10000            1           1 FREQUENCY
433132335F4142434445464748494A4B4C4D4E4F505152535455565758595A5F
433132335F4142434445464748494A4B4C4D4E4F505152535455565758595A5F

FIRST_FIVE_SAME          10000           10          10 FREQUENCY
466976655F30
466976655F39


SQL>
SQL> select column_name, count(*)
2  from user_tab_histograms
3  where table_name = 'TEST_HSTGRM'
4  group by column_name order by 1;

COLUMN_NAME          COUNT(*)
------------------ ----------
A_SMALLER_COLUMN           10
B_LARGER_COLUMN            10
C_GREATEST_COLUMN           1
FIRST_FIVE_SAME            10

SQL> 

Notice how Oracle could not (did not) compute a Histogram for C_GREATEST_COLUMN. With the first 32 characters being the same, Oracle just says that there is "1 distinct value". The LOW_VALUE and HIGH_VALUE are the same.

Next, I look at the actual Histograms on these columns :

SQL> set linesize132
SQL> select column_name, endpoint_value, endpoint_actual_value, endpoint_number
2  from user_tab_histograms
3  where table_name = 'TEST_HSTGRM' order by 1,3,2;

COLUMN_NAME                                ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE          ENDPOINT_NUMBER
------------------ -------------------------------------- ------------------------------ ---------------
A_SMALLER_COLUMN     339429957176373000000000000000000000                                           1000
A_SMALLER_COLUMN     339430036404535000000000000000000000                                           2000
A_SMALLER_COLUMN     339430115632698000000000000000000000                                           3000
A_SMALLER_COLUMN     339430194860860000000000000000000000                                           4000
A_SMALLER_COLUMN     339430274089023000000000000000000000                                           5000
A_SMALLER_COLUMN     339430353317185000000000000000000000                                           6000
A_SMALLER_COLUMN     339430432545348000000000000000000000                                           7000
A_SMALLER_COLUMN     339430511773510000000000000000000000                                           8000
A_SMALLER_COLUMN     339430591001673000000000000000000000                                           9000
A_SMALLER_COLUMN     339430670229835000000000000000000000                                          10000
B_LARGER_COLUMN      396591018990235000000000000000000000 Large_0_XY                                1000
B_LARGER_COLUMN      396591018990235000000000000000000000 Large_1_XY                                2000
B_LARGER_COLUMN      396591018990235000000000000000000000 Large_2_XY                                3000
B_LARGER_COLUMN      396591018990235000000000000000000000 Large_3_XY                                4000
B_LARGER_COLUMN      396591018990235000000000000000000000 Large_4_XY                                5000
B_LARGER_COLUMN      396591018990235000000000000000000000 Large_5_XY                                6000
B_LARGER_COLUMN      396591018990235000000000000000000000 Large_6_XY                                7000
B_LARGER_COLUMN      396591018990235000000000000000000000 Large_7_XY                                8000
B_LARGER_COLUMN      396591018990235000000000000000000000 Large_8_XY                                9000
B_LARGER_COLUMN      396591018990235000000000000000000000 Large_9_XY                               10000
C_GREATEST_COLUMN    348881704899430000000000000000000000                                          10000
FIRST_FIVE_SAME      365599813402059000000000000000000000                                           1000
FIRST_FIVE_SAME      365599813402063000000000000000000000                                           2000
FIRST_FIVE_SAME      365599813402068000000000000000000000                                           3000
FIRST_FIVE_SAME      365599813402073000000000000000000000                                           4000
FIRST_FIVE_SAME      365599813402078000000000000000000000                                           5000
FIRST_FIVE_SAME      365599813402082000000000000000000000                                           6000
FIRST_FIVE_SAME      365599813402087000000000000000000000                                           7000
FIRST_FIVE_SAME      365599813402092000000000000000000000                                           8000
FIRST_FIVE_SAME      365599813402096000000000000000000000                                           9000
FIRST_FIVE_SAME      365599813402101000000000000000000000                                          10000

31 rows selected.

SQL> 

As we've already seen, there is no real Histogram for C_GREATEST_COLUMN. There is a single entry representing 10,000 rows.
However, for column B_LARGER_COLUMN (where the first 6 characters are the same in every row), the actual value for each Histogram endpoint is visible in the ENDPOINT_ACTUAL_VALUE column while ENDPOINT_VALUE appears to be the same.

Finally, I look at how Oracle uses the Histograms to come up with the Cardinality estimates :

SQL> explain plan for
 2  select a_smaller_column from TEST_HSTGRM where a_smaller_column = 'A_3_X';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT                           
----------------------------------------------------------------------------------------------------
Plan hash value: 2702196353

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |  1000 |  6000 |    27   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_HSTGRM |  1000 |  6000 |    27   (0)| 00:00:01 |
---------------------------------------------------------------------------------
  
Predicate Information (identified by operation id):
---------------------------------------------------
                                                                     
  1 - filter("A_SMALLER_COLUMN"='A_3_X')

13 rows selected.

SQL> select count(*) from TEST_HSTGRM where a_smaller_column = 'A_3_X';

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

SQL> 
SQL> explain plan for
 2  select b_larger_column from TEST_HSTGRM where b_larger_column = 'Large_3_XY';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2702196353

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |  1000 | 11000 |    27   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_HSTGRM |  1000 | 11000 |    27   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

  1 - filter("B_LARGER_COLUMN"='Large_3_XY')

13 rows selected.

SQL> select count(*) from TEST_HSTGRM where b_larger_column = 'Large_3_XY';

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

SQL>
SQL> explain plan for
 2  select c_greatest_column from TEST_HSTGRM
 3  where c_greatest_column = 'C123_ABCDEFGHIJKLMNOPQRSTUVWXYZ_3_XYZ';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2702196353

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             | 10000 |   371K|    27   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_HSTGRM | 10000 |   371K|    27   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

  1 - filter("C_GREATEST_COLUMN"='C123_ABCDEFGHIJKLMNOPQRSTUVWXYZ_3_XYZ'
             )

14 rows selected.

SQL> select count(*) from TEST_HSTGRM
 2  where c_greatest_column = 'C123_ABCDEFGHIJKLMNOPQRSTUVWXYZ_3_XYZ';

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

SQL>
SQL> explain plan for
 2  select first_five_same from TEST_HSTGRM where first_five_same = 'Five_3';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2702196353

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |  1000 |  7000 |    27   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_HSTGRM |  1000 |  7000 |    27   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

  1 - filter("FIRST_FIVE_SAME"='Five_3')

13 rows selected.

SQL> select count(*) from TEST_HSTGRM where first_five_same = 'Five_3';

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

SQL> 

For Column A_SMALLER_COLUMN, the Cardinality Estimate of 1,000 rows is accurate.
For Column B_LARGER_COLUMN, too, Cardinality Estimate is accurate (thus, we should look at ENDPOINT_ACTUAL_VALUE rather than ENDPOINT_VALUE in USER_TAB_HISTOGRAMS)
For Column FIRST_FIVE_SAME, we see similar results as A_SMALLER_COLUMN.

However, for column C_GREATEST_COLUMN, in the absence of the Histogram (which did not get computed), Oracle presents a Cardinality Estimate of 1 ! For this column, LOW_VALUE and HIGH_VALUE were the same and NUM_DISTINCT was 1.
For this column, Oracle does not DENSITY, even for an Outlier value :

SQL> l
  1  explain plan for
  2  select c_greatest_column from TEST_HSTGRM
  3* where c_greatest_column = 'C123_ABCDEFGHIJKLMNOPQRSTUVWXYZ_z_XYZ'
SQL> /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2702196353

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             | 10000 |   371K|    27   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_HSTGRM | 10000 |   371K|    27   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

   1 - filter("C_GREATEST_COLUMN"='C123_ABCDEFGHIJKLMNOPQRSTUVWXYZ_z_XYZ'
              )

14 rows selected.

SQL>  

Therefore, when you have columns with the first 32 chraracters/bytes being the same, do not expect Oracle to present any degree of accuracy in the Cardinality estimate. You will have to rely on other columns in the query.

.
.
.

Counting the Rows in a Table

Tanel Poder points out how COUNT(*) and COUNT(column) are not necessarily the same. A COUNT on a Column ignores NULL values in the column !

Here, I conduct a few more tests.

SQL> create table test_count (col_1 varchar2(5), col_2 varchar2(5), col_3 varchar2(5));

Table created.

SQL> insert into test_count values ('a','first','1');

1 row created.

SQL> insert into test_count values ('b',null,'2');

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from test_count;

COUNT(*)
----------
   2

SQL> select count(col_1) from test_count;

COUNT(COL_1)
------------
     2

SQL> select count(col_2) from test_count;

COUNT(COL_2)
------------
     1

SQL> 

Although the table has 2 rows, since one of the rows is a NULL in COL_2, a count on COL_2 misses that row.

SQL> insert into test_count values ('',NULL,'');

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from test_count;

COUNT(*)
----------
    3

SQL> select count(col_1) from test_count;

COUNT(COL_1)
------------
      2

SQL> select count(col_2) from test_count;

COUNT(COL_2)
------------
      1

SQL> 

I inserted a row with all NULLs. Oracle allows me to insert an ALL NULL row. Now, a count on any column returns incorrect results. Only a COUNT(*) is correct.


Now, I proceed to another test. Here I create a larger table.

SQL> select count(*) from dba_objects where object_id is null;

COUNT(*)
----------
     1

SQL> create table another_test_count as select owner, object_id, object_type from dba_objects;

Table created.

SQL> select count(*) from another_test_count;

COUNT(*)
----------
 50628

SQL> select count(owner) from another_test_count;

COUNT(OWNER)
------------
   50628

SQL> select count(object_id) from another_test_count;

COUNT(OBJECT_ID)
----------------
       50627

SQL> 

As I expected, a COUNT on OBJECT_ID is one row short as one of the OBJECT_IDs is NULL.

I now create an Index on the table.

SQL> create index another_test_count_ndx on another_test_count(object_id,owner);   

Index created.

SQL> exec dbms_stats.gather_table_stats('','ANOTHER_TEST_COUNT',estimate_percent=>100,cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> 

Now, I attempt to use the Index to count the number of rows. The index is smaller than the table so an INDEX FAST FULL SCAN should be preferred over a FULL TABLE SCAN.

SQL> select count(*) from another_test_count;

COUNT(*)
----------
   50628


Execution Plan
----------------------------------------------------------
Plan hash value: 3536081126

---------------------------------------------------------------------------------
| Id  | Operation          | Name               | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                    |     1 |    52   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE    |                    |     1 |            |          |
|   2 |   TABLE ACCESS FULL| ANOTHER_TEST_COUNT | 50628 |    52   (2)| 00:00:01 |
---------------------------------------------------------------------------------


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

SQL> alter table another_test_count modify (owner not null);

Table altered.

SQL> select count(*) from another_test_count;

 COUNT(*)
----------
    50628


Execution Plan
----------------------------------------------------------
Plan hash value: 227569207

----------------------------------------------------------------------------------------
| Id  | Operation             | Name                   | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                        |     1 |    43   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |                        |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| ANOTHER_TEST_COUNT_NDX | 50628 |    43   (0)| 00:00:01 |
----------------------------------------------------------------------------------------


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

SQL>  

In my first pass at a COUNT(*), Oracle does not use the Index even though it is smaller than the table. Why not ? Because Oracle cannot be sure that the Index captures every ROWID from the table. This is the case where, for a concatenated index, every column has a NULL for a particular row, resulting in that row being "excluded" from the Index.

As soon as I change the OWNER column (which isn't even the leading column of the Index) to a NOT NULL, the Optimizer is assured that an Index on this column *will* capture every ROWID. At the next COUNT(*), the Optimizer prefers to do an INDEX FAST FULL SCAN !

(Note : I had run the "SELECT COUNT(*) FROM ANOTHER_TEST_COUNT" twice before altering OWNER to NOT NULL and twice again after altering it to NOT NULL. I have not reported the autotrace results of each first run as it includes Parse overheads -- the recursive calls inflating the 'consistent gets' count).

.
.
.

Using an Index created by a different user

Can a query by user "C" on a table owned by user "A" actually use an Index owned and created by "B", although on "A"'s table ?
YES. "C" needs the SELECT privilege on "A"'s table as would be necessary to run the query but does not need any reference/privilege on "B"'s index.


Here's an example :

Table owner : HEMANT
Index owner : AN_INDEX_OWNER
Query run by : A_QUERY_USER

Account "A_QUERY_USER" has SELECT on the table "MY_COPY_OF_OBJECTS" in HEMANT's schema but no privileges on the index in AN_INDEX_OWNER's schema (there's no such thing as granting SELECT on an Index)
Account "AN_INDEX_OWNER" has SELECT on "MY_COPY_OF_OBJECTS" in HEMANT's schema and the privilege to create an Index

When "A_QUERY_USER" runs a query on HEMANT's table, the query does use the Index created by "AN_INDEX_OWNER" !

SQL> connect hemant/hemant
Connected.
SQL> create table my_copy_of_objects as select * from dba_objects;

Table created.

SQL> exec dbms_stats.gather_table_stats('','MY_COPY_OF_OBJECTS',estimate_percent=>100);

PL/SQL procedure successfully completed.

SQL> 
SQL> create user a_query_user identified by a_query_user;

User created.

SQL> grant create session to a_query_user;

Grant succeeded.

SQL> grant plustrace to a_query_user;

Grant succeeded.

SQL> grant select on my_copy_of_objects to a_query_user;

Grant succeeded.

SQL> 
SQL> create user an_index_owner identified by an_index_owner;

User created.

SQL> -- needs "CREATE ANY INDEX" and  SELECT  to be able to create an index
SQL> grant create session,create any index to an_index_owner;

Grant succeeded.

SQL> grant select on my_copy_of_objects to an_index_owner;

Grant succeeded.

SQL> alter user an_index_owner default tablespace users;

User altered.

SQL> alter user an_index_owner quota unlimited on users;

User altered.

SQL> 
SQL> connect an_index_owner/an_index_owner
Connected.
SQL> create index hemant_m_c_o_o_ndx_1 on hemant.my_copy_of_objects(object_id);

Index created.

SQL> create index hemant_m_c_o_o_ndx_2 on hemant.my_copy_of_objects(owner);

Index created.

SQL> 
SQL> -- Verify indexes owned by AN_INDEX_OWNER
SQL> select index_name, table_owner, table_name from user_indexes order by 1;

INDEX_NAME                     TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------ ------------------------------
HEMANT_M_C_O_O_NDX_1           HEMANT                         MY_COPY_OF_OBJECTS
HEMANT_M_C_O_O_NDX_2           HEMANT                         MY_COPY_OF_OBJECTS

SQL> 
SQL> -- Verify that indexes on HEMANT's tables are owned by AN_INDEX_OWNER
SQL> connect / as sysdba
Connected.
SQL> select owner, index_name, table_owner, table_name from dba_indexes where table_name = 'MY_COPY_OF_OBJECTS' order by 1,2;

OWNER                          INDEX_NAME                     TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
AN_INDEX_OWNER                 HEMANT_M_C_O_O_NDX_1           HEMANT                         MY_COPY_OF_OBJECTS
AN_INDEX_OWNER                 HEMANT_M_C_O_O_NDX_2           HEMANT                         MY_COPY_OF_OBJECTS

SQL> 
SQL> REM REM REM ################
SQL> 
SQL> -- now we query the table
SQL> connect a_query_user/a_query_user
Connected.
SQL> select owner, object_name, object_id from hemant.my_copy_of_objects where object_id > 54087;

OWNER                          OBJECT_NAME                     OBJECT_ID
------------------------------ ------------------------------ ----------
HEMANT                         MY_COPY_OF_OBJECTS                  54123

SQL> select count(*) from hemant.my_copy_of_objects where owner = 'HEMANT';

  COUNT(*)
----------
        17

SQL> 
SQL> -- rerun the queries, we avoid parse overheads now
SQL> 
SQL> set autotrace on
SQL> select owner, object_name, object_id from hemant.my_copy_of_objects where object_id > 54087;

OWNER                          OBJECT_NAME                     OBJECT_ID
------------------------------ ------------------------------ ----------
HEMANT                         MY_COPY_OF_OBJECTS                  54123


Execution Plan
----------------------------------------------------------
Plan hash value: 2159204631

----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                      |    34 |  1224 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MY_COPY_OF_OBJECTS   |    34 |  1224 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | HEMANT_M_C_O_O_NDX_1 |    34 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID">54087)


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

SQL> select count(*) from hemant.my_copy_of_objects where owner = 'HEMANT';

  COUNT(*)
----------
        17


Execution Plan
----------------------------------------------------------
Plan hash value: 360370019

------------------------------------------------------------------------------------------
| Id  | Operation         | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                      |     1 |     6 |     5   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |                      |     1 |     6 |            |          |
|*  2 |   INDEX RANGE SCAN| HEMANT_M_C_O_O_NDX_2 |  1876 | 11256 |     5   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   2 - access("OWNER"='HEMANT')


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

SQL> 

.
.
.