OraFAQ Forum: Performance Tuning » INVISIBLE INDEX-11g

Home » RDBMS Server » Performance Tuning » INVISIBLE INDEX-11g

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

INVISIBLE INDEX-11g [message #467041]

Thu, 22 July 2010 02:16

akilabaskaran
Messages: 29
Registered: May 2007
Location: chennai

Junior Member

Hi all,

I have been testing the invisible index with 11g

below is the script used

create table test_invisible (id number, descr varchar2(10));

create index idx_id_invisible on test_invisible(id) invisible;

insert into test_invisible select intproductid,nvclabel from product_tbl  where rownum <=100

select * from test_invisible where id >104781

the explain plan is 

SELECT STATEMENT, GOAL = ALL_ROWS
 TABLE ACCESS FULL		TEST_INVISIBLE	


and i use the index hint as 

select /*+  index (test_invisible idx_id_invisible) */  id  from test_invisible where id = 104781;

then also the optimiser uses the full table scan

i then change the OPTIMIZER_USE_INVISIBLE_INDEXES parameter to true using

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = true;

even then the optimiser uses the full table scan

kindly clarify why i am not able to use the index even after the hint.
is there any way of including index for my sql ?

my version of oracle is Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

Report message to a moderator

Re: INVISIBLE INDEX-11g [message #467047 is a reply to message #467041]

Thu, 22 July 2010 02:29

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

1/ No statistics gathered
2/ Too few rows.

=> Insert more rows (at least 10000) and gather statistics.

Regards
Michel

Report message to a moderator

Re: INVISIBLE INDEX-11g [message #467051 is a reply to message #467047]

Thu, 22 July 2010 02:38

akilabaskaran
Messages: 29
Registered: May 2007
Location: chennai

Junior Member

hi,

i have inserted now 100000 rows and have gathered index statistics by dropping and recreating the index as below

insert into test_invisible select intproductid,nvclabel from play.product_tbl  where rownum <=100000

create index idx_id_invisible on test_invisible(id) invisible compute statistics;

even now no luck
still using FTS

Report message to a moderator

Re: INVISIBLE INDEX-11g [message #467061 is a reply to message #467051]

Thu, 22 July 2010 03:02

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQB> create table test_invisible (id number, descr varchar2(128));

Table created.

SQB>  create index idx_id_invisible on test_invisible(id) invisible;

Index created.

SQB> insert into test_invisible select object_id, object_name from dba_objects;

11691 rows created.

SQB> commit;

Commit complete.

SQB> ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = true;

Session altered.

SQB> exec dbms_stats.gather_table_stats (user,'TEST_INVISIBLE', cascade => true, estimate_percent => null);

PL/SQL procedure successfully completed.

SQB> set autotrace traceonly explain
SQB> select * from test_invisible where id = 104781;

Execution Plan
----------------------------------------------------------
Plan hash value: 3049948720

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    22 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_INVISIBLE   |     1 |    22 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_ID_INVISIBLE |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   2 - access("ID"=104781)

SQB> select * from test_invisible where id >104781
  2  /

Execution Plan
----------------------------------------------------------
Plan hash value: 3049948720

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

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

   2 - access("ID">104781)

SQB> @v

Version Oracle : 11.1.0.6.0

It works for me.
Post your session as I did.

Regards
Michel

Report message to a moderator

Re: INVISIBLE INDEX-11g [message #467074 is a reply to message #467061]

Thu, 22 July 2010 03:59

akilabaskaran
Messages: 29
Registered: May 2007
Location: chennai

Junior Member

Hi kindly find my session as below

SQL> create table test_invisible (id number, descr varchar2(128));
 
Table created
 
SQL> create index idx_id_invisible on test_invisible(id) invisible;
 
Index created
 
SQL> insert into test_invisible select object_id, object_name from dba_objects;
 
76602 rows inserted
 
SQL> commit;
 
Commit complete
 
SQL> ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = true;
 
Session altered
 
SQL> exec dbms_stats.gather_table_stats (user,'TEST_INVISIBLE', cascade => true, estimate_percent => null);
 
PL/SQL procedure successfully completed

explain plan for select * from test_invisible where id = 104781;

PLAN_ID	OPERATION	OBJECT_NAME	ROWS	BYTES	CPU_COST	TIME
164	SELECT STATEMENT		1	28	15463	1
164	TABLE ACCESS	TEST_INVISIBLE	1	28	15463	1
164	INDEX	IDX_ID_INVISIBLE	1		8171	1


explain plan for select * from test_invisible where id > 104781;

PLAN_ID	OPERATION	OBJECT_NAME	ROWS	BYTES	CPU_COST	TIME
165	SELECT STATEMENT		1	28	21734	1
165	TABLE ACCESS	TEST_INVISIBLE	1	28	21734	1
165	INDEX	IDX_ID_INVISIBLE	1		14443	1

yeah it is using the index.. i can see it from the plan table.
Thanks a lot.

Since i do not have auto trace facility granted to my username, i used explain plan window in the plsql developer and it is still showing FTS.

But even then the execution plan seems to use the index.

I modified the OPTIMIZER_USE_INVISIBLE_INDEXES value to false and used the hint to see if the index is being used.

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = false;

explain plan for select /*+  index (test_invisible idx_id_invisible) */  id  from test_invisible where id > 104781;

the explain plan is 

PLAN_ID	OPERATION	OBJECT_NAME	ROWS	BYTES	CPU_COST	TIME
171	SELECT STATEMENT		1	5	17955333	1
171	TABLE ACCESS	TEST_INVISIBLE	1	5	17955333	1

i saw some articles mentioning that when the OPTIMIZER_USE_INVISIBLE_INDEXES is set to false, then the index can be used by including the hint in the sql.
but in my case, the index is not used.
kindly provide suggestions for this scenario.

Thanks Micheal.

Report message to a moderator

Re: INVISIBLE INDEX-11g [message #467120 is a reply to message #467074]

Thu, 22 July 2010 05:07

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

I modified the OPTIMIZER_USE_INVISIBLE_INDEXES value to false and used the hint to see if the index is being used.

I see nothing like this in your post and this is not my experience:

SQB> explain plan for select /*+  index (test_invisible idx_id_invisible) */  id  from test_invisible where id > 104781;

Explained.

SQB> 
SQB> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3139657276

------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |     1 |     5 |    13   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_INVISIBLE |     1 |     5 |    13   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   1 - filter("ID">104781)

13 rows selected.

Regards
Michel

Report message to a moderator

Re: INVISIBLE INDEX-11g [message #467135 is a reply to message #467120]

Thu, 22 July 2010 05:17

akilabaskaran
Messages: 29
Registered: May 2007
Location: chennai

Junior Member

hi micheal,

i found an article by burleson in

http://www.dba-oracle.com/t_11g_new_index_features.htm

in that he says that

'query can be modified to explicitly use the invisible index with a hint'

but in our cases the optimiser does not seem to use the index.

Kindly explain whether i have understood things wrongly.

Report message to a moderator

Re: INVISIBLE INDEX-11g [message #467137 is a reply to message #467135]

Thu, 22 July 2010 05:22

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Remove this site from your mind. It is bullshit and this simple example proves it.
Read Oracle documentation instead.

Regards
Michel

Report message to a moderator

Re: INVISIBLE INDEX-11g [message #467139 is a reply to message #467041]

Thu, 22 July 2010 05:27

rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.

Senior Member

Report message to a moderator

Re: INVISIBLE INDEX-11g [message #467140 is a reply to message #467137]

Thu, 22 July 2010 05:27

akilabaskaran
Messages: 29
Registered: May 2007
Location: chennai

Junior Member

Michel,

So there no ways of explicitly using an invisible index.
we can change the OPTIMIZER_USE_INVISIBLE_INDEXES parameter to TRUE to use the invisble index.

Thanks for the clarification.

Report message to a moderator

Previous Topic:	query writing
Next Topic:	index monitoring

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 04 03:08:50 CDT 2024