Create Index advice needed
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Create Index advice needed

  1. #1
    Join Date
    Jan 2003
    Location
    Gaithersburg MD.
    Posts
    1

    Question Create Index advice needed

    I have one tabke TK3 it has 3 numeric columns 82160 rows the data is fixed as that it never changes. In the othe table TGAMES are 20 numeric columns currently about 56000 rows with 300 or so added daily.

    TK3 represents all of the unique posible combinations that may appear in a TGAMES row to get a count of how many times a unique combination has appeared I use a SQL query below. My problem is that it is exreamly slow even in a View. I need to create an index that will speed this up, I tried create index on TGAMES (P1..P20), would creating an index for each TGAME's P? column be better?. Any suggestions would be appreciated.
    SELECT A.N1 N_1, A.N2 N_2, A.N3 N_3,COUNT(GAME) COUNT
    FROM K3 A INNER JOIN TGAMES G ON
    A.N1 IN (P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P16,P17,P18,P19,P20) AND
    A.N2 IN (P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P16,P17,P18,P19,P20) AND
    A.N3 IN (P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P16,P17,P18,P19,P20)
    GROUP BY A.N1,A.N2,A.N3,GAME
    ORDER BY COUNT DESC;

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    tuff one!

    An index on TGAMES(P1,P2,..,P20) won't be used. I'd be surprised if individual indexes on P1 etc columns would be either.

    I'm having trouble getting my head round this . . . am I right that the logic requires all of TGAMES to be read? In which case you will be using a Full Table Scan (FTS). So the only hope with indexes is N1, N2 and N3 on TK3.

    If most of TK3 is read as well, then both will use FTS. In which case, the only hope is to tune memory (e.g. db_block_buffers, sort_area_size).
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Further thoughts . . . are only three of the Pi significant (i.e. not zero or NULL)? Are the Ni ordered (e.g. N1 > N2 > N3)? (I conclude yes to both, but I'd like confirmation.) If so, you could create a "hash" value (perhaps use a trigger) like 1000000*P(max) + 1000*P(middle) + P(min) and index on that (ditto for TK3).
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

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