index strategy on SQL tuning

Hi,

For the following query, the cost of table join between t0, t1 and t3 is very expensive. For all t1's columns used in the where clauses, I created one composite index on t1 to improve the table join. The execution plan shows the index is being used. However, the cost only reduces 10%. The actual execution time does not change too much, either. Any suggestion? Thank you.

What's your general indexing strategy? Assuming I am not concerned about the cost of maintaining the index, is it the best strategy to create indexes on ALL the columns used in the where clauses?


SELECT t1.proj_id
FROM client t0, proj t1, ref t2, proj_case t3, background_report t4
WHERE t0.comp_id IN (:sys_b_00)
AND t0.case_id = t1.assigned_to_case_id
AND t1."STATUS" = :sys_b_01
AND t1.ref_id = t2.ref_id
AND t2.ref_status IN (:sys_b_02, :sys_b_03, :sys_b_04)
AND t1.primary_case_id = t3.proj_case_id
AND t3.background_report_id = t4.background_report_id(+)

If your SQL outputs more than 15 % of rows from a table, then you don't need to create an index.

How ever, for efficient joining between 2 tables, you need to have indexes on both tables.

Tamil

Quote:

---

Originally posted by tamilselvan
If your SQL outputs more than 15 % of rows from a table, then you don't need to create an index.

---

* sharp-intake-of-breath *

Do we need to have a talk about clustering factor again?

Quote:

---

Originally posted by tamilselvan
How ever, for efficient joining between 2 tables, you need to have indexes on both tables.

---

* another-sharp-intake-of-breath *

I would say that it sometimes helps to have an index on one table -- depends on the type of join and the data volumes involved.

Quote:

---

Originally posted by slimdave
* sharp-intake-of-breath *

Do we need to have a talk about clustering factor again?

---

Tamil :

I would be sure that Dave is pointing to % of Blocks than Rows..

Aint it Dave.. to my mem we (myself & u) have had enough on this topic in some post.. ;)

Indeed.

http://www.dbasupport.com/forums/sho...threadid=38893

Hi,

1. Query execution timing difference with or without indexes may
not be much if the tables you are joining are small (few
hundred thousand rows).

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.

4. Comparing the trace is the way to go.
Check the trace for query with and without indexes.

Quote:

---

Originally posted by fossil
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.

---

These statements are obviously wrong.

SlimmDave, Try them with/without indexes and check it out yourself.

Quote:

---

Originally posted by fossil
SlimmDave, Try them with/without indexes and check it out yourself.

---

Why wasting time in trying your suggestions? Anyone that has ever seriously worked with Oracle database knows that each of your four suggestions are total nonsence.
(Well, the last one can not be disputed, however in most cases you don't realy need to trace the session to come to conclusions - there are other much more lightweight methods to get the same information).

Quote:

---

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,

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,

Quote:

---

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,

You are pulling our legs, aren't you? Of course, it's April Fools! Didn't get it untill now....

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.

Quote:

---

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.

dba_admin,


Not having indexes at all is indeed a solution in all situtations
(large/small multi table natural, innert,outer, even single value
lookup), cases except if you know for sure that you will "always" get just a few rows out of the query, still I would suggest you to run the query w/wo indexes and compare the timings.

Quote:

---

Originally posted by fossil
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.

---

AFAIK I have not made any statement like you did! I have not said this is good this is bad, I have not said this is gonna be faster. And your statements are contradicting, in one place it seems that you are backing up your statements using large tables but here you are talking about small tables.

Regarding your second statement, how can you assume there are no indexes?!?! How can you assumme joining million of rows will have to be FTS??? I mean you are assumming too many things to make such statements.

Cost doesnt matter, wanna see an example

Code:

---


select count(*) from emp;

  COUNT(*)
----------
    180002

select count(*) from dept;

  COUNT(*)
----------
    50000

select count(*) from emp where deptno = 10;

  COUNT(*)
----------
    180000

select count(*) from dept where deptno = 10;

  COUNT(*)
----------
        1

select /*+ USE_FULL(a) */ ename, dname
  2  from emp a, dept b
  3  where a.deptno = b.deptno and a.deptno between 1 and 10;

180000 rows selected.

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=34 Card=179612 Bytes=2155344)
  1    0  HASH JOIN (Cost=34 Card=179612 Bytes=2155344)
  2    1    TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=3 Card=9 Bytes=36)
  3    2      INDEX (RANGE SCAN) OF 'SYS_C002919' (UNIQUE) (Cost=2 Card=9)
  4    1    TABLE ACCESS (FULL) OF 'EMP' (Cost=30 Card=179993 Bytes=1439944)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      12449  consistent gets
          0  physical reads
          0  redo size
    2352356  bytes sent via SQL*Net to client
    132492  bytes received via SQL*Net from client
      12001  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    180000  rows processed

look cost, 34

select ename, dname
  2  from emp a, dept b
  3  where a.deptno = b.deptno and a.deptno between 1 and 10;

180000 rows selected.

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=21 Card=179612 Bytes=2155344)
  1    0  TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=19999 Bytes=159992)
  2    1    NESTED LOOPS (Cost=21 Card=179612 Bytes=2155344)
  3    2      TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=3 Card=9 Bytes=36)
  4    3        INDEX (RANGE SCAN) OF 'SYS_C002919' (UNIQUE) (Cost=2 Card=9)
  5    2      INDEX (RANGE SCAN) OF 'EMP_IDX2' (NON-UNIQUE) (Cost=1 Card=59998)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      24782  consistent gets
          0  physical reads
          0  redo size
    2352356  bytes sent via SQL*Net to client
    132492  bytes received via SQL*Net from client
      12001  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    180000  rows processed


look cost again, 21 which is lower than first query but it is slower and most costly so can you say cost 21 is better than cost 34????

---

Quote:

---

Originally posted by fossil
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.

---

I didn't just read it, I quoted it.

You keep saying that the join is always performed before the predicate is evaluated, therefore there is no point in having an index. Or am I misinterpreting you? Is this what you believe?

I think he is assumming there are no indexes at all which I dont understand how can such assumptions be made when the poster says his columns are indexed and the predicates are indeed applied!

SlimDave,


QUOTE]You keep saying that the join is always performed before the predicate is evaluated, therefore there is no point in having an index. Or am I misinterpreting you? Is this what you believe?[/QUOTE]



This is not what I said.

Can you restate, or rephrase, what it is you are saying then?

Quote:

---

Originally posted by fossil
Not having indexes at all is indeed a solution in all situtations

---

Jus CRAP :rolleyes: