Sybase Table Spaces 
Author Message
 Sybase Table Spaces

Hi everyone:

   I am an Oracle programmer, I just started working with Sybase
recently. I am having some confusion with the construct of segments
in Sybase, i.e. How table spaces, indexes, etc. fit into segments
and how segments are allocated ? Does anyone out there know how
these pieces fit together ?

   Any help would be appreciated.

Sa



Sat, 30 Dec 1995 02:38:33 GMT
 Sybase Table Spaces

|>
|>
|> Hi everyone:
|>
|>    I am an Oracle programmer, I just started working with Sybase
|> recently. I am having some confusion with the construct of segments
|> in Sybase, i.e. How table spaces, indexes, etc. fit into segments
|> and how segments are allocated ? Does anyone out there know how
|> these pieces fit together ?
|>
|>    Any help would be appreciated.
|>
|> Sa
|>
This is an excerpt from the SA Guide, page 3-24 in my manual.
"Segments are named subsets of the database devices available to a
particular SQL Server database.  A segment can best be described as a
label that points to one or more database devices.  Segment names are used
in CREATE TABLE and CREATE INDEX commands to place these database objects
on specific database devices."

In Sybase, I can create a named logical device that points to a single
physical device.  These devices, stored in system table sysdevices,
can be tape devices or disk devices.  The disk device can be a unix file
system or a raw disk partition.  I can put databases on disk devices.

When a database is created or altered, the SA specifies which logical
device(s) that the database is to reside on.  For example,

create database test on
       device1=20,
       device2=40,
       device3=60,
       log on device4=80

places the database test on logical devices 1,2, and 3 and places the database
log (journal) on device4.  (Each database has its own journal.)

I can further define how the database objects will be allocated across these
different devices by creating segments within the database.  A segment is a
label that a database uses to access a particular logical device.  I can now
allocate Table1 to reside on SegmentX, which happens to reside on logical device
A which points to physical drive rd002e.  A segment allows me to place a
table or index on one or more physical devices.

Sounds complicated, but it really isn't that bad.  Take a look at you SA Guide!

--
+--- Consultant --------+--------------- Location -----------------------------+
|   David W. Pledger,   |  By Phone   : (513)865-6800 X2940                    |

|Custom Database Apps.  |  By Mail    : PO Box 498, Springboro, OH  45066-0498 |
+-----------------------+------------------------------------------------------+



Sat, 30 Dec 1995 04:31:14 GMT
 Sybase Table Spaces
----------------------------------------------------------------------------------

Quote:

>Hi everyone:

>   I am an Oracle programmer, I just started working with Sybase
>recently. I am having some confusion with the construct of segments
>in Sybase, i.e. How table spaces, indexes, etc. fit into segments
>and how segments are allocated ? Does anyone out there know how
>these pieces fit together ?

>   Any help would be appreciated.

>Sa

----------------------------------------------------------------------------------

SYBASE consists of physical devices (eg. UNIX partitions such as /dev/rsd4e) and
logical devices (i.e. internal names which point to the physical devices.  For
example tapedump1).  Segments are simply a label which point to one or more of
the logical devices.  So if you wanted to spread a table or its index(es) across multiple physical disks for performance reasons, you could create a segment which
consists of one or more logical devices which in turn point to physical devices
on separate physical disks.

Example:

Physical device      Sybase logical device name
---------------      --------------------------
/dev/rsd4e           ProdDisk1
/dev/rsd5g           ProdDisk2
/dev/rsd5h           ProdDisk3

Segment              Logical device(s)
-------              -----------------
ProdSeg1             ProdDisk1, ProdDisk3
ProgSeg2             ProdDisk2, ProdDisk3
ProdSeg3             ProdDisk3

I hope this clarifies it for you.  I'm really a programmer/analyst so a real DBA
might be able to provide you with a more in-depth and accurate description of
segments.  Additionally, there is a good tutorial on segments in the Systems Administration guide (I believe).

Anil



Sat, 30 Dec 1995 21:29:52 GMT
 Sybase Table Spaces

Quote:

>Hi everyone:

Hi.

Quote:
>   I am an Oracle programmer, I just started working with Sybase

A convert!!

Quote:
>recently. I am having some confusion with the construct of segments
>in Sybase, i.e. How table spaces, indexes, etc. fit into segments
>and how segments are allocated ? Does anyone out there know how
>these pieces fit together ?

Ok, basically it's like this. Sometimes you want to put different parts
of a table onto different physical devices: i.e. data and index. This is
good for performance tuning or space usage tuning. If, for instance,
the index is on a different device, then you can be reading index pages
and data pages without seek time.

By default, everything is put onto whatever physical device the database
happens to be allocating from currently. If this is OK with you, then you
never need to worry about segments. (We've never had to use them since
most of our stuff is CPU bound and i/o fine.)

However, if you want to split data portions or data and index or whatever,
then you need to specify where the different portions go. SYBASE does not
allow you to specify the physical location for those things. It does
allow you to specify a 'segment'. A segment is a logical name for a
SYBASE device. A SYBASE device is a logical name for a physical device.
(Note: you have three layers of abstraction here.)

So, you create a SYBASE device to point to a phyhsical device:

        db_dev_1 --> /dev/rsd3a
        db_dev_2 --> /dev/rsd7a
        log_dev_1 -> /dev/rsd0a

You create a database on the SYBASE devices:

        create database a on db_dev_1  = 50,
                             db_dev_2  = 50
                      log on log_dev_1 = 10

If you now specify nothing in the create statements for the DB objects
(tables, etc.) they will be placed on whatever SYBASE device (and therefore
physical device) that the DB happens to allocating from at that particular
moment. However, if you want *control* over where the objects get placed,
then you create segments.

You create segments to point to SYBASE devices:

                table_seg --> db_dev_1
                index_seg --> db_dev_2

Then you create objects on segments:

            create table x (...) ON table_seg
            create inonclustered index y on x(...) ON index_seg

Now you are 100% assured that the table and the index will always be on
different physical devices.

But wait, there's more!

If you do not specify segments, object space is allocated out of the entire
database pool. Therefore, if you have a 50 meg database, a table can take
up to 50 MB's of space. If, however, you do specify segments, then the
object space is allocated out of the segment pool. So, you can have a
bizillion bytes free in the database, but if the segment is full then
you cannot allocate any more space to the objects on that segment (e.g.
you cannot allocate any more rows to table x even though there is 40 MB's
free on index_seg, and therefore 40 MB's free in the database).

Also, keep in mind that this only works with physical devices on different
controllers. If the above device allocation had been:

        db_dev_1 --> /dev/rsd3a
        db_dev_2 --> /dev/rsd3b
        log_dev_1 -> /dev/rsd3c

Then segmenting would accomplish nothing.

Hope this helps,

Topher
--
 Christopher A. White                    | She whispers words to clear

 GTech Corporation, West Greenwich, RI   |     but now, at last, I'm blind
 The opinions expressed are mine alone   |        - Dream Theatre



Sat, 30 Dec 1995 22:37:44 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. pg space vs oracle space with same table?

2. TEXT field in Blob Space vs Table Space

3. Lost in space/space lost, a space management problem

4. Oracle Free Space or Space used - DBA (now index space usage)

5. Disk space a table/tables are using.

6. Opening Tables with spaces in the Table Name

7. foreign key between tables in different table spaces

8. system tables in user table space

9. Dynamically Updating Table Lookup CTRL-SPACE tables

10. displaying table names mapped to table spaces

11. table size and table space

12. why does sybase fill columns with white spaces


 
Powered by phpBB® Forum Software