Re: Table Partitioning in Postgres:

> -----Original Message-----
> From: Jean-Luc Lachance [mailto:jllachan(at)nsd(dot)ca]
> Sent: Tuesday, February 18, 2003 10:54 AM
> To: Shridhar Daithankar<shridhar_daithankar(at)persistent(dot)co(dot)in>
> Cc: PGSQL General (E-mail)
> Subject: Re: [GENERAL] Table Partitioning in Postgres:
>
>
> Shridhar,
>
> I must disagree with "that's is an OS's job".
> OSs try to be generic. With databases, we know more about
> the data structure.
>
> If a large table could be split (partitioned) based on
> specific key, we could expect huge improvements for agregates
> queries for example when that key is involved.
>
> Also, DBA must be able to place table on different file
> systems. They know more about the application than the OS does.
>
> Even Postgresql has to be told to perform vaccum and analyze.
> If the OS had enough intelligence we could trust it to do a
> good job, but until then ...

Oracle's Rdb has a nice syntax for index creation that takes into
account separate areas. This will probably look horrible, but here is
the ASCII representation of the syntax grammar:

CREATE INDEX Subtopic? form

CREATE

INDEX

Format

CREATE - ----------- -> INDEX <index-name> ------------------
-> UNIQUE -
------------------------------------------------------------
-- ----------------------------------- -> ON <table-name> --
-> STORED NAME IS <stored-name> ---
----------------------------- <-----------------------------
> ( > <column-name> --
--------------
- ------------- ----------------------------- - - ) -
> ASCENDING - > SIZE IS <n> ---------------
> DESCENDING > MAPPING VALUES <l> TO <h> -
---------------------- , <--------------------------
------------------------------ <-------------------------------
---------------- - ----------------------- -
-> type-clause - -> compression-clause -
---------------------------------------------
----------------------- -------------------------------------->
-> index-store-clause -

type-clause =

-> TYPE IS > HASHED -------------
------------------------------ >
> ORDERED ---

> SCATTERED -

> SORTED ----------------------------------------- -

> RANKED - -----------------------------

> DUPLICATES ARE COMPRESSED -

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

- -----------------------
--------------------
> sorted-index-clause -

sorted-index-clause =

-- - -> NODE SIZE <number-bytes> ---------- - -->
-> PERCENT FILL <percentage> ---------
-> USAGE - -> UPDATE - ---------------
-> QUERY --
------------------- <---------------------

compression-clause =

- -> ENABLE COMPRESSION - ------------------------------ - ->
> ( MINIMUM RUN LENGTH <n> ) -
-> DISABLE COMPRESSION ---------------------------------

index-store-clause =

STORE ----------------------
---------------------------
-> IN <area-name> - ---------------------------------- ------ >
-> ( -> threshold-clause -> ) ----
-> USING -> ( - --> <column-name> - -> ) ----------
------- , <--------
---------------------------------------------------
> IN <area-name> - ------------------------------- -
-> ( -> threshold-clause -> ) -
--------------------------------------------------
-> WITH LIMIT OF -> ( - --> <literal> - -> ) --- ---
------ , <-----
-----------------------<--------------------------
--------------------------------------------------------
------------------------------------------------------------
-> OTHERWISE IN <area-name> ------------------------------
> ( -> threshold-clause -> ) -

threshold-clause =

-- -> THRESHOLD - -> IS - -> ( --> <val1> --> ) --------- ->
-> OF -

-> THRESHOLDS - -> ARE - -----------
-> OF -
----------------------------------
-> ( --> <val1> - --------------------------- -> ) -
-> , <val2> - -------------
-> , <val3> -

CREATE INDEX Subtopic?
[snip]