OraFAQ Forum: Performance Tuning » How to optimize this sql

Home » RDBMS Server » Performance Tuning » How to optimize this sql

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to optimize this sql [message #124926]

Wed, 22 June 2005 06:27

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

Report message to a moderator

Re: How to optimize this sql [message #124929 is a reply to message #124926]

Wed, 22 June 2005 06:32

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

Report message to a moderator

Re: How to optimize this sql [message #124930 is a reply to message #124926]

Wed, 22 June 2005 06:53

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:

Code: [Select all] [Show/ hide]

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
/

Report message to a moderator

Re: How to optimize this sql [message #124931 is a reply to message #124930]

Wed, 22 June 2005 06:56

pjsiong
Messages: 3
Registered: June 2005

Junior Member

the data type for EM_DATE is varchar2

Report message to a moderator

Re: How to optimize this sql [message #124933 is a reply to message #124929]

Wed, 22 June 2005 06:58

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?

Report message to a moderator

Re: How to optimize this sql [message #124936 is a reply to message #124933]

Wed, 22 June 2005 07:12

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).

Report message to a moderator

Re: How to optimize this sql [message #124958 is a reply to message #124926]

Wed, 22 June 2005 09:11

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.

Report message to a moderator

Re: How to optimize this sql [message #124963 is a reply to message #124931]

Wed, 22 June 2005 09:41

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.

Report message to a moderator

Previous Topic:	Need some info Capacity Planning ---- Urgent
Next Topic:	Archival of Production Database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 03 02:38:26 CDT 2025