Home » RDBMS Server » Performance Tuning » performance - index creation on text column (Oracle 10g,Solaris)
performance - index creation on text column [message #598064] Thu, 10 October 2013 04:29 Go to next message
prejib
Messages: 126
Registered: March 2009
Location: India
Senior Member
Hi ,

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 . Please note that the nullable columns can have data

SELECT * 
FROM   address 
WHERE  address1 = 'a' 
       AND address2 = 'b' 
       AND address3 = 'c' 
       AND city = 'xy' 
       AND state IS NULL 
       AND county IS NULL 
       AND country = 'IN' 
       AND postcode = '45';  

SQL> 
SQL> drop table address purge;

Table dropped.

SQL> 
SQL> CREATE TABLE address
  2    (
  3  	  address_id VARCHAR2 (40) NOT NULL,
  4  	  address1   VARCHAR2 (40) NOT NULL,
  5  	  address2   VARCHAR2 (40) NULL,
  6  	  address3   VARCHAR2 (40) NULL,
  7  	  city	     VARCHAR2(50) NOT NULL,
  8  	  state      VARCHAR2(50) NULL,
  9  	  county     VARCHAR2(50) NULL,
 10  	  country    VARCHAR2(50) NULL,
 11  	  postcode   VARCHAR2(10) NOT NULL,
 12  	  flag	     VARCHAR2(5) NULL,
 13  	  TYPE	     VARCHAR2(30) NULL,
 14  	  CONSTRAINT address_id_pk PRIMARY KEY( address_id)
 15    );

Table created.

SQL> 
SQL> INSERT INTO address
  2  		 (address_id,
  3  		  address1,
  4  		  address2,
  5  		  address3,
  6  		  city,
  7  		  state,
  8  		  county,
  9  		  country,
 10  		  postcode,
 11  		  flag,
 12  		  TYPE)
 13  VALUES	 ( '1',
 14  		  'a',
 15  		  'b',
 16  		  'c',
 17  		  'xy',
 18  		  NULL,
 19  		  NULL,
 20  		  'IN',
 21  		  '45',
 22  		  NULL,
 23  		  NULL);

1 row created.

SQL> 
SQL> INSERT INTO address
  2  		 (address_id,
  3  		  address1,
  4  		  address2,
  5  		  address3,
  6  		  city,
  7  		  state,
  8  		  county,
  9  		  country,
 10  		  postcode,
 11  		  flag,
 12  		  TYPE)
 13  VALUES	 ( '2',
 14  		  'b',
 15  		  'b',
 16  		  'c',
 17  		  'xy',
 18  		  NULL,
 19  		  NULL,
 20  		  'IN',
 21  		  '45',
 22  		  NULL,
 23  		  NULL);

1 row created.

SQL> 
SQL> INSERT INTO address
  2  		 (address_id,
  3  		  address1,
  4  		  address2,
  5  		  address3,
  6  		  city,
  7  		  state,
  8  		  county,
  9  		  country,
 10  		  postcode,
 11  		  flag,
 12  		  TYPE)
 13  VALUES	 ( '3',
 14  		  'c',
 15  		  'b',
 16  		  'c',
 17  		  'xy',
 18  		  NULL,
 19  		  NULL,
 20  		  'IN',
 21  		  '45',
 22  		  NULL,
 23  		  NULL);

1 row created.

SQL> 
SQL> COMMIT;

Commit complete.

SQL> 
SQL> SELECT *
  2  FROM   address
  3  WHERE  address1 = 'a'
  4  	    AND address2 = 'b'
  5  	    AND address3 = 'c'
  6  	    AND city = 'xy'
  7  	    AND state IS NULL
  8  	    AND county IS NULL
  9  	    AND country = 'IN'
 10  	    AND postcode = '45';


ADDRESS_ID ADDRESS1 ADDRESS2 ADDRESS3
---------------------------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
CITY STATE COUNTY
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
COUNTRY POSTCODE FLAG TYPE
-------------------------------------------------- ---------- ----- ------------------------------
1 a b c
xy
IN 45


SQL>
SQL> spool off;
  • Attachment: address.sql
    (Size: 2.26KB, Downloaded 1848 times)

[Updated on: Thu, 10 October 2013 05:04] by Moderator

Report message to a moderator

Re: performance - index creation on text column [message #598065 is a reply to message #598064] Thu, 10 October 2013 04:39 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
prejib wrote on Thu, 10 October 2013 14:59
I would like to know how can we fasten the query and improve the performance of the query by creating index on these columns


what performance issue are you facing?
Which columns do you mostly query?
What is the bottleneck that you have identified or think could be a probable issue?
What is the use of mentioning NOT NULL for the primary key column? It is implicitly not null.
Simply creating indexes does not necessarily increases the performance.

Please post the execution plan. The sticky on top of this forum has a lot of useful information.

Regards,
Lalit
icon10.gif  Re: performance - index creation on text column [message #598068 is a reply to message #598064] Thu, 10 October 2013 05:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator


Create an index (maybe unique) on "country,postcode,city,address1,address2,address3"

Re: performance - index creation on text column [message #598071 is a reply to message #598065] Thu, 10 October 2013 05:14 Go to previous messageGo to next message
prejib
Messages: 126
Registered: March 2009
Location: India
Senior Member
what performance issue are you facing?
-->While querying big table of text field the query will become slow .
Which columns do you mostly query?--> Please see the columns in where condition part of the below query
What is the bottleneck that you have identified or think could be a probable issue?--> slowness of the output
What is the use of mentioning NOT NULL for the primary key column? It is implicitly not null. --> you are correct
[code ]SELECT *
FROM address
WHERE address1 = 'a' AND address2 = 'b' AND address3 = 'c' AND city = 'xy'
AND state IS NULL AND county IS NULL AND country = 'IN'
AND postcode = '45'; [/code]
Execution plan is
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 948192439

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     1 |   224 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| ADDRESS |     1 |   224 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

   1 - filter("STATE" IS NULL AND "COUNTY" IS NULL AND "ADDRESS1"='a'
              AND "ADDRESS2"='b' AND "ADDRESS3"='c' AND "CITY"='xy' AND
              "COUNTRY"='IN' AND "POSTCODE"='45')

Note
-----
   - dynamic sampling used for this statement

19 rows selected.

Re: performance - index creation on text column [message #598073 is a reply to message #598071] Thu, 10 October 2013 05:29 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
prejib wrote on Thu, 10 October 2013 15:44
Which columns do you mostly query?--> Please see the columns in where condition part of the below query
SELECT * 
FROM   address 
WHERE  address1 = 'a'        AND address2 = 'b'        AND address3 = 'c'        AND city = 'xy' 
       AND state IS NULL        AND county IS NULL        AND country = 'IN' 
       AND postcode = '45';  



What is the purpose of the filter AND county IS NULL AND country = 'IN' in the predicate? It's counterproductive.

   1 - filter("STATE" IS NULL AND "COUNTY" IS NULL AND "ADDRESS1"='a'
              AND "ADDRESS2"='b' AND "ADDRESS3"='c' AND "CITY"='xy' AND
              "COUNTRY"='IN' AND "POSTCODE"='45')



Because of the above mentioned mistake, the smae filter is applied to the predicate.
icon10.gif  Re: performance - index creation on text column [message #598074 is a reply to message #598071] Thu, 10 October 2013 05:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Thu, 10 October 2013 12:07


Create an index (maybe unique) on "country,postcode,city,address1,address2,address3"


Re: performance - index creation on text column [message #598097 is a reply to message #598074] Thu, 10 October 2013 07:53 Go to previous messageGo to next message
prejib
Messages: 126
Registered: March 2009
Location: India
Senior Member
Thanks ..

Since all these fields are text , if we add context index on these column will that help?

create index from mt address_idx on address (address1, address2, address3, city, state, county, country postcode) indextype is ctxsys.context;

Re: performance - index creation on text column [message #598101 is a reply to message #598097] Thu, 10 October 2013 07:59 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
prejib wrote on Thu, 10 October 2013 18:23
Since all these fields are text , if we add context index on these column will that help?

create index from mt address_idx on address (address1, address2, address3, city, state, county, country postcode) indextype is ctxsys.context;



This is not the case where you need to use context index. It is used when your text consists of large documents.
Re: performance - index creation on text column [message #598106 is a reply to message #598097] Thu, 10 October 2013 08:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
if we add context index on these column will that help?


No unless you need to use operator LIKE.

Re: performance - index creation on text column [message #598177 is a reply to message #598106] Fri, 11 October 2013 00:46 Go to previous message
prejib
Messages: 126
Registered: March 2009
Location: India
Senior Member
Thanks a lot....
Previous Topic: about increasing performance speed of slow running package
Next Topic: Please help me to tune this procedure
Goto Forum:
  


Current Time: Thu Mar 28 10:00:32 CDT 2024