Performance for join 9 custom table with native SQL ?

[Former Member]

Hi Expert,

I need your opinion regarding performance to join 9 tables with native sql. Recently i have to tunning some customize extraction cost report. This report extract about 10 million cost of material everyday.

The current program actually, try to populate the condition data and insert into customize table and join all the table to get data using native sql.

SELECT /*+ ordered use_hash(mst,pg,rg,ps,rs,dpg,drg,dps,drs) */
            mst.werks, ....................................

FROM
            sapsr3.zab_info mst,
            sapsr3.zab_pc pg,
            sapsr3.zab_rc rg,
            sapsr3.zab_pc ps,
            sapsr3.zab_rc rs,
            sapsr3.zab_g_pc dpg,
            sapsr3.zab_g_rc drg,
            sapsr3.zab_s_pc dps,
            sapsr3.zab_s_rc drs
        WHERE mst.zseq_no = :p_rep_run_id
        AND mst.werks = :p_werks
        AND mst.mandt = rg.mandt(+)
        AND mst.ekorg = rg.ekorg(+)
        AND mst.lifnr = rg.lifnr(+)
        AND mst.matnr = rg.matnr(+)
        ................................................
        ...............................................   unitl all table (9 tables)
        AND ps.mandt = dps.mandt(+)
        AND ps.knumh = dps.knumh(+)
        AND ps.zseq_no = dps.zseq_no(+)
        AND COALESCE (dps.kbetr, drs.kbetr, dpg.kbetr, drg.kbetr) <> 0

It seems the query ask for database to using hashed table. would that be it will burden the database ? and impacted to others sap process ?

Please advise

Thank You and Best Regards

[Former Member]

you can only argue coming from measurements and that is not the case.

Coming from the code, I see only that you do not understand it at all, so better leave it as it is. It is not a hash table, but a hash join on these table.