index strategy on SQL tuning - Page 3
DBAsupport.com Forums - Powered by vBulletin
Page 3 of 3 FirstFirst 123
Results 21 to 27 of 27

Thread: index strategy on SQL tuning

  1. #21
    Join Date
    Dec 2002
    Posts
    36
    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.
    fossil

  2. #22
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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????

  3. #23
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #24
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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!

  5. #25
    Join Date
    Dec 2002
    Posts
    36
    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.
    fossil

  6. #26
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Can you restate, or rephrase, what it is you are saying then?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #27
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by fossil
    Not having indexes at all is indeed a solution in all situtations
    Jus CRAP
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width