Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2004

    Unanswered: crucial indexing problem with two tables in oracle database

    I have two tables that stores company information in a database. Since retrieving

    data from the tables is too slow, the structure need to be improved. The current

    structure of the tables is as follows:

    First table contains
    companyNo : unique field that is created by sequence
    companyTaxNumber:have a unique index
    companyName : have an index, but not unique

    trigger on table1:
    if insertion occurs: timestamp:0
    if update occurs : increment timestamp by 1

    Second Table Contains
    Up_companyNo2 : unique field that is created by sequence
    Up_companyTaxNumber2:have a unique index
    Up_companyName2 : have an index, but not unique

    trigger on table2:
    if insertion occurs: timestamp:0
    if update occurs : increment timestamp by 1
    update table1 where table1.companyTaxNumber=table2.Up_companyTaxNumber 2

    and timestamp=1

    the problem is the application uses table1 details, and give reference to

    "companyTaxNumber" in forms. When the company information is updated (rows are not

    updated, inserted with a new companyNo via sequence) and inserted into table1 from

    table2, the new forms will use the updated information. However, old forms cannot

    use updated ones, since reports cannot be changed.
    in conclusion,
    table2 contains last updated company information, the initial information before

    updates are not stored in table2
    table 1 contains all the updates as new entries since forms that use these entries

    cannot change the references. So, table1 gets bigger and datas retrieved

    Can you give me clear advices about the improvement techniques about indexes and

    triggers? How can I handle this situation?

  2. #2
    Join Date
    Jun 2004
    Liverpool, NY USA
    If the problem is that the two applications need to work on two different tables with the same data, but different table names and column names, why not have only one table and setup an updataeable view for the second reference. For example

    create view table2 as
    select companyNo up_companyno2,
    companyTaxNumber Up_companyTaxNumber2,
    companyName Up_companyName2,
    companyAddress Up_companyAddress2,
    from table1;

    This would allow you to select,insert,delete from table1 while calling it table2.
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Dec 2004

    clear explanation

    As I mentioned in the problem, I have two tables called table1 and table2. In
    the application, there are numerous references in the fields belong to table1,
    while creating a report. After a report have been created, no changes can be
    done on the reports. However, details on table1 can be changed and updated
    occasionally. On each change, a new row inserted into table1. Old rows never
    changes. By the way, while viewing old reports, references are not lost. This
    results in decrease of the performance. Since table1 gets larger.
    There is a problem with performance not the query. The real problem is how to
    design these two tables in oracle database and find an appropriate solution?
    Thanks for your convenience..

    I'll post a piece of code in an hour..


Posting Permissions

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