Home » RDBMS Server » Performance Tuning » search on text column
search on text column [message #273857] Fri, 12 October 2007 00:43 Go to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Hi,
We have a table business_name with 28558157 records in it.
Below is the statistic about the table :

BLOCKS        570227
EMPTY_BLOCKS  3213
AVG_SPACE     975
AVG_ROW_LEN   139
LAST_ANALYZED 11/10/2007 9:23:44 PM
PARTITIONED   YES

table is partitioned on column latest_in
PART_LATEST_N    21658507
PART_LATEST_Y     6899650

There are two index on column name_value_tx
1)
CREATE INDEX BNM_FI ON BUSINESS_NAME 
(UPPER("NAME_VALUE_TX"))
  INITRANS   2
  MAXTRANS   255
LOCAL (  
  PARTITION PART_LATEST_N
    LOGGING
    NOCOMPRESS
    TABLESPACE LATESTN
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION PART_LATEST_Y
    LOGGING
    NOCOMPRESS
    TABLESPACE LATESTY
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                BUFFER_POOL      KEEP
               )
)
NOPARALLEL;

2)
  CREATE INDEX TX_ID ON BUSINESS_NAME
   (NAME_VALUE_TX)
   INDEXTYPE IS CTXSYS.CONTEXT;



Both the below search takes more than 1 min..
SELECT name_value_tx
  FROM business_name bnm
 WHERE bnm.latest_in = 'Y'
   AND contains (bnm.name_value_tx, 'IBM') > 0


SELECT name_value_tx
  FROM business_name bnm
 WHERE bnm.latest_in = 'Y'
   AND upper(bnm.name_value_tx) like 'IBM%' 



any way to make it run fast?

We are in Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
TNS for Linux: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production



Re: search on text column [message #273916 is a reply to message #273857] Fri, 12 October 2007 04:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I can't comment on the Text query, but the second query will do a full table scan of your table.
It's reading about 115,000 records a second - that's pretty good going.

You're getting a FTS because there are no indexes that you can apply.
For the second query, you could create a Function based index on upper(name_value_tx) - that should help.
Re: search on text column [message #273982 is a reply to message #273857] Fri, 12 October 2007 09:42 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

there exist a function based index-

CREATE INDEX BNM_FI ON BUSINESS_NAME 
(UPPER("NAME_VALUE_TX"))

Re: search on text column [message #274290 is a reply to message #273982] Mon, 15 October 2007 06:58 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
And the reason that you didn't list that index in your OP is........

[added by edit]
Mea Culpa - I missed that index in the Op.
tricky, as it's almost a page of text, but that's life for you.


Post the explain plan for the queries, and let's see what the Optimiser is doing.

[Updated on: Mon, 15 October 2007 06:59]

Report message to a moderator

Previous Topic: DB link problem
Next Topic: degradation after restart
Goto Forum:
  


Current Time: Sat Dec 10 01:20:15 CST 2016

Total time taken to generate the page: 0.13029 seconds