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

    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
    table1
    companyNo : unique field that is created by sequence
    companyTaxNumber:have a unique index
    companyName : have an index, but not unique
    companyAddress
    companyCountry
    timestamp

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

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

    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
    slowly.


    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
    Location
    Liverpool, NY USA
    Posts
    2,509
    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,
    timestamp
    from table1;

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

  3. #3
    Join Date
    Dec 2004
    Posts
    2

    clear explanation

    Hi,
    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..

    sozmen

Posting Permissions

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