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

[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 :

SELECT /*+ USE_CONCAT */ COUNT(*) FROM DI_MATCH_KEY   
WHERE NORM_COUNTRY_CD = 'US'  
AND ((( NORM_CONAME_KEY1 ='WILM I'  OR 
NORM_CONAME_KEY2 = 'WILM I' OR NORM_CONAME_KEY23 = 'WILM I'
OR NORM_CONAME_KEYFIRST ='WILLIAM' )  
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

[debasisdas]

That time is normal for 250 million records.

[subashsavji]

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 :

SELECT /*+ USE_CONCAT */ COUNT(*) FROM DI_MATCH_KEY   
WHERE NORM_COUNTRY_CD = 'US'  
AND ((( NORM_CONAME_KEY1 ='WILM I'  OR 
NORM_CONAME_KEY2 = 'WILM I' OR NORM_CONAME_KEY23 = 'WILM I'
OR NORM_CONAME_KEYFIRST ='WILLIAM' )  
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

[amitpatel66]

Check out for explanation on USE_CONCAT hint