index strategy on SQL tuning

[fossil]

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.

[pando]

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

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????

[slimdave]

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?

[pando]

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!

[fossil]

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.

[slimdave]

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

[abhaysk]

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

Jus CRAP