sql - Checking for the presence of text in a text column efficiently -


I have approximately 2,000,000 lines in order to retrieve the rows I need to do a query from the column where the string value Appears as part of

When I run the query, I know the position of the string, but not before the hand. Therefore, a scene that takes a substring is not an option.

As far as I think I have three options

  1. like '%%'
    2. Li> use instr
  2. Use of substrate

I have the option of creating a function based index, if I am good for DBA

At this time all questions take about two seconds Are there any of these experiences, which of these options will work best or if there is any other option? Selection will be used to delete every few seconds, it will usually choose 10 rows.

Edit with some more information

Objects are using a table to store objects with arbitrary keys and values So we are restricted to the extent of controlling them, come out from the outset, there is something like the text column 'key 1 = ABC, key2 = def, keyn = GHI' I know this is bad denormalised, but do we know Keys (to some extent) as that Gee, this is a reliable way to store values ​​and again. To retrieve a single row is quite fast because we are searching the whole column, which is indexed. But performance is not good if we want to retrieve the rows with line 2 = DEF.

We can create a table with the column for the most common key, but I was wondering if there was any way to improve the performance with the current set-up.

oracle 10: 

  create table test (tst_test VARCHAR2 (200) )); Select index * REGEXP_REPLACE (REGEXP_REPLACE (REGEXP_SUBSTR (tst_test, 'key1 = [^, * *'], 'key1 = ([^,] *)', '' 1 ')) Test * Where to test REGEXP_REPLACE ( REGEXP_SUBSTR (TST_TEST, 'KEY1 = [^, * *'], 'KEY1 = ([^, * *' ',' 1 '] =' Exam '

This new selected Index will be used.

You will need several indices because you have the key in the data.

one INDEX Presence, of course, demonstrates effects, but it relies heavily on REGEXP : 

  SQL & gt; Index ix_test Take the test (tst_test) 2 / created index executed in 0,016 seconds in the SQL> test INSERT 2 (tst_test) 3 Select 'KEY1 =' || Level || '; KEY2 =' || (level + 10000) 4 Connect with dual 5 6 level & lt; = 1000000 7/1000000 rows were executed in 47,781 seconds, the SQL & Trunkcat table test2 table was truncated, in which 2,546 seconds SQL & gt; Drop index ix_test 2 / Index dropped SQL executed in 0 seconds; Index ix_re_1 on test test (REGEXP_REPLACE (REGEXP_SUBSTR (tst_test, 'key1 = [^,] *'), 'key1 = ([^,] *)', '\ 1') 2 / index 0,015 seconds SQL & Gt; executed; Choose INSERT 2 (tst_test) 3 in the examination 'KEY1 =' || Level || '; KEY2 = '|| (Level + 10000) 4 to dual level 5 6 & lt; = 1000000 7/1000000 rows were executed in 53,375 seconds

As you can see, my very fast machine ( core 2 4300 , 1 GB RAM ) You can insert a 20000 record per second for a indexed field, and this rate is almost being used with the index : plain or function Based


Comments

Post a Comment