Text :: Creating Single Context Index On One-to-many And Lookup Table?
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.
i tried creating an context index. But it is aborted in the middle. do to reuse the spce i want to drop the index. But it shows an error that " cannot drop index marked as process" how to delete the index.
i WANTED TO SIMPLIFY THIS CODE SO THAT I NEED NOT DO ALL THESE manipulations but still get the result of UNI_CUG_SITE,
Create Table CUG_SITE compress nologging As Select C_Key, S_Key From A group by C_Key, S_Key / commit / --Creating a table for all Single Sited Customer --- [code]........
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.
I have 2 tables, TABLE1 AND LOOKUP_TABLE. The requirement is to insert the distinct (AREA,CITY) from TABLE1 into LOOKUP_TABLE, where LOOKUP_TABLE.LOOKUP_ID = LOOKUP_SEQ.NEXTVAL;
-- Test Case
DROP SEQUENCE lookup_seq; CREATE SEQUENCE lookup_seq START WITH 1; DROP TABLE table1; CREATE TABLE table1
[code]...
Expected data in LOOKUP_TABLE LOOKUP_ID AREA CITY --------- --------- ------- 1 area1 city1 2 area2 city2 3 area3 city3 4 area4 city4 5 area5 city5
Currently this is what I'm doing.
INSERT INTO lookup_table SELECT sl_no, area, city FROM table1; DELETE FROM lookup_table a WHERE
[code]...
Is there a better way of doing this, using a single insert statement?
create table units (id number primary key , agent number, constraint agnent_fk foreign key (agent) references agent_lookup (id) )create table agent_lookup (id number primary key , agent_name varchar2 (40))INSERT INTO AGENT_LOOKUP (ID, AGENT_NAME) VALUES (1, 'X1'); INSERT INTO AGENT_LOOKUP (ID, AGENT_NAME) VALUES (2, 'X2'); INSERT INTO AGENT_LOOKUP (ID, AGENT_NAME) VALUES (3, 'X3');INSERT INTO UNITS (ID, AGENT) VALUES (100, 1); INSERT INTO UNITS (ID, AGENT) VALUES (101, 2); INSERT INTO UNITS (ID, AGENT) VALUES (102, 3);I want to make units table uses user_lookup table instead of agent_lookup table; where user_lookup contains the same names that exist on agent_lookup, but with different IDs.
create table user_lookup ( id number primary key , user_name varchar2 (40))INSERT INTO USER_LOOKUP (ID, USER_NAME) VALUES (10, 'X1'); INSERT INTO USER_LOOKUP (ID, USER_NAME) VALUES (20, 'X2'); INSERT INTO USER_LOOKUP (ID, USER_NAME) VALUES (30, 'X3');So, I need to update the table units and SET the value of Agent to the ID column of the user_lookup table .
I thought of this, but sure it will not work:
update units ut set ut.agent = (select u.id from user_lookup u where agent_lookup.agent_name = u.user_name ) where ut.agent = agent_lookup.id
i need to get values from lookup table dynamically,i am getting the missing keyword error.
create or replace procedure xyz(cur1 OUT SYS_REFCURSOR) AS vsql varchar2(2000); CURSOR CURSOR1 is SELECT DQS_SRC_COL_NM,LDIC_SEQUENCE FROM look_up WHERE LDIC_SOR ='friend'; BEGIN [code]....
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.
I use APEX 4.2.0. In Tabular form, I have column of Select list type. This list has too many values and the end user must choose all these values along the tabular form rows. In other words, If the LOV consists of X, Y , Z, The End user should add three rows and choose a unique value for each row.
The problem is : This LOV is supposed to have too many values. What comes to mind is:
1- Whenever the end user picks a value, this value should disappear from the LOV of the second row ? But I have no clue how to do it? OR 2- Whenever the end use picks a value, I should notify him/her about the remaining values that he should pick . But I have no clue how to do it ?
At the end the purpose is to make the user aware that he still has some rows to add ( values to choose ... )
I currently have a 5 column index on a table with over 2 billion records (paritioned on created_date (weekly) that is not very effective.I am contemplating replacing this 5 key index and creating a new single column index made up by hashing of all the 5 five columns.
Is this a wise stratgey? How can I implement this so it is most effective and I dont shoot myself in the foot?
I have in my database (OLTP-System) a table with about 6000000 records and a zise of about 2GB.
the way to create multi_column indexes on the table?
What are the rule to define the best-position of a column in an index?
index_1(col_1,Col_2,col_3) and not [ (col_1,Col_3,col_2) or (col_2,Col_3,col_1) or (col_2,Col_1,col_3) or (col_3,Col_2,col_1) or (col_3,Col_1,col_2) ] ?
I have to create some indexes in a production database. Do I need to Compute Statistics after creating indexes? Or when I create they automatically are computed?
The version I'm using is:
Oracle Database 10g Release 10.2.0.5.0 - 64bit Production PL/SQL Release 10.2.0.5.0 - Production CORE 10.2.0.5.0 Production TNS for 64-bit Windows: Version 10.2.0.5.0 - Production NLSRTL Version 10.2.0.5.0 - Production
using pl/sql code i am creating text file on specific path of database server. i need to compress this file .how to compress the text file using pl/sql?
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
I am writing a procedure that will be called from a java wrapper.
The procedure do a lot of data manipulations and in between i am creating global temp table and saving the data into it for each request thats given as a parameter to the procedure. After all the processing i have to write the data from this global temp table into a physical table and atlast drop the temp table.
Create or replace proc_name ()
update table........
delete from ..........
CREATE GLOBAL TEMPORARY TABLE TSAAG ( supplier_id numeric(10) not null, supplier_name varchar2(50) not null, contact_name varchar2(50) )
insert into............
drop table TSAAG;
End;
creating a global temp table inside a procedure is expensive...
Do we have anything like creating table before and calling the instanse of it in procedure.
I have a view on base tables holding historical data for previous 60 months(one table per month) with union all operators.create index on those base tables will improve performance or creating a primary key with disabled novalidate will improve for retrieving data?
The view has around 8 million data and used as a fact table with 4 dimension tables.A DTS package from MSSql side refreshes OLAP cube by retrieving data from these tables in oracle.
During developing Domain Index for Oracle 11.2.0.1.0 (problem also appears in 12с) i was faced with misunderstanding of parameter types of function ODCIIndexInsert in case of creating indextype WITH ARRAY DML option.According to Oracle documentation [URL}...
In case of WITH ARRAY DML option Oracle will invoke ODCIIndexInsert with following signature
FUNCTION ODCIIndexInsert( ia ODCIIndexInfo, ridlist ODCIRidList, newvallist varray_of_column_type, env ODCIEnv) RETURN NUMBER
In my case indexed column has datatype NUMBER so i defined varray_of_column_type as SYS.ODCINumberList STATIC FUNCTION ODCIIndexInsert(ia in sys.ODCIIndexInfo, ridlist in sys.ODCIRidList, newvallist in sys.ODCINumberList, env in SYS.ODCIEnv) RETURN NUMBER
Indextype was created as
CREATE INDEXTYPE test_index_type FOR test_eq(number, number) USING index_methods WITH ARRAY DML(number, sys.ODCINumberList)
[code]...
(problem occurs in all cases) CREATE TABLE test_table (id NUMBER (19,0)); CREATE INDEX test_index ON test_table(id) INDEXTYPE IS test_index_type;
When attempting to insert data in the table insert into test_table values (1);
oracle raise exception
CODEError starting at line 53 in command: insert into test_table values (1) Error at Command Line:53 Column:1 Error report:
[code]...
So my question is.Is it normal behavior of oracle (according to documentation)?What is correct signature of ODCIIndexInsert function in case of INDEXTYPE creation with 'WITH ARRAY DML' option and fact that indexed column has NUMBER datatype?
By the way if i define indextype without 'WITH ARRAY DML' option signature is clear, and working. But this approach doesn't satisfies our performance needs.Also if i define index type with option 'WITH ARRAY DML WITHOUT COLUMN DATA' and use signature static function ODCIIndexInsert(ia sys.odciindexinfo, ridlist sys.odciridlist, env sys.ODCIEnv) return number
Everything works too. But this approach doesn't satisfies our business needs.Is it a way to define ODCIIndexInsert parameter types (in case of indexing number column) so that batch inserting works according to documentation ?
FUNCTION ODCIIndexInsert( ia ODCIIndexInfo, ridlist ODCIRidList, newvallist varray_of_column_type, env ODCIEnv)
I am attaching full sql script to recreate environment and reproduce the problem.Type definition:
CREATE OR REPLACE TYPE index_methods AS OBJECT ( step number, STATIC FUNCTION ODCIGetInterfaces(ifclist OUT SYS.ODCIObjectList) RETURN NUMBER, STATIC FUNCTION ODCIIndexCreate (ia SYS.ODCIIndexInfo, parms VARCHAR2, env SYS.ODCIEnv) RETURN NUMBER, STATIC FUNCTION ODCIIndexAlter (ia sys.ODCIIndexInfo, parms IN OUT VARCHAR2, altopt number, env sys.ODCIEnv)
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
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.
Am trying to use the SYS_CONTEXT variables inside a trigger. After reading the documentation I found that the below query when used inside the trigger code will return the trigger owner.
Select Username from User_Users
Will any of the below variables return the trigger owner when used inside the trigger code
sys_context('USERENV','CURRENT_USER') or sys_context('USER','CURRENT_SCHEMA')
I am trying to implement a Jtree with Context Menu. When Context Menu is implemented the nodes wont expand or collapse.This happens with Sample Trees Packaged application for Project Documentation tree.
I'm trying to find out if I can create a context sensitive menu in Oracle Forms 10g. So far I haven't found much in the way of info. where I can find some sample code or a demo I can learn from?
The idea being that when a user right clicks an item they are then presented with relevent options for that item.
Create a table with 100 records.Then write a BULK COLLECT Cursor that can reduce the number of context switches by using a bulk fetch to query records in sets or all at once.
