Oracle Hack With

Oracle Hack With As requirement: there is an old permission table With 7 levels (1-7 ). Except Level 1, each user ID is associated with a superior user ID. Www.2cto.com creates a new table and extracts the IDs of each person corresponding to the 7-level table. Create table new_Competence (userid varchar (10), parentid vachar (10), level number (2), avail_start varchar (8), avail_end varchar (8), level1 number (2 ), level2 number (2 ),... Level7 number (2),); Implementation Scheme: start with the general consideration, a small cursor pulls all, then write a process, and call it cyclically. In one test, it took more than half an hour to complete 40 thousand of data. After careful analysis, the number of queries is too large. About 4000 records are recorded in the cursor, and each data record needs to be queried 3-4 times (average ). Www.2cto.com first considers optimization, extracts global conditions, creates a temporary table (4000), changes it, And then tests it for 2 minutes. Much better, but not ideal. There are many TRIM () functions in the condition and index optimization cannot be used. So I tried to use WITH to write an SQL statement (omitted some less important query conditions ): WITH Temp AS (SELECT * FROM Old_Competence WHERE avail_start <= & APL_DATE and avail_end> = & APL_DATE), LEV1 AS (SELECT * FROM Temp WHERE level = 1 ), LEV2 AS (SELECT * FROM Temp WHERE level = 2), LEV3 AS (SELECT * FROM Temp WHERE level = 3), LEV4 AS (SELECT * FROM Temp WHERE level = 4 ), LEV5 AS (SELECT * FROM Temp WHERE level = 5), LEV6 AS (SELECT * FROM Temp WH ERE level = 6), LEV7 AS (SELECT * FROM Temp WHERE level = 7) (SELECT LEV1. *, null uc1, null uc2, null uc3, null uc4, null uc5, null uc6, null uc7 FROM LEV1 union all select LEV2. *, LEV1.unitcode uc1, null uc2, null uc3, null uc4, null uc5, null uc6, null uc7 FROM LEV1, LEV2 WHERE LEV1.book _ id = LEV2.parent _ id union all select LEV3. *, LEV1.unitcode uc1, LEV2.unitcode uc2, null uc3, null uc4, null u C5, null uc6, null uc7 FROM LEV1, LEV2, LEV3 WHERE LEV1.book _ id = LEV2.parent _ id AND LEV2.book _ id = LEV3.parent _ id... (Omitted FROM the three middle tables) union all select LEV7. *, LEV1.unitcode uc1, LEV2.userid uc2, LEV3.userid uc3, LEV4.userid uc4, LEV5.userid uc5, LEV6.userid uc6, LEV7.userid uc7 FROM LEV1, LEV2, LEV3, LEV4, LEV5, LEV6, LEV7 WHERE LEV1.book _ id = LEV2.parent _ id AND LEV2.book _ id = LEV3.parent _ id AND LEV3.book _ id = LEV4.parent _ id AND LEV4.book _ id = LEV5.parent _ id AND LEV5.book _ id = LEV6.parent _ id _ id AND LEV6.book _ id = LEV7.pare Nt_id); convert it into a cursor, and a loop is ready. Execute it for 3 seconds! Get started. Conclusion: The powerful feature of WITH allows you to write multiple intermediate table statements WITH a clearer structure and easier reading. layer-by-layer Filtering is somewhat similar to the pipeline concept. This is especially suitable for self-join queries of hierarchical relationships (such as in this example. Another advantage is that the intermediate table only exists in the memory, which is completely harmless (better than the Global Temporary) and looks more like a pipeline.