Skip to Main Content
  • Questions

  • Slow select after bulk insert-remove

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Vladimir.

Asked: August 22, 2016 - 1:40 pm UTC

Last updated: September 02, 2016 - 4:23 pm UTC

Version: 11.2.0.1.0

Viewed 1000+ times

You Asked

I have the problem, that I could describe in several steps:
1. Insert 160K into table t_reg_account. Here I use simple insert statement without any hints. I_ACC_PK index on participant_code,
account_type, account_value, account_sequence.
2. Execute select:
   SELECT * FROM
   ( SELECT ra.* FROM t_reg_account ra
      WHERE ra.participant_code = 'PA1'
        AND ra.account_type = 'P1'
        AND ra.account_value = '111558888888'
   ORDER BY ra.account_sequence desc )
   WHERE rownum = 1;


Please note, that subselect returns all 160K row.

   SELECT ra.* FROM t_reg_account ra
      WHERE ra.participant_code = 'PA1'
        AND ra.account_type = 'P1'
        AND ra.account_value = '111558888888'
   ORDER BY ra.account_sequence desc


It is executed pretty fast(several consistent gets, from execution plan below)
3. Remove all rows from table t_reg_account using DELETE statement.
4. Execute select from 2. Now the same query consumes several hundreds of consistent gets on empty table.

I understood that after "delete all statement", I_ACC_PK index is into "inappropriate state" and I found that index rebuild
fixes the problem(I made index analysis, after rebuild it has the same values as at the beginning). However I don't understand
why did this happen. Could you, please, explain me what is wrong with the index and how to fix it.

All performed steps in details are below.
====================================================================================================================

159299 rows are in t_reg_account

SQL> SELECT * FROM
2 ( SELECT ra.* FROM t_reg_account ra
3 WHERE ra.participant_code = 'PA1'
4 AND ra.account_type = 'P1'
5 AND ra.account_value = '111558888888'
6 ORDER BY ra.account_sequence desc )
7 WHERE rownum = 1;

1 row is selected

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
SQL_ID 82wrsqhu9n7yw, child number 2
-------------------------------------
SELECT * FROM ( SELECT ra.* FROM t_reg_account ra WHERE
ra.participant_code = 'PA1' AND ra.account_type = 'P1'
AND ra.account_value = '111558888888' ORDER BY
ra.account_sequence desc ) WHERE rownum = 1

Plan hash value: 1606291734

----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | VIEW | | 2 | 1134 | 3 (0)| 00:00:01 | | |
| 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| T_REG_ACCOUNT | 12806 | 1813K| 3 (0)| 00:00:01 | ROWID | ROWID |
|* 4 | INDEX RANGE SCAN DESCENDING | I_ACC_PK | 2 | | 2 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------

SQL> select * from table(dbms_xplan.display_cursor('82wrsqhu9n7yw', null, 'ALLSTATS LAST'));

SQL_ID 82wrsqhu9n7yw, child number 2

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
-------------------------------------
SELECT * FROM ( SELECT ra.* FROM t_reg_account ra WHERE
ra.participant_code = 'PA1' AND ra.account_type = 'P1'
AND ra.account_value = '111558888888' ORDER BY
ra.account_sequence desc ) WHERE rownum = 1

Plan hash value: 1606291734

----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 |
|* 1 | COUNT STOPKEY | | 1 | | 1 |00:00:00.01 | 4 |
| 2 | VIEW | | 1 | 2 | 1 |00:00:00.01 | 4 |
| 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| T_REG_ACCOUNT | 1 | 12806 | 1 |00:00:00.01 | 4 |
|* 4 | INDEX RANGE SCAN DESCENDING | I_ACC_PK | 1 | 2 | 1 |00:00:00.01 | 3 |
----------------------------------------------------------------------------------------------------------------

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

1 - filter(ROWNUM=1)

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
4 - access("RA"."PARTICIPANT_CODE"='PA1' AND "RA"."ACCOUNT_TYPE"='P1' AND
"RA"."ACCOUNT_VALUE"='111558888888')


91 rows selected.


SQL> analyze index I_ACC_PK validate structure;

Index analyzed.

SQL> select * from index_stats;

HEIGHT BLOCKS NAME PARTITION_NAME LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN
---------- ---------- ------------------------------ ------------------------------ ---------- ---------- ----------- ----------
3 1024 I_ACC_PK 159299 899 7156750 8000

BR_ROWS BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE
---------- ---------- ----------- ---------- ----------- --------------- ------------- ----------------- ----------- ----------
898 6 33161 8032 0 0 159299 1 7240192 7189911

PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
------------ ------------ -------------------- ---------- ------------ -------------- ----------------
100 1 4 0 0 3 57


DELETE FROM T_REG_ACCOUNT;

0 rows are in t_reg_account

SQL> SELECT * FROM
2 ( SELECT ra.* FROM t_reg_account ra
3 WHERE ra.participant_code = 'PA1'
4 AND ra.account_type = 'P1'
5 AND ra.account_value = '111558888888'
6 ORDER BY ra.account_sequence desc )
7 WHERE rownum = 1;

no rows selected

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
SQL_ID 82wrsqhu9n7yw, child number 3
-------------------------------------
SELECT * FROM ( SELECT ra.* FROM t_reg_account ra WHERE
ra.participant_code = 'PA1' AND ra.account_type = 'P1'
AND ra.account_value = '111558888888' ORDER BY
ra.account_sequence desc ) WHERE rownum = 1

Plan hash value: 1606291734

----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 74 (100)| | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | VIEW | | 1 | 567 | 74 (0)| 00:00:01 | | |
| 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| T_REG_ACCOUNT | 1 | 145 | 74 (0)| 00:00:01 | ROWID | ROWID |
|* 4 | INDEX RANGE SCAN DESCENDING | I_ACC_PK | 1 | | 73 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------

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


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
1 - filter(ROWNUM=1)
4 - access("RA"."PARTICIPANT_CODE"='PA1' AND "RA"."ACCOUNT_TYPE"='P1' AND
"RA"."ACCOUNT_VALUE"='111558888888')

Note
-----
- cardinality feedback used for this statement


30 rows selected.

SQL> select * from table(dbms_xplan.display_cursor('82wrsqhu9n7yw', null, 'ALLSTATS LAST'));

SQL_ID 82wrsqhu9n7yw, child number 2
-------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
-------------------------------------
SELECT * FROM ( SELECT ra.* FROM t_reg_account ra WHERE
ra.participant_code = 'PA1' AND ra.account_type = 'P1'
AND ra.account_value = '111558888888' ORDER BY
ra.account_sequence desc ) WHERE rownum = 1

Plan hash value: 1606291734

----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 |
|* 1 | COUNT STOPKEY | | 1 | | 1 |00:00:00.01 | 4 |
| 2 | VIEW | | 1 | 2 | 1 |00:00:00.01 | 4 |
| 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| T_REG_ACCOUNT | 1 | 12806 | 1 |00:00:00.01 | 4 |
|* 4 | INDEX RANGE SCAN DESCENDING | I_ACC_PK | 1 | 2 | 1 |00:00:00.01 | 3 |
----------------------------------------------------------------------------------------------------------------

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

1 - filter(ROWNUM=1)

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
4 - access("RA"."PARTICIPANT_CODE"='PA1' AND "RA"."ACCOUNT_TYPE"='P1' AND
"RA"."ACCOUNT_VALUE"='111558888888')

SQL_ID 82wrsqhu9n7yw, child number 3
-------------------------------------
SELECT * FROM ( SELECT ra.* FROM t_reg_account ra WHERE
ra.participant_code = 'PA1' AND ra.account_type = 'P1'
AND ra.account_value = '111558888888' ORDER BY
ra.account_sequence desc ) WHERE rownum = 1

Plan hash value: 1606291734

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 901 |
|* 1 | COUNT STOPKEY | | 1 | | 0 |00:00:00.01 | 901 |
| 2 | VIEW | | 1 | 1 | 0 |00:00:00.01 | 901 |
| 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| T_REG_ACCOUNT | 1 | 1 | 0 |00:00:00.01 | 901 |
|* 4 | INDEX RANGE SCAN DESCENDING | I_ACC_PK | 1 | 1 | 0 |00:00:00.01 | 901 |
----------------------------------------------------------------------------------------------------------------


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

1 - filter(ROWNUM=1)
4 - access("RA"."PARTICIPANT_CODE"='PA1' AND "RA"."ACCOUNT_TYPE"='P1' AND
"RA"."ACCOUNT_VALUE"='111558888888')

Note
-----
- cardinality feedback used for this statement


121 rows selected.

SQL> analyze index I_ACC_PK validate structure;

Index analyzed.

SQL> select * from index_stats;

HEIGHT BLOCKS NAME PARTITION_NAME LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN
---------- ---------- ------------------------------ ------------------------------ ---------- ---------- ----------- ----------
3 1024 I_ACC_PK 35621 899 1600589 8000

BR_ROWS BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE
---------- ---------- ----------- ---------- ----------- --------------- ------------- ----------------- ----------- ----------
898 6 33161 8032 35621 1600589 35621 1 7240192 1633750

PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ------------ -------------------- ---------- ------------ -------------- ----------------
23 1 4 0 0 3 56

and Connor said...

OK, here's a simple example


SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t ( x int not null, y char(100) not null);

Table created.

SQL>
SQL> insert /*+ APPEND */ into t
  2  select rownum , rownum
  3  from dual
  4  connect by level <= 200000;

200000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> create index IX on T ( x ) ;

Index created.

SQL>
SQL> exec dbms_stats.gather_table_stats('','T');

PL/SQL procedure successfully completed.

SQL>
SQL> select /*+ gather_plan_statistics */ *
  2  from
  3    ( select * from t order by x desc )
  4  where rownum = 1;

         X
----------
Y
----------------------------------------------------------------------------------------------------
    200000
200000


1 row selected.

SQL>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  f12j0s1jznzpz, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from   ( select * from t order
by x desc ) where rownum = 1

Plan hash value: 1635572796

------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |      1 |        |      1 |00:00:00.01 |       3 |
|*  1 |  COUNT STOPKEY                |      |      1 |        |      1 |00:00:00.01 |       3 |
|   2 |   VIEW                        |      |      1 |      1 |      1 |00:00:00.01 |       3 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T    |      1 |    200K|      1 |00:00:00.01 |       3 |
|   4 |     INDEX FULL SCAN DESCENDING| IX   |      1 |      1 |      1 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM=1)


22 rows selected.

SQL>
SQL>


You can see from the "buffers" column, I did very little work to get the data. Now think of that index being like a telephone book. All the entries are sorted from low to high. So when I ask for the "highest" one, I'm doing an "index descending" operation. It is like flipping the phone book over, and reading the pages from the *last* page until I find someone.

And obviously in the phone book, I'll find someone *immediately* on that last page (with a surname starting with "Z"). How many pages did I have to read? Just one. The last one.

Now I *delete* all rows from my table.

SQL> delete from t;

200000 rows deleted.

SQL> commit;

Commit complete.

SQL>
SQL> select /*+ gather_plan_statistics */ *
  2  from
  3    ( select * from t order by x desc )
  4  where rownum = 1;

no rows selected

SQL>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID  f12j0s1jznzpz, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from   ( select * from t order
by x desc ) where rownum = 1

Plan hash value: 1635572796

------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |      1 |        |      0 |00:00:00.01 |     446 |
|*  1 |  COUNT STOPKEY                |      |      1 |        |      0 |00:00:00.01 |     446 |
|   2 |   VIEW                        |      |      1 |      1 |      0 |00:00:00.01 |     446 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T    |      1 |    200K|      0 |00:00:00.01 |     446 |
|   4 |     INDEX FULL SCAN DESCENDING| IX   |      1 |      1 |      0 |00:00:00.01 |     446 |
------------------------------------------------------------------------------------------------


Now it takes 446 buffers. Let's go back to the telephone book. A delete is *not* the same as ripping out all of the pages. A delete is more like, going to each page, and erasing all of the names on each page.

So at the end of the delete, I *still* have lots of pages in my telephone book, they are all just blank.

Now I repeat my index descending operation. I start at the last page, looking for a name. No names there (because I deleted them all). So I flip back one page, and look on the next-to-last page. No names there. And so on. In fact, I'll walk through *every* page in the phone book, looking for names.

We've kept those (blank) pages because what do databases do most ? They *insert* data. So when we start adding rows back into the table, we'll reuse those pages for the new data.

But what if we are *never* planning on re-using that data? What if the phone book has shrunk because everyone has moved out of town? We can tell the database to go ahead and reclaim that space:

SQL> alter index IX coalesce;

Index altered.

SQL>
SQL> select /*+ gather_plan_statistics */ *
  2  from
  3    ( select * from t order by x desc )
  4  where rownum = 1;

no rows selected

SQL>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID  f12j0s1jznzpz, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from   ( select * from t order
by x desc ) where rownum = 1

Plan hash value: 1635572796

------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |      1 |        |      0 |00:00:00.01 |       2 |
|*  1 |  COUNT STOPKEY                |      |      1 |        |      0 |00:00:00.01 |       2 |
|   2 |   VIEW                        |      |      1 |      1 |      0 |00:00:00.01 |       2 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T    |      1 |    200K|      0 |00:00:00.01 |       2 |
|   4 |     INDEX FULL SCAN DESCENDING| IX   |      1 |      1 |      0 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------------------


and as you can see...the phone book (ie our index) is now small, and the cost of scanning it becomes small again.

Rating

  (3 ratings)

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

Comments

Vladimir Minacov, August 23, 2016 - 8:46 am UTC


Ravi B, September 02, 2016 - 10:45 pm UTC

Estimate rows still shows 200k after coalesce'ing the index?

TABLE ACCESS BY INDEX ROWID| T | 1 | 200K

I ran gather_table_stats but still E-rows shows 200k.

exec dbms_stats.gather_table_stats('','T');

It goes away after i use "method_opt=>'for all indexes for all indexed columns size auto'? with gather_table_stats.

Do you recommend gathering index stats along with table stats all the time or there are any exceptions?

Thanks!

To Ravi

Rajeshwaran Jeyabal, September 05, 2016 - 8:05 am UTC

I ran gather_table_stats but still E-rows shows 200k.
exec dbms_stats.gather_table_stats('','T');
It goes away after i use "method_opt=>'for all indexes for all indexed columns size auto'? with gather_table_stats.
Do you recommend gathering index stats along with table stats all the time or there are any exceptions?

The E-rows is due to reusing the existing plans from shared pool, by using "no_invalidate" set to False we can fix this incorrect card.

BTW - "method_opt" is used to create histograms, and it is automated since 10g with the introduction of "sys.col_usage$" - which means if you know which column need Histogram then explicitly define those "method_opt" during stats gathering process, else let it to default, then the database will do that job of us.

demo@ORA11G>
demo@ORA11G> create table t (x int not null, y char(100) not null);

Table created.

demo@ORA11G>
demo@ORA11G> insert into t(x,y)
  2  select level, 'x'
  3  from dual
  4  connect by level <=100000;

100000 rows created.

demo@ORA11G> create index t_idx on t(x);

Index created.

demo@ORA11G> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

demo@ORA11G> set serveroutput off
demo@ORA11G> select /*+ gather_plan_statistics */ *
  2  from ( select * from t order by x desc)
  3  where rownum =1;

         X Y
---------- ----------------------------------------------------------------------------------------------------
    100000 x

1 row selected.

demo@ORA11G>
demo@ORA11G> select * from table(dbms_xplan.display_cursor(format=>'IOstats last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
SQL_ID  czr90s303a1v2, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from ( select * from t order by
x desc) where rownum =1

Plan hash value: 3806458071

-------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |      1 |        |      1 |00:00:00.01 |       3 |
|*  1 |  COUNT STOPKEY                |       |      1 |        |      1 |00:00:00.01 |       3 |
|   2 |   VIEW                        |       |      1 |      1 |      1 |00:00:00.01 |       3 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T     |      1 |    100K|      1 |00:00:00.01 |       3 |
|   4 |     INDEX FULL SCAN DESCENDING| T_IDX |      1 |      1 |      1 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM=1)


22 rows selected.

demo@ORA11G> delete from t ;

100000 rows deleted.

demo@ORA11G> commit;

Commit complete.

demo@ORA11G>
demo@ORA11G> select /*+ gather_plan_statistics */ *
  2  from ( select * from t order by x desc)
  3  where rownum =1;

no rows selected

demo@ORA11G>
demo@ORA11G> select * from table(dbms_xplan.display_cursor(format=>'IOstats last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
SQL_ID  czr90s303a1v2, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from ( select * from t order by
x desc) where rownum =1

Plan hash value: 3806458071

-------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |      1 |        |      0 |00:00:00.01 |     282 |
|*  1 |  COUNT STOPKEY                |       |      1 |        |      0 |00:00:00.01 |     282 |
|   2 |   VIEW                        |       |      1 |      1 |      0 |00:00:00.01 |     282 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T     |      1 |    100K|      0 |00:00:00.01 |     282 |
|   4 |     INDEX FULL SCAN DESCENDING| T_IDX |      1 |      1 |      0 |00:00:00.01 |     282 |
-------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM=1)


22 rows selected.

demo@ORA11G>
demo@ORA11G>
demo@ORA11G>
demo@ORA11G> alter index t_idx coalesce;

Index altered.

demo@ORA11G>
demo@ORA11G> select /*+ gather_plan_statistics */ *
  2  from ( select * from t order by x desc)
  3  where rownum =1;

no rows selected

demo@ORA11G>
demo@ORA11G> select * from table(dbms_xplan.display_cursor(format=>'IOstats last'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
SQL_ID  czr90s303a1v2, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from ( select * from t order by
x desc) where rownum =1

Plan hash value: 3806458071

-------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |      1 |        |      0 |00:00:00.01 |       2 |
|*  1 |  COUNT STOPKEY                |       |      1 |        |      0 |00:00:00.01 |       2 |
|   2 |   VIEW                        |       |      1 |      1 |      0 |00:00:00.01 |       2 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T     |      1 |    100K|      0 |00:00:00.01 |       2 |
|   4 |     INDEX FULL SCAN DESCENDING| T_IDX |      1 |      1 |      0 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM=1)


22 rows selected.

demo@ORA11G> select column_name, histogram
  2  from user_tab_col_statistics
  3  where table_name ='T'
  4  and column_name='X';

COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
X                              NONE

1 row selected.

demo@ORA11G> exec dbms_stats.gather_table_stats(user,'T',no_invalidate=>false);

PL/SQL procedure successfully completed.

demo@ORA11G> select column_name, histogram
  2  from user_tab_col_statistics
  3  where table_name ='T'
  4  and column_name='X';

COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
X                              NONE

1 row selected.

demo@ORA11G> select /*+ gather_plan_statistics */ *
  2  from ( select * from t order by x desc)
  3  where rownum =1;

no rows selected

demo@ORA11G>
demo@ORA11G> select * from table(dbms_xplan.display_cursor(format=>'IOstats last'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
SQL_ID  czr90s303a1v2, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from ( select * from t order by
x desc) where rownum =1

Plan hash value: 3806458071

-------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |      1 |        |      0 |00:00:00.01 |       2 |
|*  1 |  COUNT STOPKEY                |       |      1 |        |      0 |00:00:00.01 |       2 |
|   2 |   VIEW                        |       |      1 |      1 |      0 |00:00:00.01 |       2 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T     |      1 |      1 |      0 |00:00:00.01 |       2 |
|   4 |     INDEX FULL SCAN DESCENDING| T_IDX |      1 |      1 |      0 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM=1)


22 rows selected.

demo@ORA11G>

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library