Home » RDBMS Server » Performance Tuning » Filter in Oracle
Filter in Oracle [message #340138] Mon, 11 August 2008 11:25 Go to next message
krshnn592
Messages: 5
Registered: August 2008
Junior Member
Hi,

I have the following query

SELECT c.customer_name, c.creation_date
FROM ra_customers c
WHERE c.creation_date > SYSDATE - 30
OR customer_id IN
(SELECT customer_id FROM so_headers_all h
WHERE h.open_flag = 'Y');

EXPLAIN PLAN for this query is

SELECT STATEMENT
FILTER
TABLE ACCESS FULL RA_CUSTOMERS
TABLE ACCESS BY INDEX ROWID SO_HEADERS_ALL
INDEX RANGE SCAN SO_HEADERS_N1


Can anyone tell me how predicate c.creation_date > SYSDATE - 30
is performed by FILTER OPERATION.Is it like this:--

First TABLE ACCESS FULL RA_CUSTOMERS is executed.
Now for each row in RA_CUSTOMERS,first condition c.creation_date > SYSDATE - 30 is checked.If satisfied then returned,Otherwise
the condition
customer_id IN
(SELECT customer_id FROM so_headers_all h
WHERE h.open_flag = 'Y')
is cheked in TABLE ACCESS BY INDEX ROWID SO_HEADERS_ALL
INDEX RANGE SCAN SO_HEADERS_N1.
If satisfied row is returned.

Regards

Krishnan


Re: Filter in Oracle [message #340141 is a reply to message #340138] Mon, 11 August 2008 11:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read anf follow OraFAQ Forum Guide.

Regards
Michel
Re: Filter in Oracle [message #340403 is a reply to message #340138] Tue, 12 August 2008 11:33 Go to previous messageGo to next message
huiyi
Messages: 3
Registered: August 2008
Location: china
Junior Member

TABLE ROWS?
AFTER FILTER ROWS?
INDEX INFORMATION?
Re: Filter in Oracle [message #341082 is a reply to message #340403] Sat, 16 August 2008 01:45 Go to previous messageGo to next message
krshnn592
Messages: 5
Registered: August 2008
Junior Member
I have not understood what u have told.

Can anyone clear my understanding.I know that tis is stupid question.Please excuse me for that.

Regards
Krisnan
Re: Filter in Oracle [message #341225 is a reply to message #341082] Sun, 17 August 2008 21:50 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Your plan says that table RA_CUSTOMERS is read first. Oracle will always apply a filter as soon as it has enough data to do so.

In your case, the first WHERE clause will be applied during the full table scan of RA_CUSTOMERS.

Since the subquery is on an OR predicate, it will only be performed for those rows that fail the first predicate.

Ross Leishman
Re: Filter in Oracle [message #341351 is a reply to message #341225] Mon, 18 August 2008 08:34 Go to previous message
krshnn592
Messages: 5
Registered: August 2008
Junior Member
Hi Ross Leishman ,


Many Many thanks for your response.

Thanks
krishnan

[Updated on: Mon, 18 August 2008 08:34]

Report message to a moderator

Previous Topic: Gather Schema stats
Next Topic: dbms job performance issue
Goto Forum:
  


Current Time: Sat Dec 10 13:08:04 CST 2016

Total time taken to generate the page: 0.09563 seconds