Text :: Add Multi Indexes On One Table?
Jul 22, 2012
I been doing a little testing with indexing the contents of URLs (database v11.2.x.x).
ctx_ddl.drop_preference('TEST_URL');
ctx_ddl.create_preference('TEST_URL','URL_DATASTORE');
ctx_ddl.set_attribute('TEST_URL','Timeout','3600');
create index datastores_text on test_url_search ( doc ) indextype is ctxsys.context parameters ( 'Datastore TEST_URL' );
SELECT SCORE(1),DOC from TEST_URL_SEARCH WHERE CONTAINS(DOC, 'London', 1) > 0
This works. i like to more
e.g. multi words search e.g. ' London Games' where if London or games are present it return a URL with a score,
or
Search in the Chinese.
The only way I can see to do this is create different indexes as the attributes for 'URL_DATASTORE' are limited. If I can what does the 'Contains' statement look like?
View 0 Replies
ADVERTISEMENT
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
Sep 11, 2012
I have noticed that Oracle text related objects, particularily the $I tables are some of the largest objects in our database. I have been actively pursuing utilizing Oracle advanced compression in our databases for OLTP table compression and LOB object compression. I have been unable to find any documentation or notes on if it is advisable to implement either table OLTP or LOB compression for Oracle text objects.
View 1 Replies
View Related
Mar 13, 2013
I have a multi-record block with several text items.
On one of the text items i want to enter a value and then check whether the same value for the item has already been entered on any other records within the block.
If it has already been entered then i want to display a message and null out the field.
I have tried using app_record.for_All_records from a when-validat-item trigger but this does not work as you get a 'FRM-40737 Illegal restricted procedure go_block in when-validate-item trigger ' error
View 17 Replies
View Related
Sep 10, 2012
RDBMS - 11.1.0.7, I it possible to convert indexes of a non-partitioned table to hash partitioned indexes by retaining table as non-partitioned?
If yes, is this what it is Creating a Hash-Partitioned Global Index - can be created for partitioned and non-partitioned tables?
View 7 Replies
View Related
Aug 29, 2012
Is Oracle will support Multi value storage ? In what way we can use Nested table? In real time application where we can use nested table . What is the usage of nested table in real time application.
View 2 Replies
View Related
Jan 18, 2011
I want drop some old partitions from big table but this will not increase free space on disk. So I want to move table with indexes to anothers tablespaces. What is the fastest way to do that? ALTER TABLE ... MOVE TABLESPACE ...? CTAS ? Or something else?
View 4 Replies
View Related
Oct 4, 2012
I have a table like MyTab(a int, b int), and I am required to create a primary key index and a non-unique index on this table using columns (a,b) in a specific table space.
The back end database is Oracle 10g.
Here's what I have tried so far, needless to say, unsuccessfully.
Alter Table MyTab
Add Constraint c_1 primary key (a, b)
Using Index (Create index mytab_idx on MyTab(a, b))
Using index tablespace results_index
So my question are:
1. is this is possible? if so, what is the correct syntax.
2. assuming it is possible, using this sort of construct before? it appears to be conflicting and inconsistent to me.
View 4 Replies
View Related
Sep 26, 2012
what analyzing a table does to existing indexes? Do I need to rebuild the indexes after dbms_stats.gather_table_stats command ?
View 4 Replies
View Related
Jul 5, 2012
I have this sql (generated by discoverer plus), and work fine:
SELECT COUNT(o10475761.SUC_ID)
FROM GAR_DW.ARTICULOS o10475528,
GAR_DW.EMPRESAS o10475602,
GAR_DW.L_DIA o10475639,
[code]...
We need change the table stock_por_sucursal for a view like this, with similar indexes each table
select * from stock_por_sucursal_old
union all
select * from stock_por_sucursal_new
and the result is not good:
SQL Statement from editor:
SELECT COUNT(o10475761.SUC_ID)
FROM GAR_DW.ARTICULOS o10475528,
GAR_DW.EMPRESAS o10475602,
GAR_DW.L_DIA o10475639,
GAR_DW.V_STOCK_POR_SUCURSAL o10475761,
[code]...
finally add a HINT /*+ gather_plan_statistics push_pred(TABLE) */ and the result was not very good in this case, but improved the resolution of the view, the rest got worse
SQL Statement from editor:
SELECT /*+ gather_plan_statistics push_pred(o10475761) */ COUNT(o10475761.SUC_ID)
FROM GAR_DW.ARTICULOS o10475528,
GAR_DW.EMPRESAS o10475602,
GAR_DW.L_DIA o10475639,
[code]...
View 2 Replies
View Related
Mar 6, 2013
I have one control table as below.I want to rebuild all indexes for the tables in control table.
The control table is having the following data.
SEQ_IDTABLENAME SCHEMA_NAME
1GEDIS_ORDER_FORM_STATES ALL
2GEDIS_NOTES ALL
3GEDIS_CARD_TYPE_AUDIT APRT
4FAX_HEADER OMS
In the control_table schema_name "ALL" means this is for 30 schemas(The table is existed in 30 schemas).Except for schema_name "ALL" ,the table is existed in the particular schema(The table is existed in the only one schema).
I tried the following code it is executing for all 30 schemas(ALL).But it is not executing for specific schemas.
CREATE OR REPLACE PROCEDURE Rebuilding_index
IS
l_sql VARCHAR2(4000);
CURSOR cur_tab_schema
IS
SELECT tablename,schema_name
FROM control_table3;
[code]....
This contains the 30 schema names.
SELECT owner_name FROM global_bu_mapping;
View 4 Replies
View Related
Aug 24, 2007
I'm wanting to create a query that will give me a summary of parts and labor from work orders. However there are three tables. work_order, parts, labor. Sometimes there will only be parts, sometimes just labor, or sometimes both. Well my query will only return results when they are on both. Sample query is below. Do I need to create a temp table to gather the data or is there a way to do this with a sub query.
select work_order.wono, sum(parts.cost), sum(labor.cost)
from work_order, parts, labor
where
work_order.wono = parts.wono and
work_order.wono = labor.wono
group by work_order.wono;
View 5 Replies
View Related
Nov 3, 2011
Just like Multi-table insert(using INSERT ALL) is there multi-table Update facility in Oracle? In which version?
View 1 Replies
View Related
Mar 11, 2011
I have created table as below
create table emp_temp as select * from emp;
the table is created, but the constraints are not copied. Is there any way to copy all the constraints.
View 3 Replies
View Related
Mar 19, 2012
getting how many local and global indexes on particular oracle table
View 2 Replies
View Related
Jan 26, 2012
I have created a table like below-
PROMPT CREATE TABLE tst_fetch_vendor_data
CREATE TABLE tst_fetch_vendor_data (
vendor_data_seq_no NUMBER NOT NULL,
study_seq_no NUMBER NOT NULL,
vendor_record_seq_no NUMBER NOT NULL,
control_column_seq_no NUMBER NOT NULL,
resolved_value VARCHAR2(4000) NULL,
original_value VARCHAR2(4000) NULL,
transaction_user VARCHAR2(30) NOT NULL,
[code]....
Its just a temporary table, in which data comes and goes. I am using this in middle of a process.I am using it in a process like below--
--EXECUTE IMMEDIATE 'TRUNCATE TABLE TST_FETCH_VENDOR_DATA DROP STORAGE';
insert /*+ append */ into tst_fetch_vendor_data
(select * from vendor_data vd
where vd.control_column_seq_no in
(select control_column_seq_no from temp_control_column));
dbms_stats.gather_table_stats('EPDSYSREP','TST_FETCH_VENDOR_DATA',ESTIMATE_PERCENT=>100,
METHOD_OPT=>'for all indexed columns size auto',CASCADE=>True);
code to use that table..This table can contain data from 0 to 108000000 records.Now my questions are-
1. How much should I select sampling size (currently its 100%)Can I use dbms_stats.auto_sample_size, what will be the effect?
2. dbms_stats is good approach or should I use dynamic sampling.
3. what about the approach using CTAS instead of inserting data through insert.
4. What about pl/sql table with index or with clause query.
5. Do I need to rebuild index after inserting data into table.
View 3 Replies
View Related
Jul 11, 2013
I have a requirement to import text files which are generated from 3d modelling software xsteel where it records all geometric information and i want to import this information into oracle table.
CREATE TABLE dstv_head ( wo_no VARCHAR2(12),struct VARCHAR2(12),rev_no NUMBER,
mark VARCHAR2(12),pos VARCHAR2(12),grade VARCHAR2(12),qty NUMBER,PROFILE VARCHAR2(24),TYPE VARCHAR2(12),
len NUMBER,width_web NUMBER,width_bottom NUMBER,flange_thk NUMBER,web_thk NUMBER,radius NUMBER,kgm NUMBER,
kgm1 NUMBER,kgm2 NUMBER,bevel_plus NUMBER,bevel_minus NUMBER,holes_yn VARCHAR2(1),holes_v_yn VARCHAR2(1),
hole_x_dim NUMBER,hole_y_dim NUMBER,hole_dia NUMBER,no_of_holes NUMBER)
-- All the data which has to go under specific field for example **9005.nc1 will go into wo_no field, 1239401A will go under struct.
ST
** 9005.nc1 --WO_NO
1239401A - STRUCT
1 -REV_NO
9005 -MARK
9005 --POS
S275JR --GRADE
2 --QTY
[code]....
View 24 Replies
View Related
Sep 24, 2010
I do have problem with a table which have same structure of other table with indexes created being the same. Both tables have partions & subpartitions. Let us consider two tables table1 & table2. The problem i face is the index for table1 not being used when its being joined with other respective tables in a query. Whereas for table2 its working perfectly and cost is also less. I have used the explain plan and compared and query timing also takes more time. what might be causing it?
View 2 Replies
View Related
Dec 14, 2006
I want to load data to a table and from a simple file text, using a Vb.net application which will connect to a oracle 10g , or a SqlServer or a MySql database, depending the params.
When i connect to a SqlServer Database i use the sql command "BULK INSERT CODPOSTAL2 FROM file.txt with( DATAFILETYPE = 'char',FIELDTERMINATOR = ';', ROWTERMINATOR = '
')"m" and it works fine.
With a DB Mysql i use "LOAD DATA INFILE file.txt INTO TABLE CODPOSTAL2 FIELDS TERMINATED BY ';''" and also works.
My problem is with Oracle. I tried the same example as MySql, but it gaves the error "wrong" ou "unknown command". I also tried in Sql*Plus but it seems to not recognised the command "LOAD".
Another thing, i can't use the Oracle Loader, it must be like this.
View 5 Replies
View Related
Sep 12, 2012
Have got basic form on a table and have a textarea which holds Notes added by user.
So Notes database field is updated on Save / Apply changes button being pressed.But would really like any text added / appended to the Notes field to be prefixed by userid and date / timestamp.
Is it possible via dynamic actions or Javascript to have any new text added / typed to be auto prefixed as per above.
Would only want the first key press in the filed to trigger the auto-prefix and if added text was deleted then the auto prefix to be deleted as well ?? If user doesn't press Save / Apply changes obviously want to leave existing Notes as is.
View 5 Replies
View Related
May 5, 2010
Is there text changed trigger with text item function like when_list_changed trigger of list item?
View 7 Replies
View Related
Apr 17, 2012
Can I use rich text item on oracle form10G with some simple features like BOLD, UNDERLINE, ITALIC and if possible one more feature like spell check.
I Google my requirement, but mostly I found win word attachment. Further more if I can save this type of data in field then how can I print in report.
View 3 Replies
View Related
Dec 30, 2010
I have a requirement in one of my forms screen.I have a text box(large text area) which should display a help text file when i move my cursor on the topics displayed on the screen.know the code and the properties to be changed in the text box to accommodate large external file text.
View 1 Replies
View Related
Feb 16, 2010
I have a form that has one text box in it, and I want this value to be inserted into a table called Staff Name, which has only one column, but im unsure as to how I would get my SQL statement to pick up this value.
I tried:
begin
insert into StaffName values ('addstaffmember');
end;
Add staff member is the name of the text box. this statement compiled but when I ran the form and tried to click save it would not work.
View 5 Replies
View Related
Oct 14, 2012
What is the best way to load the data from text file to the table in PL/SQL .How can i write a program for that ?
-data is separated by ',' in text file for each columns in table
View 19 Replies
View Related
Dec 23, 2009
Is there any way to convert HTML format text to Plain Text ?
View 26 Replies
View Related
Jun 27, 2013
i want to extract data from my local database table to text file using plsql
View 4 Replies
View Related
Oct 8, 2012
I want to get all the column values in a table and save them into a text file.Beside UTL_FILE, is there any other method which will result better performance in writing to text file?
noted that the data does exist 32k.
View 39 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
Jun 19, 2012
I'm new to Oracle Text. I want to implement search for the unique ids. Like google search when the user start typing 123 it need to brings anything starting with 123 and has show like entries how google will shows. When I add number 4 to like 1234 then it has bring numbers starting with 1234.
View 2 Replies
View Related