| Theoretical Question Regarding Full Table Scans [message #142192] |
Thu, 13 October 2005 13:25  |
scottwmackey
Messages: 515
Registered: March 2005
|
Senior Member |
|
|
I have a query similar to the following
SELECT /*+ ORDERED FULL(a) PARALLEL (a) FULL(b) PARALLEL (b) FULL(c) PARALLEL (c) FULL(d) PARALLEL (d) */
*
FROM desc_a a,
desc_b b,
desc_c c,
desc_d d,
desc_e e,
desc_f f,
(SELECT /*+ FULL(m_in) PARALLEL (m_in) FULL (o) PARALLEL (o)*/
m_in.*,
o.expiration_dt,
o.master_id o_master_id
FROM master m_in,
option o
WHERE m_in.master_id = o.master_id(+)
) m
WHERE b.id(+) = a.id
AND c.id(+) = a.id
AND d.id(+) = a.id
AND e.id(+) = a.id
AND f.id(+) = a.id
AND m.cip(+) = a.cip
Here's what I know to be always true. desc_a will have around 500K records. desc_b, c, d, e, and f will have as many or fewer, but never more than desc_a. id is unique for each table, thus all rows for all of these tables will be hit once and only once. master may contain about 10% more records than desc_a. For each run of the query, about 90% or more of the rows in master will be selected. option has approximately 25% of the rows that master has. For each query, all rows in option should be selected.
So here's my question, is there any reason I shouldn't be doing a full table scan on all of these tables and trying to convince Oracle to hash join them? I have analyzed the tables and the CBO still wants to join all of these by index and perform nested loops. Everything I have read regarding this says the slowest possible query is an index search that will hit every row. So, here's the kicker. When I let Oracle do what it want's, i.e. without hints, the query finishes about 4 minutes. When I add the hints to do what I thought was supposed to be fast, it doesn't seem to ever finish. Could this have anything to do with CPU (since hashes are CPU intensive) or RAM (maybe it's requiring virtual memory and doing a lot of swapping to and from disk). Could my results change dramatically, i.e. the full table scans become much faster than the index scans, when I move to a different box. Anybody have any experience with this or just some insights?
[Updated on: Thu, 13 October 2005 13:26] Report message to a moderator |
|
|
|
| Re: Theoretical Question Regarding Full Table Scans [message #142203 is a reply to message #142192] |
Thu, 13 October 2005 15:24  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Can we assume that your tables have a bunch of other columns that are being selected that are not indexed, so that oracle can't simply read the indexes and get the answer but it has to read the index and go hit the tables?
Does oracle (without hints) do a full scan of any of your tables? Not even desc_a and/or master?
Any partitions or bitmap indexes or anything else unusual?
Any difference if you have or don't have the parallel hint in combination with the full hints?
Perhaps it can scan those indexes and join them together quickly, which I would think is likely. Can we see the 2 plans?
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
