471,320 Members | 2,051 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,320 software developers and data experts.

Sub : Query to be tuned which uses a table hint /*+ USE_CONCAT */ in Oracle 10g.

I am firing a query which uses a table hint /*+ USE_CONCAT */ on a table which 250 million data with 13 indexes on it. The problem is query is taking a lot of time to execute i.e. more than 3 minutes.

Query :
Expand|Select|Wrap|Line Numbers
  1. SELECT /*+ USE_CONCAT */ COUNT(*) FROM DI_MATCH_KEY   
  2. WHERE NORM_COUNTRY_CD = 'US'  
  3. AND ((( NORM_CONAME_KEY1 ='WILM I'  OR 
  4. NORM_CONAME_KEY2 = 'WILM I' OR NORM_CONAME_KEY23 = 'WILM I'
  5. OR NORM_CONAME_KEYFIRST ='WILLIAM' )  
  6. AND NORM_STATE_PROVINCE = 'CA' ) OR NORM_ADDR_KEY2 = 'CALMN 12 3 OSAI')
Indexes for columns on this table are :

1.ADDRESS_SOURCE_CD
2.DUNS_NBR
3.AGN_ID(Primary Key)
4.SOURCE_SYSTEM
5.NORM_ADDR_KEY2, NORM_COUNTRY_CD
6.NORM_CITY, NORM_COUNTRY_CD, NORM_STATE_PROVINCE
7.NORM_CONAME_KEY23, NORM_COUNTRY_CD, NORM_STATE_PROVINCE
8.NORM_CONAME_KEY1, 9.NORM_COUNTRY_CD, .NORM_STATE_PROVINCE
10.NORM_COUNTRY_CD, NORM_STATE_PROVINCE
11.NORM_CONAME_KEYFIRST, NORM_COUNTRY_CD, NORM_STATE_PROVINCE
12.NORM_CONAME_KEY2, NORM_COUNTRY_CD, NORM_STATE_PROVINCE
13.NORM_PHONE_NBR, NORM_COUNTRY_CD

Can anyone help me in this regard to know why it is taking time and what is the use of this table hint
Feb 13 '08 #1
3 4365
debasisdas
8,127 Expert 4TB
That time is normal for 250 million records.
Feb 13 '08 #2
I am firing a query which uses a table hint /*+ USE_CONCAT */ on a table which 250 million data with 13 indexes on it. The problem is query is taking a lot of time to execute i.e. more than 3 minutes.

Query :
Expand|Select|Wrap|Line Numbers
  1. SELECT /*+ USE_CONCAT */ COUNT(*) FROM DI_MATCH_KEY   
  2. WHERE NORM_COUNTRY_CD = 'US'  
  3. AND ((( NORM_CONAME_KEY1 ='WILM I'  OR 
  4. NORM_CONAME_KEY2 = 'WILM I' OR NORM_CONAME_KEY23 = 'WILM I'
  5. OR NORM_CONAME_KEYFIRST ='WILLIAM' )  
  6. AND NORM_STATE_PROVINCE = 'CA' ) OR NORM_ADDR_KEY2 = 'CALMN 12 3 OSAI')
Indexes for columns on this table are :

1.ADDRESS_SOURCE_CD
2.DUNS_NBR
3.AGN_ID(Primary Key)
4.SOURCE_SYSTEM
5.NORM_ADDR_KEY2, NORM_COUNTRY_CD
6.NORM_CITY, NORM_COUNTRY_CD, NORM_STATE_PROVINCE
7.NORM_CONAME_KEY23, NORM_COUNTRY_CD, NORM_STATE_PROVINCE
8.NORM_CONAME_KEY1, 9.NORM_COUNTRY_CD, .NORM_STATE_PROVINCE
10.NORM_COUNTRY_CD, NORM_STATE_PROVINCE
11.NORM_CONAME_KEYFIRST, NORM_COUNTRY_CD, NORM_STATE_PROVINCE
12.NORM_CONAME_KEY2, NORM_COUNTRY_CD, NORM_STATE_PROVINCE
13.NORM_PHONE_NBR, NORM_COUNTRY_CD

Can anyone help me in this regard to know why it is taking time and what is the use of this table hint
its normal time. but you can try different indexes including this normal index
index on perticular tablespace, organisation index others it generally used in the datawarehousing concept
Feb 13 '08 #3
amitpatel66
2,367 Expert 2GB
Check out for explanation on USE_CONCAT hint
Feb 13 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by Mahesh Hardikar | last post: by
3 posts views Thread by hrishy | last post: by
2 posts views Thread by Pramod Ramachandran | last post: by
12 posts views Thread by zwasdl | last post: by
reply views Thread by Jim Kennedy | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.