CODEWITHSUNDEEP
oracle-databaseindexingvirtualexplain
odysseus
odysseus

Reputation: 15

Simulate Oracle Index Without Creating It

Is there a way for me to test a new index (i.e. in memory?) without actually creating it yet? I would like to test it out and see if the Explain Plan is better before I hand off the index creation to the DBA.

My database is Oracle 12c.

Upvotes: 1

Views: 871

Answers (2)

devnull
devnull

Reputation: 580

Plain answer: NO. Invisible indexes still need to be created on storage Location - not Memory, Create a subset of your data in the same way it is stored - i.e. same tablespace, storage Location etc. to reduce time for creating it. Unfortunatly this is not a 100% solution. Ask your DBA if there are times where DB not used heavily and create the index during that slot.

Upvotes: 1

Marmite Bomber
Marmite Bomber

Reputation: 21075

Starting with 11g Oracle prepared exact for this reason inivisible indexes - the basic idea is simple.

You created a new indes as inivisible. i.e. no other session will see it and will not get possible negative sideffects (note that contrary to a popular belief more indixes means better performance - a new index can ruin the performence of some queries).

So only session that sets OPTIMIZER_USE_INVISIBLE_INDEXES can use and test the invisible index. Only after you are sure there are no negative effect, you can ALTER the index as visible.

See here for more details

Upvotes: 2

Related Questions