03-12-04, 15:57 #1Registered User
- Join Date
- May 2003
Unanswered: how can i improve on this join clause
I have a table like this
WORK_TABLE2_ID NOT NULL NUMBER
CIA_VERSION NOT NULL NUMBER
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)
03-12-04, 19:18 #2Drunkard
- Join Date
- Nov 2002
- Desk, slightly south of keyboard
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....
(select * from tableb where column1 = 'FRED') tableb
where tablea.column1 = 'FRED' and
table2 ... = table1......
I think you should consider re-writing the code as....
where table1.tablename = 'WSF_BELLPERIODMASTER' and
table2.table_name = table1.tablename
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.
BillPlease 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.