-
Originally posted by fossil
Hi,
2. For joining large tables (million's of rows) you don't
need an index on any column in the where clause.
3. Multi table query will join the tables first and then
filter the rows according to the where clause. You should
not be having an index lookup for the filter.
quite wrong indeed!
I suggest you try dbms_xplan.display in 9i and you will see how predicates are applied before join to reduce the result set! (of course this is not always the case but when the index is selective this will be)
-
Jmodic,
You should have given your points to prove me wrong instead of furioulsy discarding what I said.
Thanks,
fossil
-
Pando,
Point(according to original poster) is what would it take to run the query faster if you are having
multi million rows tables.
Having indexes on the columns of the where clause would apply the predicate first and it will reduce the resultset, however, its going to make query run much slower than not having indexes on it. 15% for a multi million rows table is quite a big result set.
Not having indexes on the columns in the where clause would have CBO do Full table scan and join the tables first and then apply filter(see the explain plan).
Indexes are preferred when selecting a very very small number of rows may be upto 5%.
Moreover, must try query both ways to and compare the timing.
Thanks,
fossil
-
Originally posted by fossil
Pando,
Point(according to original poster) is what would it take to run the query faster if you are having
multi million rows tables.
Having indexes on the columns of the where clause would apply the predicate first and it will reduce the resultset, however, its going to make query run much slower than not having indexes on it. 15% for a multi million rows table is quite a big result set.
Not having indexes on the columns in the where clause would have CBO do Full table scan and join the tables first and then apply filter(see the explain plan).
Indexes are preferred when selecting a very very small number of rows may be upto 5%.
Moreover, must try query both ways to and compare the timing.
Thanks,
No, no no. If you put an index on the large table the CBO does not have to use it. It will look at the predicate, the table and index statistics, and make a choice based on minimum cost.
This stuff about "Indexes are preferred when selecting a very very small number of rows may be upto 5%." is all wrong .. index usage is based on block selectivity not row selectivity. Read the link I posted previously.
What you need to do is go and read the section in the Concepts Guide on how the optimizer works.
-
2. For joining large tables (million's of rows) you don't
need an index on any column in the where clause.
Not true if low number of rows is gonna be returned, the poster in no where states that this is going to return many rows and your statement simply doesnt fit in general
3. Multi table query will join the tables first and then
filter the rows according to the where clause. You should
not be having an index lookup for the filter.
Here in no where you mentions about index, too general, does not apply to all cases, plus the poster says his indexes are being used by his predicates which means there are index and not as you said in your last post "Not having indexes on the columns in the where clause would have CBO do Full table scan". In his case his predicates are applied first for t1 then joined
As for original poster, you dont compare costs, that is used internally, not by you! And you should post the query plan for this kind of questions
-
Slim Dave,
"No, no no. If you put an index on the large table the CBO does not have to use it. It will look at the predicate, the table and index statistics, and make a choice based on minimum cost."
Yes, CBO would choose the plan based on mininum cost, however, having minimum cost doesn't make the query run faster.
Check the cost when you have indexes on all the columns in the where clause, the cost would be less when compared to not having indexes
on the columns of the where clause.
Now compare the timing by running the querty w/wo indexes.
fossil
-
Fossil,
You are pulling our legs, aren't you? Of course, it's April Fools! Didn't get it untill now....
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Pando,
"Not true if low number of rows is gonna be returned, the poster in no where states that this is going to return many rows and your statement simply doesnt fit in general"
"Here in no where you mentions about index, too general, does not apply to all cases, plus the poster says his indexes are being used by his predicates which means there are index and not as you said in your last post "Not having indexes on the columns in the where clause would have CBO do Full table scan". In his case his predicates are applied first for t1 then joined"
Can your statement fits in general ?.
My statement fits in general, here is how ?.
If you have small tables having small number of rows, then
you don't need indexes as tables are small, FTS will be faster
then index lookup.
If you very large tables multi million rows and joinig them then
also FTS will be fastest. Will do the join first, sorts them (if you
have order by) and starts returning rows while filtering.
Not having indexes indeed applies to almost all the cases
just on the cost of query returning few rows.
"As for original poster, you dont compare costs, that is used internally, not by you! And you should post the query plan for this kind of questions"
Cost can be misleading ?. Run the queries both ways and check
the timing.
fossil
-
Originally posted by fossil
If you very large tables multi million rows and joinig them then
also FTS will be fastest. Will do the join first, sorts them (if you
have order by) and starts returning rows while filtering.
This is so wrong it must be an april fool's day thing I guess.
You are saying that if I have a 1 billion row table joined to a fifty row table, and I have a predicate on the billion row table that filters out all but one row, then Oracle will ...
- Join the fifty row table to the 1 billion row table
- Order the result set
- Filter the ordered set to remove all but those based on the single row of the billion row table?
There are probably more dumb ways to execute such a query, but this would be way up there on the dumbness scale.
-
SlimDave,
Didn't you read what I am saying ?. You did not.
Looks like you don't have anything left to prove your point
"for having indexes on the columns of the where clause" and
lost sense of the problem of the original poster.
fossil
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|