Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2004
    Posts
    28

    Unanswered: Need Help with an Indexing Strategy

    Hey guys, im new to this site so go easy with me, lol.

    I have a database model that i need to create an indexing strategy to increase performance (its for a uni assignment) i was just wondering who could help me at all and what you info you need to help me.

    Thanks
    Arnielover

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Need Help with an Indexing Strategy

    For a start, do all tables have primary keys? They should, and these will be indexed automatically.

    Second, have any other necessary UNIQUE constraints been defined? Again, these will be indexed automatically.

    Third, have all appropriate foreign keys been defined? If not, define them. Then add an index corresponding to the foreign key columns (99% of the time this should be done).

    Beyond that, it is a question of looking at the main queries you need to handle, and using your skill, judgement and experience to decide whether additional indexes are required.

  3. #3
    Join Date
    Jan 2004
    Posts
    28
    thanks for the reply, sorry about posting this question in another forum. Here is my DDL code that i have written, i have no experience in DBA and inly know the basics of the subject:

    CREATE TABLE org_unit
    (
    ou_id NUMBER(4) CONSTRAINT ou_pk PRIMARY KEY,
    ou_name VARCHAR2(40) CONSTRAINT ou_name_uq UNIQUE
    CONSTRAINT ou_name_nn NOT NULL,
    ou_type VARCHAR2(30) CONSTRAINT ou_type_nn NOT NULL,
    ou_parent_org_id NUMBER(4) CONSTRAINT ou_parent_org_unit_fk
    REFERENCES org_unit
    );

    CREATE TABLE project
    (
    proj_id NUMBER(5) CONSTRAINT project_pk PRIMARY KEY,
    proj_name VARCHAR2(40) CONSTRAINT proj_name_uq UNIQUE
    CONSTRAINT proj_name_nn NOT NULL,
    proj_budget NUMBER(8,2) CONSTRAINT proj_budget_nn NOT NULL,
    proj_ou_id NUMBER(4) CONSTRAINT proj_ou_fk REFERENCES org_unit,
    proj_planned_start_dt DATE,
    proj_planned_finish_dt DATE,
    proj_actual_start_dt DATE
    );

    CREATE TABLE employee
    (
    emp_id NUMBER(6) CONSTRAINT emp_pk PRIMARY KEY,
    emp_name VARCHAR2(40) CONSTRAINT emp_name_nn NOT NULL,
    emp_hiredate DATE CONSTRAINT emp_hiredate_nn NOT NULL,
    ou_id NUMBER(4) CONSTRAINT emp_ou_fk REFERENCES org_unit
    );

    CREATE TABLE activity
    (
    act_id NUMBER(6),
    act_proj_id NUMBER(5) CONSTRAINT act_proj_fk REFERENCES project
    CONSTRAINT act_proj_id_nn NOT NULL,
    act_name VARCHAR2(40) CONSTRAINT act_name_nn NOT NULL,
    act_type VARCHAR2(30) CONSTRAINT act_type_nn NOT NULL,
    act_planned_start_dt DATE,
    act_actual_start_dt DATE,
    act_planned_end_dt DATE,
    act_actual_end_dt DATE,
    act_planned_hours number(6) CONSTRAINT act_planned_hours_nn NOT NULL,
    act_people_budget NUMBER(8,2) CONSTRAINT act_people_budget_nn NOT NULL,
    CONSTRAINT act_pk PRIMARY KEY (act_id, act_proj_id)
    );

    CREATE TABLE employee_on_project
    (
    ep_emp_id NUMBER(6) CONSTRAINT ep_emp_fk REFERENCES employee,
    ep_proj_id NUMBER(5) CONSTRAINT ep_proj_fk REFERENCES project,
    ep_hourly_rate NUMBER(5,2) CONSTRAINT ep_hourly_rate_nn NOT NULL,
    ep_mgr_emp_id NUMBER(6),
    CONSTRAINT ep_pk PRIMARY KEY(ep_emp_id, ep_proj_id),
    CONSTRAINT ep_mgr_fk FOREIGN KEY (ep_mgr_emp_id, ep_proj_id) REFERENCES employee_on_project
    );

    CREATE TABLE employee_on_activity
    (
    ea_emp_id NUMBER(6),
    ea_proj_id NUMBER(5),
    ea_act_id NUMBER(6),
    ea_planned_hours NUMBER(3) CONSTRAINT ea_planned_hours_nn NOT NULL,
    CONSTRAINT ea_pk PRIMARY KEY(ea_emp_id, ea_proj_id, ea_act_id),
    CONSTRAINT ea_act_fk FOREIGN KEY (ea_act_id, ea_proj_id) REFERENCES activity ,
    CONSTRAINT ea_ep_fk FOREIGN KEY (ea_emp_id, ea_proj_id) REFERENCES employee_on_project
    );

    CREATE TABLE activity_order
    (
    ao_act_id NUMBER(6),
    ao_proj_id NUMBER(5),
    ao_prior_act_id NUMBER(6),
    CONSTRAINT ao_pk PRIMARY KEY (ao_act_id, ao_prior_act_id, ao_proj_id),
    CONSTRAINT ao_act_fk FOREIGN KEY (ao_act_id, ao_proj_id) REFERENCES activity (act_id, act_proj_id),
    CONSTRAINT ao_prior_act_fk FOREIGN KEY (ao_prior_act_id, ao_proj_id) REFERENCES activity (act_id, act_proj_id)
    );

    CREATE TABLE work_unit
    (
    wu_emp_id NUMBER(5),
    wu_act_id NUMBER(6),
    wu_proj_id NUMBER(5),
    wu_start_dt DATE CONSTRAINT wu_start_dt_nn NOT NULL,
    wu_end_dt DATE CONSTRAINT wu_end_dt_nn NOT NULL,
    CONSTRAINT wu_pk PRIMARY KEY (wu_emp_id, wu_proj_id, wu_act_id, wu_start_dt),
    CONSTRAINT wu_ea_fk FOREIGN KEY (wu_emp_id, wu_proj_id, wu_act_id)
    REFERENCES employee_on_activity( ea_emp_id, ea_proj_id, ea_act_id)
    );

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    OK, your database has primary keys and foreign keys defined - good.
    Now look at the indexes defined on the tables and see if the foreign keys all have corresponding indexes. You can use a tool like Toad, or select from user_indexes and user_ind_columns to find out what indexes exist.

  5. #5
    Join Date
    Jan 2004
    Posts
    28
    right i used toad to look at the indexes, it seems that there are only indexes used for the primary keys.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    OK, then adding indexes on each of the foreign keys is a good next step.

    Look out for redundant indexes, where one index is covered by the leading columns of another index. e.g. if you have...

    create index idx1 on t1 (a, b, c);
    create index idx2 on t1 (a, b);

    ...then idx2 is redundant

  7. #7
    Join Date
    Jan 2004
    Posts
    28
    right so does that include composite foreign keys too, im a bit confused, sorry

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes, regardless of why the indexes exist, if the "leading edge" of one index includes all the columns from another index in the same order, then you only need the longer index.

  9. #9
    Join Date
    Jan 2004
    Posts
    28
    right thanks mate, ive indexed all the foreign keys and the composite foreign keys. what else should i be looking for? Im also finding it difficult to justify my reasons for indexing in a performance point of view.

    Thanks
    Arnielover

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    In a nutshell, you are looking for queries that will be performed often, and seeing what indexes would help them to run faster. I suggest you read this:

    http://download-west.oracle.com/docs...6idx.htm#11977

    ... and also follow the links it gives to other relevant docs.

  11. #11
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151

    Re: Need Help with an Indexing Strategy

    Selecting an Index Strategy , Chapter 5
    http://download-east.oracle.com/docs...920/a96590.pdf
    Joel Pérez

  12. #12
    Join Date
    Jul 2003
    Posts
    2,296
    trace all application sessions, run tkprof on the trace files and search for FULL TABLE SCAN in the .prf

    that would at least lead you to the queries that are not using indexes.
    from there you can examine the code and determine why the query is not using an index and if it only needs a hint, or if you need to create anew index for the query.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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