Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Posts
    34

    Unanswered: how can i improve on this join clause

    I have a table like this

    desc work_table2

    WORK_TABLE2_ID NOT NULL NUMBER
    APPLICATION_ID NUMBER
    APPLICATION_VERSION NUMBER
    JOB_ID NUMBER
    CUSTOMER_ID NUMBER
    ATTRIBUTE_ID NUMBER
    TEMPLATE_ID NUMBER
    DISTRICT_ID NUMBER
    SCHOOL_OID VARCHAR2(32)
    PERSON_OID VARCHAR2(32)
    CREATE_DATETIME DATE
    SCHEMA_NAME VARCHAR2(32)
    TABLE_NAME VARCHAR2(32)
    COLUMN_NAME VARCHAR2(32)
    ROW_ID NUMBER
    VALUE VARCHAR2(100)
    ACTION VARCHAR2(15)
    CIA_VERSION NOT NULL NUMBER
    SUB_ROW_ID NUMBER(9)


    This is table from which I will be inserting data into other tables. Some sort of standby data table.

    Data in this table comes in column format which i
    convert into a row. i.e for 1 row in the target table comprises of 10 columns then i get 10 different rows for that in work_table2

    The important columns here are table_name, column_name, value which gives me the table in which i have to insert the values, the columns of the table & the required values.

    I create views out of this table so i can directly insert into the destination tables from the views. The tables to insert into reside in another schema.

    Apart from these columns I also require the job_id, customer_id, action column which are part of the tables i am going to insert into but will not come from the column_name column of the work_table2 but do come as separate columns in work_table2

    For every row to be inserted into the table i take row_id as the separator and use it in my join clause

    In some cases i get the same row_id for two different rows so i use sub_row_id to get individual rows.
    In some cases it is quite possible that i may not get data for few of the columns of the target table. In that case I have I have to show null values in the empty columns
    Here is the query i use to create the view. Actually i have created a view generator
    PLSQL code which automatically creates views for all the tables.
    Though this query gives me the desired output I am very scared looking at the join clause
    Could you suggest a better way to get the output

    (please see the attachment)

    Bye
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    It's one of the problems of using automated sql generators. Read my Introduction to Sql Performance Tuning at http://www.billmagee.co.uk/oracle/sqltune/index.html for an example of how a particular automater failed.

    I took a quick look at your statement, the first thing I noticed is this, your code generally seems to take the form....
    PHP Code:
    select 
    from tablea
    (
    select from tableb where column1 'FRED'tableb
    where tablea
    .column1 'FRED' and
    table2 ... = table1...... 
    This is seen in your where clause as ... "table_name='WSF_BELLPERIODMASTER'".

    I think you should consider re-writing the code as....

    PHP Code:
    select 
    from  table1,
            
    table2
    where table1
    .tablename 'WSF_BELLPERIODMASTER' and
    table2.table_name table1.tablename 
    etc etc....

    This is a more traditional (certainly more common) form of what you have achieved by the inline views, except that Oracle is far more likely to understand it and be able to optimise it accordingly.

    If Oracle still can not optimise it as well as you'd like it will invariably be due to it not understanding the 'skew' of data within your tables. If this is the case, you only need to help Oracle along (with Histograms or somesuch during the analyse) for the optimiser to work its wonders.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

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