Results 1 to 4 of 4
  1. #1
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137

    Unanswered: Table partitions

    I've never used table partitions, but I'm analyzing a table that might be a good candidate for them.

    The table contains information from differents schemas, but the data it's the same, except for the column that indicates the origin schema.

    Code:
    CREATE TABLE gbl_collaterals
       (collateral_id                  NUMBER(13,0),
        origin_schema                  VARCHAR2(30),
        timestamp                      DATE,
        collateral_class               VARCHAR2(6),
        collateral_value               NUMBER(18,0))
    /
    There's a process on the schemas that report to this table that runs this every time it's executed (once a day):
    Code:
    DELETE FROM gbl_collaterals WHERE origin_schema = 'SCHEMA_A';
    INSERT INTO gbl_collaterals ...
    Since I don't know the exactly number of schemas that might update this table I don't know how to partition that table.

    PARTITION BY HASH implies a fixed amount of partitions, which I don't know so I supposed it's not good.

    1) PARTITION BY LIST would be okay? How should I set it up?
    2) Do I still get benefits if I set the partitions to work on the same tablespace?

    Thanks!!!

  2. #2
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    D'Oh, now I understand... a table partition is a segment, it cannot be dynamic!!

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Partitioning by HASH does imply a fixed amount of partitions, BUT.. that doesn't mean you NEED to know the fixed amount of value of the key on where you hash on. Partition By List would require you that.

    I would create a partition by LIST and add schemas as necessary. That will give you better maintenance (that delete that you're executing right now is just awfull, you would rather truncate it) and performance (for example, you could compress and move partition data that is not used frequently to your slower disk and so on).

  4. #4
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    Yes, I agree with you, I don't like that delete statement at all, but I cannot truncate the table because I need to preserve the info of the others schemas in the same table.

    I was testing with this table structure:
    Code:
    CREATE TABLE gbl_collaterals
       (collateral_id                  NUMBER(13,0),
        origin_schema                  VARCHAR2(30),
        timestamp                      DATE,
        collateral_class               VARCHAR2(6),
        collateral_value               NUMBER(18,0))
    PARTITION BY LIST (origin_schema)
    ( PARTITION AR VALUES ('AR_SCHEMA)
    , PARTITION BR VALUES ('BR_SCHEMA')
    , PARTITION UY VALUES ('UY_SCHEMA')
    , PARTITION OTHERS VALUES (DEFAULT)
    )
    /
    I'm getting better performance because of the different segments but I'm not completely sure about adding partitions as necessary.

    Thanks anyway!

Posting Permissions

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