cancel
Showing results for 
Search instead for 
Did you mean: 

On 15.5 onwards, does the standard temp tables created by ASE are DOL instead of ALLPAGES? (temp table issue)

Former Member
0 Kudos

Hi

Im looking for a way which i could use indexes on my temp tables without worrying about not having statistics or trying to force indexes.

The way i could find is to create the temp table as ALLPAGES and create a clustered index

Doing this way im sure that the leaves are the table's pages and so then they will be in the desired order (and i will save some index space...  a plus    )

What is bugging me is that i got the info that ALLPAGES tables do require more catalog locking effort than DOL tables. I did a quick look on the web and sybooks but i wasnt able to find more info....   It doesnt make sense to me, as naming and catalog efforts should be the same for both....

Does anyone have more info to share ? 

Thanks a lot!

Alexandre

Accepted Solutions (1)

Accepted Solutions (1)

former_member188958
Active Contributor
0 Kudos

If you don't specify a lock scheme when creating the table, the lock scheme is controlled by the server-wide sp_configure setting "lock scheme".  You can change it to datarows with

sp_configure "lock scheme", 0, datarows

I don't think you can avoid either managing statistics or forcing indexes (or entire plans).  One or the other needs attention for anything but the most trivial cases.

Former Member
0 Kudos

Hi Bret   thanks for the info..  i`ve forgot about the default server setting..  going to check that...

Oh my, going to have to build a test case to check timing for both cases...  I still could'nt see a reason for ALLPAGES needing more effort than DOL  (catalog-wise lock speaking)...

former_member188958
Active Contributor
0 Kudos

I can't think of anything either, unless perhaps the source was actually talking about the greater levels of contention back when the system catalogs themselves all used allpages locking.

Answers (0)