Home » SQL & PL/SQL » SQL & PL/SQL » Performance Tuning (Performance Tuning)
Performance Tuning [message #414170] Mon, 20 July 2009 12:10 Go to next message
Praseeja
Messages: 12
Registered: February 2006
Junior Member
Hi,

I am trying to tune a SQL query which is always going into a Full table Scan. Can you please advise me for the below query to avoid Full table scan

Select * from
FROM C CS LEFT OUTER JOIN (
SELECT ADT.VAL, CSCH.VAL
FROM CC C1, ADJ A
WHERE C1.TYPE = A.TYPE
AND A.CHAR_TYPE_ = 'char') CSA
ON ( CS.ID = CSA.ID), b
where CS.CS_TYPE = 'TYPE1'
and b.id = cs.id
and b.seq = 1
and b.user = 'USER'


Index on tables as below:
B - id and seq
C - id
CC - id, type and Seq_num
ADJ - TYPE

Since the table CASE_CHAR is huge table , i wanted to avoid a full table scan on this table. I wanted the report to display the details of the table C even if data is not existing in the table CC. But the table CC should be displayed only if the column type matches with the Type Code in the ADJ table.

Grateful if could please advise on tuning the above query.


Thanks!!!
Re: Performance Tuning [message #414172 is a reply to message #414170] Mon, 20 July 2009 12:16 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Post formatted SQL using <code tags> as describe in above
Re: Performance Tuning [message #414174 is a reply to message #414170] Mon, 20 July 2009 12:55 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Praseeja wrote on Mon, 20 July 2009 13:10
Hi,

I am trying to tune a SQL query which is always going into a Full table Scan. Can you please advise me for the below query to avoid Full table scan

Select * from
FROM C CS LEFT OUTER JOIN (
SELECT ADT.VAL, CSCH.VAL
FROM CC C1, ADJ A
WHERE C1.TYPE = A.TYPE
AND A.CHAR_TYPE_ = 'char') CSA
ON ( CS.ID = CSA.ID), b
where CS.CS_TYPE = 'TYPE1'
and b.id = cs.id
and b.seq = 1
and b.user = 'USER'


Index on tables as below:
B - id and seq
C - id
CC - id, type and Seq_num
ADJ - TYPE

Since the table CASE_CHAR is huge table , i wanted to avoid a full table scan on this table


You have succeeded. there is no full table scan on CASE_CHAR. As a matter of fact, there is no scan of it at all as it is not in your query.
Re: Performance Tuning [message #414249 is a reply to message #414174] Tue, 21 July 2009 03:31 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
joy_division wrote on Mon, 20 July 2009 19:55
Praseeja wrote on Mon, 20 July 2009 13:10
Hi,

I am trying to tune a SQL query which is always going into a Full table Scan. Can you please advise me for the below query to avoid Full table scan

Select * from
FROM C CS LEFT OUTER JOIN (
SELECT ADT.VAL, CSCH.VAL
FROM CC C1, ADJ A
WHERE C1.TYPE = A.TYPE
AND A.CHAR_TYPE_ = 'char') CSA
ON ( CS.ID = CSA.ID), b
where CS.CS_TYPE = 'TYPE1'
and b.id = cs.id
and b.seq = 1
and b.user = 'USER'


Index on tables as below:
B - id and seq
C - id
CC - id, type and Seq_num
ADJ - TYPE

Since the table CASE_CHAR is huge table , i wanted to avoid a full table scan on this table


You have succeeded. there is no full table scan on CASE_CHAR. As a matter of fact, there is no scan of it at all as it is not in your query.



Even better: this will perform like a blast. Within seconds it will return with the error that CSA.ID is not defined. Or that you used "from from".

Anonymizing a query is fine, but make sure that it still is valid
Re: Performance Tuning [message #414300 is a reply to message #414249] Tue, 21 July 2009 08:01 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Frank has better eyes than me.
Previous Topic: Sql to loop through date
Next Topic: Query using
Goto Forum:
  


Current Time: Sat Dec 10 05:24:21 CST 2016

Total time taken to generate the page: 0.11528 seconds