Home » RDBMS Server » Performance Tuning » How to optimize this sql
How to optimize this sql [message #124926] Wed, 22 June 2005 06:27 Go to next message
pjsiong
Messages: 3
Registered: June 2005
Junior Member
Hi, I have a sql which is as follow:

select a.hawb_no,b.chk_out_date
from table_A a ,Table_B b
where a.COMPANY_CODE = 'CT'
and a.EM_DATE between '20050616' and '20050622'
and b.Hawb_No=a.HAWB_NO

and the table_a has company_code+em_date been indexed, table_b
has field hawb_no been indexed.
but the explain plan for this sql always so full scan on table_b
even I have analyze and gather stats for table_b. I expect that the index on table_b should be used for faster execution.

Anyone can help me on this?
Thanks
Re: How to optimize this sql [message #124929 is a reply to message #124926] Wed, 22 June 2005 06:32 Go to previous messageGo to next message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
HI

Since you have mentioned gathering of indexes, CBO must be getting used in execution of this query.

FTS on a table is not always bad. Since CBO is getting used, it may have found that doing FTS on table_b is the cheapest option available.

Also, for further investigation, if you wish to perform, use INDEX hint in the query hinting to use index on table_b and then compare the two execution plans. That'll make the reason why FTS is being used, more clear.

Regds
Girish
Re: How to optimize this sql [message #124930 is a reply to message #124926] Wed, 22 June 2005 06:53 Go to previous messageGo to next message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
What is the datatype of table_a.em_date? If it's a DATE, then you should code:
SELECT a.hawb_no
,      b.chk_out_date
FROM   table_a      a
,      table_b      b
WHERE  a.company_code = 'CT'
AND    a.em_date BETWEEN TO_DATE('20050616','YYYYMMDD')
                     AND TO_DATE('20050622235959','YYYYMMDDHH24MISS')
AND    a.hawb_no = b.hawb_no
/
Re: How to optimize this sql [message #124931 is a reply to message #124930] Wed, 22 June 2005 06:56 Go to previous messageGo to next message
pjsiong
Messages: 3
Registered: June 2005
Junior Member
the data type for EM_DATE is varchar2
Re: How to optimize this sql [message #124933 is a reply to message #124929] Wed, 22 June 2005 06:58 Go to previous messageGo to next message
pjsiong
Messages: 3
Registered: June 2005
Junior Member
FTS may not be a bad choice for small table...but the table_b has about 1 million records..how can I be convinced that FTS is a better choice than index search?
Re: How to optimize this sql [message #124936 is a reply to message #124933] Wed, 22 June 2005 07:12 Go to previous messageGo to next message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
how can I be convinced that FTS is a better choice than index search?

For this, you need to compare both execution plans, one thats using FTS (with no hints specified & chosen by CBO), other with forced use of index on table_b (using INDEX hint).
Re: How to optimize this sql [message #124958 is a reply to message #124926] Wed, 22 June 2005 09:11 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Gotta show us some more info. Capture a session of you gathering optimizer statistics on both tables, and then running your query in autotrace mode showing both explain plan and statistics. Post all that for us to have a look.
Re: How to optimize this sql [message #124963 is a reply to message #124931] Wed, 22 June 2005 09:41 Go to previous message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
If EM_DATE holds DATEs, then why is it defined as a VARCHAR2? The optimizer is treating this column like the string it's defined as instead of like the date that it is. Moreover, someday, you will get junk data in that column.

For more information, please read this thread.
Previous Topic: Need some info Capacity Planning ---- Urgent
Next Topic: Archival of Production Database
Goto Forum:
  


Current Time: Fri Mar 29 07:12:20 CDT 2024