Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2003
    Posts
    2

    Question Unanswered: Indexing an Hash table.

    After inserting values into an hash table say #TBL_TMP , i created an index for an hash table say IDX_TEMP with 2 keys.
    Finally values are selected from the hash table with an constraint.
    when i executed the Store proc, the showplan shows that index is not being used for the hash table , also i forced an index to the hash table but still it's the same . Also at the end of the showplan i get a message

    Server Message: Number 1563, Severity 10
    Server 'USLCREMAC12_SRVR', Line 22:
    The sort for #TBL_TEMP00003120003891169 is done in Serial



    what does this mean???

    Can any one pl. let me know whether indexes can be used for hash tables. ????

  2. #2
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159

    Re: Indexing an Hash table.

    Hi,

    I'm assuming you have a situation like this:

    create proc proc_name

    as

    create #t1 ( a int, b varchar(25) )

    create index idx_1 on #t1 ( a )

    /* Insert some rows into #t1 */

    select * from t1 where a = 5


    If so, the optimiser never sees the index because it is created by the stored procedure. To effectively use the index, you need to nest the use of the index inside another stored proc called by the procedure that creates it.


    create table #t1 ( a int, b varchar(25))
    go
    create index idx_1 on #t1 ( a )
    go
    create proc inner_proc_name

    as

    select * from #t1 where a = 5

    go
    drop table #t1
    go
    create proc proc_name

    as

    create table #t1 ( a int, b varchar(25))

    create index idx_1 on #t1 ( a )

    exec inner_proc_name

    go


    This way when inner_proc_name is created the index exists and can be included in the optimisation step

    The sort for #TBL_TEMP00003120003891169 is done in Serial
    means that the sort was not done using a parallel query. Is the dboption "select into/bulkcopy/pll sort" enabled? Do you have sufficient worker processes?

  3. #3
    Join Date
    Jun 2003
    Posts
    2

    Talking Re: Indexing an Hash table.

    Yeh richard ,
    thanks for the solution , it did work out ...

    Thanks once again ....

  4. #4
    Join Date
    Aug 2002
    Location
    Omaha, Nebraska
    Posts
    21

    Re: Indexing an Hash table.

    Originally posted by richardcrossley
    Hi,

    I'm assuming you have a situation like this:

    create proc proc_name

    as

    create #t1 ( a int, b varchar(25) )

    create index idx_1 on #t1 ( a )

    /* Insert some rows into #t1 */

    select * from t1 where a = 5


    If so, the optimiser never sees the index because it is created by the stored procedure. To effectively use the index, you need to nest the use of the index inside another stored proc called by the procedure that creates it.
    ...
    Not necessarily true. Procedures can use indexes created on #temp tables within. To effectively use the index, it should have accurate statistics for it. In the scenario above, the index was created BEFORE the data was loaded into the table. Stored procedures which create temp tables, and then indexes on those tables "see" the index without the need for a sub-procedure. It's a question of what statistics the optimizer "sees" after the index is created here.

    There was a very similar discussion of all this in the forums.sybase.com server in sybase.public.ase.general with subject "# tables indexing" beginning on 6/10/2003. My response goes into detail about the advantages/necessity of using subprocedures.

    news://forums.sybase.com/3EE89D18.29...est.com.nospam

  5. #5
    Join Date
    Jun 2003
    Location
    Sydney, Australia
    Posts
    66
    Kevin,

    thanks for pointing accross to the other thread. Creating an index on #temp and using it in the same stored proc - sounds like great progress. Over the years, I have also come to trust the opinion of Eric Miner. But I'm surprised to hear the feature was available since 11.0.x. I must take this with a grain of salt, since I well remember having to split stored procs, back in 1998-99 with 11.0.1 and 11.0.3. BTW, the 12.5 documentation on the Sybase web site still says "you cannot create and use an index in the same stored procedure".

    From your explanation, I gather that a child procedure will always see a table called #temp and an index called "whatever". Even if these are different objects at each invokation, they form a constant configuration, and their names can be re-resolved without having to recompile or re-optimise the child ( I may be confusing the various steps of procedure compilation). But a query executing in the second half of a parent, may note the presence of the index as a new element in the schema, and decides to re-optimise.

    Clever, I will try it next time.

    Andrew Schonberger

Posting Permissions

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