How much time the index creation takes.

Hi,

  I am creating an index , and i want to know how much time it will take to finish.  Is been running for like 20 hours, and i will like to know the % it has complete so far.

  Below is the sql .

  CREATE INDEX "xxxxx"."CTXT_IDX_DVC_1" ON "xxxxxx"."DVC_DOCUMENT_
VAULT" ("DVC_CONTENT")      INDEXTYPE IS "CTXSYS"."CONTEXT" ;


  Thank you.
LVL 1
joe_echavarriaDatabase AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
It takes as long as it takes... Sorry, too much depends on your specific system.

you can see how many rows have been indexed with:
select idx_docid_count from ctx_user_indexes where idx_name='CTXT_IDX_DVC_1';

On slower hardware, I once had a Text index that would take 7 days to rebuild.
0
joe_echavarriaDatabase AdministratorAuthor Commented:
How can we know how many rows it still needs to rebuild ?
0
slightwv (䄆 Netminder) Commented:
Take the number of rows in the table, subtract it from how many have been indexed?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

joe_echavarriaDatabase AdministratorAuthor Commented:
Yes, but the query

 select idx_docid_count from ctx_user_indexes where idx_name='CTXT_IDX_DVC_1';

  does not return any value.


something might be wrong ?
0
slightwv (䄆 Netminder) Commented:
I suppose I should make one additional comment on my last post:
It may not be exact if new rows are being inserted into the table while the index is being created.  There is a ctx_user_pending view that will show the 'new' rows awaiting indexing.


I should also ask if you know that by default, a Text Context index needs to be manually synced when new rows are added to the table?

Text indexes are not like your normal BTree indexes.  They require special handling.
0
slightwv (䄆 Netminder) Commented:
>>  does not return any value.

Are you logged in to the database as the user that owns the index?

In your example above this would be: xxxxx.

You specific that as the owning schema.

You could try:
select idx_docid_count from ctxsys.ctx_indexes where idx_owner='XXXXX' and idx_name='CTXT_IDX_DVC_1';

Where XXXXX is the correct index owner.
0
joe_echavarriaDatabase AdministratorAuthor Commented:
Currently the database is not being used now.  No users are connected.

select * from ctx_user_indexes  -- returns no rows.


select idx_docid_count from ctxsys.ctx_indexes where idx_owner='XXXXX' and idx_name='CTXT_IDX_DVC_1';  --- returns 58627.   This value does not increment.


select count(*) from smartsolve.DVC_DOCUMENT_VAULT; --- returns 253573
0
slightwv (䄆 Netminder) Commented:
>>select * from ctx_user_indexes  -- returns no rows.

This needs to be executed while logged in to the database as the OWNER of the index.

>>--- returns 58627.   This value does not increment.

It can take time to index each individual row.  Again, Text indexes are NOT your normal index.  There is a lot happening behind the scenes for each and every row being indexed.

>>--- returns 253573

If you have 253,573 rows and you have only indexed, 58,627 of them, you'll be waiting for quite a while...

You'll need to estimate the time:  How long has it taken to index the 58,000?  If all the data in that column is about average, you should be able to estimate the time remaining.

Remember my post above:  I had a Text index that would take 7 days to build.
0
slightwv (䄆 Netminder) Commented:
VERY rough math:  If the 58,627 has been running for an hour, you should have a little over three hours to go.
0
joe_echavarriaDatabase AdministratorAuthor Commented:
no, 58,627.00 in a day.

So, we have 3 days to go at least.
0
slightwv (䄆 Netminder) Commented:
Ah yes, you said 20 hours...  I should have scrolled back up.  My faulty brain 'remembered' 20 minutes...

3 days give or take a half day.  It all depends on the average size of the data and how much work each average row takes to index.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
joe_echavarriaDatabase AdministratorAuthor Commented:
Yeah, thanks you.

Let us wait on monday.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.