Saurabh Nainwal, April 30, 2019 - 2:43 pm UTC
Hi Chirs thanks for quick reply. My situation is about oracle text search where the primary key ids in all the available tables are dumped in a table and the datastore is a user datastore. The table is range partitioned and the text search index is on the partition column. There is a scenario where there is some problem in a partition during data load the partition data will be removed and reinserted and the corresponding local index rebuild. How to achieve this?If I do it the way you have demonstrated its giving ORA 01249 error whether i delete or truncate the data in the partition
April 30, 2019 - 3:04 pm UTC
Please provide complete code (create table + inserts + alter tables + index rebuilds) showing what you're trying to do. Without this it's unclear to me what the exactly the problem is.
Saurabh Nainwal, April 30, 2019 - 4:30 pm UTC
Create table t (
id varchar2(10), part_id char
) partition by range( part_id) (
partition p1 values less than ( 'F' ),
partition p2 values less than ( 'L' )
);
insert into t values ( 'a1', 'A' );
insert into t values ( 'a2', 'F' );
commit;
CREATE INDEX t_idx ON t(part_id) INDEXTYPE IS CTXSYS.CONTEXT LOCAL;
lets assume i wanna repopulate the second partition and rebuild the index
ALTER TABLE t TRUNCATE PARTITION p2;
insert into t value('a2','L');
commit;
ALTER INDEX t_idx REBUILD partition p2; --throwing error
April 30, 2019 - 4:53 pm UTC
I'm still not seeing the error...
SQL> select * from v$version;
BANNER
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL>
SQL> Create table t (
2 id varchar2(10), part_id char
3 ) partition by range( part_id) (
4 partition p1 values less than ( 'F' ),
5 partition p2 values less than ( 'L' )
6 );
Table T created.
SQL> insert into t values ( 'a1', 'A' );
1 row inserted.
SQL> insert into t values ( 'a2', 'F' );
1 row inserted.
SQL> commit;
Commit complete.
SQL>
SQL> CREATE INDEX t_idx ON t(part_id)
2 INDEXTYPE IS CTXSYS.CONTEXT LOCAL;
Index T_IDX created.
SQL>
SQL> ALTER TABLE t TRUNCATE PARTITION p2;
Table T truncated.
SQL>
SQL> insert into t values ('a2','L');
Error starting at line : 1 in command -
insert into t values ('a2','L')
Error report -
ORA-14400: inserted partition key does not map to any partition
SQL> commit;
Commit complete.
SQL>
SQL> ALTER INDEX t_idx REBUILD partition p2;
Index T_IDX altered.
Saurabh Nainwal, April 30, 2019 - 5:20 pm UTC
Thanks Chris. Is this correct way of index rebuilding after truncate? or is there any better way to repopulate a partition and build the index?
May 01, 2019 - 10:02 am UTC
The rebuild is unnecessary! You just need to sync the index. Or create it with the SYNC (ON COMMIT) parameter:
Create table t (
id varchar2(10), part_id char
) partition by range( part_id) (
partition p1 values less than ( 'F' ),
partition p2 values less than ( 'L' )
);
insert into t values ( 'a1', 'A' );
insert into t values ( 'a2', 'F' );
commit;
CREATE INDEX t_idx ON t(part_id)
INDEXTYPE IS CTXSYS.CONTEXT LOCAL
parameters ( 'sync (on commit)' );
ALTER TABLE t TRUNCATE PARTITION p2
update indexes;
select partition_name, status
from user_ind_partitions
where index_name = 'T_IDX';
insert into t values ('a2','K');
commit;
select * from t;
ID PART_ID
a1 A
a2 K
select * from t
where contains ( part_id , 'K' ) > 0;
ID PART_ID
a2 K
Saurabh Nainwal, April 30, 2019 - 5:46 pm UTC
Hi Chris. Thanks you were all the time right. I was doing it wrong. I gave different name to index partition while rebuilding the index I was using table partition name. However I will appreciate if you will reply is this the correct way or is there a better way.
On the side note what is the difference between recreate and rebuild index? can we recreate a local partition index and how? is this better than rebuild?
Saurabh Nainwal, May 01, 2019 - 4:35 pm UTC
Chris, is there a way to track the sync(on commit) operation completion in case of millions of records insert inside the partition?
May 01, 2019 - 6:07 pm UTC
When you have sync (on commit), the database will update the index when you commit your transactions. So this forms part of your commit time processing.
If you're NOT syncing on commit, you can see outstanding changes by querying ctx_user_pending
Saurabh Nainwal, May 11, 2019 - 7:54 pm UTC
Hi Chris
I have run into a issue with the oracle text search index. My oracle search index is local index with 6 range partitions with sync (on commit). There are close to 2 million records.
scenario 1: The create index ran for 1.5 hrs and the operation was aborted due to closed connection. In user_ind_partition tables only first 3 partitions where Valid while the 4th partition was Valid to but apart from corresponding $I table no other table viz. $K were not populated. Partition 5th and 6th were INPROG. I truncated the partition 4th and tried to reload it but got an error "trying to access a partition or index which is unusable".
scenario 2: Dropped the index. Include the "parallel 4" in create index clause and ran it again. The process took 5 hrs and all the partitions but 4th dint build and the process aborted with "PGA Memory reached by the instance exceeds PGA_AGGREGATE_LIMIT".
Please help me to find out the fastest and cleanest way to build this index.
May 14, 2019 - 2:40 pm UTC
How large are the documents you're indexing? 1.5 hours to index 2 million rows sounds waaaaayyyyy too long.
Trace the index creation to see what's going on.
exec dbms_monitor.session_trace_enable ( null, null, true, true );
create index ...
exec dbms_monitor.session_trace_disable ( );
Then grab the trace file and use TKPROF or similar to review the results. Find out more about this at:
https://blogs.oracle.com/sql/how-to-create-an-execution-plan#tkprof
Saurabh Nainwal, May 14, 2019 - 5:07 pm UTC
Hi Chris
Thanks for your reply. I will try to trace the session. However now I am using dbms_pclxutil.build_part_index procedure to build the index and its not failing. However the index is taking a time of 16 hours. I have 80 odd modules with each module has many tables and the requirement is to search across all the columns of the tables in a module. So you can think of a module document as a record starting from the master table to all the corresponding children tables.
I have observed one more thing that optimizing the index immediately after create index reduces the count of tokes in $I table. Do we need to optimize the index after create?
May 15, 2019 - 11:12 am UTC
Saurabh Nainwal, May 15, 2019 - 5:48 pm UTC
Thanks a lot Chris. The whole conversation was really helpful. One last question, to speed up the indexing I am using No logging and no cache for lob. What exactly these options mean?
Regarding Oracle Text
Rajeshwaran, Jeyabal, May 18, 2019 - 9:25 am UTC
May 20, 2019 - 11:31 am UTC
Well the reference guide is a blow-by-blow description of the syntax for all the commands and parameters.
The developer's guide gives you an overview of Oracle Text, getting started guides, and how-tos for various operations.
"Getting started" looks like a good place to begin to me!