Skip to Main Content
  • Questions

  • Index is not being used by the table

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Kumar.

Asked: September 24, 2011 - 4:18 am UTC

Last updated: September 29, 2011 - 7:49 am UTC

Version: 10.2.0.3.0

Viewed 1000+ times

You Asked

Hello Tom,

This is Kumar working in a MNC. In out daily routine we will get some issues relating to the application and Database as well.


My current problem is:

We use Indexed tables and well tuned queries in our application. But in some part of day or night, Some of the tables were not making use of the indexes which were created on the tables. So while selecting a row from a table we are getting an unexpected results from the tables as if the index is not created on it.

NOTE: This Problem is being faced only in some part of Day or Night. But in remaining timings, It is giving the Normal expected results and index is working fine.

Does any other processing in the Database effect the usage of indexes?

Please suggest a solution to our problem.

Thanks in Advance.



Thank you very much for the response.

We have three databases Day(With actual data),Backup(with backup) and Night(with no data and will be inserted only in night and will be copied into Day database later) with 2 servers which will be working on 2 instances of the single Database. We will be switching the regions from Day to night during which the my application will be in sink with Night Database and Night database pointing to the Day database for information. Later we will perform Database Switch during which the Night database will be pointing to the Backup database and Day database will be going under Processing. After the Completion of the Processing then again we will perform the Database switch. Now Night Database will be pointing to Day.Later region will be completely switched to day.

My problem in database is getting started between first region switch and first Database switch. It is working fine during remaining timings.

The table in the database is given as follows.

create table records(system_no char(3),cust_id char(10),rec_no char(4),start_date char(8),end_date char(9),--------);

create index recpk on records(system_no,cust_id,rec_no);

The field rec_no contains the values in descending order from 9999 to 0001 which is a part of the primary key as given above

"Unexpected result" in the sense , While I use the following query

select /*+ INDEX_ASC(records recpk) */ * from records where ((system_id='123' and cust_id='3456218791') and rec_no>'0000' ) and rownum<2 order by system_id asc,cust_id asc,rec_no asc;

The above query should retrieve the minimum rec_no from the "records" table basing on the Index.

But it is retrieving maximum rec_no from the table with out using index.


Note:- The query is working fine in one instance1 and giving wrong info in other instance2 and vice verse.

I am not able to figure the problem, why is the database not using index only in such particular situation. Should I alter any Parameters during that Time.

Please suggest a Solution.

Thanks in Advance.

and Tom said...

this query is wronger than wrong:

select /*+ INDEX_ASC(records recpk) */ * from records where ((system_id='123' and cust_id='3456218791') and rec_no>'0000' ) and rownum<2 order by system_id asc,cust_id asc,rec_no asc;

it is giving CORRECT information on all nodes because what is says to do is:

find one random records that match the predicate and then sort it.

You want to get the data matching your predicate, sort them and then get the first two.


IT HAS TO BE:

select * from (
select /*+ first_rows(2) */ * from records where ((system_id='123' and cust_id='3456218791') and rec_no>'0000' )
order by system_id asc,cust_id asc,rec_no asc)
where rownum < 2;


that is the ONLY way to write that query semantically correctly.


Use that and only that query. Find the developer that wrote the bad query and educate them on how rownum works. This is called a top-n query and can only be written using rownum with an inline view. There are no shortcuts, you have to use the inline view to do a top-n query - period.

http://docs.oracle.com/cd/E11882_01/server.112/e17118/pseudocolumns009.htm#SQLRF00255



You cannot rely on a hint being observed - they are called HINTS, they are not directives. If the index is unavailable for any reason (different name, unusable, whatever), then the query will just ignore it. If it were a directive, the query would fail, but it won't. You have to code it the way I've demonstrated and I would recommend not using the INDEX_ASC hint.


ugh, and you've used strings to store all kinds of numbers :(

Rating

  (5 ratings)

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

Comments

moving all records from production database to development database

Subhranshu Swain, September 26, 2011 - 7:09 am UTC

How to copy all the datas from two different environment.
Insert..Select query and Create table as Select does not work due to two different environment.Thanks
Tom Kyte
September 26, 2011 - 7:40 am UTC

if insert ... select works, then create as select would work as well. I do not understand your comment about it not working.

However, that said - I would prefer to just restore a backup - it'll be the fastest approach (and it'll test your ability to recover which is always a good thing as well!)

Harsh Maheshwari, September 28, 2011 - 12:24 am UTC

Hi TOM,
This side Harsh Maheshwari working for a MNC.
I have table name employee which have column a,b,c,d,e and i had created a index on this EMP_INDX( a,b,c,d,e) so that column-e has postion 5th. now i am running a select query and i want for that specific query column-e position should change to 1st.( or can i change column position in ina index?).
Could you please give me your suggestion on this?
Thanks
Harsh
Tom Kyte
September 28, 2011 - 5:39 am UTC

You would have to create an entirely new index, that is all. You will want to check to see if that allows you to drop the old index (can this new index be used instead of the old index), but that is all - just create a new one.

But how about this (on 11.2.0.1)

hoek, September 28, 2011 - 1:08 pm UTC

From OTN, but I guess (more or less) related to the original poster here:
https://forums.oracle.com/forums/thread.jspa?threadID=2291134
My latest testcase:
select banner from v$version where rownum=1;
exec dbms_stats.delete_table_stats(user,'TEST_INDEX_COUNT6');
drop table test_index_count6 purge;
create table test_index_count6 (a number);
insert into test_index_count6 
select 1
from   dual
connect by level <= 1000000;
insert into test_index_count6
select 2
from   dual
connect by level <= 2;
commit;
create index ind_a_tic6 on TEST_INDEX_COUNT6(a);
exec dbms_stats.gather_table_stats(user,tabname=>'TEST_INDEX_COUNT6',cascade=>true);
-- selecting a=2:
explain plan for select * from test_index_count6 where a=2;
select * from table(dbms_xplan.display);
-- counting a=2:
explain plan for select count(*) from test_index_count6 where a=2;
select * from table(dbms_xplan.display);
-- counting a=1:
explain plan for select count(*) from test_index_count6 where a=1;
select * from table(dbms_xplan.display);
--
-- start all over using display_cursor:
--
set serveroutput off
exec dbms_stats.delete_table_stats(user,'TEST_INDEX_COUNT6');
drop table test_index_count6 purge;
create table test_index_count6 (a number);
insert into test_index_count6 
select 1
from   dual
connect by level <= 1000000;
insert into test_index_count6
select 2
from   dual
connect by level <= 2;
commit;
create index ind_a_tic6 on TEST_INDEX_COUNT6(a);
exec dbms_stats.gather_table_stats(user,tabname=>'TEST_INDEX_COUNT6',cascade=>true);
-- selecting a=2:
select /*+ gather_plan_statistics */ * from test_index_count6 where a=2;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
-- counting a=2:
select /*+ gather_plan_statistics */ count(*) from test_index_count6 where a=2;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
-- counting a=1:
select /*+ gather_plan_statistics */ count(*) from test_index_count6 where a=1;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));


No matter what I try, I keep getting full table scans, except for the (very first) time I ran the example from the thread and posted the outcome over there.
During the day I reconnected several times, laptop has been hibernating as well, and I created a new testcase.
In short: I know that not all indexes are good, and that not all full table scans are evil.
But full scanning only to get 2 rows out of a million?
Is my testcase flawed or am I missing something else or...?

Wish I could ask some collegues as well, but they already went to OOW, one day I'll be there as well ;-)

Regards,
Martijn
Tom Kyte
September 28, 2011 - 2:13 pm UTC

it is because of the stats (or rather, the LACK of stats :) )


When I ran it - your dbms_stats.gather_stats commands failed due to your word wrapping (the exec had a line feed after it). So, in all cases, we used dynamic sampling. Without stats gathered, you would see:


ops$tkyte%ORA11GR2> -- counting a=2:
ops$tkyte%ORA11GR2> select /*+ gather_plan_statistics */ count(*) from test_index_count6 where a=2;

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

ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9pqb4xj77s7zx, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from test_index_count6
where a=2

Plan hash value: 1917981987

------------------------------------------------------------------------------------------
| Id  | Operation         | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |      1 |        |      1 |00:00:00.01 |       3 |
|   1 |  SORT AGGREGATE   |            |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  2 |   INDEX RANGE SCAN| IND_A_TIC6 |      1 |      2 |      2 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------

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

   2 - access("A"=2)

Note
-----
   - dynamic sampling used for this statement (level=2)


24 rows selected.

ops$tkyte%ORA11GR2> -- counting a=1:
ops$tkyte%ORA11GR2> select /*+ gather_plan_statistics */ count(*) from test_index_count6 where a=1;

  COUNT(*)
----------
   1000000

ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  33uf1whr4a6dy, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from test_index_count6
where a=1

Plan hash value: 3219185018

-----------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |      1 |        |      1 |00:00:00.13 |    1591 |    579 |
|   1 |  SORT AGGREGATE    |                   |      1 |      1 |      1 |00:00:00.13 |    1591 |    579 |
|*  2 |   TABLE ACCESS FULL| TEST_INDEX_COUNT6 |      1 |    836K|   1000K|00:00:00.53 |    1591 |    579 |
-----------------------------------------------------------------------------------------------------------

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

   2 - filter("A"=1)

Note
-----
   - dynamic sampling used for this statement (level=2)


24 rows selected.



which is exactly what you expect (i assume). However, when I "fix" your script, I see:

ops$tkyte%ORA11GR2> -- counting a=2:
ops$tkyte%ORA11GR2> select /*+ gather_plan_statistics */ count(*) from test_index_count6 where a=2;

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

ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9pqb4xj77s7zx, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from test_index_count6
where a=2

Plan hash value: 3219185018

-----------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |      1 |        |      1 |00:00:00.04 |    1588 |   1582 |
|   1 |  SORT AGGREGATE    |                   |      1 |      1 |      1 |00:00:00.04 |    1588 |   1582 |
|*  2 |   TABLE ACCESS FULL| TEST_INDEX_COUNT6 |      1 |    500K|      2 |00:00:00.04 |    1588 |   1582 |
-----------------------------------------------------------------------------------------------------------

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

   2 - filter("A"=2)


20 rows selected.

ops$tkyte%ORA11GR2> -- counting a=1:
ops$tkyte%ORA11GR2> select /*+ gather_plan_statistics */ count(*) from test_index_count6 where a=1;

  COUNT(*)
----------
   1000000

ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  33uf1whr4a6dy, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from test_index_count6
where a=1

Plan hash value: 3219185018

-----------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |      1 |        |      1 |00:00:00.11 |    1588 |   1582 |
|   1 |  SORT AGGREGATE    |                   |      1 |      1 |      1 |00:00:00.11 |    1588 |   1582 |
|*  2 |   TABLE ACCESS FULL| TEST_INDEX_COUNT6 |      1 |    500K|   1000K|00:00:00.53 |    1588 |   1582 |
-----------------------------------------------------------------------------------------------------------

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

   2 - filter("A"=1)


20 rows selected.



Now, why does it do that with stats gathered?

Because by default, the method-opt => auto and auto will not gather histograms unless it has some reason to believe it needs to.

If we did this *right after* you ran your testcase:




ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats(user,estimate_percent => 100, method_opt=> 'for columns a size 254', tabname=>'TEST_INDEX_COUNT6' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> -- selecting a=2:
ops$tkyte%ORA11GR2> select /*+ gather_plan_statistics */ * from test_index_count6 ensure_hard_parsex where a=2;

         A
----------
         2
         2

ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  4278v0zpuwuq3, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from test_index_count6
ensure_hard_parsex where a=2

Plan hash value: 1048529912

--------------------------------------------------------------------------------------------------
| Id  | Operation        | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |      1 |        |      2 |00:00:00.01 |       4 |      1 |
|*  1 |  INDEX RANGE SCAN| IND_A_TIC6 |      1 |      2 |      2 |00:00:00.01 |       4 |      1 |
--------------------------------------------------------------------------------------------------

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

   1 - access("A"=2)


19 rows selected.

ops$tkyte%ORA11GR2> -- counting a=2:
ops$tkyte%ORA11GR2> select /*+ gather_plan_statistics */ count(*) from test_index_count6 ensure_hard_parsex where a=2;

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

ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  baz0t5tfxpnxs, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from test_index_count6
ensure_hard_parsex where a=2

Plan hash value: 1917981987

------------------------------------------------------------------------------------------
| Id  | Operation         | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |      1 |        |      1 |00:00:00.01 |       3 |
|   1 |  SORT AGGREGATE   |            |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  2 |   INDEX RANGE SCAN| IND_A_TIC6 |      1 |      2 |      2 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------

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

   2 - access("A"=2)


20 rows selected.

ops$tkyte%ORA11GR2> -- counting a=1:
ops$tkyte%ORA11GR2> select /*+ gather_plan_statistics */ count(*) from test_index_count6 ensure_hard_parsex where a=1;

  COUNT(*)
----------
   1000000

ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  c2v5b5tbbup5s, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from test_index_count6
ensure_hard_parsex where a=1

Plan hash value: 3219185018

-----------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |      1 |        |      1 |00:00:00.12 |    1588 |   1582 |
|   1 |  SORT AGGREGATE    |                   |      1 |      1 |      1 |00:00:00.12 |    1588 |   1582 |
|*  2 |   TABLE ACCESS FULL| TEST_INDEX_COUNT6 |      1 |   1000K|   1000K|00:00:00.63 |    1588 |   1582 |
-----------------------------------------------------------------------------------------------------------

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

   2 - filter("A"=1)


20 rows selected.



you'd get the 'right' statistics. In fact, you probably don't even need the method_opt, just the running of the queries should be enough (but might not be in this truly massively skewed example - we might not sample 2 at all - it might not get picked up)

See also

http://www.oracle.com/technetwork/issue-archive/2006/06-may/o36asktom-099107.html

"Why Does My Plan Change?"


Great explanation

hoek, September 29, 2011 - 5:29 am UTC

Dang, should have known about method_opt...brings back some 9i related memories.

Thanks a lot for your time and the examples.

¿ensure_hard_parsex? Never seen that before and apparently Google neither.

(and how did you spot the line feed btw? can't see it here..probably a windoze thing then?)

Regards,
Martijn


Tom Kyte
September 29, 2011 - 7:49 am UTC

ensure_hard_parsex could have been replaced with "x" or any other identifier, it is just a correlation name.

I used it to ensure a hard parse would happen which would use the new statistics. Statistics invalidate cursors in a lazy fashion (10g and above) and therefore, just gathering statistics and running the identical query again MIGHT not hard parse - which would still use the old plan. It might take a few seconds or minutes for the plans to change after a stats gather - for the 'demo' I needed to avoid that.

It was probably the size window I was pasting into:


exec dbms_stats.gather_table_stats(user,tabname=>'TEST_INDEX_COUNT6',cascade=>true);


was very long - and the dbms_stats call has no spaces, so the word wrapping in my window must have been the culprit.

LOL

hoek, September 29, 2011 - 6:24 am UTC

"¿ensure_hard_parsex? Never seen that before and apparently Google neither."

omg..please forget about that one...
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

Hints

Need more information on hints? Check out the complete hint descriptions