Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    4

    Unanswered: optimization partition table question, help!!!

    hello,
    I have a question for a optimization select access:

    i have a partition table with 20 million registres

    table dates (date1 date, ...)
    PARTITION BY RANGE (date1)
    (
    PARTITION date012003 VALUES LESS THAN (TO_DATE(''2003-01-01'', ''YYYY-MM-DD"))
    ,PARTITION date022003 VALUES LESS THAN (TO_DATE(''2003-02-01'', ''YYYY-MM-DD''))
    ,PARTITION date032003 VALUES LESS THAN (TO_DATE(''2003-03-01'', ''YYYY-MM-DD''))
    ...
    )
    my question is :

    What is the best method for 'SELECT ' to access the partition :

    a) create an index :
    create index dates_date on dates(date1);
    b) Delete the partition and create the index dates_date.

    c) another.


    Thanks in advance.

  2. #2
    Join Date
    Oct 2003
    Posts
    4
    hello,

    The select is :

    Select * from dates
    where date1 >= to_Date('2003/03/01','YYYY/MM/DD');

    what is the best method for this select.

    Thanks.

  3. #3
    Join Date
    Oct 2003
    Posts
    2
    Originally posted by james7
    hello,

    The select is :

    Select * from dates
    where date1 >= to_Date('2003/03/01','YYYY/MM/DD');

    what is the best method for this select.

    Thanks.
    Il faut tout d'abord créer un tablespace différent pour chaque partition si possible sur des dispques différents, pour éviter les risques de contention et les problèmes liés aux accès concurrents(perte de performance).
    Il faut créer un index pour la table partitionnée dates.
    Ensuite, faites votre requête: Select * from dates
    where date1 >= to_Date('2003/03/01','YYYY/MM/DD');.
    Il n'est pas nécessaire de spécifier le nom de la partition, car Oracle va le déterminer tout seul, grâce à la definition des plages de valeurs.
    Tout simplement.
    A+.

Posting Permissions

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