| Query to be tuned which uses a table hint /*+ USE_CONCAT */ in Oracle 10g. [message #299773] |
Wed, 13 February 2008 02:58  |
suneelchetty
Messages: 5
Registered: February 2008
Location: bangalore
|
Junior Member |
|
|
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
|
|
|
|
|
|
|
|
| Re: Query to be tuned which uses a table hint /*+ USE_CONCAT */ in Oracle 10g. [message #300182 is a reply to message #299773] |
Thu, 14 February 2008 08:57  |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
Try rewriting it as UNION:
SELECT SUM(CNTR) FROM
(
SELECT COUNT(*) CNTR FROM DI_MATCH_KEY
WHERE NORM_COUNTRY_CD = 'US' AND NORM_CONAME_KEY1 ='WILM I'
AND NORM_STATE_PROVINCE = 'CA'
UNION ALL
SELECT COUNT(*) CNTR FROM DI_MATCH_KEY
WHERE NORM_COUNTRY_CD = 'US' AND NORM_CONAME_KEY2 = 'WILM I'
AND NORM_STATE_PROVINCE = 'CA'
UNION ALL
SELECT COUNT(*) CNTR FROM DI_MATCH_KEY
WHERE NORM_COUNTRY_CD = 'US' AND NORM_CONAME_KEY23 = 'WILM I'
AND NORM_STATE_PROVINCE = 'CA'
UNION ALL
SELECT COUNT(*) CNTR FROM DI_MATCH_KEY
WHERE NORM_COUNTRY_CD = 'US' AND NORM_CONAME_KEYFIRST ='WILLIAM'
AND NORM_STATE_PROVINCE = 'CA'
UNION ALL
SELECT COUNT(*) CNTR FROM DI_MATCH_KEY
WHERE NORM_COUNTRY_CD = 'US' AND
NORM_ADDR_KEY2 = 'CALMN 12 3 OSAI'
) XX
Look at explain.
As I understand - each branch of union is supposed to use a different index.
HTH.
Michael
|
|
|
|
| Re: Query to be tuned which uses a table hint /*+ USE_CONCAT */ in Oracle 10g. [message #300203 is a reply to message #300182] |
Thu, 14 February 2008 10:38 |
suneelchetty
Messages: 5
Registered: February 2008
Location: bangalore
|
Junior Member |
|
|
I have observed the query which u have sent but my concern is that doing a sum of the count giving by union all will not be the result i am expecting b'coz the result will have duplicates so I want the result with out duplicates and the query which u sent ran in a second. Thanks a lot and if u could suggest me the result with out duplicates.
I want the query to give the count with out dupilcates.
Explain plan of this I am enclosing here. 
[Updated on: Thu, 14 February 2008 10:47] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Query to be tuned which uses a table hint /*+ USE_CONCAT */ in Oracle 10g. [message #300989 is a reply to message #300303] |
Mon, 18 February 2008 22:42 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
So it's returning 74,000 rows in about 3 minutes. How did you measure that? Did you fetch them all in SQL*Plus? Did you process them in a PL/SQL loop? Is that very slow in your environment? Depending on load and capacity, that sort of performance may be "normal".
Tell me how long this takes:
SELECT * FROM (
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')
)
WHERE ROWNUM > 1
This will ACCESS all of the data but return NONE of it. This gives you a benchmark best performance on top of which you will have to add time to fetch, transport, and render/process the rows.
If this is still slow, we tune the SQL. If it is fast, we look at other factors.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
