Home » SQL & PL/SQL » SQL & PL/SQL » SQL query
SQL query [message #190720] Thu, 31 August 2006 23:37 Go to next message
uicmxz
Messages: 48
Registered: July 2006
Member
I have huge address table (55 mill. records)
with combine keys holder_id, holder_cd, and address code.
select * from address table
/
holder_id holder_cd address code address_line1
123 PA 1 245 Grove dr.
124 PR 2 2412 Rige ave

And also I have temp table that has three columns customer_id, provider_id, and prescr_id. All have different address and I need to select address for customer_id, provider_id, and prescr_id. I have indexes for all ID, but it is not help. The query was running more than our and I cancel it.
I run explain plan that show me full table scan for temp table. What is the best way create query and how to improve performance.
My query like this:
select
holder_id, holder_cd, address_line1
from address where holder_id in
(select customer_id from temp)
where (holder_cd = 'PA'and address code = '1')
union
select
holder_id, holder_cd, address_line1
from address where holder_id in
(select provider_id from temp)
where (holder_cd = 'RR'and address code = '2')
union
select
holder_id, holder_cd, address_line1
from address where holder_id in
(select prescr_id from temp)
where (holder_cd = 'CR'and address code = '3')

What I am doing wrong? How make query that will run fast?

Thanks



Re: SQL query [message #190734 is a reply to message #190720] Fri, 01 September 2006 01:14 Go to previous messageGo to next message
sandeepk7
Messages: 137
Registered: September 2006
Senior Member

Hi,
Try anyone of there query

select
holder_id, holder_cd, address_line1
from address where holder_cd = 'PA'and address code = '1' and exists (select 'X' from temp where customer_id=holder_id)
union
select
holder_id, holder_cd, address_line1
from address where holder_cd = 'RR'and address code = '2' and exists (select 'X' from temp where provider_id=holder_id)
union
select
holder_id, holder_cd, address_line1
from address where holder_cd = 'CR'and address code = '3' and exists (select 'X' from temp where prescr_id=holder_id)


select
holder_id, holder_cd, address_line1
from address where (holder_cd = 'PA'and address code = '1' and exists (select 'X' from temp where customer_id=holder_id)) or (
(holder_cd = 'RR'and address code = '2' and exists (select 'X' from temp where provider_id=holder_id)) or
(holder_cd = 'CR'and address code = '3' and exists (select 'X' from temp where prescr_id=holder_id))

Regards
Sandy


Re: SQL query [message #190739 is a reply to message #190720] Fri, 01 September 2006 01:47 Go to previous messageGo to next message
pareshr
Messages: 18
Registered: August 2006
Location: Ahmedabad
Junior Member
Hi,


Please Try with this query

SELECT
holder_id, holder_cd, address_line1
FROM address inner join temp ON (holder_id =customer_id)
WHERE holder_cd = 'PA' AND address code = '1'
UNION
SELECT
holder_id, holder_cd, address_line1
FROM address inner join temp ON (provider_id =customer_id)
WHERE holder_cd = 'RR' AND address code = '2'
UNION
SELECT
holder_id, holder_cd, address_line1
FROM address inner join temp ON (provider_id =customer_id)
WHERE prescr_id = 'CR' AND address code = '3'

and confirm that abt whether index is created on holder_id column or not. And if index is there then can u give me explain plan for this query. So i can analyse it.
Re: SQL query [message #190740 is a reply to message #190739] Fri, 01 September 2006 01:48 Go to previous messageGo to next message
pareshr
Messages: 18
Registered: August 2006
Location: Ahmedabad
Junior Member

Hi,

Sorry instead of Previous query use this one

SELECT
holder_id, holder_cd, address_line1
FROM address inner join temp ON (customer_id =holder_id)
WHERE holder_cd = 'PA' AND address code = '1'
UNION
SELECT
holder_id, holder_cd, address_line1
FROM address inner join temp ON (provider_id =holder_id)
WHERE holder_cd = 'RR' AND address code = '2'
UNION
SELECT
holder_id, holder_cd, address_line1
FROM address inner join temp ON (provider_id =holder_id)
WHERE prescr_id = 'CR' AND address code = '3'
Re: SQL query [message #190752 is a reply to message #190720] Fri, 01 September 2006 02:13 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You will be doing 3 full table scans on TEMP, as you have 3 subqueries of the form
SELECT customer_id FROM temp

That query has to get data from every row in the table TEMP, so a FTS is the best approach for this.

In order to minimise the sorts performed, and to use the index on ADDRESS, I might try rewriting the query like this

select holder_id, holder_cd, address_line1
from   address
      ,(select customer_id temp_id,'PA' temp_cd,'1' temp_code from temp
        union all
        select provider_id temp_id,'RR' temp_cd,'2' temp_code from temp
        union all
        select prescr_id   temp_id,'CR' temp_cd,'3' temp_code from temp) temp_ids
where holder_id = temp_Id
and   holder_cd = temp_cd
and   address_code = temp_code;


This should be quicker getting the data back from TEMP, and should only access ADDRESS once rather than 3 times.

Thinking about it, you can get away with only one FTS of TEMP if you use an artificial rowsource to let you split each row of TEMP up into 3 rows, like this:

select holder_id, holder_cd, address_line1
from   address
      ,(select decode(lvl,1,customer_id,2,provider_id,3,prescr_id) temp_id
              ,decode(lvl,1,'PA',2,'RR',3,'CR')                    temp_cd
              ,decode(lvl,1,'1' ,2,'2' ,3,'3')                     temp_addr_code
        from   temp
             ,(select level lvl from dual connect by level where level <=3) rowsource) temp_Ids
where holder_id = temp_Id
and   holder_cd = temp_cd
and   address_code = temp_addr_code;


Previous Topic: compute sum of balances based on year
Next Topic: How can i find Oracle Procedure File
Goto Forum:
  


Current Time: Wed Dec 07 20:16:00 CST 2016

Total time taken to generate the page: 0.10464 seconds