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

**debasisdas** · Feb 13 '08, 08:44 AM

That time is normal for 250 million records.

**subashsavji** · Feb 13 '08, 10:51 AM

Originally posted by suneelchetty

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 :
[code=oracle]
SELECT /*+ USE_CONCAT */ COUNT(*) FROM DI_MATCH_KEY
WHERE NORM_COUNTRY_CD = 'US'
AND ((( NORM_CONAME_KEY 1 ='WILM I' OR
NORM_CONAME_KEY 2 = 'WILM I' OR NORM_CONAME_KEY 23 = 'WILM I'
OR NORM_CONAME_KEY FIRST ='WILLIAM' )
AND NORM_STATE_PROV INCE = 'CA' ) OR NORM_ADDR_KEY2 = 'CALMN 12 3 OSAI')[/code]

Indexes for columns on this table are :

1.ADDRESS_SOURC E_CD
2.DUNS_NBR
3.AGN_ID(Primar y Key)
4.SOURCE_SYSTEM
5.NORM_ADDR_KEY 2, NORM_COUNTRY_CD
6.NORM_CITY, NORM_COUNTRY_CD , NORM_STATE_PROV INCE
7.NORM_CONAME_K EY23, NORM_COUNTRY_CD , NORM_STATE_PROV INCE
8.NORM_CONAME_K EY1, 9.NORM_COUNTRY_ CD, .NORM_STATE_PRO VINCE
10.NORM_COUNTRY _CD, NORM_STATE_PROV INCE
11.NORM_CONAME_ KEYFIRST, NORM_COUNTRY_CD , NORM_STATE_PROV INCE
12.NORM_CONAME_ KEY2, NORM_COUNTRY_CD , NORM_STATE_PROV INCE
13.NORM_PHONE_N BR, 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

**amitpatel66** · Feb 13 '08, 11:48 AM

Check out for explanation on USE_CONCAT hint

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