Results 1 to 11 of 11

Thread: Indexes

  1. #1
    Join Date
    Jan 2004
    Location
    India
    Posts
    191

    Unanswered: Indexes

    Can any one help me on Proper Indexes. For example
    Say i have one table

    Table Name :Test

    a varcahar2(10),
    b varchar2(10),
    c varchar2(10)
    d varchar2(10)


    I have a concanated index on column (a,b,c)(test_idx) and one more index on column (b) (test_idx1).

    My question is, Should i require index Test_idx1? as this column (b) is already present in the Concanated index.
    If i drop index test_idx1,should oracle optimiser will use index test_idx when the sql query has only one where clause on column b.
    Is it , as example of redundant indexes.?

    Thanks in advance.


    Thanks,
    Pagnint
    (No need to search web before posting new question)

  2. #2
    Join Date
    Jul 2003
    Location
    England
    Posts
    152
    If I'm not mistaken, this would be a case for index skip scanning, which would mean the index would be used in your example above - not sure if this opt method exists before 9i though.
    Regards
    Dbabren

  3. #3
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Hi dbabren,

    Yes we have oracle 9i. So what u say, should i drop the second index?

    Thanks,
    Pagnint
    (No need to search web before posting new question)

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Do tests on your test environment (and if possible with prod data) as it depends on your data distribution for column A. Some index skip scans work very well but sometimes they dont work well. Also 9.0.1 can cause ora-600 errors with skip scans so you may have to patch it if you get these errors.

    Alan

  5. #5
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Thankuuuuuuuuuuu


    Cheers,
    Pagnint
    (No need to search web before posting new question)

  6. #6
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Hi,
    I make some test in my test database.

    Before droping second index:
    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2472 Card=1 Bytes=5)
    1 0 SORT (AGGREGATE)
    2 1 INDEX (FAST FULL SCAN) OF 'I_BACKLOG_SCHED_BOOK' (NON-UN
    IQUE) (Cost=2472 Card=2773507 Bytes=13867535)
    )


    After droping the index
    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=246 Card=1 Bytes=5)
    1 0 SORT (AGGREGATE)
    2 1 INDEX (FAST FULL SCAN) OF 'PK_BACKLOG_SCHED_HISTORY' (UN
    IQUE) (Cost=246 Card=202064 Bytes=1010320)

    Instead of doing Index Skip scan, oracle is doing Fast full scan . is it ok?

    Thanks,
    Pagnint
    (No need to search web before posting new question)

  7. #7
    Join Date
    Jul 2003
    Location
    England
    Posts
    152
    From what I've read about skip scanning, a lot depends on the distinctiveness of the initial column.

    eg if first column is gender for the sake of argument, as there are usually only 2 values, Oracle does 2 queries for the second entry in the index, one for Male, one for Female - 2 queries.

    Based on that I assume that at some point the optimiser will decide that it make more sense to full scan the index, instead of skip scan - ie if your first column in the index has many distinct values, which will involve many queries to satisfy.

    Am I making sense?!?
    Regards
    Dbabren

  8. #8
    Join Date
    Jul 2003
    Location
    England
    Posts
    152
    As for your explain plans, the second one is only about 10% of the cost of the first - no choice mate.

    I assume your query times match with the explain plans?
    Regards
    Dbabren

  9. #9
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Thanks
    "
    As for your explain plans, the second one is only about 10% of the cost of the first - no choice mate.

    I assume your query times match with the explain plans?

    "

    Can u please explain above said thing. What i would understand from the explain plan...better to go with second plan as card is less for this.
    I read some where that cost is nothing but randomly generated number. So not to worry for the cost factor?

    Thanks,
    Pagnint
    (No need to search web before posting new question)

  10. #10
    Join Date
    Jul 2003
    Location
    England
    Posts
    152
    cost will be based on the quality of your statistics. If your statistics are old or imcomplete then the cost figures will be inaccurate. I only use them as guidelines when I alter SQL statements as I feel they give me an indication of whether the query has improved or not, but I always run the actual queries to get real timings.

    I would suggest running both queries on your test sys, and then compare the run times back to the explain plans. That will tell you the accuracy of the cost part of your explain plans.
    Regards
    Dbabren

  11. #11
    Join Date
    Feb 2004
    Posts
    108
    Not sure what query you used to collect those numbers.
    But you might also want to verify that there will not be any queries with something like :
    col_b = value
    and col_a != someValue
    Droping Idx2 on Col_B will kill such queries.

    This could be the reason why the table ever had the second index on col_B.

Posting Permissions

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