Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Aug 2011
    Posts
    7

    Unanswered: Managing a Large Table

    Hello,

    I am working with an online application with the database in Oracle 10G. We have a table with 10 million rows and this table is subjected to grow in future also. Moreover we cannot archive some of these rows as these records are required for referencing.

    We have all necessary indexes on the table but querying this table takes a lot of time especially when it is joined with other tables.

    Request you to please suggest me some methods with which I can manage this table in a better way so that queries joining this table would execute faster..

    Thanks..
    Eben Thomas

  2. #2
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Have you thought about partitioning the table?
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    10 million rows is not a large table, I do instant queries on tables containing billions of rows. Show the query and describe the tables. Have you gathered statistics on the tables?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The number of rows within a table can have minimal to NO impact on SQL performance.

    post (formatted) EXPLAIN PLAN for those SQL that complete "too slowly"
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Aug 2011
    Posts
    7
    Hello

    Below is the Select Query

    SELECT TAB1.C6, TAB2.C8, TAB3.C4,TAB3.C5,TAB2.C10,TAB3.C6 FROM TAB1
    INNER JOIN TAB2 ON TAB2.C2 = TAB1.C2 AND TAB2.C3 = TAB1.C3
    AND TAB2.C4=TAB1.C4 AND TAB2.C5 = TAB1.C5
    INNER JOIN TAB3 ON TAB3.C1 = TAB1.C1 ;

    And below is the description of the tables

    -- THE COUNT OF THE ROWS IN TAB1 IS 108207471
    create table TAB1
    (
    C1 NUMBER(10) not null,
    C2 NUMBER(10) not null,
    C3 NUMBER(10) not null,
    C4 NUMBER(10) not null,
    C5 NUMBER(10) not null,
    C6 DATE,
    C7 DATE,
    C8 DATE,
    C9 DATE,
    C10 DATE,
    C11 DATE,
    C12 CHAR(1),
    C13 NUMBER(10),
    C14 VARCHAR2(255),
    C15 DATE,
    C16 DATE not null,
    C17 NUMBER(10) not null,
    C18 NUMBER(1) not null,
    );
    -- Create/Recreate primary, unique and foreign key constraints
    alter table TAB1 add constraint XPKTAB1 primary key (C1);

    -- Create/Recreate indexes
    create unique index XAK1TAB1 on TAB1 (C17, C3, C4, C5, C2);
    create index XIE1TAB1 on TAB1 (C18, C2, C3, C4, C5);
    create index XIE2TAB1 on TAB1 (C18, C5, C2, C3, C4);
    create index XIE3TAB1 on TAB1 (C18, C5, C3, C4, C2);
    create index XIE4TAB1 on TAB1 (C18, C8, C12, C2, C1);
    create index XIE5TAB1 on TAB1 (C18, C12, C3, C4, C5, C2);
    create index XIE6TAB1 on TAB1 (C18, C9, C8, C12, C2, C1);
    create index XIE8TAB1 on TAB1 (C18, C17, C2, C3, C4, C5);
    create index XIE9TAB1 on TAB1 (C18, C2, C12);

    -- THE COUNT OF THE ROWS IN TAB2 IS 3000
    create table TAB2
    (
    C1 NUMBER(10) not null,
    C2 CHAR(1) default 'S' not null,
    C3 NUMBER(10) not null,
    C4 NUMBER(10) not null,
    C5 NUMBER(10) not null,
    C6 NUMBER(10) not null,
    C7 DATE not null,
    C8 DATE not null
    )

    -- Create/Recreate primary, unique and foreign key constraints
    alter table TAB2 add constraint XPKTAB2 primary key (C1);
    -- Create/Recreate indexes
    create index XIE1TAB2 on TAB2 (C2, C3, C4, C5, C6);
    create index XIE2TAB2 on TAB2 (C8);

    -- THE COUNT OF THE ROWS IN TAB3 IS 76504475
    create table TAB3
    (
    C1 CHAR(1) default 'N' not null,
    C2 NUMBER(10) not null,
    C3 DATE not null,
    C4 DATE not null,
    C5 DATE not null,
    C6 VARCHAR2(1),
    C7 NUMBER(10)
    )
    ;
    -- Create/Recreate primary, unique and foreign key constraints
    alter table TAB3 add constraint F1 foreign key (C2) References TAB1 (C2);
    -- Create/Recreate indexes
    create index XIE1TAB3 on TAB3 (C1);
    create index XIE2TAB3 on TAB3 (C1, C4);
    create index XIE3TAB3 on TAB3 (C1, C2);
    create index XIF1TAB3 on TAB3 (C2);
    create unique index XPKTAB3 on TAB3 (C1, C2, C3, C4, C7);

    Creating Partitions is surely a option which we will look into, but it would be a great learning for me if I could learn how to tune the select query.

    Thanks..
    Eben Thomas

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SELECT tab1.c6,
           tab2.c8,
           tab3.c4,
           tab3.c5,
           tab2.c10,
           tab3.c6
    FROM   tab1
           inner join tab2
             ON tab2.c2 = tab1.c2
                AND tab2.c3 = tab1.c3
                AND tab2.c4 = tab1.c4
                AND tab2.c5 = tab1.c5
           inner join tab3
             ON tab3.c1 = tab1.c1;
    are statistics current on all tables & indexes?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Aug 2011
    Posts
    7
    I have attached the formated explain plan in the attachment ExplainPlan.jpg
    Attached Thumbnails Attached Thumbnails ExplainPlan.JPG  

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    it appears SQL would benefit from INDEX on TAB1.C1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Aug 2011
    Posts
    7
    TAB1.C1 is the primary key of TAB1 and it is already indexed

    create index XIE4TAB1 on TAB1 (C18, C8, C12, C2, C1);

    The PL/SQL Developer doesnt allow me to create another index on TAB1.C1 it gives a ORA-01408 : such column list already indexed

    Can you please help..

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >create index XIE4TAB1 on TAB1 (C18, C8, C12, C2, C1);
    not as above, but do as below
    create index XIE4TAB1 on TAB1 (C1, C8, C12, C2, C18);
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I'm afraid you make it impossible to properly analyze your situation using this contrived example.

    INNER JOIN TAB3 ON TAB3.C1 = TAB1.C1 ;
    alter table TAB1 add constraint XPKTAB1 primary key (C1);
    create table TAB3
    (
    C1 CHAR(1) default 'N' not null,
    So, let's see. TAB3.C1 can only contain 256 unique values (well, if you store DBCS data, it's a bit more but not even close to 10 million. You presumably join this with TAB1.C1, so it would also contain at most 256 unique values. At the same time, TAB1.C1 is the primary key of a table that presumably has 10 million rows. Huh?

  12. #12
    Join Date
    Aug 2011
    Posts
    7
    Code:
    SELECT 
      TAB1.C6, 
      TAB1.C8,
      TAB1.C10, 
      TAB3.C4,
      TAB3.C5,
      TAB3.C1 
       FROM TAB1 
            INNER JOIN TAB2 ON 
                            TAB2.C3 = TAB1.C2 AND TAB2.C4 = TAB1.C3 
                            AND TAB2.C5=TAB1.C4 AND TAB2.C6 = TAB1.C5
           INNER JOIN TAB3 ON 
                            TAB3.C2 = TAB1.C1 ;

    I am sorry i had typed the worng code... These are the correct Joins.
    Thanks for correcting
    Last edited by Eben Thomas; 08-26-11 at 15:04.

  13. #13
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    your obfuscation is helping no one!

    SQL> alter table TAB3 add constraint F1 foreign key (C2) References TAB1 (C2);
    alter table TAB3 add constraint F1 foreign key (C2) References TAB1 (C2)
    *
    ERROR at line 1:
    ORA-02270: no matching unique or primary key for this column-list
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  14. #14
    Join Date
    Aug 2011
    Posts
    7
    Code:
    create table TAB3
    (
    C1 CHAR(1) default 'N' not null,
    C2 NUMBER(10) not null,
    C3 DATE not null,
    C4 DATE not null,
    C5 DATE not null,
    C6 VARCHAR2(1),
    C7 NUMBER(10)
    )
    ;
    -- Create/Recreate primary, unique and foreign key constraints 
    alter table TAB3 add constraint F1 foreign key (C2) References TAB1 (C1);
    -- Create/Recreate indexes 
    create index XIE1TAB3 on TAB3 (C1);
    create index XIE2TAB3 on TAB3 (C1, C4);
    create index XIE3TAB3 on TAB3 (C1, C2); 
    create index XIF1TAB3 on TAB3 (C2); 
    create unique index XPKTAB3 on TAB3 (C1, C2, C3, C4, C7);

    This is the correct code....

  15. #15
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >This is the correct code....
    You don't get a second chance to make a first impression.

    You're On Your Own (YOYO)!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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