Skip to Main Content

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Connor McDonald

Thanks for the question, Anup.

Asked: January 26, 2017 - 9:26 pm UTC

Last updated: July 03, 2018 - 12:35 am UTC

Version: 12.1.0.2

Viewed 10K+ times! This question is

You Asked

Hi
With the advent of In-Memory capabilities in 12C is there a way to make the optimizer ignore indexes for particular table for a session.
The reason being if we want to use Oracle 12C as HTAP (Hybrid Transactional Analytical Processing) we would like to use the same tables for OLTP and for Analysis using In-Mem Columnar store .
We want to develop new Analytical functionality on top of existing OLTP application using the same tables but pinning the table to In-Mem Store. The existing application also have some analytical indexes which are used for some other processes.
To get benefit from In-Mem Columnar Store we would need optimizer to go for full scan . Now, if in the new functionality we make the indexes invisible the existing processes would be affected. So to make sure that the only the new functionality is affected by the use of In-Mem is there a way to make the Indexes invisible for optimizer for a session ?

Thanks
Anup

and Connor said...

You should not have to do that - that is one of the benefits of the in-memory feature - the optimizer is aware of it.

So when deciding on plans, it will choose between (for example) an index scan into the buffer cache (row style), or a scan of the in-memory area (column style).

Nice blog on the topic here

https://blogs.oracle.com/In-Memory/entry/oracle_database_in_memory_the

If you have particular statements that you want explicit control over, then you use can hints, eg

https://blogs.oracle.com/optimizer/entry/controlling_access_to_the_in


Rating

  (1 rating)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

Invisible indexes supporting constraints

Rajeshwaran, Jeyabal, July 02, 2018 - 12:58 pm UTC

Team,

Was reading this content from this blog post https://connor-mcdonald.com/2016/09/06/partitioning-an-existing-index

one question: with the system generated unique index on PK, does the database drop that index while modifiying the constraint?
in the below demo, during the modify constraint -does the index T_PK is getting dropped? any reason - why it is not available after the modify constraint statement?
demo@ORA12C> create table t
  2  as
  3  select *
  4  from all_objects;

Table created.

demo@ORA12C> alter table t add constraint t_pk
  2  primary key(object_id);

Table altered.

demo@ORA12C> select index_name
  2  from user_indexes
  3  where table_name ='T';

INDEX_NAME
----------
T_PK

demo@ORA12C> create unique index t_idx2 on t(object_id)
  2  global partition by hash(object_id)
  3  ( partition p1,
  4    partition p2 )
  5  online invisible;

Index created.

demo@ORA12C> select index_name
  2  from user_indexes
  3  where table_name ='T';

INDEX_NAME
----------
T_PK
T_IDX2

demo@ORA12C> alter table t modify constraint t_pk using index t_idx2;

Table altered.

demo@ORA12C> select index_name
  2  from user_indexes
  3  where table_name ='T';

INDEX_NAME
----------
T_IDX2

demo@ORA12C> alter index t_pk invisible;
alter index t_pk invisible
*
ERROR at line 1:
ORA-01418: specified index does not exist


demo@ORA12C> alter index t_idx2 visible;

Index altered.

demo@ORA12C> drop index t_pk;
drop index t_pk
           *
ERROR at line 1:
ORA-01418: specified index does not exist


demo@ORA12C>


Connor McDonald
July 03, 2018 - 12:35 am UTC

When you did this:

alter table t add constraint t_pk primary key(object_id);

you (in effect) never asked for an index to be created, you simply asked for a constraint. So in that circumstance, the index is "ours" not "yours". So when you modified the contraint to use the other index, the original index (which *you* never created anyway) is no longer required to implement the constraint.
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

Performance

Get all the information about database performance in the Database Performance guide.