Text :: Index For Domain Index With Composite Domain Index (CDI) Very Slow
Jun 27, 2012
I am on 11.2.0.3 Enterprise Edition. We are using the new feature "Composite Domain Index" for a Domain index on a very large table (>250.000.000 rows). It really works with mixed queries. We added two number columns using FILTER BY.We have lots of DML on this table. Therefore, we are executing synchronize and optimize once the week. The synch behaves pretty normal. But "optimize_index" takes a very very long time to complete. I have switsched on 'logging' for the optimize process. The $I table takes some time but is finished normally. But the optimization of the $S table (that is the table created for the CDI feature) is running over 12 hours now - and far from being finished. From the logfile, I can see that it optimizes 1000 rows every 20 minutes. Here is the output of the logfile:
Oracle Text, 11.2.0.3.0
14:33:05 06/26/12 begin logging
14:33:05 06/26/12 event
14:33:05 06/26/12 process $N for optimize: SEQDEV.GEN_GES_DESCRIPTION_CTX_I
14:33:16 06/26/12
14:33:16 06/26/12
[code]....
I haven't found a recommendation from Oracle not to use "optimize_index" for Domain Indexes with CDI. But in my case, it would be much faster just to drop and recreate the Domain Index in question.
View 5 Replies
ADVERTISEMENT
Apr 1, 2013
I have a table in my application that has the following structure
Create table EVENT (project_id number, DISC_CMPLNT_TX clob, MAINT_ACT_TX CLOB,LGBK_CMT_TX VARCHAR2(1000 CHAR))
PARTITION BY RANGE (project_id)
(PARTITION pmax VALUES LESS THAN (MAXVALUE));
I have created the below preference on the three columns of the table.
begin
ctx_ddl.create_preference ('skipJoinLexer', 'BASIC_LEXER');
ctx_ddl.set_attribute ('skipJoinLexer', 'skipjoins', ';:.,()<>*"''#-');
ctx_ddl.create_preference ('logbookTextDS', 'MULTI_COLUMN_DATASTORE');
ctx_ddl.set_attribute ('logbookTextDS', 'COLUMNS', 'DISC_CMPLNT_TX, MAINT_ACT_TX,LGBK_CMT_TX');
end;
I have created a local partitioned index with the parameter NOPOPULATE create index lb_text_idx on ac_maint (DISC_CMPLNT_TX) indextype is ctxsys.context local parameters ('lexer skipJoinLexer datastore logbookTextDS NOPOPULATE');
When an new value of project_id is inserted into table1 , I create a partition using the following command where prjId is the new value of the project_id ALTER TABLE EVENT split PARTITION pmax AT ('||prjId||') INTO ( PARTITION p_' || prjId || ', PARTITION pmax)
Now I run a huge data load into the EVENT table for a project Id say 1 and insert millions of records.I want to rebuild the lb_text_idx for this partition alone so that data is available for searching by the application. I did a rebuild using the command " alter index lb_text_idx rebuild partition p_1".
However when I run the below sql, I get a count of 0. I know that the text MAINT is definitely present in the table.
SELECT COUNT(1) FROM EVENT WHERE PROJECT_ID = 1 and contains (DISC_CMPLNT_TX, 'MAINT' ) > 0
When I run the below sql select parameters,status from user_ind_partitions where partition_name = 'P_1' , I get parameters as null and status as usable.Am I getting 0 records because the parameters are set to null? How do I rebuild with the same parameters for the specific partition alone.
View 3 Replies
View Related
Oct 9, 2012
get a better understading of CATSEARCH and Domain Index in oracle
View 3 Replies
View Related
Jun 30, 2012
I have created domain indexes on text columns of a materialised view to use "contains" clause when searching for data. The select query with "contains" clause does not return any records, however I was able to retrive data using via regular query using a like search.
-> will exec ctx_ddl.sync_index('index_name')'resolve my problem?
-> since the view is a materialized view, how can i make sure that the latest data added are also picked up?
View 2 Replies
View Related
Nov 8, 2012
I have a lot of queries on a table with WHERE clause:
WHERE CR_DATE > :y AND CR_VALUE = :x
Actually, there is an index on (CR_DATE) but much more selective index is on (CR_DATE, CR_VALUE). If I do not UPDATE any records on this table, is there any difference in INSERT operation (or another problem) when I replace actual index with multi-column index?
View 1 Replies
View Related
Feb 25, 2012
I need to create a composite unique index on varchar2, number and CLOB column. I haven't used such index before that have the CLOB column indexing. I found the below link related to CLOB indexing...
[URL]......
Links from where I can get related info. Also I would like to know the impact of such index on performance. I have to store and process around 50 million records in such a way, will it be beneficial to use this index?
View 11 Replies
View Related
Jun 28, 2011
I have a huge table (about 60 gb) partition over range. The index on this table is global index created on 4 columns together. I have a query which is running very slowly. The explain plan is showing the use of this global index.Explain plan is not showing pstart and pend because the index is global.
View 6 Replies
View Related
Jun 23, 2011
I have a global index and I want to convert it to local index.Is there a way to recreate local index with out dropping the global index.
I can create a local index first and then drop the global index. But is there a way to create it with out dropping the global index, just convert it.
View 5 Replies
View Related
Nov 29, 2010
I am facing the error "ORA-01502: index or partition of such index is in unusable state " while loading the text data using
sql loader with direct path (direct = Y ,rows = 10000) option. Table consists an composite non unique index. If I query the dba indexes for the effected index it shows the index status as VALID. There was no maintaince done on the effected table or index. I have tried loading the same data using conventional path but didn't found any issues for the same.
View 3 Replies
View Related
Nov 19, 2010
I have a query which had a join:
a.c1=b.c1 and a.c2=@var
where @var is user supplied input at runtime...We had a index on a.c2 . The CBO would use this index to generate an opitimised query plan.We found some records from table "b" were dropping due to inner join. So we made a change in join. It'd be like
a.c1(+)=b.c1 and nvl(a.c2,@var)=@var
This query is no longer using the index, instead its doing a full table scan causing the query to slowdown.I have tried creating index on nvl(a.c2,'31-dec-9999')
But the CBO won't use it.Anyway to create index on this col so that full table scan can be avoided?
View 2 Replies
View Related
Aug 9, 2013
How to force an index if the table not using the index?
View 10 Replies
View Related
Aug 27, 2012
We have occurrences of enq : TX - index contentions in the database. Using the SQL ID, we have identified the INSERT statement and the table which they are trying to insert.
This table has almost 25 different indexes, some of which are unique as well.I am wondering how to identify the actual index causing issue, out of these 25 indexes.
Is there any way to pin point to the name of index which is causing the lock?My plan is, once the index is identified, I would like to check the extents and inittrans and other attributes of this index to fix.
View 5 Replies
View Related
May 12, 2011
I have created an non unique index lk_fein on lookup_fein( code,map_id,trash). When I check the explain plan it does a full table scan on lookup_fein. if I force it to use index by it does and the cost also decreases.
SQL> SELECT WORK_FEIN,
2 NON_FEIN ,
3 FI_FEIN ,
4 MFEIN ,
5 TOTAL_FEIN ,
[code]...
View 1 Replies
View Related
Jul 11, 2012
Can we create non-cluster index on a clustered index?
View 5 Replies
View Related
Sep 17, 2013
I am on Oracle 11.2.0.3 on Linux and have implemented Oracle Text.I created Oracle Text indexes with default setting. However in an oracle white paper I read that the default setting may not be right. Here is the excerpt from the white paper by Roger Ford:URL....(Part of this white paper below....)Index Memory.
As mentioned above, cached $I entries are flushed to disk each time the indexing memory is exhausted. The default index memory at installation is a mere 12MB, which is very low. Users can specify up to 50MB at index creation time, but this is still pretty low. This would be done by a CREATE INDEX statement something like: CREATE INDEX myindex ON mytable(mycol) INDEXTYPE IS ctxsys.context PARAMETERS ('index memory 50M'); Allow index memory settings above 50MB, the CTXSYS user must first increase the value of the MAX_INDEX_MEMORY parameter, like this: begin ctx_ adm. set_ parameter('max_index_memory', '500M'); end; The setting for index memory should never be so high as to cause paging, as this will have a serious effect on indexing speed. On smaller dedicated systems, it is sometimes advantageous to temporarily decrease the amount of memory consumed by the Oracle SGA (for example by decreasing DB_CACHE_SIZE and/or SHARED_POOL_SIZE) during the index creation process.
Once the index has been created, the SGA size can be increased again to improve query performance." (End here from the white paper excerpt)My question is:
1) To apply this procedure (ctx_adm.set_parameter) required me to login as CTXSYS user. Is that right? or can it be avoided and be done from the application schema? This user CTXSYS is locked by default and I had to unlock it. Is that ok to do in production?
2) What is the value that I should use for the max_index_memory should it be 500 mb - my SGA is 2 GB in Dev/ QA and 3GB in production. Also in the index creation what is the value I should set for index memory parameter - I had left that at default but how should I change now? Should it be 50MB as shown in example above?
3) The white paper also refer to rebuilding an index at some interval like once in a month: ALTER INDEX DR$index_name$X REBUILD ONLINE; We are on Oracle 11g and the white paper was written in 2003.
View 5 Replies
View Related
Aug 3, 2010
Any on give explanation for difference between Index and Clustered Index?
It will be great if i get explanation how memory allocation and Execution takes place.?
View 4 Replies
View Related
Jan 10, 2011
What is the difference between index rebuild and index rebuild online.
View 3 Replies
View Related
Mar 27, 2013
Is this the right query to determine index fragmentation ?
SELECT AVG (tfrag)
FROM (SELECT /*+ ORDERED USE_NL(i) INDEX(i DR$TEXT_IDX$X) */
i.token_text,
(1
[code]...
The reason I am asking that before index rebuild it returned 86% but after rebuild (ALTER INDEX .. REBUILD) it returned
96% which does not make sense.
I did try ctx_report.index_stats but it takes more time to run.
View 5 Replies
View Related
Oct 10, 2013
We have a table called address and having the address fields and city ,state etc. The table will store huge amount of data .We need to query on the table. I would like to know how can we fasten the query and improve the performance of the query by creating index on these columns...Query is given below . note that the nullable columns can have data
SELECT *
FROM address
WHERE address1 = 'a'
[Code]....
View 9 Replies
View Related
Apr 16, 2013
I've been successfully setting up text indexes on multiple columns on the same table (using MULTI_COLUMN_DATASTORE preferences), but now I have a situation with a one-to-many data collection table (with a FK to a lookup table), and I need to search columns across both of these tables. Sample code below, more of my chattering after the code block:
CREATE TABLE SUBMISSION
( SUBMISSION_ID NUMBER(10) NOT NULL,
SUBMISSION_NAME VARCHAR2(100) NOT NULL
);
CREATE TABLE ADVISOR_TYPE
[code]...
I've looked at DETAIL_DATASTORE and USER_DATASTORE, but the examples in Oracle Docs for DETAIL_DATASTORE leave me a little bit perplexed. It seems like this should be pretty straightforward. I'm trying to avoid creating new columns, and keeping the trigger adjustments to a minimum.
View 3 Replies
View Related
Oct 5, 2013
Let's consider such table that all rows fit into single block:
SQL> create table test as select rownum id, '$'||rownum name from dual connect by level <= 530;
Table created.
SQL> create index i_test on test(id);
Index created.
SQL>
SQL> begin
[code].....
why does approach with full scan take longer even if table occupies only one data block? PS. 11gR2
View 8 Replies
View Related
Aug 6, 2012
Im in the midst of installing a patch on my UIM. However, in the planning phase, the Oracle guide recommends that I backup my database schema and database domain for UIM in case the patching fails and might affect the whole UIM app. May I know how to do this?
DB : 11.2.0.1.0
UIM : 7.2.0.0.0
View 8 Replies
View Related
Dec 8, 2011
Do Oracle SQL support Domain Concept?
View 3 Replies
View Related
Jan 20, 2010
I create an instance with oracle scripts,.. postDBCreation.sql :
-SERVICE_NAME cag1.crick.cagis.gov should be cag1.mycrof.cagis.gov .... ultraSearchCfg.sql SERVICE_NAME=cag1.crick.cagis.gov should be ca1.mycrof.cagis.gov .. Impact: All DBlinks show as LINKNAME.CRICK.CAGIS.GOV and this instance is labeled CAG1.CRICK.CAGIS.GOV in the Grid Control Console.
View 5 Replies
View Related
Nov 29, 2011
I have a field for email address. I would like to create a field to capture just the domain (after the @ and before the .com) so I can process a count of records by domain.
View 17 Replies
View Related
Oct 20, 2010
If I'm looking on v$session - values in osuser column for some machines displayed with DOMAINNAME, and for other - without. Even same user (MYDOMAIN\ADMINISTRATOR) from 2 different machines displayed in 2 different ways on the same db:
MYDOMAIN\Administrator from machine A, and administrator from machine B.
Both machines are belongs to same windows domain. DB (oracle 10g) have no "DOMAIN" defined.
What make the difference? And can I somehow to affect this behaviour? I mean, can I define that all users will be displayed as DOMAINNAME\USERNAME or MACHINENAME\USERNAME(in case of no domain exists)?
View 8 Replies
View Related
Sep 9, 2010
I want to try installing Oracle E-business suite on Mandriva But when I try e.g. domainame I get blank.
A) How can I check my domainname ?How can I set it?Here is the hosts file
[Select all] [Show/ hide]
127.0.0.1 localdomain
192.168.0.2 david.domain.com david
View 3 Replies
View Related
Apr 22, 2011
When Oracle was installed, there was an error in the server's domain name (missing a "."). Is there an easy way to fix this domain name in the Oracle installation after the fact? I know about tnsnames.ora, pfile, system parameters, and I'm pretty sure I can do emagent -config. My concern is that the "Installed Products" would not like the fact that the domain name is now "abc.proj.org" when installation was done on "abcproj.org". The /etc/hosts file had "abcproj.org", and we want to fix it to "abc.proj.org".
I need to reinstall Oracle -- is there a better way?
View 3 Replies
View Related
Apr 30, 2010
I'm using Dev10g Rel.2....If I call a report with paramform=no and pass it a value from forms then report runs well. but if I call report thru parameter form and pass value from parameter form the report doesn't run with error:
The webpage cannot be displayed.
Interesting fact is that If I call the report from a client on same domain as that of Application Server then it runs even thru paramform. I've also entered the application server I.P and name on other domain's client but no way.
View 1 Replies
View Related
Dec 1, 2012
how to send email (PL/sql Procedure) to private domain through google mail.
View 2 Replies
View Related
