Skip to Main Content
  • Questions

  • Taking More Indexing Time on ORACLE database While Performing QUERYS

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, balaji.

Asked: March 11, 2019 - 9:50 am UTC

Last updated: March 18, 2019 - 2:38 pm UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Hi Team,

Very impressive for your way of explaining.

So we have ORACLE db production server,from that we are trying to perform indexing with 40 Querys on every day.For Example i am showing few Querys::

DROP INDEX GINQCON_ix1 FORCE;

 CREATE INDEX GINQCON_ix1 ON GINQCON_GINRecord (XMLContent) INDEXTYPE IS CTXSYS.CONTEXT parameters ('SYNC (ON COMMIT) LEXER GINQCON_LEXER STOPLIST GINQCON_STLIST');

 DROP INDEX GINQCON_ix2 FORCE;

 CREATE INDEX GINQCON_ix2 ON GINQCON_GINRecord (ID) INDEXTYPE IS CTXSYS.CONTEXT parameters ('SYNC (ON COMMIT) LEXER GINQCON_LEXER STOPLIST GINQCON_STLIST');


Here the Total execution time taking with all DROP and CREATE Querys nearly 420 seconds..

But if we are doing same thing on by Weekend days(Saturaday, Sunday), it is nearly going 900 seconds...

We don't know what exactly happening on database on Weekend days. And we checked there is no parallel jobs running on db server....


May be your suggestion was very helpful to us....

and Chris said...

Hang on...

Are you dropping and re-creating the indexes every day?!

If so, my suggestion is:

DON'T!

That'll save you 420+ seconds.

Rating

  (2 ratings)

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

Comments

table of conte[n|x]t

Racer I., March 15, 2019 - 6:20 am UTC

Hi,

Caveat : I know nothing about Oracle text.

We have in all our praxis found only one use case for (regular) index rebuilt, when we implemented our own AQ framework. Now we have an enhancements request pending that would make even that unnecessary.

But Oracle itself seems to not be averse to some optimizing on text indexes (OPTIMIZE_INDEX) :
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/ccref/CTX_DDL-package.html#GUID-28E079B1-D5CA-4264-B1C0-A1C5CE174C55
At a rate of once a month or so. Or selectively on frequently searched tokens if those querys show unacceptable slowdown, not pro-actively.

https://stackoverflow.com/questions/1916244/how-to-sync-and-optimize-an-oracle-text-index

Here someone mentions :

Putting this here as an update for Oracle 12C users. If you use the index in real time mode, then it keeps items in memory, and periodicially pushes to the main tables, which keeps fragmentation down and enables NRT search on streaming content. Here's how to set it up

exec ctx_ddl.drop_preference ( 'your_tablespace' );
exec ctx_ddl.create_preference( 'your_tablespace', 'BASIC_STORAGE' );
exec ctx_ddl.set_attribute ( 'your_tablespace', 'STAGE_ITAB', 'true' );
create index some_text_idx on your_table(text_col) indextype is ctxsys.context PARAMETERS ('storage your_tablespace sync (on commit)')

this will set up the index in NRT mode. It's pretty sweet.

Don't know about the streaming part, but keeping the index fragmentation low sounds good. Don't know if this results in a broken index if the database crashes before flushing though. If so you'll have to (online) rebuild it.

regards,

balaji pedapati, March 18, 2019 - 2:17 pm UTC

Then please tell me how can we do indexing every day with out dropping and creating indexes...

So you are saying because of dropping and creating indexes it is taking more time...Is it correct???

Can you elaporate your answer in proper way......
Chris Saxon
March 18, 2019 - 2:38 pm UTC

I don't understand why you're dropping & recreating the indexes daily. This means:

- There's a period of time where you have no indexes => slow queries
- You're doing a bunch of unnecessary work => using up resources

You're creating them with the "SYNC (ON COMMIT)" option. So the database will maintain them for you as part of your transactions. There's no need to "do indexing every day".

As Racer comments above, there are some issues with Oracle Text indexes which may mean you want to update the index. But you'd do this by rebuilding the index. Not dropping it!
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

Design

New to good database design? Check out Chris Saxon's full fundamentals class.