| Filter in Oracle [message #340138] |
Mon, 11 August 2008 11:25  |
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 #341082 is a reply to message #340403] |
Sat, 16 August 2008 01:45  |
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 |
rleishman
Messages: 3728
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
